Soft Delete Database Design: deleted_at, Unique Indexes and Purging

What a soft delete is and why deleted_at beats is_deleted

A soft delete marks a row as gone without removing it. Done properly, it is a data lifecycle with three parts: a deleted_at column that records when the row was retired, uniqueness constraints that ignore retired rows, and a purge job that eventually removes them for good. If you skip the purge, you have an ever-growing table of rows nobody can see.

Teams adopt soft deletes for undo ("restore this project"), audit trails ("who removed this invoice, and when?") and support recovery, where an engineer can bring back a customer's data without a backup restore. The pattern fits best in a monolith with a shared database and user-facing records, because one schema and one ORM layer can apply the rule everywhere.

A boolean holds the same information as deleted_at IS NULL but drops the timestamp. That timestamp is what the purge job keys on, so a boolean design ends up adding a column later anyway. Choose a status enum when deletion sits alongside states such as archived or suspended, and record the time of each transition next to it.

Copy-- PostgreSQL. A nullable timestamp: NULL means live, a value means retired at that time.
CREATE TABLE projects (
    id          bigserial   PRIMARY KEY,
    account_id  bigint      NOT NULL,
    name        text        NOT NULL,
    created_at  timestamptz NOT NULL DEFAULT now(),
    deleted_at  timestamptz NULL
);

-- Uniqueness applies only to live rows, so a deleted name can be reused.
CREATE UNIQUE INDEX projects_account_name_live
    ON projects (account_id, name)
    WHERE deleted_at IS NULL;

-- Soft delete: record when, instead of removing the row.
UPDATE projects SET deleted_at = now()
WHERE id = 42 AND deleted_at IS NULL;

-- Undo / support recovery: clear the timestamp.
UPDATE projects SET deleted_at = NULL
WHERE id = 42;

-- Every read of live data carries the same predicate.
SELECT id, name FROM projects
WHERE account_id = 7 AND deleted_at IS NULL;

-- Purge: the timestamp drives retention. Delete in batches to keep locks short.
DELETE FROM projects
WHERE id IN (
    SELECT id FROM projects
    WHERE deleted_at < now() - interval '30 days'
    LIMIT 1000
);

A PostgreSQL table where a nullable deleted_at column defines live rows, scopes a partial unique index, supports undo, and drives a batched 30-day purge.

How to keep deleted rows out of every read path

Once a row can be "deleted" while still physically present, every query that reads the table carries an obligation: filter on deleted_at IS NULL. Reads are where soft deletes usually fail, because one missed predicate gives a wrong answer and raises no error. A monthly revenue report written as SELECT count(*) FROM orders o JOIN customers c ON c.id = o.customer_id WHERE o.created_at >= '2024-01-01' runs cleanly and includes cancelled orders and removed customers. The corrected query adds o.deleted_at IS NULL and puts c.deleted_at IS NULL in the join condition. Each soft-deletable table in a join needs its own predicate.

There are three common ways to enforce this, and each fails differently:

None of these mechanisms is complete on its own. A practical approach is to pick one primary mechanism. Then grant reporting users access only to the views or to RLS-protected roles, and treat any direct query against the base table as something that needs justification in code review.

Copy-- BROKEN: runs cleanly but counts soft-deleted orders
-- and orders belonging to soft-deleted customers.
SELECT count(*) AS orders_this_month
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.created_at >= '2024-01-01'
  AND o.created_at <  '2024-02-01';

-- CORRECTED: every soft-deletable table in the join gets its own predicate.
-- The customer filter sits in the join condition so it stays correct
-- if this is later changed to a LEFT JOIN.
SELECT count(*) AS orders_this_month
FROM orders o
JOIN customers c
  ON c.id = o.customer_id
 AND c.deleted_at IS NULL
WHERE o.deleted_at IS NULL
  AND o.created_at >= '2024-01-01'
  AND o.created_at <  '2024-02-01';

