39 Partition for the delete

We deleted one month of events from the book’s events table, 164,383 rows, with an ordinary delete ... where created_at .... It wrote 32 MB of WAL, left 164,383 dead tuples behind, and the vacuum that cleaned them up wrote another 12 MB. Then we removed the same month from a partitioned copy of the table by detaching and dropping one partition. That wrote under 150 KB.

At two billion rows, that delete becomes the nightly job that runs for hours and floods the replicas. The drop stays the same size: unlink some files, update the catalog.

That’s the honest pitch. Partitioning is a data lifecycle feature first. It isn’t a general speedup, and for queries that don’t carry the partition key it makes things slower.

Every benefit comes from separate tables

A partitioned table is a parent that holds no rows. Every row lives in exactly one child, a partition, chosen by the value of the partition key. Each partition is an ordinary table with its own heap, indexes, visibility map, and autovacuum schedule. That separation buys four things:

• Retention is a drop. Removing old data is dropping a table: no dead tuples, almost no WAL, nothing for vacuum.
• Maintenance works on pieces. Autovacuum handles a 30 GB partition, not a 3 TB table. Old partitions freeze once, and later vacuums skip their pages. You can reindex or repack one at a time.
• Hot indexes stay small. The current month’s indexes fit in memory even when the full history doesn’t.
• Pruning. A query that filters on the key touches only the partitions that can match.

Partitioning doesn’t make lookups faster

A B-tree descent costs three or four pages at a million rows or a billion (A B-tree is a shallow tree of pages), so splitting a table doesn’t make a well-indexed lookup faster. When the query doesn’t name the key, splitting multiplies the lookups: a lookup by id read 7 pages on events and 39 on events_p, its monthly-partitioned copy, searching every partition’s index. With 14 partitions that’s small. With 1,000 it isn’t (Every partition a query can’t prune costs planning time).

For faster time-range reads there’s a cheaper tool: a 24 KB BRIN index on created_at (BRIN keeps one summary per range of pages) counted March’s events in 3,251 pages; the partitioned table, pruned to March, in 2,988.

So partition for what only partitioning gives you: cheap retention, per-partition maintenance, or a table whose vacuum and index builds no longer fit your windows. Leave a table under tens of gigabytes alone unless it has a retention requirement, and wait until pg_stat_statements confirms your top queries carry the key.

 Index Scan using events_pkey on events (actual rows=1.00 loops=1)
   Index Cond: (id = 1234567)
   Buffers: shared hit=3 read=4
 Planning:
   Buffers: shared hit=22 read=1

 Append (actual rows=1.00 loops=1)
   Buffers: shared hit=31 read=8
   ->  Index Scan using events_p_2025_10_pkey on ... (actual rows=0.00 ...)
         Index Cond: (id = 1234567)
         Buffers: shared hit=3
   ...  (one scan per partition, 14 in all)
 Planning:
   Buffers: shared hit=202 read=4 dirtied=4

select lab.prove('a lookup by id alone: one index scan per partition on events_p',
  (select count(distinct r) from unnest(rels('select * from events_p where id = 1234567')) r) = :nparts);

select lab.prove('a lookup by id: about 7 pages on events, several times that on events_p',
  lab.buffers('select * from events where id = 1234567') <= 10
  and lab.buffers('select * from events_p where id = 1234567') > 3 * lab.buffers('select * from events where id = 1234567'));

select lab.prove('with BRIN, the unpartitioned month read is within 15% of the pruned partition read',
  lab.buffers(:'q_mid') < 1.15 * lab.buffers(:'q_mid_p'));

Range fits time; list and hash have narrow jobs

Declarative partitioning (partition by, PostgreSQL 10+, better in every release since) has three strategies:

This chapter’s events_p is range-partitioned by month: fourteen partitions of about 3,000 pages each, against 35,173 for events. Its primary key is (id, created_at), which is required (why).

List fits few, stable values, like EU data on its own storage. Hash gives no lifecycle benefit; it only splits one huge, busy table into medium ones for maintenance.

create table events_p (
  id          bigint generated always as identity,
  account_id  bigint not null,
  project_id  bigint not null,
  user_id     bigint,
  kind        text not null,
  payload     jsonb not null default '{}',
  created_at  timestamptz not null default now(),
  primary key (id, created_at)
) partition by range (created_at);

select format(
         'create table events_p_%s partition of events_p '
         'for values from (%L) to (%L)',
         to_char(m, 'YYYY_MM'), m, m + interval '1 month')
