Wikibooks: PostgreSQL/Partitioning

• Format: Book
• Language: English
• Subjects: DML
• Online Access: https://en.wikibooks.org/wiki/PostgreSQL/Partitioning

Nav 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 10 = 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 ) . = Table inheritance syntax = 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 AND logdate ...