How does soft delete work in Postgres, in short?

Postgres has no built-in soft delete. Every approach is a convention you build from ordinary features: columns, UPDATE, DELETE, triggers, views, partial indexes and transactions. The two common designs differ in where a deleted row ends up.

  • deleted_at column. The row stays in its table. A timestamp marks it as deleted, and every reader must filter it out. Deleting and restoring are both UPDATEs.
  • Archive table. The row is physically deleted from the live table and copied into a separate table in the same transaction. The live table contains only live data. Restoring means inserting the row back.

The sections below show how each one works, where it breaks, and how to choose between them.

How does a deleted_at soft delete work in Postgres?

A deleted_at soft delete replaces DELETE with UPDATE. You add a nullable timestamptz column, for example ALTER TABLE orders ADD COLUMN deleted_at timestamptz, and treat NULL as "live" and any timestamp as "deleted at this moment." Deleting becomes UPDATE orders SET deleted_at = now() WHERE id = $1, and restoring becomes UPDATE orders SET deleted_at = NULL WHERE id = $1. The row never leaves the table, so its primary key, its relationships and every other column stay exactly as they were.

Teams adopt this for three reasons:

  • Undo is cheap. Recovering from an accidental delete is a single-row update, not a backup restore or a point-in-time recovery.
  • The table becomes an audit trail. The timestamp records when the row was deleted. A deleted_by column can record who deleted it.
  • Restores are fast and safe. Nothing has to be reinserted, so sequences, IDs and references that point at the row all remain valid.

The cost is that deletion is now a convention, not a database guarantee. Postgres still treats every soft-deleted row as an ordinary row. Every read path must add WHERE deleted_at IS NULL. That includes application queries, ORM default scopes, joins, reporting jobs, analytics exports and the ad hoc query someone runs in psql during an incident.

One missed filter is enough to leak data. Take SELECT count(*) FROM orders WHERE customer_id = $1. It quietly counts cancelled orders, and nothing errors. Joins are especially easy to get wrong, because the filter has to appear on every soft-deletable table in the query, not just the one in the FROM clause.

-- Add the soft-delete marker: NULL means live, a timestamp means deleted.
ALTER TABLE orders ADD COLUMN deleted_at timestamptz;

-- Soft delete: the row stays in the table, only the marker changes.
-- The IS NULL guard keeps the original deletion time if this runs twice.
UPDATE orders
SET deleted_at = now()
WHERE id = 1042
  AND deleted_at IS NULL;

-- Restore: clear the marker. The id and foreign keys are untouched.
UPDATE orders
SET deleted_at = NULL
WHERE id = 1042;

-- Correct read: every read path must repeat this filter.
SELECT count(*)
FROM orders
WHERE customer_id = 77
  AND deleted_at IS NULL;

-- Bug: the filter is missing, so soft-deleted orders are counted too.
-- The query runs without error and the number is silently wrong.
SELECT count(*)
FROM orders
WHERE customer_id = 77;

-- Join bug: orders is filtered but order_items (also soft-deletable) is not,
-- so deleted line items still contribute to the total.
SELECT o.id, sum(i.amount) AS total
FROM orders o
JOIN order_items i ON i.order_id = o.id
WHERE o.customer_id = 77
  AND o.deleted_at IS NULL
  -- missing: AND i.deleted_at IS NULL
GROUP BY o.id;

Adding a nullable deleted_at column, soft-deleting and restoring a row with UPDATE, and how a missing deleted_at IS NULL filter on a single-table query or one side of a join quietly includes deleted rows.

How do partial indexes and partial unique indexes help with deleted_at?

The same filter that is easy to forget is also what makes indexing work. If live rows are the only ones the application reads in hot paths, index only those: CREATE INDEX orders_customer_live_idx ON orders (customer_id) WHERE deleted_at IS NULL;. The index excludes soft-deleted rows entirely, so it stays proportional to the live data rather than the full history. It is cheaper to scan, more likely to fit in memory, and is not modified when rows that are already soft-deleted get updated.

