If you have a table with a very huge amount of data, it may be helpful to scatter the data to different physical tables which share a common data structure. In such use cases, where DML statements concern only one of those physical tables, you can get great performance benefits from partitioning. Typically this is the case, if there is any timeline or a geographical distribution of the values of a column.
Declarative-partitioning-syntax: since version 10Edit
Postgres 10 introduced a declarative partition-defining-syntax in addition to the previous table-inheritance-syntax. With this syntax the necessity to define an additional trigger disappears, but in comparision to the previous solution the functionality stays unchanged.
First, you define a master table containing a partitioning methode which is
PARTITION BY RANGE (column_name) in this example:
CREATE TABLE log ( id int not null, logdate date not null, message varchar(500) ) PARTITION BY RANGE (logdate);
Next, you create partitions with the same structure as the master and ensure, that only rows within the expected data range can be stored there. Those partitions are conventional, physical tables.
CREATE TABLE log_2015_01 PARTITION OF log FOR VALUES FROM ('2015-01-01') TO ('2015-02-01'); CREATE TABLE log_2015_02 PARTITION OF log FOR VALUES FROM ('2015-02-01') TO ('2015-03-01'); ... CREATE TABLE log_2015_12 PARTITION OF log FOR VALUES FROM ('2015-12-01') TO ('2016-01-01'); CREATE TABLE log_2016_01 PARTITION OF log FOR VALUES FROM ('2016-01-01') TO ('2016-02-01'); ...
First, you define a master table, which is a conventional table.
CREATE TABLE log ( id int not null, logdate date not null, message varchar(500) );
Next, you create partitions with the same structure as the master table by using the table-inheritance mechanism
INHERITS (table_name). Additionally you must ensure that only rows within the expected data range can be stored in the derived tables.
CREATE TABLE log_2015_01 (CHECK (logdate >= DATE '2015-01-01' AND logdate < DATE '2015-02-01')) INHERITS (log); CREATE TABLE log_2015_02 (CHECK (logdate >= DATE '2015-02-01' AND logdate < DATE '2015-03-01')) INHERITS (log); ... CREATE TABLE log_2015_12 (CHECK (logdate >= DATE '2015-12-01' AND logdate < DATE '2016-01-01')) INHERITS (log); CREATE TABLE log_2016_01 (CHECK (logdate >= DATE '2016-01-01' AND logdate < DATE '2016-02-01')) INHERITS (log); ...
You need a function, which transfers rows into the appropriate partition.
CREATE OR REPLACE FUNCTION log_ins_function() RETURNS TRIGGER AS $$ BEGIN IF (NEW.logdate >= DATE '2015-01-01' AND NEW.logdate < DATE '2015-02-01' ) THEN INSERT INTO log_2015_01 VALUES (NEW.*); ELSIF (NEW.logdate >= DATE '2015-02-01' AND NEW.logdate < DATE '2015-03-01' ) THEN INSERT INTO log_2015_02 VALUES (NEW.*); ELSIF ... ... END IF; RETURN NULL; END; $$ LANGUAGE plpgsql;
The function is called by a trigger.
CREATE TRIGGER log_ins_trigger BEFORE INSERT ON log FOR EACH ROW EXECUTE PROCEDURE log_ins_function();
It's a good idea to create an index.
CREATE INDEX log_2015_01_idx ON log_2015_01 (logdate); CREATE INDEX log_2015_02_idx ON log_2015_02 (logdate); ...
Many DML statements like
SELECT * FROM log WHERE logdate = '2015-01-15'; act only on one partition and can ignore all the others. This is very helpfull especially in such cases where a full table scan becomes necessary. The query optimizer has the chance to generate execution plans which avoid scanning unnecessary partitions.
In the shown example new rows will mainly go to the newest partition. After some years you can drop old partitions as a whole. This shall be done with the command
DROP TABLE - not with a
DELETE command. The
DROP TABLE command is much faster than the
DELETE command as it removes the complete partition in one single step instead of touching every single row.