Soft Deletes Without the Footguns

A soft delete is one column and a good intention, and that is exactly why it goes wrong. Add a bare deleted_at to a busy table and you have quietly broken your unique constraints, leaked tombstoned rows into every query in the codebase, and left your foreign keys pointing at records the application swears no longer exist.

Soft delete is worth doing. Regulated firms rarely get to hard-delete a customer record the moment someone clicks a button — there are retention duties, audit trails, and the ordinary need to answer “what did this look like last Tuesday”. But the naive version causes more incidents than it prevents. What follows is the pattern I actually deploy on PostgreSQL 17, the three things it fixes, and the point at which you should stop soft-deleting and move the rows to an archive table instead.

The column that starts the trouble

Start with the column itself. Use a nullable timestamp, not a boolean. A boolean is_deleted tells you a row is gone; a nullable deleted_at tells you a row is gone and when, which is the fact a supervisor or a retention policy will eventually ask for. NULL means live; a timestamp means tombstoned.

-- Live rows have deleted_at IS NULL. Deleted rows carry the timestamp.
ALTER TABLE account
  ADD COLUMN deleted_at timestamptz;

That single line is where most implementations stop, and it is where the three footguns are armed. Take them in order, because each has a specific fix.

Footgun one: uniqueness that no longer holds

Say account.email is unique. A user closes their account, you set deleted_at, and they sign up again with the same address. Now you have two rows with the same email, and your UNIQUE (email) constraint has stopped the second one dead — even though the first is meant to be gone. The application logically deleted the row; the constraint did not get the memo.

The fix is a partial unique index scoped to live rows. Uniqueness is enforced only where deleted_at IS NULL, so tombstoned duplicates are simply not counted.

-- Drop the plain constraint first if one exists, then:
CREATE UNIQUE INDEX account_email_live_uniq
  ON account (email)
  WHERE deleted_at IS NULL;

One live account per email; any number of deleted ones. This is the single most important line in the whole pattern, and the one most naive implementations are missing. If your uniqueness spans several columns, list them all in the index; the WHERE clause stays the same. PostgreSQL has supported NULLS NOT DISTINCT on unique indexes since version 15 if you also need to treat null business keys as colliding — but for the soft-delete predicate itself, the partial index is what you want.

Footgun two: deleted rows leaking into every query

The second failure is subtler and far more common in production: every query in the codebase now has to remember to add AND deleted_at IS NULL. Miss it in one report, one join, one background job, and deleted records reappear where they should not — in a customer count, a billing run, an export to a regulator. “Remember to filter everywhere” is not a control; it is a latent incident waiting for the one developer who forgets.

Make the default safe instead of the exception. The cleanest option is a view that exposes only live rows, and to point the application at the view rather than the base table.

-- Application reads and writes account_live; base table stays hidden.
CREATE VIEW account_live
  WITH (security_barrier = true) AS
  SELECT * FROM account
  WHERE deleted_at IS NULL;

A simple view like this is automatically updatable in PostgreSQL, so INSERT and UPDATE flow through to the base table; the deletion itself becomes an UPDATE account_live SET deleted_at = now(). The security_barrier flag stops a cleverly written user function in a query from seeing rows the view is meant to hide. This is the same one-table-many-views discipline I have written about for dynamic data masking in the warehouse: the base table is the source of truth, and access goes through a controlled surface.

Where you cannot repoint every consumer at a view — a shared database with many teams, or a BI tool that insists on hitting real tables — use row-level security so the filter travels with the table itself.

ALTER TABLE account ENABLE ROW LEVEL SECURITY;

CREATE POLICY account_hide_deleted ON account
  FOR SELECT
  USING (deleted_at IS NULL);

-- A separate role for retention/audit work bypasses the policy
-- via its own permissive policy or the BYPASSRLS attribute.

Now a developer who forgets the filter gets live rows anyway, because the database refuses to show the rest. The forgetful path is the safe path. That is the property you are buying, and it is what keeps self-service BI from turning into a governance mess when analysts query the same tables the application writes.

Footgun three: foreign keys and a planner full of tombstones

Foreign keys do not understand soft delete. A child row can point at a parent you have logically deleted, and the constraint will happily let it, because to the database the parent still exists. Decide the semantics deliberately: usually you soft-delete parent and children together in one transaction, and you accept that historic children keep pointing at historic parents — which is correct, because the audit trail needs both. Do not try to make a foreign key enforce liveness; it cannot, and bending it will only produce orphan-cleanup jobs that fight the constraint.

The performance side is easier and worth doing early. On a table that is mostly live, tombstones are noise the planner should not have to wade through. Make your hot indexes partial too, so they index only the rows queries actually touch.

-- Smaller index, and the planner ignores deleted rows for free.
CREATE INDEX account_status_live_idx
  ON account (status)
  WHERE deleted_at IS NULL;

A partial index over live rows is smaller, stays in cache better, and lets the planner skip tombstones without a filter step. On a table that is 90% deleted after a few years, the difference is not marginal.

When soft delete is the wrong tool

Soft delete earns its keep when deleted rows are read reasonably often — undo, audit, “show me cancelled orders” — and stay a minority of the table. It stops earning its keep when the tombstones vastly outnumber the live rows, or are essentially never read again. At that point every query, index and vacuum is paying to carry data nobody looks at, and the honest move is an archive table: copy the row to account_archive in the same transaction, then hard-delete it from account. The live table stays lean, the history lives somewhere purpose-built, and your retention job has one clear place to enforce its policy.

The same care applies to how you delete, not just how you hide. A delete that runs twice must not double-tombstone or resurrect a row — the same idempotency thinking that makes a POST safe to retry applies to a soft delete under load.

A soft delete is not a checkbox on a column. It is a partial unique index, a default-safe read path, an explicit decision about foreign keys, and an honest answer to when the row should simply leave. Get those four right and deleted_at is an asset. Get them wrong and it is a duplicate-key error waiting for your busiest afternoon.