Partition retention: detach, prove the archive, then delete

Last updated: 5 Oct 20266 min read

tutorial

A partitioned table can retire a time range by detaching or dropping its partition. This is faster than deleting rows one at a time, but it changes query reachability for every row in that range. Retention rules, legal holds, downstream readers, and backup copies must be checked before a partition is removed. A detached partition still occupies storage and may need explicit access controls.

Operational decision

An audit service keeps online records for eighteen months and archives older monthly partitions. Build a manifest containing partition name, date bounds, row count, checksum sample, active hold flags, and expected archive object. Disable new writes into the retiring range, then detach the partition through the version-appropriate PostgreSQL procedure in a maintenance window. The text contract deliberately leaves the actual destructive SQL to an approved migration because the target partition and lock behavior depend on the environment. Verify the archive can be restored and queried in an isolated database before scheduling the detached table for deletion. Check that no query or downstream CDC consumer still depends on the former parent-table route. If a hold is found, stop at the detached state and preserve access under the hold policy. Record a second approval for deletion after the rollback window ends. A completed export job is not proof that its data is complete or restorable.

Audit partition retirement gate
Partition: exact month and bound manifest
Precondition: no new writes and no active hold
Detach: approved migration, lock and query impact observed
Archive: row count, digest sample, restore and query pass
Delete: separate decision after rollback window and reader audit

Cost and verification

Keeping detached partitions and archive copies costs storage. Dropping too early can make a lawful or operational restore impossible; keeping everything online can raise query planning and maintenance cost. Detach and drop have different lock behavior, which varies by PostgreSQL version and method. Measure user query latency during the change and confirm the parent table no longer routes to the retired range. A retention calendar is a scheduling input, not deletion authorization by itself.

Common Mistakes

Do not drop a partition before verifying its archive can be restored.
Do not overlook legal holds or consumers that still read the old range.
Do not assume detaching a partition also frees its storage.

Connected lessons

Practice and check

devops

operations