What is soft delete in Postgres, and why model it as a nullable deleted_at?

A soft delete marks a row as gone without removing it. The row stays in the table, and every query that serves live data has to exclude it. That makes soft delete a query-correctness contract more than a storage choice. Disk usage is the minor cost. The real risk is that each SELECT, join, uniqueness check and aggregate must honor the marker, and any one that forgets returns data the user believes is deleted.

Teams adopt it for three reasons: undo for accidental deletes, an audit trail of what existed, and letting support restore a customer's records without a backup restore. These needs are strongest in a monolith with one schema and one data-access layer, where a single convention can reach every query path. If nobody has a concrete restore requirement, you take on the contract's costs and get little back.

Model the marker as a nullable deleted_at timestamptz rather than an is_deleted boolean. The timestamp records when the delete happened, which a boolean discards. Retention rules ("purge rows deleted more than 30 days ago") need that value, and so do support questions about when a record disappeared. The definition of live also stays simple: deleted_at IS NULL. Using timestamptz stores an absolute instant, so comparisons are not skewed by server or session time zones.

A minimal table needs only id bigint generated always as identity primary key, email text not null, created_at timestamptz not null default now(), and deleted_at timestamptz with no default, so that new rows start out live.

-- Minimal soft-delete table: live rows are defined as deleted_at IS NULL.
CREATE TABLE customers (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email      text        NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  -- No default: new rows start out live. timestamptz stores an absolute instant.
  deleted_at timestamptz
);

-- Soft delete: record when it happened instead of removing the row.
-- The IS NULL guard keeps the original deletion time if this runs twice.
UPDATE customers
SET    deleted_at = now()
WHERE  id = 42
  AND  deleted_at IS NULL;

-- Every query that serves live data must carry this predicate.
SELECT id, email
FROM   customers
WHERE  deleted_at IS NULL;

-- Support restore: clearing the marker makes the row live again.
UPDATE customers
SET    deleted_at = NULL
WHERE  id = 42;

-- Retention purge: the timestamp is what makes a 30-day window possible.
DELETE FROM customers
WHERE  deleted_at < now() - interval '30 days';

A minimal customers table with a nullable deleted_at timestamptz, plus the soft-delete, live-read, restore and 30-day purge statements that the timestamp enables.

Who should use soft delete in Postgres?

Soft delete fits teams whose users or support staff actually need to undo deletes, and whose tables hold records with business meaning: customers, orders, invoices, projects, documents. If a support ticket saying "I deleted this by mistake, can you bring it back?" is a routine event, soft delete is worth the contract it imposes.

It works best when:

  • One data-access layer reaches every query path. A monolith with a single ORM, or a service that owns its schema outright, can apply the deleted_at IS NULL filter consistently. When several services, reporting tools and scripts all read the same tables directly, the filter becomes hard to enforce without views or row-level security (covered below).
  • The team can own the follow-on work. That means partial indexes, partial unique constraints, cascade logic, a restore path that respects independent deletes, a purge job, and leak tests. A team that only adds the column and a default ORM scope gets the cost without the safety.
  • Records have a restore or audit requirement but no strict erasure deadline that the purge job can't meet. Soft delete can coexist with erasure obligations only if the purge window is shorter than what the obligation allows.

It is a poor fit for teams that want "deleted means gone" semantics everywhere, for high-churn operational tables such as sessions, queue entries and caches, and for tables read mostly by tools outside the team's control. The section on when to avoid soft delete covers those cases in more detail.

How does soft delete work in Postgres, end to end?

