Partitioning in PostgreSQL: the real benefit is operational Intermediate

Quick summary

Partitioning is not a query-speed tool. A well-placed B-tree index is usually as fast or faster. The real value of partitioning is operational: instant removal of old data, small vacuums, small indexes, cold data separated from hot data.

Benefit	Real?	Condition
Retention: DETACH + DROP instead of DELETE	Yes, the biggest	Partitioned by time, deleting whole partitions
Per-partition vacuum/bloat	Yes	Old data is rarely modified
Smaller indexes that fit RAM better	Yes, for large tables	The index really is too large for RAM
Queries faster than a good index	Usually not	Pruning only replaces the upper levels of the index
Less I/O when scanning a large range	Yes	Seq scan of one partition instead of the whole table

The PostgreSQL docs give a rule of thumb: partitioning is only worthwhile when the table is larger than the server’s RAM.

A fair baseline: is partitioning faster than an index?

A 300,000-row table (200-byte payload, about 72 MB), append-only data by created_at like a real log, 3 months. Three configurations; every number is the third (warm) run, and these are real tables so the plans show shared:

• E1: plain table, no index.
• E2: plain table + B-tree on created_at.
• E3: table partitioned by month, each partition indexed.

SELECT sum(length(payload)) FROM event_xxx WHERE created_at >= '2026-02-01' AND created_at < '2026-03-01'; -- Q1: 1 month
SELECT sum(length(payload)) FROM event_xxx WHERE created_at = '2026-02-15';                                -- Q2: 1 day

Configuration	Q1 (1/3 of table): plan	Buffers	Time	Q2 (1/90 of table): plan	Buffers	Time
E1 flat, no index	Parallel Seq Scan	9,152	13.2 ms	Parallel Seq Scan	9,152	6.4 ms
E2 flat + B-tree	Index Scan	2,913	22.4 ms	Index Scan	107	0.75 ms
E3 partition + index	Seq Scan 1 partition	2,829	19.9 ms	Index Scan 1 partition	107	0.77 ms

Reading this table:

• Q2: E2 and E3 are almost identical (107 buffers, 0.75 ms versus 0.77 ms). Partitioning does not beat the index.
• Q1: E1 wins on time thanks to a parallel scan, despite reading 3 times as many buffers. Fewer buffers is not necessarily faster.
• Total index size: 2,112 kB (flat) versus 736 + 664 + 736 = 2,136 kB (3 partitions). No savings, it is just split up.

Practical conclusion: if you only want time-based queries to be fast, add an index first. Do not partition for speed.

Note: numbers from PostgreSQL 17.11 in Docker; the small table fits entirely in RAM. For tables larger than RAM, the advantage of small indexes and compact hot data becomes clearer. Script: examples/src/postgres-partitioning/partitioning-baseline-demo.sql.

The real benefit: retention via DETACH/DROP

Removing all of January (103,334 rows) from the same data:

Method	Time	Consequence
DELETE ... WHERE created_at < '2026-02-01'	59 ms	Creates 103,334 dead tuples; the file is still 72 MB even after a plain VACUUM
ALTER TABLE ... DETACH PARTITION + DROP TABLE	0.7 ms + 1.7 ms	Frees the file immediately, no dead tuples, no vacuum needed

On a 5-billion-row table, DELETE runs for hours, generates enormous WAL, and leaves bloat. DROP still takes milliseconds.

DETACH PARTITION normally takes ACCESS EXCLUSIVE on the parent table. Since PG14, DETACH PARTITION ... CONCURRENTLY needs only SHARE UPDATE EXCLUSIVE (with restrictions, see the ALTER TABLE docs). Use that variant on a live system.

How partition pruning works

A partitioned table is a logical table with no storage of its own. Each child partition is an ordinary table. Three ways to split:

Kind	When to use	Example
RANGE	Time series, retention	created_at by month
LIST	A finite set of values	region, tenant group
HASH	Spread write load/size evenly, no natural range	tenant_id modulus 4

Pruning is the planner eliminating partitions that cannot contain rows satisfying WHERE, based on bounds, before reading any data.

Partition pruning: only 1 of 3 partitions is scanned

Enlarge diagram

Query → event_partWHERE created_at = Feb 15

◀ Prev▶ PlayNext ▶↺ RestartStep 1/3

