Before PostgreSQL 10, Postgres users partitioned their data using inheritance based partitioning. The method used constraints to define the partitions and rules or triggers to route the data to appropriate partition. A user had to write and maintain code for all that. PostgreSQL 10 introduced declarative partitioning, which is much easier to setup and requires almost no maintenance. PostgreSQL 11
is adding a number of partitioning related enhancements that work with declarative partitioning. Users who have implemented inheritance based partitioning would want to move to declarative partitioning (after upgrading to v11, of course) to benefit from those features. Here’s how they can do so.
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!