The Hidden Costs of Soft Deletion

Soft deletion is a widely used pattern: instead of issuing a DELETE, you add a deleted_at timestamp to a table and perform an update, marking the row as removed rather than actually removing it.

UPDATE foo SET deleted_at = now() WHERE id = $1;

The appeal is clear. Deletion becomes reversible. If a record is truly gone via a hard DELETE, recovering it means digging into the storage layer — difficult at best. With soft deletion, you simply set deleted_at back to NULL and the record is back:

-- and like magic, it's back!!
UPDATE foo SET deleted_at = NULL WHERE id = $1;

But that theoretical reversibility comes with some serious practical downsides that often outweigh the benefits.

Deletion Logic Leaks Everywhere

The first problem: every query in your codebase must now remember to filter out soft-deleted rows. A typical select needs an additional predicate:

SELECT *
FROM customer
WHERE id = @id
    AND deleted_at IS NULL;

Forgetting that deleted_at clause can silently return data that was supposed to be hidden, with potentially dangerous consequences. Some ORMs (like acts_as_paranoid) hide this complexity by automatically appending the clause to every query. But that doesn't make things safer — it makes developers less aware that the filter exists, and anyone querying the database directly (an operator running a quick check, for example) is even more likely to miss it.

Foreign Keys Become Meaningless

A more structural problem is that soft deletion essentially disables foreign key enforcement, which exists to guarantee referential integrity. Without foreign keys, you might delete a customer and forget its invoices, leaving orphaned rows. With them, the database blocks the operation:

ERROR:  update or delete on table "customer" violates
    foreign key constraint "invoice_customer_id_fkey" on table "invoice"

DETAIL:  Key (id)=(64977e2b-40cc-4261-8879-1c1e6243699b) is still
    referenced from table "invoice".

Soft deletion breaks this guarantee. A customer can be soft-deleted while its invoices remain active. The foreign key is still satisfied because the customer row technically exists, but there is no equivalent check enforcing that the invoices are also soft-deleted. The database can no longer help you keep related data in a consistent state, and you're left writing application logic to compensate for a feature the database could handle for you.

Hard Pruning Becomes a Nightmare

Regulatory requirements like GDPR make it increasingly unacceptable to retain data indefinitely. Soft-deleted rows are no exception, so you'll eventually need a batch process that hard-deletes old soft-deleted records after a certain retention period.

That's where things get messy. The foreign keys that soft deletion rendered mostly useless now work against you, because you can't remove a parent record without also removing its dependencies. ON DELETE CASCADE is an option, but it's generally considered dangerous for high-fidelity data. The alternative is complex, multi-table queries — often using CTEs in systems like Postgres — to delete an entire dependency graph in one operation:

WITH team_deleted AS (
    DELETE FROM team
    WHERE (
        team.archived_at IS NOT NULL
        AND team.archived_at < @archived_at_horizon::timestamptz
    )
    RETURNING *
),

--
-- team resources
--
cluster_deleted AS (
    DELETE FROM cluster
    WHERE team_id IN (
        SELECT id FROM team_deleted
    )
    OR (
        archived_at IS NOT NULL
        AND archived_at < @archived_at_horizon::timestamptz
    )
    RETURNING *
),
invoice_deleted AS (
    DELETE FROM invoice
    WHERE team_id IN (
        SELECT id FROM team_deleted
    )
    OR (
        archived_at IS NOT NULL
        AND archived_at < @archived_at_horizon::timestamptz
    )
    RETURNING *
),

--
-- cluster + team resources
--
subscription_deleted AS (
    DELETE FROM subscription
    WHERE cluster_id IN (
        SELECT id FROM cluster_deleted
    ) OR team_id IN (
        SELECT id FROM team_deleted
    )
    RETURNING *
)

SELECT 'cluster', array_agg(id) FROM cluster_deleted
UNION ALL
SELECT 'invoice', array_agg(id) FROM invoice_deleted
UNION ALL
SELECT 'subscription', array_agg(id) FROM subscription_deleted
UNION ALL
SELECT 'team', array_agg(id) FROM team_deleted;

The abbreviated version above is already unwieldy; a real-world example can involve dozens of tables in a single, fragile query. And this isn't just a one-time headache: if a new dependency is added to the schema later and someone forgets to update the pruning query, it will work fine — until the first hard-deletion cycle hits the new table and the whole process suddenly fails, possibly a year after the code change was made.

Does Undelete Even Get Used?

The core justification for soft deletion is recoverability after accidental deletion. But in practice, does anyone actually restore records this way? The author's experience, spanning more than a decade at companies like Heroku and Stripe — all of which used soft deletion — suggests it almost never happens. The main reason: data deletion almost always has side effects beyond the database. External systems may have been notified, objects removed from blob storage, servers spun down. Reversing the process means undoing all of those operations as well, and those "undo" paths rarely exist.

Stripe did occasionally undelete customer records in the early days, but only rarely and under exceptional circumstances. Even at Heroku, when an important user accidentally deleted an app, the team chose not to attempt undeletion despite having the infrastructure. The risk of an untested procedure failing during an emergency was too high. Instead, they rolled forward: create a new app and help the user copy over the data. Soft deletion was theoretically the perfect safety net for this exact case, and it still wasn't used.

A Better Pattern: Centralized Deleted Records

Soft deletion does have one legitimate use: it lets you refer back to deleted data for support tickets, debugging, or audits. But the traditional pattern of leaving rows in place in the original tables causes all the problems above.

A better compromise is to move deleted data to a dedicated table. A flexible jsonb column can capture the properties of rows from any other table:

CREATE TABLE deleted_record (
    id uuid PRIMARY KEY DEFAULT gen_ulid(),
    deleted_at timestamptz NOT NULL default now(),
    original_table varchar(200) NOT NULL,
    original_id uuid NOT NULL,
    data jsonb NOT NULL
);

Deletion becomes a two-step process — insert the record into the deleted records table, then hard-delete the original:

WITH deleted AS (
    DELETE FROM customer
    WHERE id = @id
    RETURNING *
)
INSERT INTO deleted_record
		(original_table, original_id, data)
SELECT 'foo', id, to_jsonb(deleted.*)
FROM deleted
RETURNING *;

This approach solves all the major issues with traditional soft deletion:

  • Normal queries no longer need a deleted_at IS NULL predicate everywhere.
  • Foreign keys remain fully functional, so you can't delete a parent record while orphaning its dependencies.
  • Handling regulatory data retention becomes trivial — you can prune old records with a single statement like DELETE FROM deleted_record WHERE deleted_at < now() - '1 year'::interval.

Accessing historical data is slightly less convenient than reading from the original table, but not significantly so. And yes, converting the rows back to their original table format for undeletion is awkward — but if you're honest about how often undeletion actually happens, that tradeoff is worth it.