Mastering PostgreSQL 12 - Third Edition

Speeding up partitions

Every major release of PostgreSQL provides the end user with improved partitioning. The same holds true for PostgreSQL 12. This time, partition pruning has been speeded up dramatically. Why should we care? If you have got more than just a handful of partitions, fast removal of partitions during planning and execution is vital to ensure that the overhead of partitioning does not go through the roof. 

Let's do a small test and see what happens. Let's execute the following SQLs in PostgreSQL 11 as well as in PostgreSQL 12:

test=# CREATE TABLE part (id int) PARTITION BY RANGE (id);CREATE TABLE

First of all, a normal range partitioned table is created. The second challenge is to create a really large number of partitions ...