What is the difference between a soft delete and a hard delete?

A hard delete removes the row with DELETE. Once the transaction commits, the data is gone from the live table, and getting it back means restoring from a backup or replaying a log. A soft delete keeps the row and marks it as deleted, so every read path has to filter it out. Use a nullable deleted_at timestamp for the marker, not an is_deleted boolean. A timestamp tells you when the row was deleted, which helps with debugging and audit. It also lets a background job purge rows older than a retention window. A boolean only says that a row was deleted, so there is nothing to base a purge on. Archival moves the row out of the hot table into a separate table, schema or cold store. The live table stays lean and the history is kept somewhere queries don't touch by default.

The three approaches make different trade-offs:

Property Hard delete Soft delete (deleted_at) Archival
Recovery Backup restore only Set deleted_at back to NULL Copy row back to live table
Audit trail None in the table Row plus deletion time Row in archive, often with metadata
Storage growth Shrinks with deletes Grows without bound unless purged Live table shrinks; archive grows
Query complexity None added Every query, join and index must filter Only queries that read history
Privacy erasure Satisfied by the delete Not satisfied; data still present Not satisfied until archive is purged

The last row matters most. Of the three, only a hard delete actually erases the data. With the other two, erasure is something you still have to do later.

Why do soft deletes leak into every query?

A soft delete turns one write path into a rule that every read path has to follow. Once a table has a deleted_at column, "the rows in this table" no longer means what it says. Every SELECT, every JOIN, every COUNT(*) and every SUM() needs deleted_at IS NULL, and a join across three soft-deleted tables needs it three times. The database cannot enforce this, so the rule lives only in developers' heads and in whatever scoping helper the ORM provides.

The failures are quiet. A missed filter does not throw an error. It returns plausible rows. A dashboard overstates active customers, a CSV export includes accounts users asked you to remove, or an admin search shows a "deleted" invoice. Raw SQL in reports, analytics jobs and one-off scripts usually skips the ORM's default scope, so these paths are where the filter goes missing most often.

Tests rarely catch it, because fixtures seldom contain soft-deleted rows. A query with no filter and a correctly filtered query return the same result against a clean test database. The bug only appears when production data includes deleted rows, and nobody may notice for months.

Referential integrity also drifts. Foreign keys and ON DELETE CASCADE fire on DELETE, not on UPDATE. Setting deleted_at on a parent leaves its children live and valid as far as the database is concerned. An order can still reference a soft-deleted customer, and the database considers it consistent.

-- Schema: FK with ON DELETE CASCADE, plus soft-delete columns
CREATE TABLE customers (
  id         bigint PRIMARY KEY,
  name       text NOT NULL,
  deleted_at timestamp NULL
);

CREATE TABLE orders (
  id          bigint PRIMARY KEY,
  customer_id bigint NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
  total       numeric(10,2) NOT NULL,
  deleted_at  timestamp NULL
);

INSERT INTO customers VALUES (1, 'Acme Ltd', NULL);
INSERT INTO orders    VALUES (10, 1, 250.00, NULL);

-- "Delete" the customer. This is an UPDATE, so the cascade never fires.
UPDATE customers SET deleted_at = CURRENT_TIMESTAMP WHERE id = 1;

-- Correct report: the filter must be repeated for every soft-deleted table.
SELECT c.name, SUM(o.total) AS revenue
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.deleted_at IS NULL
  AND c.deleted_at IS NULL
GROUP BY c.name;           -- returns no rows

-- The same report with one filter missed: no error, just plausible data.
SELECT c.name, SUM(o.total) AS revenue
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.deleted_at IS NULL
GROUP BY c.name;           -- returns 'Acme Ltd', 250.00

-- The live order still points at a soft-deleted parent, and the FK is satisfied.
SELECT o.id, c.deleted_at
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.deleted_at IS NULL
  AND c.deleted_at IS NOT NULL;  -- returns order 10

Soft-deleting a customer with an UPDATE skips ON DELETE CASCADE and leaves a live order pointing at it, and a report that leaves out one deleted_at filter brings the deleted customer's revenue back without raising an error.

