Pilotbase running a catalog query that lists every column holding a user: six foreign keys and five columns found by name

A user deleted their account three weeks ago. Your app set deleted_at, hid the profile and moved on, which is what the soft-delete pattern is for. Then the e-mail arrives: they want their data erased. Not hidden, erased. Under the GDPR you have one month to answer (Article 12(3), extendable by two months for complex cases), and "we flagged the row" is not an answer.

Erasing one person turns out to be a question about your whole schema: which tables hold this user, in what order can they go, and which rows must stay because the tax office wants them for years. We built a lab schema, loaded the same 5.1 million rows into PostgreSQL 18.6, MySQL 8.4.11 and SQLite 3.54, and purged 2,000 soft-deleted accounts on each. Everything below was run and measured; the scripts are plain SQL plus a short Python loop.

The lab: 100,000 users, 2,400 soft-deleted

A small shop with a community attached. 2,400 users have deleted_at set; 2,000 of them are past a 30-day grace period (people do come back and ask for their account), so those are the ones to erase.

text
table                   rows       how it points at the user
users                   100,000    -
sessions                299,783    user_id, FK on delete cascade
addresses               100,025    user_id, FK
orders                  301,263    user_id, FK, plus a copy of the e-mail and the shipping name/address
order_items             752,513    order_id -> orders (on delete cascade)
comments                200,000    author_id, FK on delete set null
follows                 300,000    follower_id and followee_id, two FKs to users
audit_log             1,000,000    actor_id, no FK; staff rows name the user only in a text column
events                2,000,000    user_id, no FK, no index (analytics)
support_tickets          50,000    requester_email, no FK
newsletter_subscribers   60,000    email, no FK

That spread is normal. The foreign keys were added by whoever wrote the first migrations; analytics, the audit log, the help desk import and the newsletter list came later, and nobody wired them to users with a constraint.

Why `DELETE FROM users` is the wrong first move

The one-liner, run on each engine against the 2,000 accounts past their grace period:

sql
delete from users where deleted_at < '2026-10-02 09:00:00';
text
PostgreSQL 18:  ERROR: update or delete on table "users" violates foreign key constraint
                "addresses_user_id_fkey" on table "addresses"
MySQL 8.4:      ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key
                constraint fails (`lab`.`addresses`, CONSTRAINT `addresses_ibfk_1` ...)
SQLite 3.54:    (no error) 2,000 users deleted, leaving behind:
                  6,053 orders, 5,943 sessions, 2,000 addresses, 3,961 comments
                  and 11,785 follows pointing at users that no longer exist
                  PRAGMA foreign_key_check: 29,865 violations

PostgreSQL and MySQL refuse, which is the good outcome. SQLite doesn't check foreign keys unless the connection has run PRAGMA foreign_keys = ON; it's off by default for backwards compatibility and has to be set on every connection. With it on, SQLite refuses too (FOREIGN KEY constraint failed). With it off, you get a database where the personal data is still there, now attached to nobody, which is harder to find later, not easier.

And even where the delete is refused, look at what would have happened if addresses had been on delete cascade like sessions: the orders would still block it, and the 2 million analytics events, the audit log, the tickets and the newsletter list would never have been touched, because no constraint connects them to users. A clean DELETE with no error proves nothing about erasure.

Step 1: find every column that holds the user

Two questions for the catalog. First, which columns have a foreign key to users, and what does each do on delete. Second, which columns look like they hold a user but have no constraint: names like user_id, actor_id, owner_id, and anything with email or phone in it. On PostgreSQL, one query does both and also says whether the column has an index (the first column of some index), which matters in step 3:

sql
-- 1. every column with a foreign key to users, and what ON DELETE does
select c.conrelid::regclass as tbl, a.attname as col, 'fk' as found_by,
       case c.confdeltype when 'a' then 'no action' when 'r' then 'restrict' when 'c' then 'cascade'
                          when 'n' then 'set null' when 'd' then 'set default' end as on_delete,
       exists (select 1 from pg_index i where i.indrelid = c.conrelid and i.indkey[0] = a.attnum) as indexed
