Detaching and Archiving Old Partitions in PostgreSQL

This guide retires one partition safely: detach it without blocking traffic, dump it to durable storage, prove the dump is restorable, and only then reclaim the space. It implements the retention stage of Partition Lifecycle & Retention Management within Partitioning Implementation Patterns & Routing.

Prerequisites

Step 1 — Confirm the partition is genuinely cold

Detaching a partition that still receives writes produces errors in the application, not in the database. Check before acting:

SELECT relname,
       n_tup_ins,
       n_tup_upd,
       last_autoanalyze,
       pg_size_pretty(pg_total_relation_size(relid)) AS size
FROM   pg_stat_user_tables
WHERE  relname = 'events_2025_06';
    relname     | n_tup_ins | n_tup_upd |   last_autoanalyze    |  size
----------------+-----------+-----------+-----------------------+--------
 events_2025_06 |         0 |         0 | 2025-07-02 03:14:08+00 | 391 GB

Zero inserts since the counters were last reset, and an autoanalyze timestamp from a year ago, is the profile of a partition safe to retire. Non-zero recent inserts mean either late-arriving data or a bounds mistake, and both need investigating before anything is detached.

Operational note: pg_stat_user_tables counters reset when the statistics collector is reset or the server is rebuilt from a base backup. Cross-check against the partition’s bounds and the application’s late-arrival tolerance rather than trusting the counter alone.

DBA tip: Query the partition for its true max timestamp (SELECT max(occurred_at) FROM events_2025_06) as a second confirmation. It costs one index scan and catches the case where writes arrived long after the month closed.

Step 2 — Detach concurrently

DETACH CONCURRENTLY avoids the ACCESS EXCLUSIVE lock on the parent that the plain form takes:

-- cannot run inside a transaction block
ALTER TABLE events DETACH PARTITION events_2025_06 CONCURRENTLY;

The operation runs in two internal phases and waits for concurrent transactions to finish between them. If it is cancelled or the session dies, the partition is left transitional and must be finished explicitly:

-- only needed if the concurrent detach was interrupted
ALTER TABLE events DETACH PARTITION events_2025_06 FINALIZE;
What blocks during a detach, and for how long Plain DETACH takes an access exclusive lock on the parent table for the whole operation, so every query against the parent queues behind it, including reads of unrelated partitions. DETACH CONCURRENTLY takes a weaker lock, waits for existing transactions to drain, and lets reads and writes on other partitions continue throughout. ALTER TABLE … DETACH PARTITION ALTER TABLE … DETACH PARTITION … CONCURRENTLY reads of other partitions — BLOCKED writes to the current partition — BLOCKED the detach itself every query against the parent queues behind the ACCESS EXCLUSIVE lock, for its whole duration seconds — but the queue behind it is not reads of other partitions — continue writes to the current partition — continue the detach itself a weaker lock is taken, and the operation waits for existing transactions to drain between phases seconds to minutes, depending on open transactions The concurrent form is slower and blocks nothing. Its one requirement is that no transaction stays open indefinitely — an idle-in-transaction session will stall the second phase for as long as it lives.

Operational note: Check for idle-in-transaction sessions before starting: SELECT pid, state, xact_start FROM pg_stat_activity WHERE state = 'idle in transaction' ORDER BY xact_start;. One forgotten psql session can hold the detach for hours.

SRE tip: Set lock_timeout in the session anyway. The concurrent form takes weaker locks, not no locks, and a timeout turns a stall into a retryable failure rather than an unbounded wait.

Step 3 — Dump the detached table

The detached table is now an ordinary table and can be dumped on its own:

#!/usr/bin/env bash
set -euo pipefail

TABLE=events_2025_06
TARGET=s3://archive/events/${TABLE}.dump

pg_dump \
  --host="$PGHOST" --dbname=app \
  --table="public.${TABLE}" \
  --format=custom --compress=9 --no-owner --no-privileges \
  --file="/var/tmp/${TABLE}.dump"

sha256sum "/var/tmp/${TABLE}.dump" > "/var/tmp/${TABLE}.dump.sha256"
aws s3 cp "/var/tmp/${TABLE}.dump"        "$TARGET"
aws s3 cp "/var/tmp/${TABLE}.dump.sha256" "${TARGET}.sha256"

Operational note: --format=custom is required for selective restore and parallel restore later; a plain SQL dump of a 400 GB table is a single-threaded restore that runs for hours.

DBA tip: Dump from a replica if one exists. A 391 GB sequential read on the primary competes with live traffic for cache and I/O for the whole duration, and the archive does not care which node produced it.

Step 4 — Verify by restoring, not by looking at the file

A dump that exists is not a dump that works. Restore it into a scratch schema and compare:

-- on the source, before dropping
SELECT count(*)                                        AS rows,
       sum(hashtext(t::text)::bigint)                  AS checksum
FROM   events_2025_06 t;
# restore into a scratch database
createdb archive_verify
pg_restore --dbname=archive_verify --no-owner "/var/tmp/${TABLE}.dump"
-- in archive_verify, the same two numbers must match exactly
SELECT count(*)                       AS rows,
       sum(hashtext(t::text)::bigint) AS checksum