When should you use soft deletes, and when should you avoid them?

That cost is worth paying for some tables and not others, so make the decision per table rather than setting a framework-wide default.

Soft delete is worth it where someone will plausibly ask for the row back, or ask what it used to be. That covers user-facing content with an undo button, accounts that can be reactivated, and orders that invoices and refunds still reference. Hard delete fits data that churns quickly or can be derived from something else, such as sessions, tokens, cache rows and join rows you can rebuild from their source. Keeping expired tokens adds index weight and attack surface and preserves no history anyone needs.

Avoid soft delete, even on tables that matter, in these cases:

  • No purge job exists. Without one, the table only grows. Live rows become a shrinking fraction of every scan and index.
  • You need uniqueness but have no partial indexes. A unique email constraint blocks re-signup after a soft delete. Databases like MySQL push you toward workarounds such as composite keys that include deleted_at or nullable sentinel columns.
  • The data is personal. A row with deleted_at set is still stored, backed up, replicated and readable by anyone with database access. An erasure obligation requires the data to be gone. A flag only hides it.
Data category Examples Default Reason
User-facing content Posts, documents Soft Undo and moderation history
Accounts Users, orgs Soft, then purge Reactivation, but personal data must expire
Transactional records Orders, payments Soft or archive Referenced by financial records
Ephemeral state Sessions, tokens Hard High churn, no history value
Derived data Caches, rebuildable join rows Hard Recreate from source

How do you implement soft deletes safely in the database?

Put the "live rows only" rule in one place. Use your ORM's default scope or global query filter, or create a view such as active_users that selects WHERE deleted_at IS NULL, and point application reads at it. Reading deleted rows should require an explicit opt-in, such as a named method like with_deleted(), so anyone reviewing the code can see that the default was overridden.

Neither approach covers everything. Raw SQL, BI and reporting tools, and other services that share the database all bypass ORM scopes. A view only helps the clients that query it. Hidden scopes also cause confusing bugs: a lookup by primary key returns nothing for a row an engineer can see in psql. Write the scope's existence into onboarding docs.

Index for the common query. In PostgreSQL, CREATE INDEX ... WHERE deleted_at IS NULL keeps deleted rows out of the index entirely. A partial unique index, CREATE UNIQUE INDEX ... (email) WHERE deleted_at IS NULL, lets a new account reuse an email or slug that belongs to a deleted row. On engines without partial indexes, a composite unique key on (email, deleted_at) will not work as expected, because most engines treat each NULL as distinct from every other NULL. Make deleted_at non-nullable and store a sentinel such as the epoch for live rows instead.

Decide what happens to child rows when a parent is soft deleted. The options are to cascade the soft delete inside the same transaction, to block the delete while live children exist, or to leave the children visible as orphans on purpose. Cover the chosen behavior with tests that delete a parent and then assert on the state of its children. Restores need a conflict check too. If a new row has claimed the email since the deletion, restoring the old row will violate the unique index. Detect that case and report it instead of failing on a constraint error.

CREATE TABLE users (
  id         bigserial PRIMARY KEY,
  email      text NOT NULL,
  deleted_at timestamptz            -- NULL means live
);
CREATE TABLE posts (
  id         bigserial PRIMARY KEY,
  user_id    bigint NOT NULL REFERENCES users(id),
  deleted_at timestamptz
);

-- Email must be unique among live rows only, so deleted accounts don't block reuse
CREATE UNIQUE INDEX users_email_live_uniq ON users (email) WHERE deleted_at IS NULL;
-- Deleted rows never enter this index, keeping it small for the common query
CREATE INDEX posts_user_live_idx ON posts (user_id) WHERE deleted_at IS NULL;

-- One place that defines "live"; point application reads here
CREATE VIEW active_users AS SELECT * FROM users WHERE deleted_at IS NULL;

-- Cascade soft delete: now() is fixed per transaction, so parent and children
-- share one timestamp that a restore can match on later
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: bring back only children removed by that cascade, then the parent
BEGIN;
UPDATE posts p SET deleted_at = NULL
  FROM users u
 WHERE u.id = 42 AND p.user_id = u.id AND p.deleted_at = u.deleted_at;
