Retention is an operational change with business rules, not merely a DELETE statement. Begin with which records stay available, where history belongs, and how recovery works.
Retention is a controlled lifecycle
Establish the invariant
Specify periods and exceptions. Count retained and candidate records and independently verify the predicate. An archive or snapshot needs a tested recovery path; existence does not prove timely restoration.
Bound the work
Large deletes produce WAL, leave dead tuples, affect replicas, and can hold locks. Committed batches limit transaction scope, but do not guarantee immediate disk reclamation. Cascading foreign keys can make actual work larger than the visible batch.
Pause thresholds
Monitor lock waits, replication lag, storage headroom, transaction duration, and application latency. Define thresholds before starting and track progress durably.
Test alternatives
Partition removal is useful when retention matches boundaries, but may not fit an existing schema. Copying retained data requires handling writes, indexes, constraints, and cutover. A small local benchmark is insufficient.
References and further reading
Primary sources for the technical concepts in this article. The examples and decisions above are my synthesis, not quotations from these sources.
- PostgreSQL documentationRoutine vacuuming
Dead tuples, maintenance, and space reuse after deletes.
- PostgreSQL documentationExplicit locking
Lock modes and interactions during maintenance.