The query filters on the partition key (created_at). The parent table is just a "table of contents"; the data lives in the child partitions.

The plan for Q2 on E3 has only one node, with no trace of the other two partitions:

Aggregate
  ->  Index Scan using event_2026_02_created_at_idx on event_2026_02 event_part
        Buffers: shared hit=107

A query with no condition on the partition key (e.g. WHERE payload LIKE 'nomatch%') must wrap all three partitions in an Append and scan them all. The cost equals a plain table, plus the Append.

Runtime pruning: values known only at execution

With prepared statements and a generic plan, the planner does not know the parameters, so pruning moves to executor initialization (and also to execution, with subqueries or parameterized nested loops). A real plan with plan_cache_mode = force_generic_plan:

Aggregate (actual rows=1 loops=1)
  ->  Append (actual rows=3334 loops=1)
        Subplans Removed: 2
        ->  Index Only Scan using event_2026_02_created_at_idx on event_2026_02 event_part_1
              Index Cond: (created_at = $1)

Subplans Removed: 2 is the sign that runtime pruning is working. Partitions eliminated at initialization do not appear in EXPLAIN. All pruning can be disabled with enable_partition_pruning (default on), for diagnosis only.

Broken pruning: wrapping the partition key in a function

The planner can only compare when the condition applies directly to the key column. The two queries below produce the same logical result but scan all 3 partitions:

WHERE date_trunc('month', created_at) = '2026-02-01'   -- function wraps the key
WHERE created_at::text >= '2026-02'                    -- cast wraps the key

Parallel Append
  ->  Parallel Index Only Scan using event_2026_01_created_at_idx ...
  ->  Parallel Index Only Scan using event_2026_03_created_at_idx ...
  ->  Parallel Index Only Scan using event_2026_02_created_at_idx ...

Rewrite it as a range: created_at >= '2026-02-01' AND created_at < '2026-03-01'. This is a classic bug, especially with an implicitly cast timestamptz.

Operations: what makes partitioning painful

Partitions must exist ahead of incoming data

An INSERT with a date that belongs to no partition fails:

ERROR:  no partition of relation "event_part" found for row
DETAIL:  Partition key of the failing row contains (created_at) = (2026-04-10).

Two defenses: create partitions several periods ahead (cron, or the pg_partman extension), and/or keep a DEFAULT partition as a safety net.

But DEFAULT has a cost: every ATTACH of a new partition makes PostgreSQL scan DEFAULT to make sure no row belongs to that partition, while holding ACCESS EXCLUSIVE on the DEFAULT partition (the parent only gets SHARE UPDATE EXCLUSIVE). A suitable CHECK on DEFAULT lets it skip that scan.

Attaching a partition without a rescan

For a table that already has data, add a CHECK matching the bounds before ATTACH to skip validation, then drop the redundant CHECK:

ALTER TABLE event_2026_04 ADD CONSTRAINT p CHECK (created_at >= '2026-04-01' AND created_at < '2026-05-01');
ALTER TABLE event_part ATTACH PARTITION event_2026_04 FOR VALUES FROM ('2026-04-01') TO ('2026-05-01');
ALTER TABLE event_2026_04 DROP CONSTRAINT p;

The life cycle of a partition

Loading diagram…

Constraints and limits to know

Topic	Fact
Primary key / unique	Must include every partition key column, and the key cannot be an expression. There is no global uniqueness on other columns.
Foreign key	Other tables can reference a partitioned table (PG12+), but the referenced columns need a unique constraint, which must include the partition key.
UPDATE of the partition key	The row is moved to another partition (delete + insert), costlier than a normal update.
Number of partitions	The planner handles up to a few thousand well if queries prune down to few partitions; each session loads metadata for every partition it touches, so RAM grows. Do not assume “more is better”.
Partition-wise join/aggregate	Available but off by default (enable_partitionwise_join, enable_partitionwise_aggregate); enable deliberately.

When to partition and when not to

Partition when	Do not partition when
There is a time-based retention policy (logs, audit, events)	The table is smaller than RAM and you only want fast queries: add an index
The table is larger than RAM and access concentrates on recent partitions	Queries usually do not filter by the key (every query scans everything)
You want to vacuum/reindex piece by piece without touching the whole table	You need global uniqueness on a non-key column
You want to push old data to cheap storage	There is no automated process for creating/dropping partitions yet