UPDATE users u SET deleted_at = NULL
 WHERE u.id = 42 AND u.deleted_at IS NOT NULL
   AND NOT EXISTS (SELECT 1 FROM users o
                    WHERE o.email = u.email AND o.deleted_at IS NULL);
-- If the last UPDATE reports 0 rows, a newer live account owns the email:
-- ROLLBACK and surface the conflict. Under concurrent writes the unique index
-- still rejects the restore, so handle that error the same way.
COMMIT;

PostgreSQL schema with a live-rows view, partial and partial unique indexes, a transactional cascading soft delete, and a restore that refuses to proceed when a newer row has claimed the same email.

How do you give data retention an end date?

A soft delete without a purge is a decision to keep the row forever. Pick a retention window (30 or 90 days, whatever support and legal agree on) and enforce it with a scheduled job that hard-deletes or archives rows whose deleted_at is older than that window.

Lifecycle: live (deleted_at is NULL) → soft-deleted (deleted_at set, restorable) → after the retention window, either purged (row removed) or archived (moved to cold storage, then removed from the live table).

Run the purge in small batches, such as a few thousand rows per transaction with a short pause between batches. One large DELETE holds locks for its full duration, bloats the transaction log, and arrives at replicas as one large change, which is how replication lag spikes. Batching keeps each transaction short and lets replicas keep up. Support the job with an index on deleted_at, ideally a partial index covering only non-NULL values. Without it, every batch scans the live table to find a handful of expired rows.

Retention and erasure are separate problems. A soft-deleted row containing personal data is still stored personal data, so an erasure request cannot wait out the retention window or stop at setting deleted_at. It needs its own path: an immediate hard delete or anonymization of identifying columns. That path must also reach every copy, including backups (or a documented expiry policy for them), application logs, search indexes, caches, and analytics or warehouse exports. Inventory those copies before you promise anyone deletion.

-- PostgreSQL. Partial index: covers only soft-deleted rows, so it stays small
-- and each purge batch finds expired rows without scanning the live table.
CREATE INDEX CONCURRENTLY IF NOT EXISTS orders_deleted_at_idx
  ON orders (deleted_at)
  WHERE deleted_at IS NOT NULL;

-- One purge batch: move up to 5000 expired rows to the archive table
-- (same column layout as orders) and delete them from the live table.
-- The scheduler runs this in its own short transaction, sleeps briefly,
-- and repeats until it affects 0 rows, so replicas can keep up.
WITH expired AS (
  SELECT id
  FROM orders
  WHERE deleted_at IS NOT NULL
    AND deleted_at < now() - interval '90 days'  -- retention window
  ORDER BY deleted_at
  LIMIT 5000
  FOR UPDATE SKIP LOCKED  -- don't block on rows another worker holds
),
moved AS (
  DELETE FROM orders o
  USING expired e
  WHERE o.id = e.id
  RETURNING o.*
)
INSERT INTO orders_archive
SELECT * FROM moved;

-- Erasure request: separate path that does not wait for the retention window.
-- Scrub identifying columns now, in the live table AND the archive.
-- Backups, logs, search indexes and warehouse copies need their own handling.
UPDATE customers
SET email = NULL, full_name = NULL, phone = NULL, deleted_at = now()
WHERE id = 12345;

UPDATE orders_archive
SET shipping_address = NULL
WHERE customer_id = 12345;

A PostgreSQL partial index on deleted_at, a batched purge that archives and removes expired soft-deleted rows, and a separate immediate anonymization path for erasure requests.

What are the alternatives to soft deletes?

The most common alternative keeps the main table honest: delete the row for real, and copy it into an append-only history table in the same transaction. The application can write that copy, or a trigger can, which also catches ad hoc SQL and maintenance scripts that bypass application code. Hot-path queries need no filter because deleted rows are gone. Recovery becomes an explicit re-insert from history. If you also record updates, the actor and a timestamp, the same table serves as an audit trail.