Uniqueness benefits even more. A plain unique constraint on users.email means a deleted account permanently reserves its address, so nobody can sign up with it again. A partial unique index scopes the rule to live rows: CREATE UNIQUE INDEX users_email_live_key ON users (lower(email)) WHERE deleted_at IS NULL;. Any number of deleted rows can share an email, but only one live row can hold it. The same pattern works for slugs and usernames. One consequence for upserts: ON CONFLICT must repeat the predicate, as in ON CONFLICT (lower(email)) WHERE deleted_at IS NULL, or Postgres will not match the index as the arbiter.

The limit is that the planner only uses a partial index when it can prove the query's WHERE clause implies the index condition. SELECT * FROM orders WHERE customer_id = $1 AND deleted_at IS NULL matches. SELECT * FROM orders WHERE customer_id = $1 does not, because that query may need deleted rows the index never stored. The planner falls back to another index or a sequential scan.

-- Index only live orders; soft-deleted history is never stored here.
CREATE INDEX orders_customer_live_idx
  ON orders (customer_id)
  WHERE deleted_at IS NULL;

-- One live row per email (case-insensitive); deleted rows may repeat it.
CREATE UNIQUE INDEX users_email_live_key
  ON users (lower(email))
  WHERE deleted_at IS NULL;

-- Matches: the WHERE clause implies deleted_at IS NULL, so the partial index is usable.
EXPLAIN SELECT * FROM orders
 WHERE customer_id = 42 AND deleted_at IS NULL;

-- Does not match: this query may need deleted rows the index never stored,
-- so the planner uses another index or a sequential scan.
EXPLAIN SELECT * FROM orders
 WHERE customer_id = 42;

-- Re-signup with an address that belongs only to a soft-deleted account succeeds.
UPDATE users SET deleted_at = now() WHERE lower(email) = 'ana@example.com';
INSERT INTO users (email) VALUES ('Ana@example.com');

-- Upsert: repeat the index expression and predicate so Postgres can infer
-- users_email_live_key as the arbiter. Expressions need their own parentheses.
INSERT INTO users (email, display_name)
VALUES ('ana@example.com', 'Ana')
ON CONFLICT ((lower(email))) WHERE deleted_at IS NULL
DO UPDATE SET display_name = EXCLUDED.display_name;

This example creates a partial index and a partial unique index scoped to rows where deleted_at IS NULL, compares a query that can use the index with one that cannot, and shows an upsert whose ON CONFLICT clause repeats the index predicate.

Why don't foreign keys and ON DELETE CASCADE understand soft deletes?

A foreign key checks only that the referenced row exists. Setting deleted_at on a customer leaves the row physically present, so every order pointing at it stays valid, and new orders can still be inserted against it. ON DELETE CASCADE never fires because no DELETE statement ran. The database sees an UPDATE to one column and has no reason to touch child tables.

If soft-deleting a parent should soft-delete its children, you have to build that yourself. Application code works until a second service or a manual SQL session updates the parent directly. A trigger keeps the rule in the database. One sketch is an AFTER UPDATE OF deleted_at row trigger on customers that runs UPDATE orders SET deleted_at = NEW.deleted_at WHERE customer_id = NEW.id AND deleted_at IS NULL. Restores need the reverse logic. Restores also need a way to tell rows deleted by the cascade apart from rows that were deleted independently.

To hide deleted rows by default, you can expose a view such as active_customers defined as SELECT * FROM customers WHERE deleted_at IS NULL, or add a row-level security policy with the same predicate. Both approaches have gaps:

  • Views: A simple view is auto-updatable, so a DELETE through it removes the row physically unless an INSTEAD OF trigger or rule rewrites it into an update.
  • Row-level security: Superusers and BYPASSRLS roles skip policies entirely. The table owner also skips them unless FORCE ROW LEVEL SECURITY is set. Migrations, backfills and admin tools often run as exactly these roles.
  • Indirection: Every layer adds a place where the visible data differs from the stored data. That makes slow queries, schema changes and "missing row" bug reports harder to trace.
-- Cascade a soft delete from customers to their live orders.
CREATE FUNCTION cascade_customer_soft_delete() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  -- Skip orders already deleted on their own so their timestamps survive.
  -- Reusing NEW.deleted_at lets a restore match cascaded rows by timestamp.
  UPDATE orders
     SET deleted_at = NEW.deleted_at
   WHERE customer_id = NEW.id
     AND deleted_at IS NULL;
  RETURN NULL;  -- return value is ignored for AFTER row triggers