To convert a large live table to partitions: create a new partitioned table in parallel, copy in batches, then rename; or ATTACH the old table as a partition (with a CHECK) and split it gradually. With real tables this is weeks of work and needs its own plan.

How RANGE, LIST, and HASH route data

An INSERT into the parent is always routed to the right child partition. LIST by region (east/west, everything else goes to DEFAULT):

 partition_name | count
----------------+-------
 account_east   |    10
 account_west   |    10
 account_other  |    10

HASH by tenant_id (4 buckets; only 20 distinct tenant_id values, hence the skew):

 partition_name | count
----------------+-------
 session_h0     |    30
 session_h1     |    30
 session_h2     |    15
 session_h3     |    25

HASH can only prune with an equality condition on the hash column itself. Its main purpose is spreading write load and size evenly, not speeding up range reads.

Real-world example

18 months of audit log, 40 million rows per month

The audit table keeps 18 months, RANGE by month. At the start of each month, a cron job creates partitions for the next 2 months and runs DETACH ... CONCURRENTLY then DROP on the partition older than 18 months.

Cleanup takes milliseconds instead of a monthly 40-million-row DELETE (dead tuples, WAL, bloat).

“Last N days” queries touch only 1-2 partitions. A new feature needing “a user’s full history, no date limit” will scan every partition: do not abandon partitioning, add an index (user_id, created_at) on the parent (PostgreSQL creates the matching index on each partition).

Trade-offs

Pros

• DETACH/DROP of a partition removes old data instantly, with no dead tuples and no vacuum
• Vacuum, reindex, and backup split per partition; cold data moves to cheap storage
• Pruning replaces the upper levels of the index and allows a compact seq scan of one partition when a large range is needed

Cons

• Not faster than a good B-tree index in the measurements above
• Queries not filtering by the partition key cost the same as without partitioning, plus Append
• Unique/PK must include the partition key; no global uniqueness on other columns
• More operational work: creating partitions ahead, monitoring, managing DEFAULT, planning time growing with partition count

Production checklist

1. Choose the partition key by how retention and the main queries filter data, not by preference.
2. Create partitions at least 2 periods ahead; alert when future partitions are running out.
3. Decide whether to have DEFAULT; if so, monitor the number of rows landing in it.
4. Use DETACH ... CONCURRENTLY on live systems; ATTACH with a CHECK to avoid a scan.
5. Write conditions directly on the key (as a range), without wrapping it in functions or casts.
6. Check EXPLAIN with prepared statements/generic plans: look for Subplans Removed.
7. Keep the number of partitions reasonable (weekly or monthly, not hourly unless needed).
8. Design the PK to include the partition key from the start.

Common mistakes

Common pitfall

Partitioning by created_at in the belief that queries will be faster. For a table smaller than RAM, an index on created_at gives the same result (0.75 ms versus 0.77 ms in the measurement above).

Then a few months later a query filtering by user_id turns out to scan every partition. Partition for retention and operations, and verify against a baseline that has an index.

Summary

Key takeaways

• Partitioning is not faster than a well-placed B-tree index; always also measure a “flat + index” baseline
• The biggest benefits: retention via DETACH/DROP (2.4 ms versus a 59 ms DELETE + 72 MB of bloat in the demo), smaller vacuums and indexes
• Pruning only works when WHERE applies directly to the partition key; a function or cast around the key scans every partition
• With generic plans, pruning moves to runtime; look for Subplans Removed in EXPLAIN
• Create partitions ahead, weigh DEFAULT, ATTACH with CHECK, and DETACH CONCURRENTLY in production
• PK/unique must include the partition key; many partitions increase planning time and RAM

Self-check

1. Why does a one-day query read the same 107 buffers on both the indexed flat table and the indexed partitioned table?
2. Can WHERE date_trunc('month', created_at) = '2026-02-01' be pruned? How would you rewrite it?
3. Why does having a DEFAULT partition make ATTACH slower?
4. When does DETACH PARTITION normally need ACCESS EXCLUSIVE, and how do you avoid it?

References

• PostgreSQL 17 — Table Partitioning
• PostgreSQL 17 — CREATE TABLE
• PostgreSQL 17 — ALTER TABLE