The guide is adapted from postgres docs here. This approach uses table inheritance and insert trigger function
1. Create Base table
First create the table which will be partitioned. This table will hold no data, but only define the schemaCREATE TABLE measurement ( city_id int not null, logdate date not null, peaktemp int, unitsales int );
2. Create partition tables
Each partition table inherits from parent, we also specify checks, this are important to ensure data not matching partition does not get inserted in a partition table. Also it used by the query planner in combination with constraint_exclusion.-- ================== Partition 1 ============================ CREATE TABLE measurement_y2006m02 ( CHECK ( logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01' ) ) INHERITS (measurement); -- create index for the partition table CREATE INDEX measurement_y2006m02_logdate ON measurement_y2006m02 (logdate); -- ================== Partition 2 ============================ CREATE TABLE measurement_y2006m03 ( CHECK ( logdate >= DATE '2006-03-01' AND logdate < DATE '2006-04-01' ) ) INHERITS (measurement); CREATE INDEX measurement_y2006m03_logdate ON measurement_y2006m03 (logdate); -- ================== Partition 3 ============================ CREATE TABLE measurement_y2006m04 ( CHECK ( logdate >= DATE '2006-04-01' AND logdate < DATE '2006-05-01' ) ) INHERITS (measurement); CREATE INDEX measurement_y2006m04_logdate ON measurement_y2006m04 (logdate);
3. Insert routing
Define the insert routing function on parent table. This redirects all inserts, done on the main table to appropriate partition tableCREATE OR REPLACE FUNCTION measurement_insert_trigger() RETURNS TRIGGER AS $$ BEGIN -- Insert into partition based on date range, NEW = new record to be inserted IF ( NEW.logdate >= DATE '2006-02-01' AND NEW.logdate < DATE '2006-03-01' ) THEN INSERT INTO measurement_y2006m02 VALUES (NEW.*); ELSIF ( NEW.logdate >= DATE '2006-03-01' AND NEW.logdate < DATE '2006-04-01' ) THEN INSERT INTO measurement_y2006m03 VALUES (NEW.*); ELSIF ( NEW.logdate >= DATE '2008-04-01' AND NEW.logdate < DATE '2008-05-01' ) THEN INSERT INTO measurement_y2008m01 VALUES (NEW.*); ELSE RAISE EXCEPTION 'Date out of range. Fix the measurement_insert_trigger() function!'; END IF; RETURN NULL; END; $$ LANGUAGE plpgsql; -- attach trigger to measurement table before insert CREATE TRIGGER measurement_insert_trigger BEFORE INSERT ON measurement FOR EACH ROW EXECUTE PROCEDURE measurement_insert_trigger();
Usage
Insert a row
INSERT INTO measurement (city_id, logdate, peaktemp, unitsales) VALUES(1, '2006-03-10',0,0)
Check row is inserted in the correct partition
Select * from measurement_y2006m03;
Making select queries partition aware
This tells the query planner, to use the CHECK constraints defined on the table, to only look into appropriate partition.SET constraint_exclusion = on; SELECT count(*) FROM measurement WHERE logdate >= DATE '2006-03-01';
Use Explain to verify query planner path
SET constraint_exclusion = on; EXPLAIN SELECT count(*) FROM measurement WHERE logdate >= DATE '2006-03-01';
Removing a partition, completely including the data
DROP TABLE measurement_y2006m02;
Note: Make sure to to update the trigger function, to remove the if statement redirecting inserts to this table
Removing partition from from partition set, but still have access to the data
ALTER TABLE measurement_y2006m02 NO INHERIT measurement;
Note: Make sure to to update the trigger function, to remove the if statement redirecting inserts to this table