END;
$$;

CREATE TRIGGER customers_soft_delete_cascade
AFTER UPDATE OF deleted_at ON customers
FOR EACH ROW
WHEN (OLD.deleted_at IS NULL AND NEW.deleted_at IS NOT NULL)
EXECUTE FUNCTION cascade_customer_soft_delete();

-- Default read path that hides soft-deleted customers.
CREATE VIEW active_customers AS
  SELECT * FROM customers WHERE deleted_at IS NULL;

-- Without this, DELETE FROM active_customers would physically remove the row.
CREATE FUNCTION active_customers_soft_delete() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  UPDATE customers SET deleted_at = now() WHERE id = OLD.id;
  RETURN OLD;  -- non-null so the DELETE reports the row as affected
END;
$$;

CREATE TRIGGER active_customers_delete
INSTEAD OF DELETE ON active_customers
FOR EACH ROW EXECUTE FUNCTION active_customers_soft_delete();

A trigger cascades a customer's soft delete to their live orders, and an INSTEAD OF trigger turns a DELETE through the active_customers view into an UPDATE of deleted_at.

How does the archive table pattern work?

The archive pattern keeps the live table honest: a deleted row is physically removed and a copy lands in a separate table, such as orders_archive. The move has to happen in one transaction. If the delete commits and the insert fails, or the reverse, you either lose data or keep a duplicate. The usual form is a CTE that runs DELETE ... RETURNING * and feeds those rows into an INSERT into the archive table. A BEFORE DELETE or AFTER DELETE trigger can do the same thing, which also catches deletes from code paths that bypass the application. The cost is that the copy becomes invisible to anyone reading the calling code.

The main schema choice is how closely the archive mirrors the source. A typed mirror with the same columns is easy to query and to restore from. It usually drops foreign keys, because archived rows often reference parents that are also gone. It also keeps only the indexes that archive lookups need. A JSONB snapshot column is looser and survives most schema changes unchanged, but every read has to parse the document, and type errors go unnoticed until you use the data.

Either way, every migration on the live table needs a matching decision for the archive. That step is easy to forget. Drift then stays hidden until a restore hits a missing column or a new NOT NULL constraint.

Deletes also get more expensive. Each one now writes a full row and its index entries elsewhere, and it holds row locks for longer. Cascades make this much worse. Archiving one account can mean copying thousands of child rows. A single click then becomes a long transaction that holds locks and generates a large amount of WAL.

-- Typed mirror: LIKE copies columns, NOT NULL and defaults, but not
-- foreign keys, so archived rows can outlive their parents.
CREATE TABLE orders_archive (
  LIKE orders INCLUDING DEFAULTS,
  archived_at timestamptz NOT NULL DEFAULT now()
);
-- Keep only the indexes that archive lookups need.
CREATE INDEX ON orders_archive (customer_id, archived_at);

-- Option A: explicit move. A single statement is atomic, so the delete
-- and the insert either both commit or both roll back.
-- SELECT * relies on matching column order; a migration that changes
-- orders but not orders_archive breaks this, or silently misaligns it.
WITH moved AS (
  DELETE FROM orders
  WHERE id = 4217
  RETURNING *
)
INSERT INTO orders_archive
SELECT moved.*, now() FROM moved;

-- Option B: trigger. Use A or B, not both, or rows are archived twice.
-- This also catches deletes that bypass the application, but the copy
-- is invisible to anyone reading the calling code.
CREATE FUNCTION archive_order() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  INSERT INTO orders_archive SELECT (OLD).*, now();
  RETURN OLD;
END;
$$;

CREATE TRIGGER orders_archive_on_delete
AFTER DELETE ON orders
FOR EACH ROW EXECUTE FUNCTION archive_order();

A typed archive table without foreign keys, with rows moved by an atomic DELETE ... RETURNING CTE or by an AFTER DELETE trigger, where both depend on the archive columns staying in step with the live table.

