Postgres Parent<> Child (Table partitioning in PostgreSQL)

db.sql

#
#   Table partitioning with postgreSQL
#   the master_table should be empty, data will only be inside the childs table
#

# CREATE MASTER TABLE
CREATE TABLE master_table (
  id BIGINT,
  username VARCHAR (50) UNIQUE NOT NULL,
  description TEXT);

# CREATE CHILDS TABLES (3 childs)
CREATE TABLE child_a (CHECK (id < 10000)) INHERITS (master_table);
CREATE TABLE child_b (CHECK (id >= 10000 AND id < 100000)) INHERITS (master_table);
CREATE TABLE child_c (CHECK (id >= 100000)) INHERITS (master_table);

# LET'S CREATE THE TRIGGER
CREATE OR REPLACE FUNCTION master_table_insert_trigger()
  RETURNS TRIGGER AS $$
  BEGIN
    IF NEW.id < 10000 THEN
      INSERT INTO child_a VALUES (NEW.*);
    ELSIF NEW.id >= 10000 AND NEW.id < 100000 THEN
      INSERT INTO child_b VALUES (NEW.*);
    ELSE
      INSERT INTO child_c VALUES(NEW.*);
    END IF;
    RETURN NULL;
  END;
  $$
LANGUAGE plpgsql;
CREATE TRIGGER insert_master_table_trigger
BEFORE INSERT ON master_table
FOR EACH ROW EXECUTE PROCEDURE master_table_insert_trigger();



# INSERT FAKE DATA
INSERT INTO master_table VALUES (1, 'user1','some text.....');
INSERT INTO master_table VALUES (10, 'user2','some text.....');
INSERT INTO master_table VALUES (100, 'user3','some text.....');
INSERT INTO master_table VALUES (1000, 'user4','some text.....');
INSERT INTO master_table VALUES (10000, 'user5','some text.....');
INSERT INTO master_table VALUES (100000, 'user6','some text.....');
INSERT INTO master_table VALUES (1000000, 'user7','some text.....');
INSERT INTO master_table VALUES (10000000, 'user8','some text.....');
INSERT INTO master_table VALUES (100000000, 'user9','some text.....');
INSERT INTO master_table VALUES (1000000000, 'user10','some text.....');

# 
#   Query examples
#

# Should look only in child_a
#->  Seq Scan on master_table  (cost=0.00..0.00 rows=1 width=158) (actual time=0.004..0.004 rows=0 loops=1)
#     Filter: (id < 1000)
#->  Seq Scan on child_a  (cost=0.00..15.38 rows=143 width=158) (actual time=0.009..0.028 rows=6 loops=1)
#     Filter: (id < 1000)
#     Rows Removed by Filter: 2
EXPLAIN ANALYZE SELECT * FROM master_table WHERE id < 1000; 

# Should look in child_a and child_b
#->  Seq Scan on master_table  (cost=0.00..0.00 rows=1 width=158) (actual time=0.006..0.006 rows=0 loops=1)
#         Filter: (id < 100000)
#   ->  Seq Scan on child_a  (cost=0.00..15.38 rows=143 width=158) (actual time=0.005..0.028 rows=8 loops=1)
#         Filter: (id < 100000)
#   ->  Seq Scan on child_b  (cost=0.00..15.38 rows=143 width=158) (actual time=0.006..0.009 rows=1 loops=1)
#         Filter: (id < 100000)
EXPLAIN ANALYZE SELECT * FROM master_table WHERE id < 100000;