from generate_series(date_trunc('month', timestamptz '2025-10-06'),
                     date_trunc('month', timestamptz '2026-11-01'),
                     interval '1 month') as m
\gexec

    partition     | count  | pages
------------------+--------+-------
 events_p_2025_10 | 141830 |  2495
 events_p_2025_11 | 164383 |  2891
 ...
 events_p_2026_10 |  28034 |   493
(13 rows)

select lab.prove('every row lands in exactly one partition, and the copy keeps all 2,000,000',
  (select count(*) from events_p) = 2000000
  and (select count(*) from only events_p) = 0);

select lab.prove('a full month is about 3,000 pages; the unpartitioned events is about 35,000',
  (select max(pg_relation_size(inhrelid)) / 8192 from pg_inherits
   where inhparent = 'events_p'::regclass) between 2800 and 3100
  and pg_relation_size('events') / 8192 between 34000 and 36500);

Choose the key you delete by

The partition key should be what you retire data by, and ideally what most queries filter on. For events, logs, and metrics those are the same column, the timestamp, which is why they partition so well.

When they differ, retention usually wins. A SaaS app filters by account_id, but partitioning by account makes retention a row-by-row delete again. Partition by time and lead each partition’s indexes with account_id; spreading tenants across machines is sharding’s job.

Let retention pick the granularity, and aim for partitions of several gigabytes and counts in the tens to low hundreds. Monthly for ten years is 120 partitions. Daily is 3,650, and you’ll feel it.

The bounds are a free, coarse index

Partition pruning is skipping the partitions that can’t hold matching rows. Counting March’s events, with no index on created_at, read 2,988 pages on events_p against 35,173 on events: only March’s partition even appears in the plan.

When the value isn’t known at plan time (now(), a generic plan’s parameter), Postgres prunes as execution starts, and the plan says Subplans Removed: 11. Either way it needs the bare key: date_trunc('day', created_at) = ... scans all fourteen partitions, the half-open range scans one (Keep the indexed column bare).

Without the key, even a cheap query pays at planning time: “account 42’s latest 20 events” read partitions newest first and stopped at 23 pages, like the plain table, but planning touched 455 pages against 54.

select lab.prove('one month on events_p: only that partition appears in the plan',
  (select count(distinct r) from unnest(rels(:'q_mid_p')) r) = 1);

select lab.prove('one month reads about 3,000 pages on events_p and all ~35,000 on events',
  lab.buffers(:'q_mid_p') < 3100 and lab.buffers(:'q_mid') > 34000);

 Finalize Aggregate (actual rows=1.00 loops=1)
   Buffers: shared hit=149 read=2839
   ...
         ->  Parallel Seq Scan on events_p_2026_03 events_p
                 (actual rows=84931.50 loops=2)

 Aggregate (actual rows=1.00 loops=1)
   Buffers: shared hit=12 read=737
   ->  Append (actual rows=38235.00 loops=1)
         Subplans Removed: 11
         ->  Index Only Scan using events_p_2026_09_pkey on events_p_2026_09 ...
         ->  Index Only Scan using events_p_2026_10_pkey on events_p_2026_10 ...
         ->  Seq Scan on events_p_2026_11 events_p_3 (actual rows=0.00 loops=1)

 Limit (actual rows=20.00 loops=1)
   Buffers: shared hit=21 read=2
   ->  Append (actual rows=20.00 loops=1)
         ->  Index Scan Backward using events_p_2026_11_account_id_created_at_idx
               Buffers: shared hit=2
         ->  Index Scan Backward using events_p_2026_10_account_id_created_at_idx
               Buffers: shared hit=19 read=2
         ->  Index Scan Backward using events_p_2026_09_account_id_created_at_idx
               Index Searches: 0
         ...  (the other 11 also never executed)
 Planning:
   Buffers: shared hit=431 read=24
 Planning Time: 1.251 ms

select lab.prove('a filter on now() prunes when execution starts: Subplans Removed > 0',
  (lab.plan($$ select count(*) from events_p where created_at >= now() - interval '7 days' $$)
     @? '$.** ? (@."Subplans Removed" > 0)'));

Partition-wise plans are for reports

Pruning works one table at a time. enable_partitionwise_join joins two identically partitioned tables partition by partition, and enable_partitionwise_aggregate groups per partition. Both are off by default because every per-partition hash or sort gets its own work_mem: a plan over 100 partitions can use 100 times your budget.

