Data Retention & Legal Holds

Retention policies collide: GDPR right to erasure, SOC2 audit trails, and legal hold litigation freezes. Postgres implements policy through partitioning, soft delete, and immutable audit tables with clear ownership.

Recipe

Quick-reference recipe card - copy-paste ready.

-- Retention metadata on partitioned events
CREATE TABLE events (
  id bigint GENERATED ALWAYS AS IDENTITY,
  tenant_id uuid NOT NULL,
  occurred_at timestamptz NOT NULL,
  payload jsonb NOT NULL,
  PRIMARY KEY (id, occurred_at)
) PARTITION BY RANGE (occurred_at);
 
-- Legal hold flag (separate table avoids rewriting history)
CREATE TABLE legal_holds (
  subject_type text NOT NULL,
  subject_id uuid NOT NULL,
  hold_until date,
  PRIMARY KEY (subject_type, subject_id)
);

When to reach for this: GDPR erasure requests, finance audit retention, litigation hold, or log table disk growth.

Working Example

Customer requests deletion under GDPR. Finance requires 7-year invoice retention.

-- Pseudonymize PII in shared invoice row (retain financial facts)
UPDATE customers
SET email = 'deleted-' || id::text || '@invalid.local',
    name = 'REDACTED',
    deleted_at = now()
WHERE id = $1;
 
-- Block delete if legal hold active
SELECT 1 FROM legal_holds
WHERE subject_type = 'customer' AND subject_id = $1
  AND (hold_until IS NULL OR hold_until >= current_date);

-- Drop old event partitions past retention (no hold rows in partition)
ALTER TABLE events DETACH PARTITION events_2023_q1;
DROP TABLE events_2023_q1;

What this demonstrates:

• Pseudonymization satisfies some erasure requests while keeping invoices.
• Legal hold checked before destructive DML.
• Partition detach/drop is the scalable retention mechanism.
• Policy lives in product + legal docs, enforced in SQL jobs.

Deep Dive

Policy Matrix

GDPR Delete Implementation

-- Soft delete pattern
ALTER TABLE customers ADD COLUMN deleted_at timestamptz;
CREATE INDEX customers_active_idx ON customers (id) WHERE deleted_at IS NULL;
 
-- RLS hides deleted rows from app role
CREATE POLICY customers_active ON customers
  FOR SELECT TO app_role
  USING (deleted_at IS NULL);

Hard delete cascades only when legal confirms no hold and no statutory retention.

Audit Immutability

REVOKE UPDATE, DELETE ON audit_log FROM PUBLIC;
GRANT INSERT ON audit_log TO app_audit_role;
-- Optional: pgaudit or trigger to append-only enforcement

Retention Job

# Monthly cron: drop partitions older than policy
psql -c "SELECT drop_expired_event_partitions(24);"  # months

Gotchas

• DELETE without legal review - Spoliation risk during litigation. Fix: Hold table + legal workflow ticket.
• Soft delete without RLS - Apps still leak "deleted" rows. Fix: RLS or views with deleted_at IS NULL.
• Erasure in backups - Backups retain data until expiry. Fix: Document backup retention; crypto-shred policy.
• CASCADE surprise - GDPR delete removes invoices unintentionally. Fix: FK ON DELETE RESTRICT on regulated children.
• Full table DELETE for retention - Locks and bloat. Fix: Partition drop or batch delete with key ranges.

Alternatives

FAQs

Related

• Stakeholder Basics - executive framing
• Row-Level Security - tenant isolation
• pgaudit and Compliance Logging - audit trail
• Partitioning Basics - time retention

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.