The cost moves rather than disappears. Every tracked change becomes two writes. History tables drift as live columns are added, renamed or dropped, so every migration must touch both tables. The other option is to store each old row as a JSON document and accept weaker typing on restore. History is also personal data with its own retention clock, so erasure requests must reach it too.

Approach Hot-path queries Write overhead Recovery Main risk
Soft delete Filter required everywhere Update instead of delete Single row, instant A forgotten filter; unique constraints that collide with deleted rows
Hard delete + history table Clean Extra insert per change Single row, manual re-insert Schema drift; a second retention policy
Archive table (rows moved in batches) Clean Insert plus delete, often batched Single row, manual Drift; reads that span both tables need a union
Backups only Clean None Whole database at a point in time Slow, coarse restores

Backups make a poor sole recovery path. To recover one customer's records you restore a full copy elsewhere, extract the rows and reconcile them with everything written since.

-- PostgreSQL 11+. Append-only history for a hypothetical customers(id bigint PK, ...) table.
CREATE TABLE customers_history (
  history_id  bigserial   PRIMARY KEY,
  customer_id bigint      NOT NULL,
  operation   text        NOT NULL CHECK (operation IN ('UPDATE', 'DELETE')),
  old_row     jsonb       NOT NULL,  -- JSON tolerates later column changes; typing is weaker on restore
  changed_by  text        NOT NULL DEFAULT current_user,
  changed_at  timestamptz NOT NULL DEFAULT now()
);

-- Runs in the same transaction as the change, so ad hoc SQL and scripts are captured too.
CREATE FUNCTION customers_capture_history() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  INSERT INTO customers_history (customer_id, operation, old_row)
  VALUES (OLD.id, TG_OP, to_jsonb(OLD));
  RETURN NULL;  -- return value is ignored for AFTER row triggers
END;
$$;

CREATE TRIGGER customers_history_trg
AFTER UPDATE OR DELETE ON customers
FOR EACH ROW EXECUTE FUNCTION customers_capture_history();

-- The live table stays clean, so hot-path queries need no deleted_at filter.
DELETE FROM customers WHERE id = 1042;

-- Recovery is an explicit re-insert of the most recent snapshot.
-- Keys that no longer match a column are dropped, and new columns come back as NULL.
INSERT INTO customers
SELECT r.*
FROM (SELECT old_row FROM customers_history
      WHERE customer_id = 1042 AND operation = 'DELETE'
      ORDER BY changed_at DESC LIMIT 1) h,
     jsonb_populate_record(NULL::customers, h.old_row) AS r;

A PostgreSQL trigger copies each updated or deleted customer row into an append-only JSONB history table, so the live table can use hard deletes and a single row can still be restored by re-inserting it from history.

How do you add soft deletes to an existing table, and what does it cost?

Order matters, because each step makes the next one safe:

  1. Add a nullable deleted_at column with no default. Every existing row reads as live, so nothing changes yet. In many engines this is a cheap schema change, but check how yours handles it on a large table.
  2. Introduce the scope or view and switch reads to it before any row is ever soft-deleted. This shows which queries bypass it while the mistake is still harmless.
  3. Convert unique constraints to partial unique indexes on WHERE deleted_at IS NULL. If you skip this, a user cannot re-register an email that belongs to a soft-deleted row.
  4. Update deletion code paths, including cascades to child tables.
  5. Audit raw SQL, reports and exports. These sit outside the ORM scope, so they will count deleted rows until someone fixes them.

What it costs

Every new query needs a reviewer to ask whether it should see deleted rows. You also need tests that confirm filters and cascades hold, including through joins. If you never purge, tables and indexes keep growing. Vacuum and similar maintenance then has to process rows that no request will ever read.

Where it saves time

The database does work that would otherwise fall to people. A view or row filter enforces filtering, and partial indexes enforce uniqueness, so nobody has to remember the predicate on every query. A per-table policy limits the cost to data someone actually needs to recover. A working undo path turns an accidental delete into a one-row update rather than the restore-and-reconcile exercise described above.

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 vs hard delete, 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.