On a quarterly join of events_p to a deliveries_p partitioned by the same months, it cut 46.9 ms to 17.6. But stating the range on both sides, so each side prunes alone, was the simpler fix. Use the settings per session for reports, never for OLTP.

 GroupAggregate (actual rows=9398.00 loops=1)
   Buffers: shared hit=11878 read=181 written=83
   ...
               ->  Parallel Hash Join (actual rows=4243.00 loops=3)
                     ->  Parallel Append (actual rows=168036.33 loops=3)
                           ->  Parallel Seq Scan on events_p_2026_07 e_1 ...
                     ...
                     ->  Parallel Hash (actual rows=16741.00 loops=3)
                           ->  Parallel Append (actual rows=16741.00 loops=3)
                                 ...  (all 14 deliveries partitions)
 Execution Time: 46.935 ms

 GroupAggregate (actual rows=9398.00 loops=1)
   Buffers: shared hit=51723 read=2
   ...
               ->  Parallel Append (actual rows=4243.00 loops=3)
                     ->  Nested Loop (actual rows=1452.33 loops=3)
                           ->  Parallel Seq Scan on deliveries_p_2026_08 d_2 ...
                           ->  Index Scan using events_p_2026_08_pkey ...
                                 (actual rows=1.00 loops=4357)
                     ->  Nested Loop ...   (July)
                     ->  Nested Loop ...   (September)
 Execution Time: 17.637 ms

select lab.prove('default: the events side prunes to 3 months, the deliveries side scans every partition',
  (select count(distinct r) filter (where r like 'events_p%') from unnest(rels(:'q_join')) r) = 3
  and (select count(distinct r) filter (where r like 'deliveries_p%') from unnest(rels(:'q_join')) r) = :nparts);

set enable_partitionwise_join = on;

select lab.prove('partition-wise: only the 3 matching deliveries partitions are read',
  (select count(distinct r) filter (where r like 'deliveries_p%') from unnest(rels(:'q_join')) r) = 3);

Constraints must carry the partition key

Unique constraints must include the key

Here’s why events_p’s primary key is (id, created_at):

ERROR:  unique constraint on partitioned table must include all partitioning columns
DETAIL:  UNIQUE constraint on table "events_p" lacks column "created_at"
which is part of the partition key.

Postgres has no global index. An index on a partitioned table is one index per partition, so uniqueness is checked within one partition. If the unique key contains the partition key, equal keys land together; if not, duplicates can sit in different months unseen. (The same id went into May and September without complaint.) on conflict targets follow the same rule.

References into a partitioned table must carry the key

Every unique key on events_p includes created_at, so references events_p (id) fails, and a referencing table must carry the partition key too, as deliveries_p does with (event_id, event_created_at). Event tables partition well partly because few tables reference them.

Indexes have their own catch: create index concurrently isn’t supported on a partitioned parent. Create the index on only the parent, build each partition’s concurrently, and attach each; the parent index turns valid after the last.

select lab.prove('a unique index on id alone is rejected: it lacks the partition key',
  lab.try('create unique index on events_p (id)')
    = '0A000: unique constraint on partitioned table must include all partitioning columns');

select lab.prove('a foreign key to events_p (id) alone is rejected (42830)',
  lab.try($$ create table event_comments (
                id bigint generated always as identity primary key,
                event_id bigint not null references events_p (id),
                body text not null) $$)
    = '42830: there is no unique constraint matching given keys for referenced table "events_p"');

select lab.prove('the same id can exist twice, in different partitions',
  (select count(*) = 2 and count(distinct tableoid) = 2 from events_p where id = 1234567));

create index events_p_kind_idx on only events_p (kind);   -- indisvalid = f

select format('create index concurrently %I on %s (kind)',
              c.relname || '_kind_idx', c.oid::regclass),
       format('alter index events_p_kind_idx attach partition %I',
              c.relname || '_kind_idx')
from pg_inherits i join pg_class c on c.oid = i.inhrelid
where i.inhparent = 'events_p'::regclass
order by c.relname
\gexec

Avoid the default partition

A default partition catches rows that match no other. It looks like a safety net. It’s closer to a trap: creating a partition must scan it under lock and fails if a stray row for that range is there, it blocks detach ... concurrently, and with a foreign key pointing at the table you can’t drop it directly.

Make sure future partitions always exist instead, and treat a “no partition found” error as the alarm it should be.

ERROR:  updated partition constraint for default partition "events_p_default"
would be violated by some row

ERROR:  cannot detach partitions concurrently when a default partition exists

ERROR:  cannot drop table events_p_default because other objects depend on it
DETAIL:  constraint deliveries_p_event_id_event_created_at_fkey on table
         deliveries_p depends on table events_p_default