from pg_constraint c
join pg_attribute a on a.attrelid = c.conrelid and a.attnum = c.conkey[1]
where c.contype = 'f' and c.confrelid = 'users'::regclass
union all
-- 2. columns that look like they hold a user (by name) but have no foreign key
select format('%I.%I', k.table_schema, k.table_name)::regclass, k.column_name, 'name', null,
       exists (select 1 from pg_index i join pg_attribute a2 on a2.attrelid = i.indrelid and a2.attnum = i.indkey[0]
               where i.indrelid = format('%I.%I', k.table_schema, k.table_name)::regclass and a2.attname = k.column_name)
from information_schema.columns k
where k.table_schema = 'public' and k.table_name not in ('users', 'erasure_log')
  and (k.column_name ~ '(^|_)(user|actor|author|owner|member|customer|account|follower|followee)(_id)?$'
       or k.column_name ~ '(email|phone|ip_addr)')
  and not exists (select 1 from pg_constraint c join pg_attribute a on a.attrelid = c.conrelid and a.attnum = c.conkey[1]
                  where c.contype = 'f' and c.confrelid = 'users'::regclass
                    and c.conrelid = format('%I.%I', k.table_schema, k.table_name)::regclass and a.attname = k.column_name)
order by found_by, 1;
Pilotbase result grid: sessions, addresses, orders, comments and follows (twice) found by foreign key with their on-delete rule; orders.email, audit_log.actor_id, events.user_id, support_tickets.requester_email and newsletter_subscribers.email found by name; events.user_id not indexed

Eleven columns in nine tables. The name search found five the constraints didn't, including orders.email, a copy of the address taken at checkout that would have survived even a perfect cascade. It also found our own ledger table (erasure_log, below), which is why the query leaves it out. The name pattern is a net, not a proof: read the result, and grep the application code for tables it misses (a created_by or a recipient column would slip through this pattern).

The same two questions on MySQL 8.4, from information_schema (InnoDB creates an index on every foreign-key column itself, so the indexed check only matters for the name-found ones):

sql
-- 1. foreign keys to users (InnoDB indexes every FK column, so no indexed check needed)
select k.table_name as tbl, k.column_name as col, 'fk' as found_by, r.delete_rule as on_delete
from information_schema.key_column_usage k
join information_schema.referential_constraints r
  on r.constraint_schema = k.constraint_schema and r.constraint_name = k.constraint_name
where k.referenced_table_schema = database() and k.referenced_table_name = 'users'
union all
-- 2. columns that look like a user by name, with no foreign key
select c.table_name, c.column_name, 'name', null
from information_schema.columns c
where c.table_schema = database() and c.table_name not in ('users', 'erasure_log')
  and (c.column_name regexp '(^|_)(user|actor|author|owner|member|customer|account|follower|followee)(_id)?$'
       or c.column_name regexp 'email|phone|ip_addr')
  and not exists (select 1 from information_schema.key_column_usage k
                  where k.table_schema = c.table_schema and k.table_name = c.table_name
                    and k.column_name = c.column_name and k.referenced_table_name = 'users')
order by found_by, tbl, col;

And SQLite, where the schema lives in sqlite_schema and the pragmas are table-valued functions. There's no regexp unless an extension adds it, so the name list is spelled out:

sql
-- 1. foreign keys to users, from pragma_foreign_key_list on every table
select m.name as tbl, f."from" as col, 'fk' as found_by, f.on_delete
from sqlite_schema m, pragma_foreign_key_list(m.name) f
where m.type = 'table' and f."table" = 'users'
union all
-- 2. columns that look like a user by name, with no foreign key (SQLite has no regexp by default: LIKE/GLOB)
select m.name, c.name, 'name', null
from sqlite_schema m, pragma_table_info(m.name) c
where m.type = 'table' and m.name not in ('users', 'erasure_log')
  and (c.name in ('user_id', 'actor_id', 'author_id', 'owner_id', 'member_id', 'customer_id', 'account_id')
       or c.name like '%email%' or c.name like '%phone%')
  and not exists (select 1 from pragma_foreign_key_list(m.name) f where f."table" = 'users' and f."from" = c.name)
