New partition features and improvements coming in Postgres 11.
PostgreSQL 11, due to be released later this year, comes with a bunch of
improvements for the declarative partitioning feature that was introduced in
version 10. Here’s a quick look at what’s on the menu.
Partitioned Tables in Postgres
Postgres 10 introduced natively partitioned tables in core PostgreSQL. With this
feature, you can shard a table into multiple child tables. The parent table itself
contains no rows, but serves as a “virtual” table into which you can insert rows
and query from. Combining this with other PostgreSQL features, you can have
child tables on separate disks (tablespaces)
or even other servers
Checkout the Postgres
more on partitioned tables.
PostgreSQL 11 brings all around improvements to partitioning functionality. You
can get your hands dirty with the new features on the first beta which should
be coming out in a few weeks. Or compile it from the latest snapshot,
like we did.
So without further ado, here is the list you came here for:
1. Update Moves Rows Across Partitions
PostgreSQL 10 did not let you perform updates to rows that would result in
the row ending up in a different partition. For example, if you had a table
with 2 partitions, and 1 row in the first one:
..and you tried to update the row such that as per the partition range it should
end up in the other child table (measurement_y2017), this is what happens:
But the same statement in Postgres 11 will move the row to the correct partition:
2. Create Default Partitions
With v11 it is now possible to create a “default” partition, which can store
rows that do not fall into any existing partition’s range or list.
In v10, trying to insert such a row fails:
But in v11 we can first create a default partition:
Note the new syntax that says “DEFAULT” instead of “FOR VALUES …”. With
the default partition in place, we can insert rows that do not fall in any
existing partition’s range/list:
The unclaimed row ends up in the table marked as the default partition.
A word of warning though: after adding a default partition, it becomes impossible
to directly add another partition to cover a new range. You’ll need to detach
the default partition, create the new partition, “manually” move the matching
rows from the default partition to the new partition and then reattach the
3. Automatic Index Creation
Indexes had to be created manually for each partition in v10. Trying to create
a partition on the parent table fails:
In v11, if you create an index on the parent table, Postgres will automatically
create indexes on all the child tables:
Any new partitions created after the index was created, will also automagically
get an index added to it.
4. Foreign Key Support
It was not possible to have a column in a partitioned table be a foreign key.
This is what you got in v10:
But in v11, foreign keys are allowed:
5. Unique Indexes
In Postgres 10, you had to enforce unique constraints at child tables. It was
not possible to create a unique index on the master:
With Postgres 11, you can create a unique index on the master:
..and Postgres will take care of creating indexes on all existing and future
The columns in the index definition should be a superset of the partition key
columns. This means that the uniqueness is enforced locally to each partition,
and you can’t use this to enforce uniqueness of alternate-primary-key columns.
6. Partition-level Aggregation
PostgreSQL 11 comes with a new option, called enable_partitionwise_aggregate,
which you can turn on to make the query planner to push aggregation down to
the partition level. By default, this option is off, and you get plans as before:
This says the grouping happens over a superset of all the individual,
per-partition scans. Let’s turn on the new option and see the updated plan:
Now the grouping happens once per partition, and the results are just concatenated
(we’re grouping by the partition key here).
Pushing down the aggregation should result in faster queries because of better
parallelism and improved lock handling.
7. Partition by Hash
Postgres 10 came with RANGE and LIST type partitions. In 11, we have HASH type
partitions also. Hash type partitions distribute the rows based on the hash
value of the partition key. The reminder of the hash value when divided by a
specified integer is used to calculate which partition the row goes into (or
can be found in).
Here is how to create a hash partition, in this case over a partition key of
We expect each partition to contain about a third of all the rows – let’s try
Apart from these features, several improvments to the query planner and
executor and partition pruning algorithms make for faster queries on
partitoned tables in PostgreSQL 11. It is too early in the v11 release cycle
to benchmark any results though.
pgDash is an in-depth monitoring solution designed
specifically for PostgreSQL deployments. pgDash shows you information and
metrics about every aspect of your PostgreSQL database server, collected using
the open-source tool pgmetrics.
pgDash is currently in Beta and provides core reporting and visualization
functionality, including collecting and displaying PostgreSQL information and
providing time-series graphs and detailed reports. We’re actively working to
enhance and expand pgDash to include alerting, baselines, teams, and more.