Implementing a retention schedule as deletion jobs

Goal

Turn a written retention schedule (for example: session records 30 days after last activity, support tickets two years after closing) into jobs that delete on time, verifiably, without taking the service down. Which periods are right for which data is an organisational decision and not part of this method.

Prerequisites

A retention table: one row per data class with the store, the clock that starts the period (creation, last activity, closure), the period and the owner. Every table or bucket holding personal data maps to one row. Timestamps the period can be computed from (a closed_at column, not a status flag without a date).

Steps

1. Choose the mechanism per store. Time-partitioned tables: drop or detach the expired partition; the PostgreSQL documentation states that DROP TABLE or ALTER TABLE DETACH PARTITION on a partition is far faster than a bulk operation and avoids the VACUUM overhead of a bulk DELETE, that both require an ACCESS EXCLUSIVE lock on the parent table, and that DETACH PARTITION ... CONCURRENTLY needs only a SHARE UPDATE EXCLUSIVE lock. Object storage: lifecycle rules; the Amazon S3 documentation describes expiration actions that delete expired objects on the account's behalf. Everything else: a batched DELETE ... WHERE clock < now() - interval with a fixed row limit per iteration and a pause between iterations, the limit chosen from the store's observed load.
2. Write the job as idempotent and resumable: each run selects the next batch of expired rows, deletes, records the count and exits; a crash mid-run costs nothing.
3. Delete dependants first, or rely on ON DELETE CASCADE deliberately and list the cascade in the retention table.
4. Schedule the job with a lock so that two instances never run concurrently; emit per run: rows examined, rows deleted, age of the oldest remaining row.
5. Alert on the oldest remaining row: if any row is older than period plus a grace margin, the job is broken, whether or not it reports errors.
6. Start in dry-run mode: count what would be deleted, compare with expectation, then enable.
7. Propagate: derived stores (search index, analytics tables, caches) either expire by the same period on their own or subscribe to the deletion events.

Expected result

Every data class has a job or a lifecycle rule, a metric and an alert; the oldest-row age stays below the period; retention is a property of the running system rather than of a document.

Limits and test basis

Backups are outside the schedule; a backup's own retention bounds how long deleted data survives. Records under a hold need a per-record exemption flag the job honours; which cases need one is not addressed here. Lock and lifecycle behaviour follows the cited documentation; no throughput figures are claimed.