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.
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.
Summary
- Core Idea: A partitioned table is one logical table whose rows are physically distributed across multiple child tables, chosen by a partition key.
- Why It Matters: Partitioning turns expensive whole-table operations (bulk delete, vacuum, index maintenance) into cheap per-partition operations, but only along the axis the partition key defines.
- Key Concepts: partition key, partition pruning, range/list/hash partitioning, default partition, partition-wise operation.
- When to Use: Large tables where old data is regularly archived or deleted, and where most queries filter on a single, stable column.
- Limitations / Trade-offs: Queries that do not filter on the partition key get no pruning benefit, and can be slower than an equivalent unpartitioned table.
- Related Topics: partition pruning, retention strategy, index design on partitioned tables, table bloat.
Foundations
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.
Mechanics & Interactions
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.
Advanced Considerations & Applications
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.
Common Misconceptions
- "Partitioning always makes queries faster." Partitioning only helps queries whose predicates match the partition key closely enough for pruning to apply; a query that filters on an unrelated column can get slower, not faster.
- "The partition key is just a storage choice." The partition key is a structural bet on the dominant query pattern, and choosing it without looking at real
WHEREclause shapes tends to produce a table that cannot prune the queries that actually run against it. - "A default partition is a safe permanent catch-all." A default partition prevents insert failures, but rows inside it are never prunable, and letting it grow undermines the entire performance case for partitioning that table.
- "Partitioning replaces the need for indexes." Indexes and partitioning solve different problems; a partitioned table still needs the same indexes an unpartitioned table would, applied per partition.
- "You can partition on any column later without cost." The partition key becomes part of every unique constraint including the primary key, so changing it after the fact usually means rebuilding the table, not a lightweight
ALTER.
FAQs
What actually is a partitioned table in PostgreSQL?
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.
How do I choose between range, list, and hash partitioning?
- Range: time-series or ordered data, most commonly a timestamp column.
- List: low-cardinality, known-value columns like region or tier.
- Hash: even write distribution when no natural range or list grouping exists.
What is partition pruning and why does it matter?
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.
What happens if a query does not filter 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.
Why must the partition key be part of the primary key?
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.
Is deleting old data faster with partitioning?
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.
Do indexes need to be created on every child partition separately?
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.
What is a default partition and when should I use one?
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.
Does partitioning help with vacuum and autovacuum?
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.
Can I change a table's partition key after it is already in production?
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.
Is partitioning mainly a performance feature or a maintenance feature?
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.
How many partitions is too many?
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.
Related
- Partitioning Basics - hands-on range, list, and hash examples
- Declarative Partitioning DDL - parent and child table syntax in depth
- Partition Pruning - proving and debugging pruned query plans
- Retention & Detach - archiving and dropping old partitions
- Partitioning Pitfalls - default partition traps and other mistakes
- Data Modeling Foundations - the modeling process a partition key decision belongs to
Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17).