Difficulty: intermediate
Estimated Time: 20 minutes

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!

Final Notes

This scenario hopefully gave a good introduction to partitioning in PostgreSQL.

Enjoy learning about PostgreSQL? Sign up for our newsletter and get the latest tips from us each month.

Partitioning in PostgreSQL

Step 1 of 6

Range Partitioning

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.

psql

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 logtime column.

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 DEFAULT partition.

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.

Use the \d+ command in psql to see the structure of a partitioned tableset

\d+ measurement
\d+ measurement_20060201
\d+ measurement_default

Note that the constraint for the default partition is basically the opposite of all other existing constraints in the set.