How to Delete a User's Data from PostgreSQL Without Breaking the Database

A deletion request lands and someone writes DELETE FROM users WHERE id = 42;. That is usually the moment the trouble starts, because the user's data is spread across twenty tables, some of which reference rows you just deleted. This is the working method for deleting one user's data from Postgres without breaking the app.

Step 1: Find every table that holds their data

Before any DELETE, you need a list of the tables that store data about this person. Start with the obvious ones and follow the foreign keys:

• users and user_profiles
• sessions, refresh_tokens, password_resets
• orders, subscriptions, invoices
• user_events, activity_logs
• cache-like tables, search indexes, and anything keyed by user_id

The reliable way to build this list is the schema itself. Query information_schema for foreign keys pointing at your users table, then walk the chain. Do not rely on memory. This is the step where a data map earns its keep: if you already recorded which tables hold personal data and the identifiers, this step is a lookup instead of an investigation.

Step 2: Understand the foreign-key web before deleting

Your tables reference each other. If orders.user_id references users.id, you cannot delete the user while orders still point at them, unless the constraint is ON DELETE CASCADE.

Check the actual constraints:

SELECT tc.table_name, kcu.column_name,
       ccu.table_name AS foreign_table
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
  ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage ccu
  ON tc.constraint_name = ccu.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
  AND ccu.table_name = 'users';

This tells you which child tables reference users. Then the rule is simple: delete children before parents. If orders references users, delete the user's orders first, then the user. If a child table uses ON DELETE CASCADE, deleting the parent cleans it up automatically, but make sure the cascade does not wipe data you wanted to keep—or worse, data about other users.

Step 3: Delete children in dependency order, in a transaction

Wrap the whole operation in a transaction so a failure halfway through leaves nothing half-deleted:

BEGIN;

DELETE FROM user_sessions WHERE user_id = 42;
DELETE FROM orders WHERE user_id = 42;
DELETE FROM user_profiles WHERE user_id = 42;
DELETE FROM users WHERE id = 42;

COMMIT;

If anything fails, ROLLBACK and nothing is touched. Long transactions hold locks, so keep the transaction short and avoid mixing in work that is not part of this deletion.

For large tables, deleting tens of thousands of rows in one statement can lock the table for a long time. Delete in batches:

DELETE FROM user_events
WHERE user_id = 42
  AND id IN (
    SELECT id FROM user_events
    WHERE user_id = 42
    LIMIT 5000
  );

Repeat until the table is empty for that user, then move to the next table.

Step 4: Make sure the identifier is indexed

Every WHERE user_id = 42 clause in your deletion plan only performs if there is an index on user_id. Without one, Postgres scans the whole table for every DELETE. Add indexes on the identifier column for every table you delete against. A missing index turns a five-minute deletion into a five-hour one.

Step 5: Dry-run before you run

Before deleting anything real, run a SELECT with the exact same WHERE clause and count:

SELECT COUNT(*) FROM orders WHERE user_id = 42;

If the count looks wrong, the match is wrong. Count every table before you delete any of them. A dry-run that says "0 rows" in a table you expected to hit is a sign your identifier is wrong for that table, not a sign there is nothing to do.

Erasure's SQL connectors build deletion plans from a Data Map and let you run an execution preview that only counts matching rows—it never deletes. You validate the counts, then let a durable job run the parameterized deletes.

Step 6: Decide what happens to backups and caches

Deleting rows from the source of truth is not the end. The user's data may also live in:

• caches and materialized views,
• search indexes,
• backups that restore it later,
• derived tables you forgot to map.

Backups are the hard one. If a restore happens after your deletion, the data comes back. Your retention policy is the tool here: define how long backups are kept, and accept that within that window, a restore can resurrect deleted data. That is a retention decision, not a bug in your SQL.

The principle underneath it all

A safe deletion has four parts: you know every table the data lives in, you delete in dependency order inside a transaction, you dry-run with real counts, and you handle the places the data survives (caches, indexes, backups). Skip any one of those and the deletion is either incomplete or dangerous.

Erasure operationalizes this exact loop: a Data Map records which tables to delete or skip and the identifiers to match on, an execution preview shows the row counts before anything runs, and a durable worker job runs the deletes with honest outcomes. The deletion requests guide walks the full workflow, and the right to erasure guide covers the legal side. The data deletion hub ties it together.