Retention is safest when policy boundaries match physical deletion boundaries. Detach first, verify holds and propagation, then drop in a separate step.
Retention-Aligned Partition Removal
Retention becomes reliable when the physical deletion unit matches the policy boundary.
-- Lifecycle worker first proves the partition is expired and unheld.
BEGIN;
SELECT pg_advisory_xact_lock(hashtext('retention:request_logs_2026_05'));
ALTER TABLE request_logs
DETACH PARTITION request_logs_2026_05;
INSERT INTO retention_audit(partition_name, detached_at, policy_version)
VALUES ('request_logs_2026_05', now(), 'logs-v4');
COMMIT;
-- Verify downstream and hold constraints, then drop in a later step.
DROP TABLE request_logs_2026_05;Invariant: No unheld record remains queryable after its retention partition becomes eligible for removal.
Use when: High-volume time-series data must expire without a destructive row-delete storm.
Why this boundary matters
Row deletes at scale generate WAL, dead tuples, and replica pressure. Partition removal makes expiry bounded and inspectable.
Failure policy
| Boundary | Action |
|---|---|
| Partition is inside retention | Keep attached and queryable |
| Partition is expired with no hold | Detach, verify, then drop |
| Legal hold overlaps | Move or preserve held rows before removal |
| Replica lag is high | Pause lifecycle work |
| Deletion fails | Keep the partition detached and retry safely |
| Restore occurs | Replay deletion tombstones before serving |
Trade-offs
Partition deletion is fast and avoids row churn, but coarse partitions can retain data beyond policy and excessive partitions add catalogue overhead. Per-record holds complicate an otherwise clean physical boundary.
Decision rule: Partition by retention time when expiry volume is high and most records in a partition share the same policy and hold state.
Further reference
Browse all engineering snippets · Read the retention architecture