Postgres has no built-in soft delete. The behavior comes from a convention built from ordinary features, and every part of the lifecycle has to be implemented deliberately:

  1. Delete. The application runs UPDATE ... SET deleted_at = now() instead of DELETE. Under MVCC this writes a new row version, and the old version becomes dead and is reclaimed by vacuum later. The row itself stays in the table.
  2. Read. Every query that serves live data filters on deleted_at IS NULL. The filter comes from one of three places: an ORM global scope in application code, a view that exposes only live rows, or a row-level security policy. The further down the stack the filter lives, the fewer paths can skip it.
  3. Index. Partial indexes with WHERE deleted_at IS NULL keep hot-path indexes sized to live data. Partial unique indexes enforce uniqueness, such as one live account per email, among live rows only.
  4. Cascade. Foreign keys don't react to an UPDATE, so child rows have to be soft-deleted by application code or a trigger. A delete_batch identifier records which children a given parent delete touched.
  5. Restore. Setting deleted_at back to NULL revives a row. For hierarchies, restore clears only the children carrying the parent's batch id, and it must handle unique violations when a live row has since claimed the same key.
  6. Purge. A scheduled job physically deletes rows whose deleted_at is older than the retention window, in batches, children before parents. Only this step actually removes data from the table.
  7. Verify. Fixture tests, review rules and periodic orphan queries check that deleted rows don't leak into live read paths.

The rest of this article covers each step and where it tends to fail.

How do partial indexes and partial unique constraints work with deleted_at?

Most hot-path reads only care about live rows, so index only those: create index on users (created_at) where deleted_at is null. The index stays small as deleted rows accumulate, and the table's write path skips maintaining entries for rows that will never be read through it.

The catch is that the planner uses a partial index only when it can prove, at plan time, that the query's WHERE clause implies the index predicate. A literal deleted_at is null in the query qualifies. coalesce(deleted_at, 'infinity') = 'infinity' does not, even though it is logically equivalent, because the proof is syntactic rather than semantic. Parameters cause a similar problem. If the soft-delete condition depends on a bound value, such as an ORM toggling "include deleted" through a parameter, a cached generic plan cannot assume what that value will be, so the planner falls back to a full index or a sequential scan. Run EXPLAIN on the SQL your ORM actually emits, not on a hand-written version of it.

Partial unique indexes solve re-registration. create unique index on users (lower(email)) where deleted_at is null enforces uniqueness among live users only, so a deleted account no longer blocks its address. Queries need to filter on lower(email) for this index to serve lookups too.

This index also forces a product decision. When a deleted user signs up again, do you create a new entity or restore the old one? If you allow new entities, restore can fail. Setting deleted_at back to null on the old row raises a unique violation when a live account already holds that email. Decide in advance which account wins, and make the restore path handle that error explicitly.

-- Hot-path index covering live rows only
create index users_live_created_at_idx on users (created_at) where deleted_at is null;

-- Email is unique among live users only, so deleted rows do not block re-registration
create unique index users_live_email_uniq on users (lower(email)) where deleted_at is null;

-- Literal predicate matches the index predicate, so the partial index is eligible.
-- On a small or unanalyzed table the planner may still pick a seq scan.
explain select id from users
where deleted_at is null and created_at > now() - interval '7 days';

-- Logically equivalent, but the planner cannot prove it implies deleted_at is null,
-- so the partial index is not eligible.
explain select id from users
where coalesce(deleted_at, 'infinity') = 'infinity' and created_at > now() - interval '7 days';

-- Lookups must use lower(email) to use the unique index
explain select id from users
where lower(email) = lower('ana@example.com') and deleted_at is null;

-- Restore an old account. If a live account already owns the email,
-- this raises unique_violation (SQLSTATE 23505) on users_live_email_uniq.
update users set deleted_at = null where id = 42;

-- Policy: the live account wins. Restore only if no live owner exists, and report
-- 0 rows to the caller. Keep handling 23505 in the app: a concurrent signup can
-- still commit between the check and the update, and the unique index rejects it.
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 live
    where lower(live.email) = lower(u.email)
      and live.deleted_at is null
  );

Creates a partial index and a partial unique index on live users, uses EXPLAIN to compare a literal deleted_at is null filter with an equivalent coalesce filter the planner cannot match, and shows a restore that conflicts when a live account already holds the email.

How does a global query scope filter deleted rows, and where does it leak?

A global query scope is a filter the ORM attaches to every query built from a model. Its main condition is deleted_at IS NULL. Developers no longer have to remember the condition in each query, which removes the most common bug. The cost is that the filter becomes invisible. Engineers stop thinking about deleted rows, so they miss the paths where the ORM never applies the filter.

