The Partitioning Model
Partitioning splits one logical table into many physical pieces, while letting application code keep querying it as if it were a single table.
//
Search across all documentation pages
Partitioning splits one logical table into many physical pieces, while letting application code keep querying it as if it were a single table.
That split sounds like a storage detail, but the partition key you choose becomes a structural promise about how every future query, index, and maintenance job will behave.
This page builds the mental model behind PostgreSQL's declarative partitioning, so the more mechanical pages in this section (DDL syntax, pruning, retention) make sense as consequences of that model rather than as isolated recipes.
A partitioned table in PostgreSQL is declared once, with PARTITION BY RANGE, LIST, or HASH on a chosen partition key column.
Each child partition is a real physical table that holds a defined slice of rows, and the parent table itself stores no rows at all.
CREATE TABLE events (
event_id bigint GENERATED ALWAYS AS IDENTITY,
occurred_at timestamptz NOT NULL,
PRIMARY KEY (event_id, occurred_at)
) PARTITION BY RANGE (occurred_at);Range partitioning assigns rows to a partition based on where a value falls between bounds, and it is the natural fit for time-series data like events or logs.
List partitioning assigns rows by exact value membership, which fits low-cardinality columns like region or tenant tier.
Hash partitioning spreads rows evenly across a fixed number of partitions using a hash of the key, for cases where no natural range or list grouping exists but even distribution still matters.
The choice between these three is really a choice about what future queries will filter on, because that is what determines whether PostgreSQL can skip partitions entirely.
The planner's ability to skip irrelevant partitions is called partition pruning, and it is the entire performance case for partitioning.
When a query's WHERE clause constrains the partition key to a range or a set of values, the planner can determine at plan time (or in some cases at execution time) which child partitions could possibly contain matching rows, and scan only those.
EXPLAIN (COSTS OFF)
SELECT * FROM events WHERE occurred_at >= '2026-01-01' AND occurred_at < '2026-02-01';
-- A pruned plan touches only the events_2026_01 partitionIf a query does not filter on the partition key at all, pruning cannot happen, and the query scans every partition, which can be slower than the equivalent query against one unpartitioned table due to added planning overhead.
This is why the partition key is not a storage implementation detail to pick last, it is a prediction about the dominant WHERE clause shape across the table's real query workload.
Indexes on a partitioned table are declared once on the parent and PostgreSQL propagates the definition to every existing and future child partition, but each child still stores and maintains its own physical index.
That means adding an index to a partitioned table with many partitions is really adding many indexes at once, and it should be planned with the same care as any other large-table DDL.
A default partition exists to catch rows that do not match any explicit partition bound, which prevents insert failures during rollout, but it comes at a cost: rows in the default partition cannot be pruned away from any query, because the planner cannot rule it out for any predicate.
The maintenance case for partitioning is often the stronger argument in practice, even ahead of query speed.
Deleting a month of historical data from an unpartitioned billion-row table means a slow, transaction-log-heavy DELETE that has to be vacuumed afterward; detaching a partition is a fast metadata operation that removes the data instantly.
ALTER TABLE events DETACH PARTITION events_2025_01;Vacuum and autovacuum also benefit from partitioning indirectly, because each partition is a separate table for autovacuum's purposes, so a hot, frequently-updated recent partition can be vacuumed without touching cold historical partitions that have not changed.
Choosing between range, list, and hash partitioning at real scale usually comes down to which one matches the actual filter shape in production, not which one is theoretically most elegant for the data.
| Approach | Strength | Weakness | Best Fit |
|---|---|---|---|
| Range partitioning | Natural fit for time-series data; supports easy retention via detach | Requires ongoing partition creation as time advances | Event, log, and time-series tables |
| List partitioning | Direct mapping to a known, small value set | New values need new partitions or a default catch-all | Region, tenant tier, or other low-cardinality columns |
| Hash partitioning | Even write distribution with no natural range or list key | No pruning benefit unless queries filter on the hash key with equality | High-write tables needing spread, not time- or category-based access |
| No partitioning | Simplest to operate; no pruning planning needed | Bulk delete and vacuum cost scale with full table size | Tables that stay small or whose deletes are rare |
Partitioning also interacts with the primary key and unique constraint rules in PostgreSQL: any unique constraint, including the primary key, must include the partition key as part of its columns, because uniqueness can only be enforced within each physical partition, not across all of them at once.
That constraint shapes key design earlier than most teams expect, and it is worth resolving during the logical modeling stage rather than discovering it while writing the first CREATE TABLE PARTITION BY statement.
WHERE clause shapes tends to produce a table that cannot prune the queries that actually run against it.ALTER.It is one logical table declared with PARTITION BY, backed by multiple physical child tables that each hold a defined slice of rows.
The parent table itself stores no rows.
Partition pruning is the planner's ability to skip child partitions that cannot possibly contain matching rows, based on the query's WHERE clause.
It is the main performance benefit of partitioning, and it only fires when a query filters on the partition key.
The planner cannot rule out any partition, so it scans all of them, which can be slower than querying an equivalent unpartitioned table due to added per-partition planning overhead.
Uniqueness constraints, including the primary key, can only be enforced within a single physical partition, not across all partitions at once.
Including the partition key in the constraint is how PostgreSQL guarantees global uniqueness is actually upheld.
Yes, when old data aligns with whole partitions - detaching or dropping a partition is a fast metadata operation, compared to a row-by-row DELETE that then needs to be vacuumed on an unpartitioned table.
No - an index declared on the parent table propagates to every existing and future child partition automatically.
Each child still stores its own physical copy of that index.
A default partition catches rows that do not match any explicit partition's bounds, preventing insert failures during rollout or unexpected values.
It should be monitored and kept small, because rows inside it can never be pruned.
Indirectly, yes - each partition is a separate table for autovacuum's purposes, so a hot recent partition can be vacuumed frequently while cold historical partitions are left untouched.
Not as a lightweight operation - the partition key affects every unique constraint on the table, so changing it typically requires rebuilding the table under a new partitioning scheme.
This is why the key deserves real analysis before the first partition is created.
Both, but the maintenance case (fast retention via detach, isolated vacuum per partition) is often the stronger and more reliable win in production, ahead of raw query speed.
Query speed depends entirely on whether pruning actually applies.
There is no universal number, but planning overhead grows with partition count, and an unbounded, ever-growing partition count (one per day forever, with no retention) eventually strains planning time even when pruning works correctly.
Retention policies exist partly to keep this bounded.
Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17).
Reviewed by Chris St. John·Last updated Jul 15, 2026