FROM   events_2025_06 t;
  rows     |      checksum
-----------+---------------------
 418223104 |  -8871220394112889
The verification gate, and what happens on each outcome After the dump is uploaded it is restored into a scratch database and compared with the source on row count and checksum. A match allows the drop to proceed and the space to be reclaimed. A mismatch leaves the detached table in place, alerts an operator, and loses nothing — which is the entire reason the drop is a separate step from the detach. detached tablestill on disk, off the parent dump + uploadcustom format, sha256 alongside restore & comparerows + checksum match → DROP391 GB returned mismatch → keepalert, nothing lost This is why detach and drop are never combined. Between them sits a state where the data is out of the way of the live table, still fully present, and cheap to put back — the only point in the process where a mistake costs nothing. Track the detached-but-not-dropped set as a metric. A partition sitting there for a week means archival is failing quietly and the disk saving you planned for is not happening.

Operational note: hashtext(t::text) is order-independent when summed, which is what makes it comparable across two servers with different physical row orders. It is not a cryptographic checksum and does not need to be.

SRE tip: For very large partitions, verify a deterministic sample (WHERE id % 100 = 0) plus the exact row count. Full-table checksums on a 400 GB partition cost an hour of CPU on both sides.

Step 5 — Drop and record

Only now is the drop safe, and it should record what happened:

Disk stays flat until the DROP — which is the point Detaching a 391 gigabyte partition frees nothing: the table still exists. Archiving it adds a temporary local dump before upload. Only the DROP returns the space, and it returns all of it at once. The window between detach and drop is deliberate — it is the period in which the operation is still reversible. +400 GB+200 GB0 DETACHdump written locallyverifyDROP If disk is already tight, stream the dump straight to object storage rather than writing it locally first — the temporary bump is the one part of this sequence that can fail for capacity reasons. 
BEGIN;
INSERT INTO partition_archive_log
      (table_name, row_count, checksum, archive_uri, archived_at, dropped_at)
VALUES ('events_2025_06', 418223104, -8871220394112889,
        's3://archive/events/events_2025_06.dump', now(), now());
DROP TABLE events_2025_06;
COMMIT;

The log row is what makes a restore possible two years later without archaeology: it names the object, the expected row count and the checksum to verify against.

Operational note: DROP TABLE on a detached partition returns the space at commit, and it is fast because it is a catalog change plus file unlinks — no row-by-row work regardless of size.

DBA tip: Keep the archive log forever. It is tiny, and it is the only record that a given month ever existed once the table is gone.

Verification

Confirm the parent is intact and the space came back:

-- the parent should now have one fewer child, and no gap in the remaining bounds
SELECT c.relname, pg_get_expr(c.relpartbound, c.oid) AS bounds
FROM   pg_class c JOIN pg_inherits i ON i.inhrelid = c.oid
WHERE  i.inhparent = 'events'::regclass
ORDER  BY c.relname
LIMIT  4;
    relname     |                              bounds
----------------+------------------------------------------------------------------
 events_2025_07 | FOR VALUES FROM ('2025-07-01 00:00:00+00') TO ('2025-08-01 00:00:00+00')
 events_2025_08 | FOR VALUES FROM ('2025-08-01 00:00:00+00') TO ('2025-09-01 00:00:00+00')

And confirm the query planner no longer considers the removed month, and that a query spanning the retention boundary returns rows only from the surviving range.

Failure mode table

Failure mode Root cause SRE mitigation
DETACH CONCURRENTLY never completes an idle-in-transaction session holds a snapshot the second phase must wait for find it in pg_stat_activity, terminate it, then run DETACH ... FINALIZE; set idle_in_transaction_session_timeout cluster-wide
Disk stays full after retention runs the partition was detached but never dropped because the archive step failed alert on detached tables older than seven days; drop only after a successful verify, and make the verify failure page someone
Archive is unrestorable when finally needed the dump was never restored, only written; a truncated upload or an unreadable format was never noticed verify by restoring every archive at creation time, and re-test one random archive per quarter

FAQ

What happens if DETACH CONCURRENTLY is interrupted?

The partition is left in a transitional state where it is neither fully attached nor fully detached, and pg_class shows relispartition still true while the parent no longer routes to it. Run ALTER TABLE parent DETACH PARTITION child FINALIZE to complete the operation. Until you do, further DDL on the parent will fail with an error naming the unfinished detach.

Do indexes and constraints survive the detach?

Indexes created on the parent and propagated to the child remain on the detached table but are no longer attached to the parent’s index. Foreign keys referencing the parent stop covering the detached rows immediately. That is usually what you want for an archive, but it means reattaching later requires re-validating the constraint, which scans the whole partition.

How do I verify the archive before dropping the table?

Compare a row count and an order-independent checksum computed from the live table against the same values computed from a restore of the dump into a scratch schema. Comparing file sizes proves only that bytes were written. The restore step is what proves the dump is readable, which is the property that matters at 3 a.m. two years from now.

Back to Partition Lifecycle Retention Management