Implementing Data Retention Without Breaking Your Reports

The tension

GDPR's storage limitation principle says personal data should not be kept longer than necessary. Meanwhile, the most-requested analytics feature is year-over-year comparison.

These are reconcilable, because they want different things. Compliance is about row-level data that could identify someone. Reporting mostly wants aggregates, which do not.

Aggregate before you delete

Roll up first, then delete the detail:

INSERT INTO daily_stats (site_id, date, visitors, pageviews, sessions)
SELECT site_id, CAST(timestamp AS DATE),
       COUNT(DISTINCT user_id), COUNT(*), COUNT(DISTINCT session_id)
FROM events
WHERE timestamp < ? AND timestamp >= ?
GROUP BY site_id, CAST(timestamp AS DATE)
ON CONFLICT (site_id, date) DO NOTHING;

Now a raw event can be deleted after ninety days while the daily trend survives for years. The identifiers are gone; the counts remain. That is exactly the shape the regulation is aiming for.

Verify the rollup landed before deleting. Deleting first and discovering the aggregate failed is unrecoverable.

Per-site policies

Retention is not one number. A customer in a regulated industry may want thirty days; another may want two years. Store it per site:

CREATE TABLE data_retention_policies (
  site_id      VARCHAR(64) PRIMARY KEY REFERENCES sites(id) ON DELETE CASCADE,
  enabled      BOOLEAN NOT NULL DEFAULT false,
  retain_days  INTEGER NOT NULL DEFAULT 365
);

Default to disabled. Silently deleting data because a default was set is the kind of surprise that ends a customer relationship.

Delete in batches

A DELETE covering millions of rows holds locks and bloats the WAL:

DELETE FROM events
WHERE ctid IN (
  SELECT ctid FROM events
  WHERE site_id = ? AND timestamp < ?
  LIMIT 10000
);

Loop until no rows are affected. Slower in wall-clock terms, invisible to everyone using the system meanwhile.

The sweep

A scheduler that walks sites with an enabled policy every few hours is enough — retention is not urgent to the hour. Sites without a policy are untouched, so the loop is a no-op on most deployments.

One failing site must not stop the sweep. Catch per site, log, continue.

Visitor ID rotation

Retention alone is not the whole privacy story. Rotating the visitor identifier after a period of inactivity bounds how far back any single visitor can be correlated, even within the retention window.

A sliding window is the right shape: it resets on each visit, so a regular visitor is never re-counted mid-stream, and it only elapses after genuine absence.