Implementing erasure across backups, logs, and search indexes

TL;DR — Erasure is not a single DELETE FROM users WHERE id = ?; it is a workflow across the primary store, derived stores, logs, caches, and backups. The most reliable pattern is: delete or anonymize in live systems immediately, tombstone the subject ID, propagate deletion events to indexes/caches, and prevent backup restores from reintroducing erased data by running a post-restore purge job before the system serves traffic. Reading time: ~7 min

What it is and where it sits

In a real system, personal data does not live in one place. The request that created a user record probably also wrote an audit log line, emitted an event, fed a search index, populated a cache, and got swept into nightly backups. "Right to erasure" implementation is the architecture that coordinates removal or irreversible anonymization across all of those copies, with special handling for systems you cannot mutate in place, especially append-only logs and immutable backups.

This sits at the boundary between your application, your data platform, and your operational recovery process. It usually adds three things that many teams do not have yet:

1. a canonical deletion workflow in the app layer;
2. a deletion event or tombstone stream consumed by downstream systems;
3. a restore-time purge step for backups.

What it replaces: ad hoc "run this SQL and hope" deletions, one-off scripts against Elasticsearch/OpenSearch, and the dangerous assumption that backup retention alone satisfies erasure.

Typical flow:

[User] ---- 1. delete account ----> [App API]
[App API] ---- 2. tx: delete/anonymize ----> [Primary DB]
[App API] ---- 3. write tombstone ----> [Erasure Queue]
[Erasure Queue] ---- 4. purge docs ----> [Search Index]
[Erasure Queue] ---- 5. evict keys ----> [Cache]
[Erasure Queue] ---- 6. mask/delete ----> [Log Pipeline]
[Backup Restore] ---- 7. restore snapshot ----> [Staging/Prod DB]
[Staging/Prod DB] ---- 8. post-restore purge ----> [Serve Traffic]

The key architectural point: backups are not usually edited in place. You handle them by limiting retention, encrypting them, controlling access, and applying the erasure set immediately after restore and before any restored environment is reachable.

How it actually works

Walk one realistic example: a SaaS app stores users in Postgres, ships JSON logs to object storage via a log pipeline, and indexes user profiles into a search cluster. A user asks to be erased.

Step 1: delete or anonymize in the primary database

Do this in one transaction and record an erasure tombstone that downstream systems can consume. If foreign keys or legal retention rules prevent hard delete, replace identifying fields with irreversible placeholders and keep only the minimum record needed to prove the erasure request was processed.

⚠️ Hard deletes can break referential integrity, analytics, invoices, and fraud investigations. Run the exact SQL in a transaction on staging with production-like constraints before touching production.

BEGIN;

INSERT INTO erasure_tombstones (subject_type, subject_id, requested_at)
VALUES ('user', '8f6b2c1e-6d3e-4d5a-a3d2-2d2d8b7d0f11', now())
ON CONFLICT DO NOTHING;

UPDATE orders
SET customer_email = NULL,
    customer_name = NULL,
    shipping_address = NULL
WHERE user_id = '8f6b2c1e-6d3e-4d5a-a3d2-2d2d8b7d0f11';

DELETE FROM sessions
WHERE user_id = '8f6b2c1e-6d3e-4d5a-a3d2-2d2d8b7d0f11';

DELETE FROM users
WHERE id = '8f6b2c1e-6d3e-4d5a-a3d2-2d2d8b7d0f11';

COMMIT;

If the DELETE FROM users fails with a foreign key error like this:

ERROR:  update or delete on table "users" violates foreign key constraint "invoices_user_id_fkey" on table "invoices"
DETAIL:  Key (id)=(8f6b2c1e-6d3e-4d5a-a3d2-2d2d8b7d0f11) is still referenced from table "invoices".

that is your signal to switch that table to anonymization, not to disable the constraint.

Step 2: emit a deletion event

