The events table has nine hundred million rows and a documented retention policy of eighteen months that has never been enforced, because enforcing it means deleting records that might be financial and might have to be kept for seven years. Nobody had established which, so nothing was deleted.
The symptom
$ mysql -e "SELECT table_rows,
ROUND((data_length+index_length)/1024/1024/1024,1) AS gb
FROM information_schema.tables WHERE table_name='events'G"
table_rows: 908,412,004
gb: 412.8
$ grep -rn 'retention' docs/
docs/data-retention.md:14: Events are retained for 18 months.
$ mysql -e "SELECT MIN(created_at) FROM events"
2019-03-04A documented policy of eighteen months and six years of data. The policy is not wrong and nobody has ever been able to act on it, because the table contains two kinds of record and the schema does not distinguish them.
Why it happens
A retention policy is a legal decision documented as a technical one. The technical work is a scheduled delete; the decision is which records are subject to which requirement, and that is a question engineering cannot answer and had never escalated.
The fix
Asking the actual question
asked three people, and got three answers:
legal seven years, for records evidencing a
financial transaction. everything else
is subject to the shorter period in the
privacy notice.
the contract two years, for everything, because that
is what the customer agreement says.
finance "keep it. storage is cheap and I have
been asked for 2020 figures twice."
all three are correct about different things, and the
binding one is the shortest applicable period for data
that identifies somebody.Six weeks of elapsed time went into reconciling three answers, which is the part of this work that is not engineering and is the part that had blocked it for six years. The technical implementation took four days.
The reconciliation, as a rule
an event attached to an order that has a payment
→ a financial record. seven years.
an event attached to an order with no payment
→ not financial. two years.
an event attached to no order at all
→ operational. eighteen months.
which is a JOIN rather than a column, and it had to
become a column — a retention rule that requires a
three-table join cannot be evaluated on 900 million
rows nightly.ALTER TABLE events
ADD COLUMN retention_class TINYINT UNSIGNED NULL,
ADD INDEX idx_retention (retention_class, created_at);
-- 1 = financial (7y), 2 = contractual (2y),
-- 3 = operational (18m)
-- backfilled in batches, and set at write time from
-- then on.Backfilling 900 million rows
UPDATE events e
LEFT JOIN orders o ON o.id = e.order_id
LEFT JOIN payments p ON p.order_id = o.id
SET e.retention_class = CASE
WHEN p.id IS NOT NULL THEN 1
WHEN o.id IS NOT NULL THEN 2
ELSE 3
END
WHERE e.retention_class IS NULL
AND e.id BETWEEN ? AND ?; -- 50,000 at a time batches 18,168
per batch ~1.4s, plus a sleep proportional to
replica lag
elapsed nine days, running continuously
replica lag p99 during the backfill: 380ms
and the guard: a job that alerts if the count of
unclassified rows stops decreasing, because a stalled
backfill on a nine-day job is otherwise invisible.Anonymising what must be kept
-- class 1 rows older than two years: keep the record,
-- remove the identification
UPDATE events
SET payload = JSON_SET(
JSON_REMOVE(payload, '$.customer_email', '$.customer_name',
'$.billing_address'),
'$.anonymised_at', NOW()
)
WHERE retention_class = 1
AND created_at < DATE_SUB(NOW(), INTERVAL 2 YEAR)
AND JSON_EXTRACT(payload, '$.anonymised_at') IS NULL
LIMIT 5000;The anonymised_at marker makes the batch idempotent, which matters because it runs nightly and will be interrupted. Keeping the record and removing the identification is what satisfies both requirements simultaneously — the financial evidence is the amounts and the dates, and the name is not part of it.
The deletion that actually runs
-- class 3, older than 18 months, by partition
ALTER TABLE events DROP PARTITION p2023_08;
-- except the table is partitioned by month, not by
-- retention class, so a partition contains all three.
--
-- which means: the drop only works for partitions
-- where every row is class 3 or is past seven years.
--
-- for the rest: a batched delete, 5,000 at a time,
-- nightly, which will take about four months to catch
-- up and then keeps pace.Partitioning by month and retaining by class means the partition drop only helps at the extremes, which is the design mistake made in 2023 by somebody who did not know a second retention class was coming. Repartitioning by class and month is possible and would be a nine-day rebuild of a four-hundred-gigabyte table for a saving that the nightly batch already delivers.
Verifying it worked
$ mysql -e "SELECT retention_class, COUNT(*),
ROUND(SUM(LENGTH(payload))/1024/1024/1024,1) AS gb
FROM events GROUP BY retention_class"
1 188,204,102 88.4
2 412,880,004 188.1
3 307,327,898 136.3
$ mysql -e "SELECT COUNT(*) FROM events WHERE retention_class IS NULL"
0
# four months of the nightly job
rows deleted: 188,204,000
rows anonymised: 41,208,000
table size: 412.8 GB → 291.4 GB
$ ./bin/retention-report
oldest class 3 row: 17 months ✓
oldest class 2 row: 23 months ✓
oldest class 1 row: 6 years, anonymised ✓The retention report asserting the oldest row per class is the check that the policy is actually being enforced rather than the job merely running, and it is three queries. Four months in, the table is shrinking for the first time in six years.
What this costs
A policy that is now enforced and will be wrong when the law changes, at which point nine hundred million rows have already been deleted under the old interpretation. That is the nature of retention and it is worth being explicit that this is irreversible — the rows classified as operational eighteen months ago are gone.
The partitioning also does not align with the retention classes, so most deletions are a batched delete rather than a partition drop. That is a decision from 2023 that cannot be cheaply undone, and it means the nightly job is permanent infrastructure rather than a one-off — with all the ways a nightly job silently stops.