Soft Delete + Tombstones

The intuitive operation is DELETE FROM posts WHERE id = ?. In a single isolated database that may be fine. In a real system, that post may already be present in materialized feeds, Redis caches, search indexes, analytics events, backups, mobile sync clients, and downstream services. If the row vanishes before those systems learn about the deletion, stale copies can remain visible or even resurrect the data.

The common relational pattern is to add nullable deleted_atand sometimes deleted_by or delete_reason. User deletes become updates. Normal reads filter for active rows. Admin or audit views can explicitly include tombstones.

-- table shape
ALTER TABLE posts ADD COLUMN deleted_at timestamptz;
ALTER TABLE posts ADD COLUMN deleted_by bigint;

-- user delete
UPDATE posts
SET deleted_at = now(), deleted_by = $actor_id
WHERE id = $post_id AND deleted_at IS NULL;

-- every normal read must include the active-row predicate
SELECT id, author_id, body, created_at
FROM posts
WHERE author_id = $author_id
  AND deleted_at IS NULL
ORDER BY created_at DESC
LIMIT 50;

-- keep active-row queries fast and uniqueness sane
CREATE INDEX posts_active_author_created_idx
  ON posts (author_id, created_at DESC)
  WHERE deleted_at IS NULL;

CREATE UNIQUE INDEX usernames_active_unique_idx
  ON users (lower(username))
  WHERE deleted_at IS NULL;

Why partial indexes matter

If 40% of rows are deleted and every query scans both active and deleted rows, soft delete silently taxes every read. Partial indexes keep active rows compact. They also let you express business rules like "active usernames must be unique" while allowing old deleted rows to retain their historical values.

• Use scopes or repository methods: centralize thedeleted_at IS NULL predicate so one forgotten query does not leak deleted data.
• Make deletes idempotent: deleting an already-deleted row should be safe and return success or a clear no-op result.
• Record who and why: audit trails are much more useful when the tombstone includes actor and reason, not only a timestamp.

Soft delete pairs naturally with outbox and CDC. The transaction updates the row and writes an event such asPostDeleted. CDC then publishes that event so caches, timelines, search indexes, and blob cleanup workers remove their copies.

BEGIN;

UPDATE posts
SET deleted_at = now(), deleted_by = $actor_id
WHERE id = $post_id AND deleted_at IS NULL;

INSERT INTO outbox_events (aggregate_id, event_type, payload, created_at)
VALUES (
  $post_id,
  'PostDeleted',
  json_build_object('postId', $post_id, 'deletedBy', $actor_id),
  now()
);

COMMIT;

-- CDC publishes PostDeleted
-- feed service removes from timelines
-- search service removes from index
-- blob worker schedules media cleanup

Soft delete is not a license to keep data forever. Most systems define a retention window: perhaps 30 days for undo, 90 days for compliance review, or 7 years for financial audit metadata. After that, a garbage collection job hard-deletes rows and cleans up external objects.

DELETE FROM posts
WHERE deleted_at IS NOT NULL
  AND deleted_at < now() - interval '90 days'
ORDER BY deleted_at
LIMIT 1000;

-- run repeatedly in small batches
-- delete dependent rows or rely on ON DELETE CASCADE where appropriate
-- delete blobs only after the metadata tombstone has propagated

• Batch small: large hard-delete transactions create locks, replication lag, and vacuum pressure.
• Respect legal holds: add a hold flag or retention policy so GC does not remove data required for investigation or compliance.
• Separate PII from audit: some systems anonymize personal fields while keeping non-personal audit facts.

In eventually consistent databases, a delete marker prevents resurrection. Suppose replica A sees a delete but replica B was down. If A simply removes the value, B might later come back with an old value and repair could copy it back. A tombstone says "this key was deleted at time T", and that marker replicates just like a write.

Cassandra keeps tombstones for a grace period, commonly controlled bygc_grace_seconds. The grace period must be longer than the maximum time a replica can be down and still rejoin safely. Compacting tombstones too early risks old data coming back from a stale replica.

Social posts, user accounts, invoices, issue trackers, emails, and product catalog entries often use soft delete. The pattern supports "undo", moderation audit, account recovery, downstream cleanup, and safe replication. The cost is that every layer must understand active vs. deleted state.

• Authorization: deleted rows should generally be invisible to normal users but visible to admins or owners in recovery flows.
• Unique constraints: decide whether a deleted username or slug can be reused, then encode that decision with partial indexes.
• Foreign keys: child rows may also need tombstones, or they may remain for audit even after the parent is hidden.
• Backups: a hard-deleted row may still exist in backups for a retention period. Compliance promises must include backup policy.