Why is restoring deleted rows harder with an archive table?

With deleted_at, undo is one UPDATE ... SET deleted_at = NULL. The row never left its table, so its primary key, foreign keys and unique values are still in place. The only conflict is with a partial unique index: if someone has since claimed the same email among live rows, the update fails loudly. That failure is the correct behavior.

An archive table breaks this symmetry. Delete is a move out, but restore is a fresh INSERT into a table that kept changing while the row was gone. Two things go wrong. A unique value such as a username or slug may have been reused. A parent row the archived record referenced may itself have been archived or hard-deleted, so the foreign key rejects the insert. If the live table uses GENERATED ALWAYS AS IDENTITY, you also need OVERRIDING SYSTEM VALUE to put the original ID back.

A safe restore runs in one transaction. It selects from the archive with an EXISTS check against the parent table and inserts with ON CONFLICT ... DO NOTHING plus RETURNING. It deletes the archive row only when a row came back. DO NOTHING skips conflicts silently, so the application has to treat "zero rows returned" as a restore failure that needs a human decision. Restoring a parent also means restoring its children in dependency order.

Neither pattern actually removes data. Erasure and privacy requests need a real purge path that hard-deletes rows from the live table, the archive table and any derived copies.

What happens to bloat, vacuum and planner statistics when most rows are soft-deleted?

To Postgres, a soft-deleted row is an ordinary live tuple. It occupies heap pages, gets scanned by sequential scans, is read by every VACUUM pass, goes into every base backup and pg_dump, and feeds the statistics that ANALYZE collects. A partial index keeps index size proportional to the live set, but the heap underneath it is not affected.

Illustrative estimate: assume a table with 50 million rows, of which 2 million have deleted_at IS NULL. Under those assumptions, about 96% of the heap is rows the application never reads. A query the planner turns into a sequential scan reads all 50 million. Autovacuum's trigger scales with table size. At the default scale factor of 0.2, roughly 10 million dead tuples must accumulate before a vacuum runs. Updates to the 2 million live rows could churn through the live set several times over without crossing that threshold. Planner statistics are sampled across all 50 million rows. null_frac on deleted_at is tracked, but correlations between deleted_at and other filter columns are not, so row estimates for combined predicates can be badly off.

A periodic purge restores proportionality, but it costs something. Batched DELETEs create dead tuples that vacuum must clean up. The freed space is reused, but the file rarely shrinks without a rewrite that takes a heavy lock. Someone also has to own and monitor the job.

An archive table puts the cost on the write path and the restore code instead. The live table stays small, and the archive can be partitioned by time so that old data is removed by dropping a partition rather than deleting rows.

Who should use deleted_at, and who should use an archive table?

deleted_at fits teams where:

  • Users or support staff need to undo deletes, such as restoring a project, a document or an account shortly after it was removed.
  • A small number of well-known code paths read the table, so the filter can be enforced in one place: a repository layer, an ORM default scope or a view.
  • Deleted rows stay a small share of the table, or a purge job keeps them that way.
  • Analysts regularly query deleted and live rows together.

An archive table fits teams where:

  • Many services, reports or ad hoc SQL users read the live table, and nobody can guarantee that every one of them applies a filter.
  • Deletes are frequent, but restores are rare and can go through a deliberate, reviewed procedure.
  • The live table has to stay small for hot-path performance, while the archive can be slow, time-partitioned and cheap to drop.
  • Schema migrations are disciplined enough that someone will update the archive whenever the live table changes.

When should soft deletes be avoided?

Skip both patterns, and use a plain DELETE, in these cases:

  • Nobody ever restores. If no product feature or support process brings rows back, a soft delete only adds filter discipline, index design and purge work without any benefit.
  • The data must actually disappear. Credentials, tokens, and personal data that is covered by erasure requests should be hard-deleted. Keeping it under a deleted_at flag or in an archive keeps it in backups, replicas and exports.
  • The data is high-volume and short-lived. Sessions, rate-limit counters, job queues and caches churn too fast. Keeping their history bloats the table for no reason, and dropping time partitions is usually the better tool.
  • Integrity depends on cascades. If correctness relies on ON DELETE CASCADE or ON DELETE RESTRICT across many tables, deleted_at silently disables those guarantees. You would have to rebuild them with triggers.
  • The table has many uncontrolled readers. Without a single enforced read path, deleted_at will eventually leak deleted rows into counts, exports or user-facing pages.

