Level 3 · 30 min

Partitioning

PostgreSQL declarative partitioning (introduced in PostgreSQL 10) splits a large table into smaller physical partitions while presenting a single logical table. The key benefit is partition pruning: queries touching only recent data scan only recent partitions.

Partition Types

Range partitioning: each partition holds rows where the partition key falls within a defined range. Best for: time-series data (partition by month, quarter, year), sequential IDs. CREATE TABLE orders (...) PARTITION BY RANGE (created_at). List partitioning: each partition holds rows with specific key values. Best for: status codes, country codes, categories. Hash partitioning: rows distributed across partitions based on hash of the key. Best for: even distribution when no natural range/list exists. Composite partitioning: partition by range then sub-partition by hash or list — e.g., partition logs by year, sub-partition by tenant_id hash for even distribution within each year.

Partition Pruning

Partition pruning is the key performance benefit: the planner examines which partitions could possibly contain rows matching the WHERE clause and skips the rest. For pruning to work: the WHERE clause must include the partition key, and the condition must be statically determinable (not a subquery result or non-immutable function). Static pruning happens at plan time (for constant predicates). Dynamic pruning happens at execution time (for parameterized queries or nested loops where the partition key is from the outer relation). Enable: enable_partition_pruning = on (default). Verify pruning with EXPLAIN: you should see only the relevant child partition nodes in the plan. Declarative partitioning (introduced in PostgreSQL 10, significantly improved in PG11-14) creates child tables with partition constraints automatically. The planner checks partition constraints against the query predicate using constraint exclusion — if the constraint and predicate are provably non-overlapping, the entire partition is eliminated from the plan. Each partition is a fully independent table with its own indexes, autovacuum settings, and even tablespace; this allows placing hot recent partitions on fast NVMe storage while archiving old partitions to slower storage or detaching them as separate read-only tables. Attaching and detaching partitions (ATTACH PARTITION / DETACH PARTITION) is a metadata operation — near-instantaneous for large datasets compared to DELETE-based archiving.

Partition Maintenance

Creating partitions in advance: for time-based partitions, create the next month's partition before the end of the current month — otherwise inserts fail. ALTER TABLE orders ATTACH PARTITION orders_2024_12 FOR VALUES FROM ('2024-12-01') TO ('2025-01-01'). Dropping old partitions: ALTER TABLE orders DETACH PARTITION orders_2020 and then DROP TABLE orders_2020 — much faster than DELETE because it drops the entire physical file. pg_partman extension automates partition creation and retention. Partition-wise joins (enable_partitionwise_join) and partition-wise aggregates (enable_partitionwise_aggregate): when both sides of a join are partitioned by the same key, the planner can join corresponding partitions directly rather than merging all partitions first.

Key Takeaways

Partition pruning only works when the WHERE clause includes the partition key. Always include the partition key in your most common queries — otherwise you scan all partitions.
Dropping a partition (DETACH + DROP TABLE) is O(1) — deletes the physical file instantly. This is why time-series data is commonly partitioned by month: old partitions are dropped instead of row-level DELETEs.
pg_partman automates partition creation and retention. Without it, you must manually create future partitions and drop old ones — easy to forget in production.

Code example

-- Range partitioning by month\nCREATE TABLE events (\n  id BIGSERIAL,\n  tenant_id UUID NOT NULL,\n  event_type VARCHAR(50),\n  created_at TIMESTAMPTZ NOT NULL\n) PARTITION BY RANGE (created_at);\n\n-- Create monthly partitions\nCREATE TABLE events_2024_12 PARTITION OF events\n  FOR VALUES FROM ('2024-12-01') TO ('2025-01-01');\n\n-- Verify pruning\nEXPLAIN SELECT * FROM events WHERE created_at >= '2024-12-01';