flowchart LR
  A[Model query] -->|scope applied| F[deleted_at IS NULL]
  B[Eager load / join] -->|depends on ORM| F
  B -.->|often skipped| L[Deleted rows returned]
  C[Raw SQL] -.-> L
  D[COUNT / SUM / GROUP BY in SQL] -.-> L
  E[BI, admin, replicas] -.-> L
  G[Job using unscoped lookup] -.-> L
  L --> H[Passed to callers as live data]

The scope lives in application code, so anything that doesn't go through the model bypasses it:

  • Raw SQL and query builders that skip the model never see the filter.
  • Associations: some ORMs scope the target table of a join or eager load, and some don't. A live order can therefore load a deleted customer.
  • Aggregates written as SQL, such as dashboard counts or revenue sums, include deleted rows. The totals come out slightly high, and nothing raises an error.
  • Reporting and admin tools usually query tables or replicas directly.
  • Background jobs often call an unscoped lookup on purpose, for example to process a pending deletion. They then pass those rows to shared code that assumes every row is live.

That last case is the hardest to catch. The bypass is deliberate and correct in one place, but the deleted rows move on to code that has no way to tell them apart from live ones.

  READ PATH                 FILTER (ORM scope)
 +---------------------+    +----------------------+
 | Model query         |===>| deleted_at IS NULL   |===> live rows only
 +---------------------+    | (scope in app code)  |
 +---------------------+ +->|                      |
 | Eager load / join   |-+  +----------------------+
 +---------------------+ |  (only if ORM scopes joined tables)
                         | often skipped
                         +---------------+
                                         v
 +---------------------+    +----------------------+
 | Raw SQL / builder   |--->|                      |
 +---------------------+    |  NO FILTER APPLIED   |
 +---------------------+    |                      |
 | COUNT/SUM/GROUP BY  |--->|  deleted rows come   |
 +---------------------+    |  back, totals run    |
 +---------------------+    |  high, no error      |
 | BI, admin, replicas |--->|                      |
 +---------------------+    |                      |
 +---------------------+    |                      |
 | Job: unscoped lookup|--->|                      |
 +---------------------+    +----------+-----------+
                                       |
                                       v
                            +----------------------+
                            | Shared code treats   |
                            | rows as live data    |
                            +----------------------+

Only queries built from the model always get the global deleted_at IS NULL scope, and eager loads and joins get it only if the ORM scopes the joined table; raw SQL, SQL aggregates, BI and admin tools, and unscoped job lookups return deleted rows that then reach shared code as if they were live.

Why don't foreign keys cascade soft deletes, and how do you handle children and restore?

Postgres has no concept of a soft delete. ON DELETE CASCADE is a referential action that fires only when a parent row is physically removed. UPDATE orders SET deleted_at = now() is an ordinary update. The parent row still exists, the foreign key is still satisfied, and nothing happens to order_items. The result is live children under a deleted parent. They show up in any query that reaches them directly instead of through the parent: reports, search indexes, and joins written from the child side.

You can cascade in application code or in a trigger. Application code is explicit and easy to read in review. It only works when every write path goes through it, so a bulk UPDATE in a migration or an admin script skips it. A trigger closes that gap. Define it AFTER UPDATE OF deleted_at on the parent, have it fire only when OLD.deleted_at IS NULL AND NEW.deleted_at IS NOT NULL, and have it run UPDATE order_items SET deleted_at = NEW.deleted_at, delete_batch = NEW.delete_batch WHERE order_id = NEW.id AND deleted_at IS NULL. The cost is less visibility. The cascade happens out of sight, and deep hierarchies chain triggers together.

Restore is where naive designs break. Clearing deleted_at on every child of a restored parent also revives children that someone deleted on their own, before the parent was deleted. Restore has to undo only the cascade it is reversing. Matching on timestamps works only partly. now() returns the same value for the whole transaction, so two unrelated deletes in one transaction become indistinguishable. A delete_batch UUID written by the original delete is safer. Restore then clears only the rows that carry that batch id.

ALTER TABLE orders      ADD COLUMN delete_batch uuid;
ALTER TABLE order_items ADD COLUMN delete_batch uuid;

