Data Retention Is a System Design Problem

Teams often treat retention as a cleanup query added after a product ships. By then the same customer data exists in the primary database, replicas, caches, search indexes, object storage, analytics tables, event logs, model features, exports, and backups.

Deletion is no longer a SQL statement. It is a distributed workflow with an evidence requirement.

The governing invariant is:

Data must not remain usable beyond its approved purpose and retention window, except under an explicit hold.

Define policy by data class #

“Keep data for 90 days” is incomplete. A retention record needs:

Do not reuse one duration across unrelated classes. Fraud evidence, transient prompts, invoices, and application telemetry have different purposes and risks.

Put expiry in the data model #

Compute deletion eligibility when the governing event occurs and store it explicitly:

ALTER TABLE audit_events
  ADD COLUMN expires_at timestamptz NOT NULL,
  ADD COLUMN retention_class text NOT NULL,
  ADD COLUMN legal_hold_id uuid;

CREATE INDEX audit_events_expiry_idx
  ON audit_events (expires_at)
  WHERE legal_hold_id IS NULL;

An explicit timestamp makes policy inspectable and avoids reconstructing meaning from mutable account state. Changes to policy can then be applied as versioned migrations with an audit trail.

Delete by partition when volume demands it #

Large row-by-row deletes create dead tuples, WAL volume, replica lag, and long cleanup cycles. Time partitioning turns expiry into a metadata operation when retention aligns with the partition key.

CREATE TABLE request_logs (
  occurred_at timestamptz NOT NULL,
  tenant_id uuid NOT NULL,
  payload jsonb NOT NULL
) PARTITION BY RANGE (occurred_at);

Detach an expired partition, verify that no hold applies, then drop it. Choose partition size from deletion cadence and operational cost—not arbitrary calendar aesthetics. Daily partitions may create catalogue overhead; monthly partitions may keep data longer than policy permits.

Partitioning is not the policy engine. Holds, per-tenant exceptions, and records with retention based on a later event may require a separate quarantine or tombstone workflow.

Track every derived copy #

Build a propagation ledger for each data class:

system of record
├── search index       deletion by document ID
├── analytics          partition expiry + subject tombstone
├── object storage     lifecycle rule + inventory verification
├── cache              bounded TTL
└── model features     dataset/version lineage + rebuild

Every edge needs an owner, delivery mechanism, retry policy, and reconciliation job. A deletion event alone is insufficient because consumers can miss it. Periodically compare authoritative tombstones with downstream state.

Prefer stable subject identifiers over copying personal attributes into event keys. That makes targeted deletion and evidence collection possible without searching arbitrary payloads.

Treat backups as delayed deletion #

Immutable backups should not be rewritten casually; doing so can damage recoverability. Instead:

• keep backup retention bounded and documented;
• encrypt backups with managed key lifecycle where appropriate;
• restrict restoration access;
• after restore, replay deletion tombstones before returning the environment to service;
• test that procedure during recovery exercises.

The honest guarantee may be “removed from active systems within 24 hours and expires from protected backups within 35 days.” Architecture and policy must say the same thing.

Make deletion observable #

Measure:

• eligible records awaiting deletion;
• age of the oldest overdue record;
• completion lag per downstream system;
• hold count and age;
• reconciliation mismatches;
• backup sets containing expired data;
• failed or poison deletion commands.

Avoid personal identifiers in metric labels. Keep detailed evidence in access-controlled audit records.

A deletion request should reach a terminal state only after required systems acknowledge or reconciliation proves absence. Partial success remains visible and retryable.

Conclusion #

Retention is an end-to-end data lifecycle, not a nightly cron. Model expiry explicitly, align physical storage with deletion units, trace derived copies, bound backup exposure, and collect evidence.

The cheapest byte to govern is the one you never collect. For everything else, deletion must be designed with the same care as creation.

References #

• PostgreSQL: Table Partitioning
• PostgreSQL: DROP TABLE
• NIST SP 800-88 Rev. 1: Media Sanitization