Downstream systems should not infer erasure by polling the database. Publish an explicit event with the stable subject ID and any secondary keys used elsewhere, such as email hash, username, or document IDs.

Example payload:

{
  "type": "privacy.user_erased",
  "subject_id": "8f6b2c1e-6d3e-4d5a-a3d2-2d2d8b7d0f11",
  "search_doc_ids": ["user:8f6b2c1e-6d3e-4d5a-a3d2-2d2d8b7d0f11"],
  "cache_keys": ["user:8f6b2c1e-6d3e-4d5a-a3d2-2d2d8b7d0f11", "profile:alice"],
  "requested_at": "2026-10-30T12:00:00Z"
}

Step 3: remove from search indexes and caches

Search is a derived store. Delete by document ID if you have it; it is faster and safer than delete-by-query. Then refresh or wait for refresh before declaring completion, otherwise the document can still appear briefly.

curl -sS -X DELETE "http://search.internal:9200/users/_doc/user:8f6b2c1e-6d3e-4d5a-a3d2-2d2d8b7d0f11?refresh=wait_for"

Typical success shape:

{"_index":"users","_id":"user:8f6b2c1e-6d3e-4d5a-a3d2-2d2d8b7d0f11","_version":4,"result":"deleted","_shards":{"total":2,"successful":1,"failed":0},"_seq_no":9812,"_primary_term":7}

If you get this instead:

{"_index":"users","_id":"user:8f6b2c1e-6d3e-4d5a-a3d2-2d2d8b7d0f11","result":"not_found","_shards":{"total":2,"successful":1,"failed":0}}

that is usually fine; treat it as idempotent success.

For Redis cache eviction:

redis-cli DEL user:8f6b2c1e-6d3e-4d5a-a3d2-2d2d8b7d0f11 profile:alice

A return value of 0 means the key was already absent, not that the erase failed.

Step 4: handle logs as a pipeline problem, not a file-editing problem

If your app logs raw emails, names, or addresses, you already lost the easy path. The practical pattern is:

• stop new leakage at the application logger or collector;
• set short retention for high-risk logs;
• for searchable log stores, delete matching records if the platform supports it;
• for immutable object storage archives, rely on retention expiry plus access controls, and document that they are excluded from normal processing.

The engineering fix starts in code: replace direct logging of PII with stable internal IDs.

Step 5: prevent backups from resurrecting erased data

This is where many teams fail. You usually cannot surgically edit old snapshots. The defensible implementation is:

1. keep an erasure_tombstones table or export outside the restored dataset;
2. after restore, before opening traffic, run a purge job against the restored database using the tombstone set;
3. rebuild derived stores from the purged database, not from the raw snapshot.

A restore runbook should literally include a blocking step: "Do not attach the restored DB to production or staging ingress until post_restore_erasure.sql exits 0." If you skip that, a support engineer can accidentally reintroduce erased users during disaster recovery.

When to use it (and when not to)

You probably do not need a dedicated erasure service if all of these are true: one primary datastore, no search index, no analytics copies, no long-lived caches, and backups are short-lived and tightly controlled. In that case, a transactional delete/anonymize path plus a restore-time purge script is often enough.

Trade-offs

Benefit and cost come together here:

• Fast removal from live systems — users disappear quickly from app views and search. Cost: more moving parts: queue consumers, retries, observability, and idempotency.

• Defensible backup story — you can explain exactly how erased data is prevented from reappearing after restore. Cost: restore runbooks get slower and more complex; recovery time objective increases.

• Lower privacy risk in logs — structured logging with IDs instead of emails reduces blast radius. Cost: debugging gets harder unless you build internal lookup tools with strict access control.

• Selective anonymization preserves required business records — invoices and ledger rows remain valid. Cost: schema work, application branching, and careful decisions about what is truly non-identifying.

• Idempotent deletion events — retries are safe. Cost: every consumer must treat not_found and duplicate messages as normal, not as pager-worthy failures.