-- Optional enforcement: expose filtered views and give reporting
-- users access to the views only, never the base tables.
CREATE VIEW active_orders AS
  SELECT * FROM orders WHERE deleted_at IS NULL;

CREATE VIEW active_customers AS
  SELECT * FROM customers WHERE deleted_at IS NULL;

REVOKE ALL ON orders, customers FROM reporting;
GRANT SELECT ON active_orders, active_customers TO reporting;

A monthly order count that silently includes soft-deleted orders and customers, the corrected query with a deleted_at predicate per joined table, and PostgreSQL views with grants that limit a reporting role to rows that are not deleted.

Unique constraints that understand soft deletes

Uniqueness is where soft deletes break most visibly. With a plain UNIQUE(email), a deleted user's row still holds the address. When that person tries to sign up again, the insert fails, even though the application considers the old account gone. The constraint has to cover live rows only.

In PostgreSQL, a partial unique index does exactly that: CREATE UNIQUE INDEX users_email_live ON users (email) WHERE deleted_at IS NULL. Deleted rows fall outside the index, so they can't collide. Other engines support filtered indexes with different syntax and restrictions, and some don't support them at all.

The obvious fallback, UNIQUE(email, deleted_at), fails without raising any error. Every live row has deleted_at set to NULL, and many engines treat NULLs as distinct from each other in unique constraints. As a result, two live rows with the same email both pass the check. The constraint only stops duplicates among deleted rows, which is the opposite of what you need.

There are two workarounds:

Both workarounds have costs beyond the table. Some ORMs and migration tools don't model partial indexes well. They may drop the index or try to recreate it on each migration, which causes schema drift. Historical rows will also contain duplicate emails. Any export or warehouse job that assumes email is globally unique will produce wrong results unless it filters on deleted_at.

Copy-- PostgreSQL: enforce uniqueness among live rows only.
CREATE TABLE users (
  id         bigserial PRIMARY KEY,
  email      text NOT NULL,
  deleted_at timestamptz
);

-- Deleted rows fall outside the index, so they never block re-registration.
CREATE UNIQUE INDEX users_email_live ON users (email) WHERE deleted_at IS NULL;

-- The naive fallback does NOT work: live rows all have deleted_at = NULL,
-- NULLs are distinct, so two live 'alice@example.com' rows both pass.
-- ALTER TABLE users ADD CONSTRAINT users_email_deleted UNIQUE (email, deleted_at);

-- MySQL (no partial indexes): a generated column holds the key while the row
-- is live and becomes NULL once deleted. NULLs are distinct, so deleted rows
-- never collide, while live rows must be unique.
CREATE TABLE accounts (
  id         BIGINT AUTO_INCREMENT PRIMARY KEY,
  email      VARCHAR(255) NOT NULL,
  deleted_at DATETIME NULL,
  email_live VARCHAR(255)
    GENERATED ALWAYS AS (CASE WHEN deleted_at IS NULL THEN email END) STORED,
  UNIQUE KEY accounts_email_live (email_live)
);

-- Alternative: rewrite the key on delete. Portable, but the original value
-- is lost from this column, which complicates restore and lookups.
UPDATE accounts
SET deleted_at = NOW(),
    email = CONCAT(email, '#deleted-', id)
WHERE id = 8213;

A PostgreSQL partial unique index, the composite UNIQUE(email, deleted_at) that silently fails, and two MySQL workarounds: a generated column that is NULL for deleted rows, and rewriting the email on delete.

Foreign keys, cascades and restore semantics

A foreign key sees a soft delete as an ordinary UPDATE, so ON DELETE CASCADE never fires. When deleted_at is set on a parent, its children stay exactly as they were, and the database won't flag it. The application has to pick one policy per relationship and apply it consistently:

• Cascade the soft delete. Children get deleted_at too.
• Leave children orphaned but hidden. Every read path joins through the parent and filters on its deleted_at.
• Block the delete. Refuse while live children exist, as ON DELETE RESTRICT would.