-- Cascade a soft delete from an order to its live items, tagging them with the parent's batch id.
CREATE FUNCTION cascade_soft_delete_order() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  UPDATE order_items
     SET deleted_at = NEW.deleted_at, delete_batch = NEW.delete_batch
   WHERE order_id = NEW.id
     AND deleted_at IS NULL;  -- items deleted earlier keep their own timestamp and batch
  RETURN NULL;                -- return value is ignored for AFTER triggers
END;
$$;

-- Fire only on the live -> deleted transition, not on every update to the row.
CREATE TRIGGER orders_soft_delete_cascade
AFTER UPDATE OF deleted_at ON orders
FOR EACH ROW
WHEN (OLD.deleted_at IS NULL AND NEW.deleted_at IS NOT NULL)
EXECUTE FUNCTION cascade_soft_delete_order();

-- Soft delete: every delete gets a fresh batch id, even when several share one transaction.
UPDATE orders SET deleted_at = now(), delete_batch = gen_random_uuid() WHERE id = 42;

-- Restore: revive only the items this cascade deleted, then the parent.
BEGIN;
UPDATE order_items i
   SET deleted_at = NULL, delete_batch = NULL
  FROM orders o
 WHERE o.id = 42
   AND i.order_id = o.id
   AND i.delete_batch = o.delete_batch;  -- items deleted independently carry a different batch
UPDATE orders SET deleted_at = NULL, delete_batch = NULL WHERE id = 42;
COMMIT;

A PL/pgSQL trigger cascades a soft delete from orders to order_items and tags the affected items with a batch id, and the restore clears only the items that carry the parent's batch, so items deleted independently earlier stay deleted.

How can the database enforce soft delete with views or row-level security?

Both approaches move the deleted_at IS NULL filter out of application code, where every query has to remember it, and into the database, where every query gets it automatically.

Views over live rows

Rename the base table (for example orders_all) and create orders as a view selecting only rows where deleted_at IS NULL. Postgres treats a single-table view with a plain filter as automatically updatable. Adding WITH CHECK OPTION rejects inserts or updates through the view that would produce a row the view cannot see. Application roles get privileges on the view only. The catch is that a soft delete makes the row leave the view, so WITH CHECK OPTION blocks it. The delete has to go through a function or run against the base table.

Row-level security

With RLS enabled, a USING (deleted_at IS NULL) policy filters reads. An UPDATE policy that has no separate WITH CHECK applies the USING expression to the new row as well, which blocks the soft delete itself. You need an explicit WITH CHECK clause for that operation. The table owner bypasses RLS unless you set FORCE ROW LEVEL SECURITY. Superusers and roles with BYPASSRLS always bypass it.

Operational cost

  • Confusion between roles. Migrations usually run as the owner and see every row, while the app sees fewer. ORMs that introspect views may not find primary keys.
  • A privileged role for admin work. Admin tools, restore and purge need a separate, tightly controlled role, ideally reached only through audited functions.
Approach Leak resistance Complexity Bypass handling
Convention-only scope Low: raw SQL, joins and reports miss it Low Implicit; any query can skip the scope
View Medium-high: holds if the app has no grants on the base table Medium Grant on the base table to a separate role
RLS High: applies to every query from non-exempt roles High BYPASSRLS role or owner, plus FORCE

How can soft delete save engineering time?

Soft delete saves time in specific, recurring situations. It does not save time by default, and a leaky implementation costs more than it saves. The savings come from these places:

  • Restores become a single statement instead of a recovery project. Without soft delete, recovering an accidentally deleted record typically means restoring a backup or point-in-time copy to a separate instance, locating the rows, copying them back, and reconciling anything that changed in the meantime. With soft delete, the restore is an UPDATE that clears deleted_at, or a batch-aware restore for hierarchies. Once that path is wrapped in an audited function, support staff can run it without involving an engineer.
  • "Where did this record go?" investigations get shorter. The row is still there, deleted_at says when it was deleted, and delete_batch says which operation removed it. Engineers can answer the question with a query instead of digging through logs or backups.
  • Undo features are cheap to build. An "undo" button or a trash view is a filter on deleted_at IS NOT NULL plus the restore path. No separate archive schema or event replay is needed.
  • Enforcing the filter in the database cuts review time. With a view or RLS, reviewers no longer have to check every new query for the deleted_at predicate, because the database applies it. Convention-only scopes push that check onto every code review.
  • Automated leak checks replace manual audits. Fixture tests and the orphan query described below find leaks in CI or on a schedule, instead of through a customer report.