What are the alternatives to deleted_at and archive tables?

  • Hard delete plus an audit log or event history. Delete the row and record the change, including the old values if needed, in an append-only log written by the application or by a trigger. This keeps the schema simple. Restores are manual or tooling-driven.
  • Trigger-maintained history tables. A trigger copies the old version of a row into a history table on every UPDATE and DELETE, along with a validity time range. This captures every change, not only deletes, and supports "what did this row look like on date X" queries. It costs more writes and storage than an archive table that only records deletes.
  • A status column instead of a timestamp. An enum or text status with values such as active, suspended and deleted works the same way as deleted_at. It has the same filtering burden, but it suits domains where "deleted" is one of several lifecycle states.
  • Time-based partitioning. For data that expires as a whole, such as logs or events, partition by time and drop old partitions. This avoids per-row deletes, dead tuples and vacuum work.
  • Change data capture into a separate store. Logical decoding can stream deletes out of Postgres into an analytics or history store. The operational database stays clean, and deleted data is available for analysis but cannot be restored with one statement.
  • Backups and point-in-time recovery. These protect against disasters, not individual mistakes. Restoring one row from a backup is slow and manual, so do not treat PITR as an undo feature.

How can choosing the right pattern save engineering time?

Most of the time spent on soft deletes goes to incidents and maintenance, not to the first implementation. Picking the pattern that matches your workload removes whole categories of that work:

  • Fewer silent-data bugs. If the table has many readers, an archive table or a hard delete removes the "forgot deleted_at IS NULL" bug class entirely. You no longer have to find the report that has been counting cancelled orders for months.
  • Cheaper support requests. If restores are routine, deleted_at turns "please bring back my project" into a single UPDATE that a support tool can run safely. The alternative is an engineer working through archive conflicts or a backup.
  • Less schema-drift work. If the schema changes often and restores are rare, avoiding an archive table removes the need for a matching migration on every change.
  • Less performance firefighting. Partial indexes, a scheduled purge, or partition drops designed in from the start prevent later work on bloated heaps, slow sequential scans and poor row estimates.
  • One place to enforce the rule. Centralising the filter in a view, a repository layer or an ORM default scope, and the cascade in a trigger, means code review checks one definition instead of every query.

The general principle is that you should pay the cost of deletion where it is cheapest for your team: at read time with deleted_at, at write and restore time with an archive table, or at log-retention time with hard deletes and an audit trail.

When should you use deleted_at, an archive table, or neither?

Answer these five questions before choosing:

  • Deletion volume. If deleted rows stay a small fraction of the table, deleted_at costs little. If deletes are routine, as with expired sessions or abandoned carts, the live table fills with rows that every query must skip.
  • Retention and erasure. If you have legal erasure requests or retention limits, every pattern needs a scheduled purge job. A soft delete does not remove the data.
  • Restore frequency. Frequent self-service undo within days favours deleted_at, because restoring is a single UPDATE.
  • Query paths. Count the ORMs, raw SQL, reports and services that read the table. Each one is a place where someone can forget the deleted_at filter.
  • Reporting. If analysts need deleted and live rows together, deleted_at handles that by dropping a predicate. An archive table needs a UNION.
deleted_at Archive table Hard delete + audit log
Restore One UPDATE Copy back; may conflict with new rows Rebuild from history, rarely automated
Query-path burden Every reader filters None on live table None
Live table size Grows with deletions Stays small Stays small
Erasure Purge job required Drop old partitions Purge log entries
Engineering time spent on Filter discipline, indexes Move/restore code Log schema, replay tooling

Default to deleted_at only when deletions are rare, short-lived and need fast restore. Move to an archive table when deletes are high-volume, restores are occasional, or many query paths touch the table. If nobody restores and you only need to know what happened, use neither: hard delete the row and record the change in an audit log or event history. That keeps the schema simple, and the cost moves to log retention.

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.
  • 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.