Why Partition Large Tables
Declarative partitioning, available since PostgreSQL 10, lets you define a parent table with a partitioning strategy and key, then attach child tables (partitions) that each hold a distinct slice of the data, while queries and applications continue to address the parent table as if it were a single table. The primary benefits are partition pruning — the planner can skip scanning partitions that can't possibly contain matching rows for a given WHERE clause, dramatically reducing I/O — and operational maintenance, since bulk data lifecycle operations like dropping old data become a near-instant DETACH/DROP of a partition instead of a slow, WAL-heavy DELETE over billions of rows.
Cricket analogy: It is like organizing a stadium's ticket archive by season instead of one giant undifferentiated pile: when someone asks for 2023 season records, the archivist pulls only the 2023 filing box instead of sifting through every season ever played.
Partitioning Strategies
PostgreSQL supports three native partitioning strategies. Range partitioning divides rows by a value range (most commonly a timestamp, giving monthly or daily partitions for time-series data), list partitioning divides by discrete matching values (like a region or tenant_id), and hash partitioning distributes rows evenly across a fixed number of partitions using a hash of the partition key, useful when there's no natural range or list boundary but you still want to spread I/O and lock contention across multiple physical tables.
Cricket analogy: Range partitioning is like sorting match records by date range into season folders; list partitioning is like sorting them by named tournament (IPL, Ashes, World Cup); hash partitioning is like randomly distributing scorecards across ten identical filing cabinets purely to balance the archivist's workload.
-- Range partitioning by month on a time-series events table
CREATE TABLE events (
id bigint GENERATED ALWAYS AS IDENTITY,
event_type text NOT NULL,
payload jsonb,
created_at timestamptz NOT NULL
) PARTITION BY RANGE (created_at);
CREATE TABLE events_2026_06 PARTITION OF events
FOR VALUES FROM ('2026-06-01') TO ('2026-07-01');
CREATE TABLE events_2026_07 PARTITION OF events
FOR VALUES FROM ('2026-07-01') TO ('2026-08-01');
CREATE INDEX ON events_2026_07 (event_type, created_at);
-- Dropping old data is now near-instant
ALTER TABLE events DETACH PARTITION events_2026_06 CONCURRENTLY;
DROP TABLE events_2026_06;Partition Pruning and Indexes
The query planner performs partition pruning both at plan time, when partition key values are known constants in the query, and at execution time, when the values come from bind parameters or subqueries evaluated during execution (runtime pruning, enabled by enable_partitionwise_join and related planner settings). Crucially, indexes are defined per-partition, not on the parent table as a single structure — creating an index on the parent with CREATE INDEX automatically creates a matching index on every existing and future partition, but each partition's index is a physically separate, independently sized B-tree.
Cricket analogy: It is like a stats analyst who, before pulling any records, first checks which season folder the query even applies to (plan-time pruning) — or, if the season depends on a variable like 'the year Kohli debuted,' figures that out on the fly before opening only that folder (runtime pruning).
Runtime (execution-time) partition pruning is what makes parameterized queries and prepared statements efficient against partitioned tables — without it, a generic plan built for a prepared statement would have to scan every partition since the specific value isn't known until execution. This capability, matured across PostgreSQL 11-12, is a major reason partitioning became practical for OLTP-style workloads, not just analytics.
Maintenance: Attaching, Detaching, and Default Partitions
New partitions are added ahead of need with CREATE TABLE ... PARTITION OF or attached from an existing table via ALTER TABLE ... ATTACH PARTITION (which, when the existing table already has a matching CHECK constraint, can skip the expensive validation scan). Old partitions are removed with ALTER TABLE ... DETACH PARTITION CONCURRENTLY (added in PostgreSQL 14, allowing detachment without blocking concurrent queries) followed by a simple DROP TABLE, which is essentially instantaneous compared to a row-by-row DELETE. A DEFAULT partition can catch rows that don't match any defined partition bound, but its presence prevents the planner from pruning it out of any query, since any row could theoretically be sitting there — so default partitions should be monitored and kept as empty as possible.
Cricket analogy: It is like pre-labeling next season's filing box months in advance (attach ahead of need), retiring a five-year-old season's box by simply removing it from the shelf rather than shredding each scorecard individually (detach/drop), while a general 'miscellaneous' box for unlabeled scorecards must always be checked by the archivist just in case a stray record ended up there.
A DEFAULT partition that accumulates a meaningful number of rows becomes a performance liability: because any query might need to check it, its presence disables full pruning benefits across the whole partitioned table, and it can grow into an unindexed, unmanaged dumping ground. Treat unexpected rows landing in the default partition as a signal to add a proper partition and migrate them out, keeping the default partition empty or near-empty.
- Declarative partitioning splits a large table into child partitions while applications continue to query the parent table transparently.
- Partition pruning lets the planner skip irrelevant partitions, at plan time for constants and at runtime for parameters/subqueries.
- PostgreSQL supports range, list, and hash partitioning strategies, chosen based on the natural shape of the partition key.
- Indexes are created per-partition; CREATE INDEX on the parent propagates to every partition automatically.
- Dropping old data via DETACH PARTITION CONCURRENTLY + DROP TABLE is vastly cheaper than a row-by-row DELETE.
- ATTACH PARTITION can skip validation scans if a matching CHECK constraint already exists on the table being attached.
- A DEFAULT partition should be kept empty; a growing default partition undermines pruning and becomes an unmanaged data dump.
Practice what you learned
1. What is the main query performance benefit of declarative partitioning?
2. Which partitioning strategy distributes rows evenly across a fixed number of partitions using a hash of the key, when there's no natural range or list boundary?
3. How does dropping an old partition compare to running DELETE on the equivalent rows in an unpartitioned table?
4. What happens to CREATE INDEX when run on a partitioned parent table?
5. Why is a growing DEFAULT partition considered a problem?
Was this page helpful?
You May Also Like
Logical Replication
Understand how PostgreSQL's publish/subscribe logical replication decodes row-level changes for selective replication, zero-downtime upgrades, and cross-version data movement.
PostgreSQL High Availability Patterns
Learn how to design a highly available PostgreSQL deployment combining replication, automated failover, connection routing, and backups to meet real RTO/RPO targets.
Connection Pooling with PgBouncer
Learn why PostgreSQL connections are expensive, how PgBouncer's pooling modes work, and how to configure and monitor it safely in production.