As an illustrative estimate, with assumed inputs only: suppose a team handles an assumed 4 accidental-deletion requests per month. Assume each one takes an engineer 2 hours through a backup-based recovery, and 10 minutes through a soft-delete restore function. That works out to roughly 8 hours versus 40 minutes per month, so about 7 engineer-hours saved. Against that, count the one-time cost of building cascades, restore, purge and leak tests, plus the ongoing cost of keeping every read path filtered. If your real restore frequency is near zero, the same arithmetic shows soft delete costing time rather than saving it. Plug in your own numbers before committing.

When should you avoid soft delete, and what are the alternatives?

Soft delete pays off only where someone actually restores rows. Sessions, job queue entries, idempotency keys and cache tables rarely get un-deleted, so a deleted_at column there adds cost and returns nothing.

High-churn tables are the second warning sign. Setting deleted_at is an UPDATE, so it writes a new row version that vacuum later has to clean up. The logically deleted row also stays in the heap indefinitely. Once deleted rows outnumber live ones, sequential scans and every non-partial index carry mostly dead weight.

Personal data is the third case. A row with deleted_at set is still stored, replicated and backed up. For an erasure request, that is not deletion.

Approach Restore Live table stays small Erasure Main cost
Hard delete From backup only Yes Yes, once backups age out Lost history
Archive table Copy back Yes No, unless archive is purged Two schemas to keep in sync
Event log Replay or rebuild Yes No; log holds the data Consumers and retention policy
Anonymization No No Yes, if done thoroughly Finding every personal field

An archive table works well when the move happens in the same transaction as the delete. Live queries then need no filter at all.

Weigh the development cost honestly. Every read path must carry the filter. Cascades and restore need their own code and tests. Applying deleted_at uniformly across a monolith means paying that cost on tables where no one will ever click "undo". Choose per table, not per codebase.

How do you purge deleted rows and test that none leak?

Purging past the retention window

A soft-deleted row is still stored data. If a user asks for erasure, or your retention policy promises removal after N days, marking deleted_at does not meet that obligation. Only the purge does, and backups and replicas have their own lifecycle that the purge does not touch. Run the purge as a scheduled job that deletes in small batches. A single large DELETE holds locks for a long time, produces a burst of WAL, and leaves vacuum with a lot of work at once.

Postgres DELETE has no LIMIT, so select the batch in a subquery: DELETE FROM orders WHERE id IN (SELECT id FROM orders WHERE deleted_at < now() - interval '90 days' ORDER BY id LIMIT 5000 FOR UPDATE SKIP LOCKED). Loop until it affects zero rows. A partial index on deleted_at WHERE deleted_at IS NOT NULL keeps each batch from scanning live rows. To archive instead of discard, wrap the delete in a CTE with RETURNING * and insert those rows into an archive table in the same statement. Purge children before parents so foreign keys don't block the job.

Catching leaks before production

  • Fixture tests: For every public read path, including list endpoints, search, exports and admin views, create a soft-deleted fixture and assert that it never appears in the results.
  • Review or lint rule: Flag raw SQL that names a soft-deletable table without a deleted_at predicate or the filtered view.
  • Periodic orphan query: SELECT c.id FROM order_items c JOIN orders p ON p.id = c.order_id WHERE p.deleted_at IS NOT NULL AND c.deleted_at IS NULL. Any rows it returns point to a cascade bug that needs investigation.

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 postgres, 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.
  • Changing a route's URL or method automatically notifies the services that consume it — no hunting through repos for callers.

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.
  • Consumers learn about a breaking change at review time rather than from production errors.
  • 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.