Implement a Data Retention Cleanup
Inventory data by class, define retention and deletion rules, dry-run the cleanup, delete in bounded batches, and verify while preserving audit evidence.
Objective
Remove data that is no longer needed while proving that legal holds and required records are preserved and that deletion is observable.
Environment
- A data inventory with owners and retention requirements
- Access to identify records by age or status without scanning the entire primary
- A dry-run mode and a way to restore or pause the job
- Legal or compliance approval for the retention schedule
Steps
1. Classify data and define retention
List tables, buckets, and logs with an owner, a retention period, and a legal or product reason. Mark records under legal hold.
Expected state: Every data class has a documented retention rule and an exception path.
2. Build a dry-run query
Select the records that would be deleted, count them by class and age, and compare with the expected volume.
Expected state: The candidate set is understood before any deletion occurs.
3. Delete in bounded batches
Delete by primary key ranges with a limit, commit per batch, and pause on error. Avoid one long transaction that locks the table.
Expected state: Deletion proceeds in small, restartable units with no user-facing lock spike.
4. Preserve audit and legal holds
Exclude held records and write an audit event per batch with the rule, count, and operator. Keep deletion evidence according to policy.
Expected state: Held data remains, and each deletion batch is traceable.
5. Verify and monitor
Check that expired rows are gone, required rows remain, and the job's lag or backlog is visible on a dashboard.
Expected state: Retention is proven by queries and the job is monitored for failure.
Decision points
Should deletion be hard or soft?
- Soft delete, then purge after a grace period
- Hard delete immediately
Use a grace period for user-facing data so accidental deletion can be reversed; purge held or regulated records only under policy.
Who approves a retention change?
- The data owner with legal review
- Engineering alone
Require the data owner and legal/compliance review; retention is a legal commitment, not only a storage optimization.
Expected outcome
Expired data is removed in controlled batches, held data is preserved, and the organization can demonstrate the retention policy is operating.
Verification
Run count queries for expired and held classes before and after, confirm the audit log contains one entry per batch, and verify the job pauses on a simulated error.
Cleanup
Archive the dry-run and batch records, update the data inventory, and schedule the next run.
Retention is a product and legal decision
Deleting data is easy; deleting the right data and proving it is hard. Start with a data inventory: what is stored, who owns it, why it is kept, and for how long. Legal holds and contractual obligations override the default schedule. Engineering should not invent retention periods in a migration script.
-- Dry run: count candidates by month
SELECT date_trunc('month', created_at) AS month, count(*)
FROM events
WHERE created_at < now() - interval '90 days'
AND legal_hold = false
GROUP BY 1
ORDER BY 1;Delete in small batches
A single DELETE FROM events WHERE created_at < ... can lock a large table, bloat storage, and generate a huge transaction. Delete by primary key ranges with a limit and commit each batch. Make the job restartable and idempotent: rerunning it after a failure must not double-delete or skip rows.
DELETE FROM events
WHERE id IN (
SELECT id FROM events
WHERE created_at < now() - interval '90 days' AND legal_hold = false
ORDER BY id
LIMIT 1000
);Prove it
Record the rule, candidate count, deleted count, and operator in an audit log. Verify held records remain and required records are intact. Monitor job lag and failures. A retention job that silently stops is a compliance problem; a retention job that deletes held data is an incident.