select lab.prove('with a stray row in the default, creating its month fails (23514)',
  lab.try(format('create table events_p_future partition of events_p for values from (%L) to (%L)',
                  :'last_month'::timestamptz + interval '2 months',
                  :'last_month'::timestamptz + interval '3 months'))
    like '23514: updated partition constraint for default partition "events_p_default" would be violated%');

select lab.prove('a default partition blocks DETACH ... CONCURRENTLY',
  lab.try(format('select dblink_exec(%L, %L)', 'dbname=' || current_database(),
                  format('alter table events_p detach partition %I concurrently',
                         'events_p_' || to_char(:'first_month'::timestamptz, 'YYYY_MM'))))
    = '55000: cannot detach partitions concurrently when a default partition exists');

Retention: detach, then drop

This is the payoff. Deleting November 2025 (164,383 rows) two ways:

The delete took under 100 ms, on small, cached data. Its cost grows with the rows, and so does the WAL every replica replays. The drop’s doesn’t. Both finished in well under a second here; only one will at a billion rows.

alter table events_p detach partition events_p_2025_11 concurrently;
drop table events_p_2025_11;

select lab.wal_bytes(format($$ delete from events where created_at >= %L and created_at < %L $$,
                            :'old_month'::timestamptz, :'old_month'::timestamptz + interval '1 month')) as delete_wal \gset
vacuum events;

alter table deliveries_p detach partition :old_deliveries concurrently;
drop table :old_deliveries;
alter table events_p detach partition :old_events concurrently;
drop table :old_events;

-- vacuum_wal and drop_wal come from pg_stat_statements
select lab.prove('deleting a month writes about 32 MB of WAL',
  :delete_wal between 25e6 and 40e6);
select lab.prove('detach + drop writes over 100 times less WAL than delete + vacuum',
  :drop_wal > 0 and (:delete_wal + :vacuum_wal) > 100 * :drop_wal);

 Delete on events (actual rows=0.00 loops=1)
   Buffers: shared hit=169243 read=5223 dirtied=2893
   WAL: records=164383 fpi=2893 bytes=32465375 buffers full=3503
 Execution Time: 85.682 ms

tuples: 164383 removed, 1835626 remain, 0 are dead but not yet removable
WAL usage: 11670 records, 2098 full page images, 12504570 bytes, 181 buffers full

Detach concurrently, or everything queues behind you

A plain detach takes an ACCESS EXCLUSIVE lock on the parent and queues everything behind it (the lock queue). detach partition ... concurrently (PostgreSQL 14+) doesn’t block queries. It can’t run in a transaction block, and if interrupted it leaves the partition “detach pending” until detach partition ... finalize.

Foreign keys set the order

With deliveries_p referencing events_p, detaching the events partition first fails with a foreign key violation. Retire the referencing partition first.

ERROR:  removing partition "events_p_2025_12" violates foreign key constraint
"deliveries_p_event_id_event_created_at_fkey_3"
DETAIL:  Key (event_id, event_created_at)=(306216, 2025-12-01 00:00:38.330131+00)
is still referenced from table "deliveries_p".

select lab.prove('foreign keys set the order: the events partition can''t go before its deliveries (23503)',
  lab.try(format('alter table events_p detach partition %I', :'old_events')) like '23503: removing partition%violates foreign key constraint%');

Create partitions before you need them

Retention removes partitions at one end; something has to add them at the other. A missing partition doesn’t degrade gracefully: every insert for that range fails. Keep several future partitions ready, created by a scheduled job, and alert when the furthest-ahead one is less than a few weeks out. The \gexec generator is most of that job. If you’d rather not own it, pg_partman is the extension most teams use (pg_cron and pg_partman keep time on your side); read its defaults for your version, because they matter.

Convert a live table without downtime

You can’t alter table ... partition by. Instead, make the whole existing table the first partition, covering all of history, and let new partitions take the future. The trick is a check constraint that proves every row fits, so attach partition skips its validation scan:

1. Online: build a unique index on (id, created_at) concurrently, and add check (created_at < '2026-11-01') as not valid, then validate it without blocking writes (the two-step move).
2. One short transaction, with lock_timeout: rename the old table, make that index its primary key, move the identity column to a new partitioned parent, attach the old table up to the bound, create the next partitions, and setval the parent’s sequence past the old ids. A partition can’t keep its own identity, so skipping that step fails the attach.

On a 1.8 million row copy the transaction took about 20 ms. Without the constraint, the attach scanned every row under its lock: minutes or hours on a terabyte. (Filling new partitions in bulk: Freeze the partition, attach it built, analyze the parent.)

