Partition Table In PostgreSQL (Create Partition) – Part 1

Partition table in PostgreSQL is very easy to do, It involve inheritance concept and trigger of PostgreSQL. Here i provide a sample to demonstrate how to partition table in PostgreSQL.

Before proceed, please understand some basic concept like,er… better i provide a concept of partition “time” in a table.

“Mother” — (Child1, Child2, Child3,Child4, Child5)

MasterTable — (Child_01_2008, Child_02_2008, Child_03_2008, Child_04_2008,Child_05_2008)

We only insert, select, update and delete on MasterTable, all child tables are transparent to user.

For example when i insert a record into MasterTable which is at January 2008. Record will auto redirect (trigger) to child table (Child_01_2008). When user select a record from Master table, postgreSQL will automatically retrieve data from all child tables which inherited from MasterTable.

1)Create a simple table call “hashvalue_PT” , it only include 2 columns “hash” and “hashtime”

 
CREATE TABLE hashvalue_PT
(
  hash bytea NOT NULL,
  hashtime timestamp without time zone NOT NULL
);

2) Create 10 tables in different months and inherantence from main hashvalue_PT table.

 
--Create Partition with check rule for validation
CREATE TABLE hashvalue_PT_y2008m01 (
CHECK ( hashtime >= DATE '2008-01-01' AND hashtime < DATE '2008-01-31' )
 ) INHERITS (hashvalue_PT);
 CREATE TABLE hashvalue_PT_y2008m02 (
CHECK ( hashtime >= DATE '2008-02-01' AND hashtime < DATE '2008-02-29' )
 ) INHERITS (hashvalue_PT);
CREATE TABLE hashvalue_PT_y2008m03 (
CHECK ( hashtime >= DATE '2008-03-01' AND hashtime < DATE '2008-03-31' )
 ) INHERITS (hashvalue_PT);
CREATE TABLE hashvalue_PT_y2008m04 (
CHECK ( hashtime >= DATE '2008-04-01' AND hashtime < DATE '2008-04-30' )
 ) INHERITS (hashvalue_PT);
CREATE TABLE hashvalue_PT_y2008m05 ( 
CHECK ( hashtime >= DATE '2008-05-01' AND hashtime < DATE '2008-05-31' )
) INHERITS (hashvalue_PT);
CREATE TABLE hashvalue_PT_y2008m06 ( 
CHECK ( hashtime >= DATE '2008-06-01' AND hashtime < DATE '2008-06-30' )
) INHERITS (hashvalue_PT);
CREATE TABLE hashvalue_PT_y2008m07 ( 
CHECK ( hashtime >= DATE '2008-07-01' AND hashtime < DATE '2008-07-31' )
) INHERITS (hashvalue_PT);
CREATE TABLE hashvalue_PT_y2008m08 ( 
CHECK ( hashtime >= DATE '2008-08-01' AND hashtime < DATE '2008-08-31' )
) INHERITS (hashvalue_PT);
CREATE TABLE hashvalue_PT_y2008m09 ( 
CHECK ( hashtime >= DATE '2008-09-01' AND hashtime < DATE '2008-09-30' )
) INHERITS (hashvalue_PT);
CREATE TABLE hashvalue_PT_y2008m010 ( 
CHECK ( hashtime >= DATE '2008-10-01' AND hashtime < DATE '2008-10-31' )
) INHERITS (hashvalue_PT);

3) Create primary key for each child tables

 
ALTER TABLE hashvalue_PT_y2008m01 ADD CONSTRAINT hashvalue_PT_y2008m01_pkey PRIMARY KEY (hashtime, hash);
ALTER TABLE hashvalue_PT_y2008m02 ADD CONSTRAINT hashvalue_PT_y2008m02_pkey PRIMARY KEY (hashtime, hash);
ALTER TABLE hashvalue_PT_y2008m03 ADD CONSTRAINT hashvalue_PT_y2008m03_pkey PRIMARY KEY (hashtime, hash);
ALTER TABLE hashvalue_PT_y2008m04 ADD CONSTRAINT hashvalue_PT_y2008m04_pkey PRIMARY KEY (hashtime, hash);
ALTER TABLE hashvalue_PT_y2008m05 ADD CONSTRAINT hashvalue_PT_y2008m05_pkey PRIMARY KEY (hashtime, hash);
ALTER TABLE hashvalue_PT_y2008m06 ADD CONSTRAINT hashvalue_PT_y2008m06_pkey PRIMARY KEY (hashtime, hash);
ALTER TABLE hashvalue_PT_y2008m07 ADD CONSTRAINT hashvalue_PT_y2008m07_pkey PRIMARY KEY (hashtime, hash);
ALTER TABLE hashvalue_PT_y2008m08 ADD CONSTRAINT hashvalue_PT_y2008m08_pkey PRIMARY KEY (hashtime, hash);
ALTER TABLE hashvalue_PT_y2008m09 ADD CONSTRAINT hashvalue_PT_y2008m09_pkey PRIMARY KEY (hashtime, hash);
ALTER TABLE hashvalue_PT_y2008m010 ADD CONSTRAINT hashvalue_PT_y2008m010_pkey PRIMARY KEY (hashtime, hash);