If you cascade in application code, run the parent update and every child update in one transaction. Use a single timestamp captured once for all of them. Without the transaction, a failure halfway through, such as a timeout or a deploy restart, can leave some children hidden and others visible. Nothing will detect that inconsistency later. The shared timestamp also helps with restores. Restoring the parent can then revive only the children whose deleted_at matches, and leave alone the ones that were deleted earlier for unrelated reasons.

Restore can also fail on uniqueness. While a user was deleted, someone may have registered the same email. Clearing deleted_at would then break the partial unique index. Do the restore inside a transaction and treat the unique-violation error as the authoritative signal. A pre-check alone can race with a concurrent insert. Roll back, then return a specific conflict that names the field and the live record holding it. Some domains should not allow restore at all, for example usernames that someone else has since claimed. In those cases, state that rule explicitly and offer a restore under a new value as the only path.

Copy-- Partial unique index: only live rows compete for an email.
CREATE UNIQUE INDEX users_email_live ON users (email) WHERE deleted_at IS NULL;

-- Soft-delete cascade: one transaction. In PostgreSQL now() is fixed at
-- transaction start, so parent and children share the same timestamp.
BEGIN;
UPDATE users SET deleted_at = now() WHERE id = 42 AND deleted_at IS NULL;
UPDATE posts SET deleted_at = now() WHERE user_id = 42 AND deleted_at IS NULL;
COMMIT;

-- Restore: revive the parent and only the children deleted with it.
CREATE FUNCTION restore_user(p_id bigint) RETURNS void LANGUAGE plpgsql AS $$
DECLARE
  v_ts    timestamptz;
  v_email text;
BEGIN
  -- Lock the deleted row so two restores cannot run at once.
  SELECT deleted_at, email INTO v_ts, v_email
    FROM users WHERE id = p_id AND deleted_at IS NOT NULL FOR UPDATE;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'user % is not deleted', p_id;
  END IF;

  -- The partial index decides; no racy pre-check.
  UPDATE users SET deleted_at = NULL WHERE id = p_id;
  UPDATE posts SET deleted_at = NULL WHERE user_id = p_id AND deleted_at = v_ts;
EXCEPTION WHEN unique_violation THEN
  -- Changes in this block are already rolled back. Name the field and holder.
  RAISE EXCEPTION 'restore conflict on email %', v_email
    USING ERRCODE = 'unique_violation',
          DETAIL = format('held by live user %s',
            (SELECT id FROM users WHERE email = v_email AND deleted_at IS NULL));
END $$;

A PostgreSQL soft-delete cascade that runs in a single transaction with a shared timestamp, and a restore function that revives only the children deleted with the parent and turns a partial-unique-index violation into a conflict error naming the field and the live row that holds it.

Index, query-plan and storage effects

Every hot lookup index should be partial, filtered to deleted_at IS NULL. A plain index on email or (tenant_id, slug) stores an entry for every tombstone, so each probe walks past dead entries and the index stays large enough to compete with live data for memory. A partial index holds only live rows, so its size tracks the live set and not the table's whole history.

The planner's choices change as the deleted share grows. When few rows are deleted, deleted_at IS NULL matches almost everything, so the planner treats it as a cheap afterthought. When most rows are deleted, that same predicate becomes highly selective. If no partial index matches the query, the planner may still scan the table or a large index. Estimates also drift. The deleted ratio changes continuously between statistics refreshes, and planners usually assume predicates are independent. That assumption fails when one tenant has deleted far more rows than another, so tenant_id = ? AND deleted_at IS NULL can be misestimated badly in either direction.

Rows that are never removed also make sequential scans, backups, replica rebuilds and vacuum or other maintenance passes heavier, because all of them process tombstones that no user will ever read.

Illustrative estimate. Assume 10 million live rows, 50,000 soft deletes per day and 500 bytes per row. After one year, about 18 million deleted rows take up roughly 9 GB of heap before indexes are counted. At that point deleted rows outnumber live ones nearly two to one.

