Partitioning in PostgreSQL
As of PostgreSQL 10, partitioning is now a native feature. Version 10 laid the groundwork for the syntax but was still lacking in some major features. 11 improved upon the feature support greatly, and 12 will continue on with that improvement.
Before version 10, partitioning was possible in PostgreSQL, but it was a result of a combination of several other features: Inheritance, Constraints & Triggers. The method was laid out in previous versions' documentation and is still available now, for example in version 9.6 - https://www.postgresql.org/docs/9.6/ddl-partitioning.html
Depending on the features needed with your partitioning requirements, the old method may still need to be used. If you are going into a brand new partitioning setup, it is highly recommended to upgrade to version 11 to have much better feature support built in.
Let's get started!
Partitioning in PostgreSQL
PostgreSQL supports several types of native partitioning: Range, List, & Hash.
Range partitioning works by splitting the data on 1 or more columns based on blocks of data with bound limits, typically time or integer series.
Log into postgres using the psql client. A
training role and database has already been created to allow easy login.
First a parent table for the partition set must be created. Already existing tables cannot currently be converted directly into partitioned tables. This table will be partitioned by time on the
CREATE TABLE measurement ( city_id bigint GENERATED BY DEFAULT AS IDENTITY, logtime timestamptz not null, peaktemp int, unitsales int ) PARTITION BY RANGE (logtime);
How the partitioning is split is entirely controlled by the constraints set on its children, not from the parent. This means the range can be the same for each child or different, as long as there is no overlap. Currently all child tables must be created manually as there is no automated table creation. Naming is also completely arbitrary, but it helps to give the tables names that make sense for their content.
CREATE TABLE measurement_20060201 PARTITION OF measurement (PRIMARY KEY (city_id)) FOR VALUES FROM ('2006-02-01') TO ('2006-02-02'); CREATE TABLE measurement_20060202 PARTITION OF measurement (PRIMARY KEY (city_id)) FOR VALUES FROM ('2006-02-02') TO ('2006-02-03');
The range is inclusive of the starting value and exclusive of the ending value. If you'd like to account for data that doesn't match any existing children, PostgreSQL 11 introduced the feature of a
CREATE TABLE measurement_default PARTITION OF measurement DEFAULT;
Data going into the default partition should be monitored since large amounts being there can be detrimental to performance. The
DEFAULT feature is supported on all native partitioning types.
\d+ command in psql to see the structure of a partitioned tableset
Note that the constraint for the default partition is basically the opposite of all other existing constraints in the set.