order by found_by, tbl, col;

All three returned the same eleven columns on the lab. Keep this list in the repository next to the purge code and re-run the query in CI or before each purge run: the day someone adds a wishlists.user_id without telling you, the list grows by one and the purge should fail loudly rather than quietly skip it.

Step 2: decide per table: delete, keep, or anonymise

The catalog can't make this call. Write it down per table before any code, because it is what you'll show if anyone asks how you handle erasure requests:

text
table                    decision                                        why
events, sessions,        delete                                          no reason to keep
follows, addresses,
newsletter_subscribers
audit_log (own actions)  delete                                          the user's own logins and exports
audit_log (staff rows)   redact the text, keep the row                   staff actions stay accountable
support_tickets          delete                                          (keep for a set period if you need
                                                                         them for a dispute, then delete)
comments                 delete                                          set null would keep text they wrote
orders, order_items      keep, anonymise: drop name, street, city,       tax records: amounts, VAT, dates and
                         e-mail and the link to the user                 the country stay

Orders are the reason erasure isn't a cascade. Article 17(3)(b) of the GDPR lets you keep what a legal obligation requires you to keep, and tax law does: HMRC wants VAT records for up to 6 years, and Germany's AO § 147 asks for 8 years for accounting vouchers such as invoices since January 2025 (10 before). What it doesn't excuse is keeping the person attached to the record when the numbers would do. Which fields your accountant needs is their call, not the database's; an issued invoice PDF may have to stay exactly as issued. In the lab we kept the amounts, VAT, order date, country and postcode, and removed everything else that names or reaches the person.

Two side notes from the same table. comments was on delete set null: had we let the cascade handle it, 3,961 comments would have stayed, signed by nobody but still containing whatever the person wrote. And follows has two columns pointing at users; the purge needs both, or other people's follower lists keep a ghost.

Step 3: the purge, children first, in batches

Same order on all three engines, 500 users per transaction, IDs passed as a list. The orders update comes before the users delete so the foreign key never sees a dangling row; users goes last and only for rows still past the grace period, in case someone restored their account mid-run:

sql
-- per batch of 500 ids (and their e-mails, collected from users BEFORE anything is deleted)
delete from events                 where user_id in (:ids);
delete from audit_log              where actor_id in (:ids);
delete from support_tickets        where requester_email in (:emails);
delete from newsletter_subscribers where email in (:emails);
delete from follows                where follower_id in (:ids);
delete from follows                where followee_id in (:ids);   -- two statements: OR defeats the index
delete from comments               where author_id in (:ids);
delete from sessions               where user_id in (:ids);
delete from addresses              where user_id in (:ids);
update orders set user_id = null, ship_name = null, ship_line1 = null, ship_city = null, email = null
                                   where user_id in (:ids);
delete from users                  where id in (:ids) and deleted_at < :cutoff;
insert into erasure_log (user_id, erased_at) values ...;          -- ids only, see below
commit;

Collect the e-mail addresses first. Three of the tables only know the user by e-mail, and once the users row is gone you can't look it up any more. For 2,000 users:

text
rows changed            PostgreSQL 18.6   MySQL 8.4.11   SQLite 3.54
events                       39,828          39,828        39,828
audit_log (own actions)      19,446          19,446        19,446
follows (both columns)       11,785          11,785        11,785
orders anonymised             6,053           6,053         6,053
sessions                      5,943           5,943         5,943
comments                      3,961           3,961         3,961
users                         2,000           2,000         2,000
addresses                     2,000           2,000         2,000
newsletter_subscribers        1,228           1,228         1,228
support_tickets                 999             999           999
total                        93,243 rows in 4 transactions on each engine

