Dropping Old Data with Partition Detach Instead of DELETE

The retention job deletes audit rows older than 400 days in batches of 10,000, every night. It deletes about 25 million rows, generates roughly 20 GB of WAL that every replica must replay, leaves the table and its indexes bloated until autovacuum catches up, and has started overlapping with the morning traffic peak. The table is partitioned by month — so all of those rows live in one partition that could be removed as a unit. DETACH PARTITION ... CONCURRENTLY followed by DROP TABLE removes a month of data in seconds, with almost no WAL and no bloat. This guide switches retention from deleting rows to detaching partitions, covers the concurrent detach’s locking and failure recovery, and shows how to archive before dropping. It belongs to Partitioning Live Tables Without Downtime.

Symptom / Error Signatures

Row-based retention on a large table shows up as:

• Nightly jobs issuing DELETE FROM audit_log WHERE created_at < now() - interval '400 days' AND id IN (...) for hours.
• WAL volume and replica lag spiking during the retention window.
• n_dead_tup climbing on the table and autovacuum running continuously; index bloat growing.
• Retention falling behind, so the table keeps growing despite the job.

Once switched to detaching, the errors to recognise are:

ERROR:  DETACH PARTITION ... CONCURRENTLY cannot run inside a transaction block
ERROR:  cannot detach partitions concurrently when a default partition exists
ERROR:  partition "audit_log_2025_07" already pending detach in partitioned table "public.audit_log"
HINT:  Use ALTER TABLE ... DETACH PARTITION ... FINALIZE to complete the pending detach operation.

Root Cause Analysis

DELETE removes rows one at a time: each deleted row is marked dead, logged to WAL, and later reclaimed by vacuum, and every index entry pointing to it must be cleaned too. The cost is proportional to the number of rows. Removing a partition is proportional to nothing: DETACH updates the catalog so the table is no longer part of the parent, and DROP TABLE unlinks its files.

A plain DETACH PARTITION takes ACCESS EXCLUSIVE on the parent, so it blocks every query on the partitioned table while it waits for and holds that lock. DETACH PARTITION ... CONCURRENTLY (PostgreSQL 14+) runs in two transactions and takes only SHARE UPDATE EXCLUSIVE on the parent and the partition: first it marks the partition as pending detach, then it waits for all queries that might still see it to finish, then it completes. Because it uses multiple transactions it cannot run inside a transaction block, and it is not allowed when the parent has a default partition. If it is interrupted — cancelled, or the session dies — the partition stays “pending detach”, and DETACH PARTITION ... FINALIZE completes it.

Immediate Mitigation

1. Detach the oldest partition concurrently. Run it outside any transaction block, with a lock timeout.

-- PostgreSQL 14+ · migration/retention role · must not run inside BEGIN … COMMIT
-- WARNING: not allowed when the parent has a default partition.
SET lock_timeout = '5s';
ALTER TABLE audit_log DETACH PARTITION audit_log_2025_07 CONCURRENTLY;
-- ROLLBACK PATH: ALTER TABLE audit_log ATTACH PARTITION audit_log_2025_07 FOR VALUES FROM ('2025-07-01') TO ('2025-08-01');

2. If the detach was interrupted, finalize it.

-- PostgreSQL 14+ · completes a pending concurrent detach
SELECT c.relname FROM pg_inherits i JOIN pg_class c ON c.oid = i.inhrelid
WHERE i.inhparent = 'audit_log'::regclass AND i.inhdetachpending;
ALTER TABLE audit_log DETACH PARTITION audit_log_2025_07 FINALIZE;

3. Archive, then drop. The detached table is an ordinary table: dump it to object storage, or move it to a cheaper tablespace, before dropping.

# Shell · retention host · read access to the detached table · archive before dropping
# WARNING: verify the archive (row count, checksum) before the DROP runs.
pg_dump --table=public.audit_log_2025_07 --format=custom --file=audit_log_2025_07.dump "$DATABASE_URL"
pg_restore --list audit_log_2025_07.dump > /dev/null && \
  psql "$DATABASE_URL" -c "DROP TABLE public.audit_log_2025_07"

4. Stop the old DELETE job once detach-based retention is running, so the two do not compete.

Permanent Fix / Long-Term Pattern

Encode retention as a scheduled job — or pg_partman’s retention setting, as in automating partition creation with pg_partman — that detaches partitions older than the policy, archives them if required, and drops them after the archive is verified. Keep the parent free of a default partition so concurrent detach is always available, and make the job idempotent: on start, finalize any pending detach, then continue.

Align partition size with retention granularity. Monthly partitions with a 400-day policy mean data is kept for up to 430 days, because a partition can only be dropped when all its rows have expired; if exact retention matters, use daily or weekly partitions. For tables not yet partitioned, the conversion in converting a Postgres table to declarative partitioning is the prerequisite, and until then, keep deletes batched and throttled as in throttling backfills to protect OLTP latency. MySQL has the equivalent in ALTER TABLE audit_log DROP PARTITION p2025_07, which removes the partition’s data without row-by-row deletion; use EXCHANGE PARTITION with an empty table first if you need to keep the data as a standalone table for archiving.

Verification Checklist

• The parent has no default partition, so DETACH ... CONCURRENTLY is permitted.
• The retention job finalizes pending detaches on start and runs outside a transaction block.
• Only partitions whose entire range is older than the policy are detached.
• Archives are verified before tables are dropped, and archive locations are recorded.
• WAL volume and replica lag during the retention window dropped to near zero after the switch.

Frequently Asked Questions

Why is DETACH so much cheaper than DELETE? Detaching changes catalog metadata and dropping unlinks files, so the cost does not depend on the number of rows. Deleting marks every row dead, logs every change to WAL and leaves space for vacuum to reclaim.

Does DETACH PARTITION CONCURRENTLY block queries? No. It takes SHARE UPDATE EXCLUSIVE on the parent and the partition, which allow reads and writes. It waits for queries that might still see the partition before completing, so it can take a while if long queries are running.

What happens if a concurrent detach is interrupted? The partition remains in a “pending detach” state: new queries no longer see it, but it is still linked to the parent. Run ALTER TABLE ... DETACH PARTITION ... FINALIZE to complete it.

Can I reattach a detached partition? Yes, with ATTACH PARTITION and its original bounds — and it will be quick if the table still carries a validated CHECK implying those bounds, as explained in attaching partitions without long locks.