Why partition
A single enormous table is slow to scan, vacuum, and index. Declarative partitioning lets you define one parent table whose rows are routed into child partitions based on a key, so each partition stays manageable.
Partitioning strategies
- Range partitioning splits by value bands, such as one partition per month of a timestamp.
- List partitioning splits by discrete values, such as one partition per region.
- Hash partitioning spreads rows evenly across a fixed number of partitions.
The parent table holds no data itself. Inserts are routed automatically to the correct child based on the partition key.
Partition pruning
The biggest win is pruning. When a query filters on the partition key, the planner skips partitions that cannot match and scans only the relevant ones.
- A query for last week only touches the recent partitions.
- Old partitions can be detached and dropped instantly instead of running a slow delete.
Costs to remember
- The partition key should appear in most queries, or pruning will not help.
- Indexes and constraints are defined per partition, though Postgres can cascade them from the parent.
Key idea
Native partitioning routes rows into child tables by a key so queries that filter on that key prune away irrelevant partitions, and old data can be dropped by detaching a whole partition.