Then the check that makes it an erasure and not a hope: count what's left in every column from step 1, by id and by e-mail. On all three engines every count came back 0: no session, address, order link, order e-mail, comment, follow, audit row, event, ticket or newsletter row for any of the 2,000.

The one table without an index decides the speed

events.user_id had no index because nothing ever looked up events by user. The purge does, once per batch, and each lookup is a full scan of 2 million rows. Time for the whole purge of 2,000 users, without and then with create index on events (user_id) (which took 0.49 s on PostgreSQL, 1.1 s on MySQL, 0.55 s on SQLite):

text
purge of 2,000 users      no index on events.user_id     with the index
                          total     events step          total     events step
PostgreSQL 18.6           1.02 s    699 ms               0.70 s    263 ms
MySQL 8.4.11              3.72 s    2,630 ms             3.26 s    1,064 ms
SQLite 3.54               2.62 s    885 ms               2.27 s    427 ms
(PostgreSQL's very first run, cold cache: 4.99 s, of which events 2,588 ms)

In a batch job that's seconds either way. The difference shows when erasure runs the way requests actually arrive: one person at a time, from a support tool or a nightly job. Deleting one user's events (about 20 rows), median of 20 users:

text
one user, delete from events where user_id = ?   no index     with index
PostgreSQL 18.6                                  42.3 ms      0.1 ms
MySQL 8.4.11                                     393.3 ms     0.2 ms
SQLite 3.54                                      48.4 ms      0.2 ms

On MySQL the time is the smaller problem. InnoDB, at its default REPEATABLE READ, locks every index record a DELETE scans, not just the rows it deletes. We held the un-indexed delete for one user open and looked at performance_schema.data_locks, while a second session tried to update an unrelated event row:

text
MySQL 8.4.11, one user's events (22 rows)    record locks held    other session's update of another event
no index on events.user_id                   2,006,315            waited, then: Lock wait timeout exceeded
with the index                               46                   went through at once

Twenty-two rows deleted, the whole table locked against writers until commit. An erasure job doing that on a live analytics table stalls every event insert behind it. PostgreSQL's DELETE only locks the rows it actually deletes, so there it costs time, not blocking. Either way: any column the purge filters on gets an index, and the discovery query's indexed column tells you which ones are missing. For the same problem on foreign keys, where the slow part hides inside the constraint trigger, see Foreign keys without an index: slow deletes and lock waits.

What the column search can't see: free text

After the purge, every column from step 1 was clean. The data wasn't. 3% of the audit log is staff actions (email_changed by a support agent), and those rows have the agent in actor_id and the user only inside a text column: user 48211: ada.moss48211.old@example.com -> ada.moss48211@example.com. Deleting by actor_id never touches them.

text
after the purge, staff audit rows that still name an erased user     621 (same on all three engines)
found by: LIKE join, 30,145 staff rows x 2,000 e-mails                PG 78.1 s   MySQL 104.7 s   SQLite 125.5 s
found by: the user id at the start of details                         PG 36 ms    MySQL 298 ms    SQLite 71 ms

Two lessons from those 621 rows. Searching free text for e-mails is slow (60 million LIKE comparisons) and incomplete: the old address in that same row, .old@, isn't in users any more, so a search by the current e-mail finds the row but a replace() of the current e-mail would leave the old one in place. Redact the whole value, don't patch out the parts you know about. And if a text column is going to mention users, give it a structure you can query (here, the id at a fixed place, or better a subject_user_id column) so the next erasure is a 36 ms lookup instead of a scan.

sql
-- PostgreSQL: keep the staff row, drop the personal part of the text
update audit_log
   set details = split_part(details, ':', 1) || ': [erased]'        -- keeps 'user <id>'
 where action = 'email_changed'
   and split_part(substring(details from 6), ':', 1)::bigint in (select user_id from erasure_log);

On the lab that rewrote the 621 rows to user 58579: [erased] in 70 ms; afterwards no staff row for an erased id contained an @.

Ticket bodies, comment texts that mention someone by name, JSON blobs, uploaded files and anything copied into a search index or a data warehouse are the same problem. The database can find the columns; it can't read your free text for you. List those places once, by hand, next to the step 2 table.

Backups: the ledger that survives a restore

Erased rows stay in last week's backup. The UK regulator's guidance, for one, doesn't expect you to rewrite backups; it expects the copy there to be "put beyond use" until the backup is overwritten on its normal cycle, and you should tell the person that. A restore is where it goes wrong: the erased rows come back as live data unless the erasure is applied again. That needs a record of who was erased that isn't itself personal data in any useful sense: the lab's erasure_log holds the user id and the time, nothing else, no e-mail and no name (keeping the e-mail "to prove we erased it" would defeat the point).

sql
create table erasure_log (user_id bigint primary key, erased_at timestamptz not null);

-- after restoring any backup, before the app reconnects: run the purge for
select user_id from erasure_log;   -- from the CURRENT ledger, not the one in the restored copy

The comment is the important line. The ledger inside a restored backup is as old as the backup and doesn't know about later erasures; keep a copy outside the database (exported with each purge run, or in a separate small database), and make "re-run the purge from the ledger" a step in your restore runbook. If you use point-in-time recovery, the same applies: restoring to the second before a bad DELETE brings back every erasure after that second, too.

Checklist

text
1. grace period: erase only rows soft-deleted more than N days ago (we used 30)
2. discovery query: FKs from the catalog + columns by name; read it, add what it misses
3. a written decision per table: delete / keep anonymised (with the legal reason) / redact text
4. collect e-mails and phones BEFORE deleting users; three tables only know the e-mail
5. index every column the purge filters on (one missing index locked 2M rows on MySQL)
6. children first, both columns of self-referencing tables, users last, in batches
7. verify: count 0 in every discovered column, by id and by e-mail
8. free text: structured ids where you can, whole-value redaction where you can't
9. ledger of erased ids outside the database; re-apply after any restore
10. answer the person within a month, saying what was kept and why

If your accounts use deleted_at already, Soft deletes in SQL covers the queries that forget the filter and the unique keys that need it; the purge above is the step that finally removes those rows. And for bulk cleanup by date rather than by person, Deleting millions of rows without hurting production has the batch sizes measured.

Run the discovery query, read every table the user touches and check the counts after a purge in one grid, on PostgreSQL, MySQL, SQLite and more. Pilotbase is free for Windows, macOS and Linux, or Docker on a server.

Get Pilotbase

Purge slow on PostgreSQL? Paste the EXPLAIN ANALYZE of the DELETE into explainViewer: it lists the foreign-key triggers with their share of the time and names the child column that needs an index.

Open explainViewer

Sources: GDPR Article 17 (right to erasure; 17(3)(b) legal obligation) and Article 12(3) (one month, extendable by two); HMRC CH15100 (VAT records, retention period not exceeding 6 years); Stripe's summary of invoice retention in Germany (AO § 147, 8 years for accounting vouchers from 2025); PostgreSQL 18 documentation: pg_constraint (confdeltype), foreign keys (referencing columns are not indexed automatically); MySQL 8.4 Reference Manual: locks set by different SQL statements (a DELETE sets record locks on every index record it scans), KEY_COLUMN_USAGE, REFERENTIAL_CONSTRAINTS; SQLite foreign key support (off unless PRAGMA foreign_keys = ON, per connection); ICO guidance on the right to erasure (backups). Our test: PostgreSQL 18.6, MySQL 8.4.11 and SQLite 3.54 on an Apple-silicon Mac mini, November 2026; the same made-up 5.1 million rows on each (100,000 users, 2,000 erased), 500 users per transaction, times from a Python client on the same machine. This article describes database mechanics, not legal advice: retention periods depend on your country and business.