4) Create an index for each child tables

 
CREATE INDEX idx_hashvalue_PT_y2008m01_hashtime ON hashvalue_PT_y2008m01 (hashtime);
CREATE INDEX idx_hashvalue_PT_y2008m02_hashtime ON hashvalue_PT_y2008m02 (hashtime);
CREATE INDEX idx_hashvalue_PT_y2008m03_hashtime ON hashvalue_PT_y2008m03 (hashtime);
CREATE INDEX idx_hashvalue_PT_y2008m04_hashtime ON hashvalue_PT_y2008m04 (hashtime);
CREATE INDEX idx_hashvalue_PT_y2008m05_hashtime ON hashvalue_PT_y2008m05 (hashtime);
CREATE INDEX idx_hashvalue_PT_y2008m06_hashtime ON hashvalue_PT_y2008m06 (hashtime);
CREATE INDEX idx_hashvalue_PT_y2008m07_hashtime ON hashvalue_PT_y2008m07 (hashtime);
CREATE INDEX idx_hashvalue_PT_y2008m08_hashtime ON hashvalue_PT_y2008m08 (hashtime);
CREATE INDEX idx_hashvalue_PT_y2008m09_hashtime ON hashvalue_PT_y2008m09 (hashtime);
CREATE INDEX idx_hashvalue_PT_y2008m010_hashtime ON hashvalue_PT_y2008m010 (hashtime);

5) Create a trigger on mother table to redirect records into child tables.

 
--create a trigger to redirect records to child table
CREATE OR REPLACE FUNCTION hashvalue_PT_func_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
    IF ( NEW.hashtime >= DATE '2008-01-01' AND NEW.hashtime < DATE '2008-01-31' ) THEN
        INSERT INTO hashvalue_PT_y2008m01 VALUES (NEW.*);
    ELSIF ( NEW.hashtime >= DATE '2008-02-01' AND NEW.hashtime < DATE '2008-02-29' ) THEN
        INSERT INTO hashvalue_PT_y2008m02 VALUES (NEW.*);
    ELSIF ( NEW.hashtime >= DATE '2008-03-01' AND NEW.hashtime < DATE '2008-03-31' ) THEN
        INSERT INTO hashvalue_PT_y2008m03 VALUES (NEW.*);
    ELSIF ( NEW.hashtime >= DATE '2008-04-01' AND NEW.hashtime < DATE '2008-04-30' ) THEN
        INSERT INTO hashvalue_PT_y2008m04 VALUES (NEW.*);
    ELSIF ( NEW.hashtime >= DATE '2008-05-01' AND NEW.hashtime < DATE '2008-05-31' ) THEN
        INSERT INTO hashvalue_PT_y2008m05 VALUES (NEW.*);
    ELSIF ( NEW.hashtime >= DATE '2008-06-01' AND NEW.hashtime < DATE '2008-06-30' ) THEN
	INSERT INTO hashvalue_PT_y2008m06 VALUES (NEW.*);
    ELSIF ( NEW.hashtime >= DATE '2008-07-01' AND NEW.hashtime < DATE '2008-07-31' ) THEN
	INSERT INTO hashvalue_PT_y2008m07 VALUES (NEW.*);
    ELSIF ( NEW.hashtime >= DATE '2008-08-01' AND NEW.hashtime < DATE '2008-08-31' ) THEN
	INSERT INTO hashvalue_PT_y2008m08 VALUES (NEW.*);
    ELSIF ( NEW.hashtime >= DATE '2008-09-01' AND NEW.hashtime < DATE '2008-09-30' ) THEN
	INSERT INTO hashvalue_PT_y2008m09 VALUES (NEW.*);
    ELSIF ( NEW.hashtime >= DATE '2008-10-01' AND NEW.hashtime < DATE '2008-10-31' ) THEN
	INSERT INTO hashvalue_PT_y2008m010 VALUES (NEW.*);
    ELSE
        RAISE EXCEPTION 'Date out of range.  Fix the measurement_insert_trigger() function!';
    END IF;
    RETURN NULL;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER trigger_hashvalue_PT_insert
    BEFORE INSERT ON hashvalue_PT
    FOR EACH ROW EXECUTE PROCEDURE hashvalue_PT_func_insert_trigger();

6) Done, Simple

Next Session i will create a function to insert million of data from partition table to test performance between partition and non partition table. Please visit Partition Table In PostgreSQL (Simulate Millions Data) – Part 2

About the Author

author image
mkyong
Founder of Mkyong.com, love Java and open source stuff. Follow him on Twitter, or befriend him on Facebook or Google Plus. If you like my tutorials, consider make a donation to these charities.

Comments

avatar
4 Comment threads
0 Thread replies
0 Followers
 
Most reacted comment
Hottest comment thread
4 Comment authors
Alejandro León MoraSumanthAustin Kennypostgresql trigger Recent comment authors
newest oldest most voted
Alejandro León Mora
Guest
Alejandro León Mora
Sumanth
Guest
Sumanth

Thanks for coming up with detailed doc! Followed the same instructions & commands works without a hitch

Austin Kenny
Guest
Austin Kenny

Thank you for this. I tried to get the inheritance to work before, and failed. Using this as a template, and using triggers made all the difference. I am curious to know, should all constraints be placed in the child tables, for example foreign key constraints? Thanks again for you tutorial and help.

postgresql trigger
Guest
postgresql trigger

Table inheritance + trigger in postgres rocks!