A purge reclaims logical space. Whether the files shrink depends on the engine. Many engines only mark the freed space as reusable for future writes and return it to the operating system only after a table rewrite or rebuild.

Designing the data retention purge

Give each soft-deletable table an explicit retention window, owned by whoever owns the data. A cart might keep deleted rows for 30 days, while an invoice may need years. A scheduled job then hard-deletes rows whose deleted_at is older than that window.

Never purge with one unbounded DELETE. It holds locks for its whole duration and generates a large burst of undo or log data. Replicas also apply it as a single large transaction, which shows up as replication lag. Instead, select the next batch of primary keys past the cutoff, ordered by primary key with a LIMIT, then delete them and commit. Repeat until nothing matches. Because each run only matches rows still past the window, rerunning after a crash is safe. Record the table, cutoff, row count and key range for each batch so you can answer "what did we purge, and when."

Batch size is a trade-off. Small batches keep lock time and replica load low, but they clear slowly. After a feature launch or a long-paused job, the backlog can take days to drain at a conservative size. Tune the size per table, and consider pausing the job when replica lag rises.

Purge children before parents. If you rely on ON DELETE CASCADE instead, do so deliberately: a cascade can turn a 500-row batch into far more work than you expect.

Audit and ledger rows often must outlive the record they describe. Do not give them a blocking foreign key. Instead, snapshot the fields they need at write time, or keep a stripped tombstone of the parent with personal data removed.

Soft delete is not erasure. Privacy obligations are met only once the purge has run and every backup containing the row has expired.

Guardrails, alternatives, and the cost in engineering time

Guardrails

Keep a registry of soft-deletable tables and run a test-suite check against it. Capture every SQL statement the tests execute. Fail the build when a query touches a registered table without a deleted_at predicate, unless an explicit annotation such as -- include-deleted marks it as intentional. Write the check against real SQL, not ORM call sites, because raw queries, reporting jobs and new joins are where the filter usually goes missing.

Monitor two signals per table:

• Deleted-to-live ratio. A rising ratio means index bloat and slower scans are coming before users notice them.
• Purge lag. Measure this as now − oldest deleted_at among rows past retention. Anything above zero means you are holding data you have promised to remove.

Alternatives

Skip soft deletes entirely when nobody can name a restore use case. In that situation, deleted_at only adds risk.

Engineering cost

The upfront work is bounded: a column, partial indexes, a purge job and the lint check. The later costs have no fixed limit. They include default-scope bugs found in production, migrations that must decide how to treat ghost rows, and compliance audits that find "deleted" data years after the fact. When you define the lifecycle (live, deleted, restorable until a set date, purged) before the first release, each of those later problems becomes a decision you have already made.

How OneEnv can help

Disclosure: OneEnv is the product behind this blog.

OneEnv (oneenv.io) is a workspace-based governance platform for the things services share — environment variables, database structure, API routes and validation rules. A change to a shared value is staged on a branch, reviewed once, and then every affected service explicitly approves or rejects it before it goes live, so you can see who a change touches before it ships instead of finding out in production.

Applied to soft delete database design, this is where it helps:

Saves time

• Every service that inherits a shared value is listed in one place, so nobody searches repos and chats to find out who is affected.
• One review per branch replaces coordinating each team separately; each service still keeps its own approve/reject.
• The dependency graph answers "what does this touch?" in seconds instead of a manual audit.

Saves cost

• Fewer production incidents and rollbacks caused by a shared value, route or column changing under a consumer unannounced.
• Less duplicated configuration to maintain: one shared definition inherited by many services.
• You can size a change's blast radius before merging, avoiding surprise cross-team firefighting.
• OneEnv has a free plan (1 workspace, unlimited services and members, full governance), so trying it costs nothing.

These are qualitative benefits, not measured results — how much you save depends on how many services share the value.

Want to try it? Start at oneenv.io.