PostgreSQL
Create PostgreSQL Table Partitioning (Part 1)
javamix
2011. 12. 8. 11:15
Five easy steps how to make a partitioned table in Postgresql
- Create master table
- Create child tables without overlapping table constraints
- Create indexes
- Create trigger function to inserting data to child tables
- Enable constraint exclusion
Simple example of PostgreSQL table partitioning
The initial situation is such that we should make the database table, which is able to archive your data to advertisers by daily basis, over many years in advertising impressions. We also know that the information is needed later, the formation of various reports. It is known also that the information is sought on a daily, a weekly, a monthly, two-month periods, a half-year cycles, and so on.
In such a situation the best option is to partition a table. This example uses a two-month sections, because the data must be able to save a reasonable long time (in this example, examines the four-year period).
1. Create very simple master table. This table can contain simple data in a ad impressions by advertiser by daily basis. (Very simple table, because this is a partitioning example):
CREATE TABLE impressions_by_day (
advertiser_id INTEGER NOT NULL,
DAY DATE NOT NULL DEFAULT CURRENT_DATE,
impressions INTEGER NOT NULL,
PRIMARY KEY (advertiser_id, DAY)
);
2. Create child tables, which inherits the master table and adds checks for dates, because we want ensure that we have only right data on each partition. Partitions starts from ’2009-01-01′ and ends to ’2012-12-31′. And each partitions contains two months data:
CREATE TABLE impressions_by_day_y2009m1ms2 (
PRIMARY KEY (advertiser_id, DAY),
CHECK ( DAY >= DATE '2009-01-01' AND DAY < DATE '2009-03-01' )
) INHERITS (impressions_by_day);
CREATE TABLE impressions_by_day_y2009m3ms2 (
PRIMARY KEY (advertiser_id, DAY),
CHECK ( DAY >= DATE '2009-03-01' AND DAY < DATE '2009-05-01' )
) INHERITS (impressions_by_day);
CREATE TABLE impressions_by_day_y2009m5ms2 (
PRIMARY KEY (advertiser_id, DAY),
CHECK ( DAY >= DATE '2009-05-01' AND DAY < DATE '2009-07-01' )
) INHERITS (impressions_by_day);
CREATE TABLE impressions_by_day_y2009m7ms2 (
PRIMARY KEY (advertiser_id, DAY),
CHECK ( DAY >= DATE '2009-07-01' AND DAY < DATE '2009-09-01' )
) INHERITS (impressions_by_day);
CREATE TABLE impressions_by_day_y2009m9ms2 (
PRIMARY KEY (advertiser_id, DAY),
CHECK ( DAY >= DATE '2009-09-01' AND DAY < DATE '2009-11-01' )
) INHERITS (impressions_by_day);
CREATE TABLE impressions_by_day_y2009m11ms2 (
PRIMARY KEY (advertiser_id, DAY),
CHECK ( DAY >= DATE '2009-11-01' AND DAY < DATE '2010-01-01' )
) INHERITS (impressions_by_day);
CREATE TABLE impressions_by_day_y2010m1ms2 (
PRIMARY KEY (advertiser_id, DAY),
CHECK ( DAY >= DATE '2010-01-01' AND DAY < DATE '2010-03-01' )
) INHERITS (impressions_by_day);
CREATE TABLE impressions_by_day_y2010m3ms2 (
PRIMARY KEY (advertiser_id, DAY),
CHECK ( DAY >= DATE '2010-03-01' AND DAY < DATE '2010-05-01' )
) INHERITS (impressions_by_day);
CREATE TABLE impressions_by_day_y2010m5ms2 (
PRIMARY KEY (advertiser_id, DAY),
CHECK ( DAY >= DATE '2010-05-01' AND DAY < DATE '2010-07-01' )
) INHERITS (impressions_by_day);
CREATE TABLE impressions_by_day_y2010m7ms2 (
PRIMARY KEY (advertiser_id, DAY),
CHECK ( DAY >= DATE '2010-07-01' AND DAY < DATE '2010-09-01' )
) INHERITS (impressions_by_day);
CREATE TABLE impressions_by_day_y2010m9ms2 (
PRIMARY KEY (advertiser_id, DAY),
CHECK ( DAY >= DATE '2010-09-01' AND DAY < DATE '2010-11-01' )
) INHERITS (impressions_by_day);
CREATE TABLE impressions_by_day_y2010m11ms2 (
PRIMARY KEY (advertiser_id, DAY),
CHECK ( DAY >= DATE '2010-11-01' AND DAY < DATE '2011-01-01' )
) INHERITS (impressions_by_day);
CREATE TABLE impressions_by_day_y2011m1ms2 (
PRIMARY KEY (advertiser_id, DAY),
CHECK ( DAY >= DATE '2011-01-01' AND DAY < DATE '2011-03-01' )
) INHERITS (impressions_by_day);
CREATE TABLE impressions_by_day_y2011m3ms2 (
PRIMARY KEY (advertiser_id, DAY),
CHECK ( DAY >= DATE '2011-03-01' AND DAY < DATE '2011-05-01' )
) INHERITS (impressions_by_day);
CREATE TABLE impressions_by_day_y2011m5ms2 (
PRIMARY KEY (advertiser_id, DAY),
CHECK ( DAY >= DATE '2011-05-01' AND DAY < DATE '2011-07-01' )
) INHERITS (impressions_by_day);
CREATE TABLE impressions_by_day_y2011m7ms2 (
PRIMARY KEY (advertiser_id, DAY),
CHECK ( DAY >= DATE '2011-07-01' AND DAY < DATE '2011-09-01' )
) INHERITS (impressions_by_day);
CREATE TABLE impressions_by_day_y2011m9ms2 (
PRIMARY KEY (advertiser_id, DAY),
CHECK ( DAY >= DATE '2011-09-01' AND DAY < DATE '2011-11-01' )
) INHERITS (impressions_by_day);
CREATE TABLE impressions_by_day_y2011m11ms2 (
PRIMARY KEY (advertiser_id, DAY),
CHECK ( DAY >= DATE '2011-11-01' AND DAY < DATE '2012-01-01' )
) INHERITS (impressions_by_day);
CREATE TABLE impressions_by_day_y2012m1ms2 (
PRIMARY KEY (advertiser_id, DAY),
CHECK ( DAY >= DATE '2012-01-01' AND DAY < DATE '2012-03-01' )
) INHERITS (impressions_by_day);
CREATE TABLE impressions_by_day_y2012m3ms2 (
PRIMARY KEY (advertiser_id, DAY),
CHECK ( DAY >= DATE '2012-03-01' AND DAY < DATE '2012-05-01' )
) INHERITS (impressions_by_day);
CREATE TABLE impressions_by_day_y2012m5ms2 (
PRIMARY KEY (advertiser_id, DAY),
CHECK ( DAY >= DATE '2012-05-01' AND DAY < DATE '2012-07-01' )
) INHERITS (impressions_by_day);
CREATE TABLE impressions_by_day_y2012m7ms2 (
PRIMARY KEY (advertiser_id, DAY),
CHECK ( DAY >= DATE '2012-07-01' AND DAY < DATE '2012-09-01' )
) INHERITS (impressions_by_day);
CREATE TABLE impressions_by_day_y2012m9ms2 (
PRIMARY KEY (advertiser_id, DAY),
CHECK ( DAY >= DATE '2012-09-01' AND DAY < DATE '2012-11-01' )
) INHERITS (impressions_by_day);
CREATE TABLE impressions_by_day_y2012m11ms2 (
PRIMARY KEY (advertiser_id, DAY),
CHECK ( DAY >= DATE '2012-11-01' AND DAY < DATE '2013-01-01' )
) INHERITS (impressions_by_day);
3. Create indexes to child tables to speed up day field usage, because almost all queries (INSERTs, SELECTs and UPDATEs) on the date field.
CREATE INDEX impressions_by_day_y2009m1ms2_day ON impressions_by_day_y2009m1ms2 (DAY);
CREATE INDEX impressions_by_day_y2009m3ms2_day ON impressions_by_day_y2009m3ms2 (DAY);
CREATE INDEX impressions_by_day_y2009m5ms2_day ON impressions_by_day_y2009m5ms2 (DAY);
CREATE INDEX impressions_by_day_y2009m7ms2_day ON impressions_by_day_y2009m7ms2 (DAY);
CREATE INDEX impressions_by_day_y2009m9ms2_day ON impressions_by_day_y2009m9ms2 (DAY);
CREATE INDEX impressions_by_day_y2009m11ms2_day ON impressions_by_day_y2009m11ms2 (DAY);
CREATE INDEX impressions_by_day_y2010m1ms2_day ON impressions_by_day_y2010m1ms2 (DAY);
CREATE INDEX impressions_by_day_y2010m3ms2_day ON impressions_by_day_y2010m3ms2 (DAY);
CREATE INDEX impressions_by_day_y2010m5ms2_day ON impressions_by_day_y2010m5ms2 (DAY);
CREATE INDEX impressions_by_day_y2010m7ms2_day ON impressions_by_day_y2010m7ms2 (DAY);
CREATE INDEX impressions_by_day_y2010m9ms2_day ON impressions_by_day_y2010m9ms2 (DAY);
CREATE INDEX impressions_by_day_y2010m11ms2_day ON impressions_by_day_y2010m11ms2 (DAY);
CREATE INDEX impressions_by_day_y2011m1ms2_day ON impressions_by_day_y2011m1ms2 (DAY);
CREATE INDEX impressions_by_day_y2011m3ms2_day ON impressions_by_day_y2011m3ms2 (DAY);
CREATE INDEX impressions_by_day_y2011m5ms2_day ON impressions_by_day_y2011m5ms2 (DAY);
CREATE INDEX impressions_by_day_y2011m7ms2_day ON impressions_by_day_y2011m7ms2 (DAY);
CREATE INDEX impressions_by_day_y2011m9ms2_day ON impressions_by_day_y2011m9ms2 (DAY);
CREATE INDEX impressions_by_day_y2011m11ms2_day ON impressions_by_day_y2011m11ms2 (DAY);
CREATE INDEX impressions_by_day_y2012m1ms2_day ON impressions_by_day_y2012m1ms2 (DAY);
CREATE INDEX impressions_by_day_y2012m3ms2_day ON impressions_by_day_y2012m3ms2 (DAY);
CREATE INDEX impressions_by_day_y2012m5ms2_day ON impressions_by_day_y2012m5ms2 (DAY);
CREATE INDEX impressions_by_day_y2012m7ms2_day ON impressions_by_day_y2012m7ms2 (DAY);
CREATE INDEX impressions_by_day_y2012m9ms2_day ON impressions_by_day_y2012m9ms2 (DAY);
CREATE INDEX impressions_by_day_y2012m11ms2_day ON impressions_by_day_y2012m11ms2 (DAY);
4. Then we need insert trigger and of course trigger function to master table. Conditions must beexactly the same as what the child tables checks.
Trigger function:
CREATE OR REPLACE FUNCTION impressions_by_day_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
IF ( NEW.DAY >= DATE '2009-01-01' AND NEW.DAY < DATE '2009-03-01' ) THEN
INSERT INTO impressions_by_day_y2009m1ms2 VALUES (NEW.*);
ELSIF ( NEW.DAY >= DATE '2009-03-01' AND NEW.DAY < DATE '2009-05-01' ) THEN
INSERT INTO impressions_by_day_y2009m3ms2 VALUES (NEW.*);
ELSIF ( NEW.DAY >= DATE '2009-05-01' AND NEW.DAY < DATE '2009-07-01' ) THEN
INSERT INTO impressions_by_day_y2009m5ms2 VALUES (NEW.*);
ELSIF ( NEW.DAY >= DATE '2009-07-01' AND NEW.DAY < DATE '2009-09-01' ) THEN
INSERT INTO impressions_by_day_y2009m7ms2 VALUES (NEW.*);
ELSIF ( NEW.DAY >= DATE '2009-09-01' AND NEW.DAY < DATE '2009-11-01' ) THEN
INSERT INTO impressions_by_day_y2009m9ms2 VALUES (NEW.*);
ELSIF ( NEW.DAY >= DATE '2009-11-01' AND NEW.DAY < DATE '2010-01-01' ) THEN
INSERT INTO impressions_by_day_y2009m11ms2 VALUES (NEW.*);
ELSIF ( NEW.DAY >= DATE '2010-01-01' AND NEW.DAY < DATE '2010-03-01' ) THEN
INSERT INTO impressions_by_day_y2010m1ms2 VALUES (NEW.*);
ELSIF ( NEW.DAY >= DATE '2010-03-01' AND NEW.DAY < DATE '2010-05-01' ) THEN
INSERT INTO impressions_by_day_y2010m3ms2 VALUES (NEW.*);
ELSIF ( NEW.DAY >= DATE '2010-05-01' AND NEW.DAY < DATE '2010-07-01' ) THEN
INSERT INTO impressions_by_day_y2010m5ms2 VALUES (NEW.*);
ELSIF ( NEW.DAY >= DATE '2010-07-01' AND NEW.DAY < DATE '2010-09-01' ) THEN
INSERT INTO impressions_by_day_y2010m7ms2 VALUES (NEW.*);
ELSIF ( NEW.DAY >= DATE '2010-09-01' AND NEW.DAY < DATE '2010-11-01' ) THEN
INSERT INTO impressions_by_day_y2010m9ms2 VALUES (NEW.*);
ELSIF ( NEW.DAY >= DATE '2010-11-01' AND NEW.DAY < DATE '2011-01-01' ) THEN
INSERT INTO impressions_by_day_y2010m11ms2 VALUES (NEW.*);
ELSIF ( NEW.DAY >= DATE '2011-01-01' AND NEW.DAY < DATE '2011-03-01' ) THEN
INSERT INTO impressions_by_day_y2011m1ms2 VALUES (NEW.*);
ELSIF ( NEW.DAY >= DATE '2011-03-01' AND NEW.DAY < DATE '2011-05-01' ) THEN
INSERT INTO impressions_by_day_y2011m3ms2 VALUES (NEW.*);
ELSIF ( NEW.DAY >= DATE '2011-05-01' AND NEW.DAY < DATE '2011-07-01' ) THEN
INSERT INTO impressions_by_day_y2011m5ms2 VALUES (NEW.*);
ELSIF ( NEW.DAY >= DATE '2011-07-01' AND NEW.DAY < DATE '2011-09-01' ) THEN
INSERT INTO impressions_by_day_y2011m7ms2 VALUES (NEW.*);
ELSIF ( NEW.DAY >= DATE '2011-09-01' AND NEW.DAY < DATE '2011-11-01' ) THEN
INSERT INTO impressions_by_day_y2011m9ms2 VALUES (NEW.*);
ELSIF ( NEW.DAY >= DATE '2011-11-01' AND NEW.DAY < DATE '2012-01-01' ) THEN
INSERT INTO impressions_by_day_y2011m11ms2 VALUES (NEW.*);
ELSIF ( NEW.DAY >= DATE '2012-01-01' AND NEW.DAY < DATE '2012-03-01' ) THEN
INSERT INTO impressions_by_day_y2012m1ms2 VALUES (NEW.*);
ELSIF ( NEW.DAY >= DATE '2012-03-01' AND NEW.DAY < DATE '2012-05-01' ) THEN
INSERT INTO impressions_by_day_y2012m3ms2 VALUES (NEW.*);
ELSIF ( NEW.DAY >= DATE '2012-05-01' AND NEW.DAY < DATE '2012-07-01' ) THEN
INSERT INTO impressions_by_day_y2012m5ms2 VALUES (NEW.*);
ELSIF ( NEW.DAY >= DATE '2012-07-01' AND NEW.DAY < DATE '2012-09-01' ) THEN
INSERT INTO impressions_by_day_y2012m7ms2 VALUES (NEW.*);
ELSIF ( NEW.DAY >= DATE '2012-09-01' AND NEW.DAY < DATE '2012-11-01' ) THEN
INSERT INTO impressions_by_day_y2012m9ms2 VALUES (NEW.*);
ELSIF ( NEW.DAY >= DATE '2012-11-01' AND NEW.DAY < DATE '2013-01-01' ) THEN
INSERT INTO impressions_by_day_y2012m11ms2 VALUES (NEW.*);
ELSE
RAISE EXCEPTION 'Date out of range. Something wrong with the impressions_by_day_insert_trigger() function!';
END IF;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;
Trigger:
CREATE TRIGGER insert_impressions_by_day_trigger
BEFORE INSERT ON impressions_by_day
FOR EACH ROW EXECUTE PROCEDURE impressions_by_day_insert_trigger();
5.Make sure that Constraint Exclusion is enabled. Constraint exclusion is driven by CHECK constraints. If constraint exclusion is disabled then query is not using check constraints and every query scans thru whole all child tables. So constraint exclusion is very important when using partitioned tables.
Set constraint exclusion on with following row on postgresql.conf:
constraint_exclusion = on
Set constraint exclusion on following command on psql or psqlrc
SET constraint_exclusion = ON;
Finally, the master table is normally available and all UPDATEs, INSERTs, SELECTs and DELETEs goes to the right child tables by date.
참조 : http://www.if-not-true-then-false.com/2009/howto-create-postgresql-table-partitioning-part-1/