Partial Indexes vs Partitioning for Hot Subsets #

An events table holds four years of history; every user-facing query touches the last seven days. Two proposals land in the same design review: a partial index on the recent window, and partitioning by month. They solve overlapping problems, cost very different amounts of work, and only one of them makes deleting old data instant.

Partial indexes are covered in partial index implementation, pruning in partition pruning analysis. This page compares them as alternatives.

The Condition Each Solves #

A partial index limits what is indexed. The table stays one relation; the index contains only rows matching its predicate, so it is small, fast to scan and cheap to maintain. It helps when queries can be proven to target the subset, and it does nothing for the table’s own size: scans of the full table, vacuum work, and the cost of deleting old rows are unchanged.

Partitioning splits the table. Each partition is a separate relation with its own indexes, statistics and storage. Queries with a predicate on the partition key touch only the matching partitions; old data is removed by detaching or dropping a partition, which is a catalog operation rather than a bulk delete; and each partition is vacuumed and analyzed independently.

Their strengths barely overlap:

Need Partial index Partitioning
Small index for a hot subset yes yes, per partition
Cheap instant deletion of old data no yes, DROP PARTITION
Reduced vacuum scope no yes, per partition
Works with a moving time window awkward: predicate must be rewritten yes, new partitions
No planning overhead yes grows with partition count
Simple schema and tooling yes more moving parts

The “moving window” row is the decisive one for time-series data. A predicate such as WHERE created_at >= '2026-09-10' becomes stale the next day, and PostgreSQL cannot use a partial index whose predicate references now(), because the predicate must be immutable. Keeping a recent-window partial index current means recreating it on a schedule.

Which structure fits the problem? Three cases. If the subset is defined by a stable condition such as a status value, a partial index is usually enough. If the subset is a moving time window and old data must be removed cheaply, partitioning fits. If queries do not carry the defining predicate, neither helps and a full index is the answer. what defines the hot subset? a stable status or flag partial index small, simple, no overhead a moving time window partitioning drop old partitions nothing in the query full index no proof, no pruning both need the query to carry the defining predicate

Annotated EXPLAIN Evidence #

Partial index on a status subset:

CREATE INDEX CONCURRENTLY events_unprocessed_idx
  ON events (occurred_at) WHERE processed = false;

EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM events WHERE processed = false ORDER BY occurred_at LIMIT 100;
Limit  (actual time=0.03..0.06 rows=100 loops=1)
  ->  Index Scan using events_unprocessed_idx on events  (actual rows=100 loops=1)
        Buffers: shared hit=6
Execution Time: 0.08 ms
-- index holds ~40k unprocessed rows out of 1.4 billion

Partitioning for a time window:

EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*) FROM events WHERE occurred_at >= '2026-09-10';

Aggregate  (actual time=412.6..412.6 rows=1 loops=1)
  ->  Append  (actual rows=90412004 loops=1)
        Subplans Removed: 46                     -- 46 monthly partitions pruned
        ->  Seq Scan on events_2026_09 events_1  (actual rows=90412004 loops=1)
Execution Time: 412.9 ms
-- only the current month's partition is read

And what partitioning does that no index can:

-- retention: remove four-year-old data
ALTER TABLE events DETACH PARTITION events_2022_09 CONCURRENTLY;
DROP TABLE events_2022_09;
-- catalog operation + file removal; no row-by-row delete, no vacuum, no bloat
What each structure shows in practice Three items are annotated. A partial index scan reading six buffers for a status subset. An Append with Subplans Removed showing time-based pruning. A DETACH and DROP PARTITION replacing a bulk delete that would otherwise rewrite and vacuum billions of rows. Index Scan using events_unprocessed_idx tiny index for a stable subset Append … Subplans Removed: 46 pruning by the partition key DETACH PARTITION + DROP TABLE retention without a bulk delete the retention difference is usually what decides the design

Step-by-Step Resolution #

  1. Characterise the subset. Stable predicate (a status, a flag, a tenant class) or a moving range (recent time)?

  2. Check whether queries carry the predicate. Neither structure helps a query that does not mention the defining column; that is a full-index problem.

  3. For a stable subset, start with a partial index. It is one statement, has no schema implications and can be dropped as easily:

    CREATE INDEX CONCURRENTLY events_unprocessed_idx ON events (occurred_at) WHERE processed = false;

  4. For a moving window with retention needs, partition. Size partitions so that a typical query touches one or two, and so that the total count stays modest — planning cost grows with partition count, as covered in planning overhead with many partitions.

  5. Use both where they complement each other. Partitioning by month plus a partial index on processed = false within each partition gives pruning for reports and a tiny index for the worker queue.

  6. Do not partition to make one query faster. If retention and vacuum scope are not problems, a good index is simpler and often as fast.

  7. Measure the migration cost honestly. Converting a large table to partitions requires a rewrite or a phased migration; a partial index is a background build.

Where each structure pays off For the hot-subset query, both approaches take under a millisecond. For a report over last month, the partial index approach takes 41 seconds because the table is scanned, while partitioning takes 0.4 seconds. For deleting four-year-old data, the partial index approach takes 96 minutes of bulk delete and vacuum, while partitioning takes two seconds. Planning time is 0.2 milliseconds with a partial index and 3.4 milliseconds across 48 partitions. hot query, partial index 0.08 ms month report, one table 41 s month report, partitioned 0.41 s retention, partitioned 2 s (vs 96 min) pruning and retention are partitioning's real wins

Before and After #

-- BEFORE: one table, retention by DELETE
DELETE FROM events WHERE occurred_at < now() - interval '4 years';   96 min, 1.4 billion dead rows to vacuum

-- AFTER: monthly partitions
ALTER TABLE events DETACH PARTITION events_2022_09 CONCURRENTLY; DROP TABLE events_2022_09;   2 s

The maintenance dimension #

Partitioning changes how autovacuum behaves. Each partition has its own thresholds, so an append-only month partition is vacuumed once and then left alone, while a single giant table keeps crossing its thresholds because of activity anywhere in it. Analyze behaves similarly, except for the partitioned parent, which needs explicit analysis — the trap in statistics on partitioned tables.

Partial indexes have their own maintenance quirk: rows moving into and out of the predicate are index inserts and deletes, so a predicate on a frequently flipping column costs more write work than a full index on the same column. A queue flag flipping once per row is fine; a boolean toggled repeatedly is not.

Neither structure fixes a missing predicate. If the application queries by a column that neither the partition key nor the index predicate mentions, both plans fall back to reading everything — which is why the first question in the workflow is about the query, not the table.

Common Pitfalls #

Partitioning for query speed alone. The complexity rarely pays without retention or vacuum-scope needs. Diagnostic signal: a partitioning proposal whose only benefit is one report. Fix: index first, measure.

A partial index with a date literal. It goes stale the next day. Diagnostic signal: index usage dropping over time. Fix: partition, or recreate the index on a schedule.

Too many partitions. Planning time grows with them. Diagnostic signal: planning time of milliseconds on simple queries. Fix: coarser partitions.

Queries without the key or predicate. Neither structure applies. Diagnostic signal: Append over all partitions, or the partial index unused. Fix: add the column to the query or use a full index.

Frequently Asked Questions #

Is a partial index a substitute for partitioning? #

For making queries on a stable subset fast, often yes. It does not reduce table size, vacuum scope or the cost of deleting old data, which are the reasons most teams partition.

Can a partial index use a moving window like the last seven days? #

No. The predicate must be immutable, so it cannot reference now(). A window predicate has to be written with a fixed date and recreated periodically, which is usually a sign that partitioning fits better.

Can I use partitioning and partial indexes together? #

Yes, and it is a common combination: partition by time for pruning and retention, and add a small partial index within each partition for a hot subset such as unprocessed rows.

Up: Partial Index Implementation