create unique index concurrently events_copy_id_created_at_key
  on events_copy (id, created_at);

alter table events_copy add constraint events_copy_old_range
  check (created_at < '2026-11-01') not valid;
alter table events_copy validate constraint events_copy_old_range;

begin;
set local lock_timeout = '2s';

alter table events_copy rename to events_copy_old;
alter table events_copy_old drop constraint events_copy_pkey;
alter table events_copy_old
  add constraint events_copy_old_pkey
  primary key using index events_copy_id_created_at_key;
alter table events_copy_old alter column id drop identity;

create table events_copy (
  id          bigint generated always as identity,
  account_id  bigint not null,
  project_id  bigint not null,
  user_id     bigint,
  kind        text not null,
  payload     jsonb not null default '{}',
  created_at  timestamptz not null default now(),
  primary key (id, created_at)
) partition by range (created_at);

alter table events_copy attach partition events_copy_old
  for values from (minvalue) to ('2026-11-01');

create table events_copy_2026_11 partition of events_copy
  for values from ('2026-11-01') to ('2026-12-01');
create table events_copy_2026_12 partition of events_copy
  for values from ('2026-12-01') to ('2027-01-01');

select setval(pg_get_serial_sequence('events_copy', 'id'),
              (select max(id) from events_copy_old));
commit;

-- scan_nocheck / scan_check: seq scans of the old table counted (in
-- pg_stat_xact_user_tables) across the attach, without and with the constraint
select lab.prove('without the check constraint, attach scans the whole table',
  :scan_nocheck = 1);
select lab.prove('with a validated check constraint, attach skips the scan',
  :scan_check = 0);

Partitioning costs most when queries can’t prune

Every partition a query can’t prune costs planning time

A query that prunes barely notices how many partitions there are. One that can’t pays for each, in planning time, memory, and locks. We planned a query with the key and one without (where account_id = 42) on 12 to 3,000 daily partitions:

With the key, the count barely matters. Without it, planning takes 52 ms, and the first query on every new connection about three times that. Unpruned queries also lock every partition and index, spilling into the shared lock table until it can run out with “out of shared memory”. Keep counts in the tens to low hundreds, and make the hot queries prune.

-- planning: median planning time of 15 runs per table, with and without the key
select lab.prove('without the key, planning grows with the partition count (1,000 is over 20 times 12)',
  (select without_key from planning where n = 1000) > 20 * (select without_key from planning where n = 12)
  and (select without_key from planning where n = 365) > 5 * (select without_key from planning where n = 12));

select lab.prove('with the key, pruning keeps 1,000 partitions planning over 20 times faster than without it',
  (select with_key * 20 < without_key from planning where n = 1000));

Four smaller traps catch partitioned tables

• Lookups by id alone search every partition. Put the time in the URL, or use an id that encodes it (UUIDv7 does).
• The parent is never analyzed by autovacuum. Analyze it from the retention job.
• Updates that change the key move the row: a delete plus an insert, never HOT. Pick a key that doesn’t change.
• Partitioning too small. A 5 GB table in 365 daily partitions gets all the overhead and none of the benefit.

The short version

• Partition for lifecycle. Deleting a month cost 45 MB of WAL and 164,383 dead tuples; dropping its partition, under 150 KB.
• It’s not an OLTP speedup: queries without the key search every partition, and BRIN gives most of the time-range benefit.
• Partition by the key you delete by (usually time); keep counts in the tens to low hundreds.
• Compare the bare key with a half-open range, and check pruning in EXPLAIN.
• Every unique key must include the partition key, and references must carry it.
• Retire with detach ... concurrently and drop, children first, with lock_timeout. Avoid default partitions.
• Create future partitions ahead of time, and alert when they run short.
• Convert a live table by attaching it whole, with a validated check constraint so the attach skips its scan.

Further reading

• Table partitioning, PostgreSQL documentation. The reference for declarative partitioning, its limitations, and the best-practices section on partition counts.
• ALTER TABLE, PostgreSQL documentation. The exact locks for ATTACH and DETACH PARTITION, the check constraint that skips the scan, and the rules for CONCURRENTLY.
• Query planning configuration, PostgreSQL documentation. What enable_partitionwise_join and enable_partitionwise_aggregate do and why they cost memory.
• CREATE TABLE, PostgreSQL documentation. The partition by and partition of syntax, including list, hash and default partitions.
• pg_partman, GitHub. The common extension for creating partitions ahead of time and applying retention; read the docs for your version’s defaults.