The big lock-in risk is not vendor lock-in; it is process lock-in. Once your legal/compliance position depends on tombstones and post-restore purge, your backup and DR procedures must always honor that contract.

In practice

Example 1: Postgres transaction plus tombstone export

psql "$DATABASE_URL" -v ON_ERROR_STOP=1 <<'SQL'
BEGIN;
INSERT INTO erasure_tombstones (subject_type, subject_id, requested_at)
VALUES ('user', '8f6b2c1e-6d3e-4d5a-a3d2-2d2d8b7d0f11', now())
ON CONFLICT DO NOTHING;
UPDATE invoices SET bill_to_email = NULL WHERE user_id = '8f6b2c1e-6d3e-4d5a-a3d2-2d2d8b7d0f11';
DELETE FROM users WHERE id = '8f6b2c1e-6d3e-4d5a-a3d2-2d2d8b7d0f11';
COMMIT;
SQL

This runs the live erase atomically and exits non-zero on the first SQL error because of -v ON_ERROR_STOP=1. Gotcha: if you forget that flag in automation, psql can continue after an error and leave you with a half-applied workflow.

To export tombstones for restore-time purge:

psql "$DATABASE_URL" -Atc "COPY (SELECT subject_id FROM erasure_tombstones WHERE subject_type='user') TO STDOUT" > erased-users.txt
echo $? 

Exit code 0 means the export completed. Store this file outside the database snapshot set, or restoring the snapshot will also roll back the tombstone history.

Example 2: Search delete and verification

curl -sS -o /tmp/delete.json -w '%{http_code}
' -X DELETE "http://search.internal:9200/users/_doc/user:8f6b2c1e-6d3e-4d5a-a3d2-2d2d8b7d0f11?refresh=wait_for"
cat /tmp/delete.json
curl -sS "http://search.internal:9200/users/_search?q=_id:user:8f6b2c1e-6d3e-4d5a-a3d2-2d2d8b7d0f11"

This deletes a single document and then verifies it no longer matches by _id. Gotcha: if your app searches through an alias spanning multiple indexes, verify against the alias too; deleting from one concrete index can still leave a duplicate in another.

Example 3: Restore-time purge script

⚠️ Run this before the restored database is reachable by app traffic, background workers, or BI tools. If you expose the DB first, erased records can be reindexed or re-cached before the purge finishes.

#!/usr/bin/env bash
set -euo pipefail
DB_URL="$1"
TOMBSTONES_FILE="$2"

psql "$DB_URL" -v ON_ERROR_STOP=1 <<'SQL'
CREATE TEMP TABLE restore_erased_users (id uuid PRIMARY KEY);
SQL

psql "$DB_URL" -v ON_ERROR_STOP=1 -c "\copy restore_erased_users FROM '$TOMBSTONES_FILE'"
psql "$DB_URL" -v ON_ERROR_STOP=1 <<'SQL'
BEGIN;
UPDATE orders o
SET customer_email = NULL, customer_name = NULL, shipping_address = NULL
FROM restore_erased_users e
WHERE o.user_id = e.id;
DELETE FROM sessions s USING restore_erased_users e WHERE s.user_id = e.id;
DELETE FROM users u USING restore_erased_users e WHERE u.id = e.id;
COMMIT;
SQL

This loads the external erasure set into a temp table and purges the restored DB before service startup. Gotcha: \copy runs client-side and needs the file on the machine executing psql; plain COPY runs server-side and often fails with file permission errors.

Further reading

• GDPR Article 17 (Right to erasure)
• EDPB Guidelines 01/2022 on data subject rights
• PostgreSQL Documentation: DELETE, UPDATE, and COPY
• Elasticsearch Reference: Delete API and Refresh parameter
• OWASP Logging Cheat Sheet

This article was written by an AI system and published pending human review. Verify anything you intend to act on.