data-retention-policy-lifecyclelisted

# Data Retention Policy Lifecycle ## What A five-phase approach to implementing data retention policies in PostgreSQL: size analysis, rule definition, maintenance procedure, scheduled execution, and validation with rollback. ## Why Tables like audit logs, error logs, and shift history grow unbounded. Without a retention policy, they consume storage, slow backups, and degrade query performance. A proper retention lifecycle ensures data is purged safely, automatically, and with full audit trail. ## How ### Phase 1: Size analysis ```sql -- How big is the table? How fast is it growing? SELECT pg_size_pretty(pg_total_relation_size('"ShiftAuditLog"')) AS total_size, (SELECT COUNT(*) FROM public."ShiftAuditLog") AS row_count, MIN("CreatedDate") AS oldest_row, MAX("CreatedDate") AS newest_row FROM public."ShiftAuditLog"; ``` ### Phase 2: Rule definition Document the retention rule with business justification: ```markdown | Table | Retention | Justification | |-------|-----------|---------------| | ShiftAuditLog | 90 days | Audit requirement: 90-day lookback | | ErrorLog | 30 days | Debugging window: 30 days max | | RefreshTokens | 7 days past expiry | Expired tokens have no value | ``` ### Phase 3: Maintenance procedure ```sql CREATE OR REPLACE PROCEDURE maintenance.purge_shift_audit_log( p_retention_days int DEFAULT 90 ) LANGUAGE plpgsql AS $$ DECLARE v_cutoff timestamptz; v_deleted bigint; BEGIN v_cutoff := now() - (p_retention_days || ' days