Performance Benefits from Partitioning

Partition pruning[379] is the mechanism PostgreSQL uses to determine which partitions can be ignored by the query planner based on partition constraints. When the planner can exclude partitions, this improves query performance because fewer partitions need to be evaluated.

To use this, make sure enable_partition_pruning is set to on. Run SHOW enable_partition_pruning; from psql and turn it on if it’s not:

sql/set_enable_partition_pruning.sql
​ ​SET​ enable_partition_pruning = ​ON​;

Enabling partition pruning is straightforward. The more challenging part will be making sure your queries include the partition key column for all queries.

The partition key column should appear in the WHERE clause so that the ...

Get High Performance PostgreSQL for Rails now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial