Default Partitions and Pruning Failures #

A range-partitioned table prunes correctly except for one child: the default partition, which appears in every plan. It holds 40 rows that arrived before the partitions were created, and it costs every query an extra scan — plus, when the time comes to add next month’s partition, an ACCESS EXCLUSIVE lock and a full scan of the default to prove no row belongs in the new range.

Pruning in general is covered in partition pruning analysis. This page covers the cases where it does not happen.

The Condition #

A default partition receives rows that match no other partition’s bounds. Because its contents are defined by exclusion rather than by a range, the planner can only prune it when the query’s conditions are provably outside every other partition’s bounds combined — which almost never happens for an ordinary range filter. In practice, a default partition is scanned by nearly every query against the table.

It also constrains DDL: ATTACH PARTITION or CREATE TABLE … PARTITION OF for a new range must verify that the default holds no row belonging to that range, which requires scanning the default under an ACCESS EXCLUSIVE lock on it. On a large default partition that is an outage.

Other pruning failures share one root cause — the planner cannot prove the partition is irrelevant:

What prunes and what does not A table of query conditions. A range or equality on the bare partition key prunes at plan time. A parameter on the key prunes at execution time, shown as Subplans Removed. A function applied to the key prunes nothing. A condition on another column prunes nothing. A default partition is almost never pruned. prunes? shown as key BETWEEN a AND b yes, plan time fewer child nodes key = $1 yes, run time Subplans Removed f(key) = value no all partitions scanned other_column = value no all partitions scanned default partition almost never always present pruning needs a provable condition on the bare key

Annotated EXPLAIN Evidence #

EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*) FROM events WHERE occurred_at >= '2026-09-01' AND occurred_at < '2026-09-02';
Aggregate  (actual time=41.2..41.2 rows=1 loops=1)
  ->  Append  (actual time=0.03..38.4 rows=412004 loops=1)
        ->  Index Only Scan using events_2026_09_occurred_idx on events_2026_09 events_1
              (actual rows=412004 loops=1)
              Index Cond: ((occurred_at >= '2026-09-01'::date) AND (occurred_at < '2026-09-02'::date))
        ->  Seq Scan on events_default events_2  (actual rows=0 loops=1)
              Filter: ((occurred_at >= '2026-09-01'::date) AND (occurred_at < '2026-09-02'::date))
              Rows Removed by Filter: 40
Execution Time: 41.4 ms
-- one monthly partition survived pruning, plus the default, which always survives

A function-wrapped key, pruning nothing:

EXPLAIN SELECT count(*) FROM events WHERE date_trunc('day', occurred_at) = '2026-09-16';

Aggregate
  ->  Append  (actual rows=412004 loops=1)
        ->  Seq Scan on events_2024_01 …  Filter: (date_trunc('day', occurred_at) = …)
        ->  … every partition, each scanned in full …

And the DDL cost of a populated default:

ALTER TABLE events ATTACH PARTITION events_2026_10
  FOR VALUES FROM ('2026-10-01') TO ('2026-11-01');
-- scans events_default under ACCESS EXCLUSIVE to prove no row belongs in the new range
-- on a 40-million-row default partition: minutes of blocked access
Three symptoms to recognise Three items are annotated. The default partition appearing in a plan that otherwise pruned correctly. Every partition scanned with the same filter, indicating a wrapped key. An ATTACH PARTITION statement scanning the default under an exclusive lock. Seq Scan on events_default (rows=0) present in every plan all partitions scanned, same Filter key wrapped in a function ATTACH scans the default exclusive lock while it runs a default partition with rows is a future outage

Step-by-Step Resolution #

  1. Check whether the default partition holds rows:

    SELECT count(*) FROM events_default;

  2. Move its rows into real partitions and keep it empty, so an ATTACH scan is instantaneous:

    BEGIN;
CREATE TABLE events_2023_11 PARTITION OF events FOR VALUES FROM ('2023-11-01') TO ('2023-12-01');
-- move rows out of the default
WITH moved AS (DELETE FROM events_default WHERE occurred_at >= '2023-11-01' AND occurred_at < '2023-12-01' RETURNING *)
INSERT INTO events SELECT * FROM moved;
COMMIT;

  3. Consider not having a default at all. Without one, inserts outside every range fail loudly, which is usually preferable to silently accumulating rows that break pruning and DDL.

  4. Automate partition creation ahead of time, so rows never need the default. A scheduled job creating the next few periods’ partitions removes the main reason people add one.

  5. Fix wrapped-key predicates by rewriting them as ranges on the bare key.

  6. Align types between the key and the values compared against it, especially timestamp versus timestamptz.

  7. Re-check the plan for the absence of the default and for fewer child nodes.

What the default partition costs With an empty default partition, the daily query takes 41 milliseconds and attaching a new partition takes 8 milliseconds. With 40 million rows in the default, the same query takes 4,120 milliseconds and attaching a partition takes 186 seconds under an exclusive lock. empty default: query 41 ms empty default: ATTACH 8 ms populated default: query 4,120 ms populated default: ATTACH 186 s locked the DDL cost is the one that causes incidents

Before and After #

-- BEFORE: default partition holding 40 million rows
Append → Index Only Scan (one month) + Seq Scan on events_default (40M rows)   Execution Time: 4120.6 ms

-- AFTER: rows moved into real partitions, default left empty
Append → Index Only Scan (one month) + Seq Scan on events_default (0 rows)      Execution Time: 41.4 ms

Why an empty default is still scanned #

Even with no rows, the default partition appears in plans, because pruning is decided from the partition bounds rather than from the contents. Scanning an empty relation costs almost nothing — a few buffers — so the plan overhead is negligible once the default is empty. The reason to care is the DDL behaviour: ATTACH PARTITION scans it, and that scan is proportional to its size.

An alternative some teams adopt is to omit the default entirely and handle the resulting insert errors in the application, treating “no partition for this timestamp” as a bug to fix rather than a case to absorb. That makes the failure immediate and local instead of appearing weeks later as a pruning anomaly. It requires the partition-creation automation to be reliable, which it should be in any case: a job that creates the next three months’ partitions and alerts if it fails is straightforward and removes the underlying problem.

Where a default must exist — ingesting data with occasional out-of-range timestamps from clients with bad clocks — monitor its row count and move rows out regularly, so it never grows large enough to make a future ATTACH expensive.

Common Pitfalls #

Letting the default accumulate rows. It blocks cheap partition attachment. Diagnostic signal: a growing row count in the default. Fix: move rows out and keep it empty.

Assuming an empty default is pruned. It is not; it is just cheap. Diagnostic signal: the default in every plan. Fix: nothing, once it is empty.

Wrapped partition keys. Nothing prunes. Diagnostic signal: every partition scanned with an identical filter. Fix: range conditions on the bare column.

Mixed timestamp types. The conversion blocks plan-time pruning. Diagnostic signal: pruning that works in one session and not another. Fix: match the key’s type exactly.

Frequently Asked Questions #

Why is my default partition always scanned? #

Its contents are defined by exclusion from every other partition’s bounds, so the planner can rarely prove it contains no matching rows. It therefore appears in almost every plan, which is cheap when it is empty and expensive when it is not.

Should I create a default partition? #

Often not. Without one, an insert outside every range fails immediately, which surfaces missing partitions as an obvious error rather than as silent accumulation that later blocks attaching new partitions.

Why is ATTACH PARTITION slow? #

If a default partition exists, PostgreSQL must scan it under an exclusive lock to prove no row belongs in the new partition’s range. The scan is proportional to the default’s size.

Up: Partition Pruning Analysis