PostgreSQL 10: Partitions of… partitions!

PostgreSQL version 10 brings a much anticipated feature: (Native) Table Partitioning.

(Emphasis on the NATIVE, PostgreSQL supported partitioning on previous versions by other means.)

There are a few gotchas you have to keep in mind when using this new feature: No PK allowed; No ON CONFLICT clauses; etc… In my experience, these are all just slight annoyances, hardly a reason to not benefit from this amazing feature.

You are also limited to two types of partitioning: RANGE and LIST.

A typical RANGE declaration would look something like this:

CREATE TABLE dt_totals (
dt_total date NOT NULL,
geo varchar(2) not null,
impressions integer DEFAULT 0 NOT NULL,
sales integer DEFAULT 0 NOT NULL
)
PARTITION BY RANGE (dt_total);
We would then create a table to act as a partition of the above:

CREATE TABLE dt_totals_201801
PARTITION OF dt_totals
FOR VALUES FROM (‘2018-01-01’) TO (‘2018-01-31’);
Any data for January would be stored in this partition. When it comes to performance for large tables, this is a fantastic new feature!