Every team that’s been burned by a bad DELETE eventually lands on the same fix: stop deleting. Add a deleted_at column, filter it out in every query, keep the row forever. It’s an easy sell, you get an audit trail for free, undelete becomes a one-line UPDATE, and nobody has to explain to a customer why their data is actually gone. Six months later the table that used to answer in a fraction of a millisecond is doing real work on every request, and nobody in the room touched the query. They touched the delete, or rather, they stopped touching it at all.
Soft delete doesn’t fail loud. It doesn’t throw, it doesn’t error, it just quietly keeps every row you have ever written and asks your indexes to filter around a table that only ever gets bigger. That’s a decision people make once, in a design doc, and then never measure again. If you’ve ever added a deleted_at column and called the job done, this is for you.
The problem
DELETE and soft-delete look interchangeable at the call site, both just remove a row from what your app can see, but they are not interchangeable underneath. A real DELETE marks the tuple dead and VACUUM reclaims that space for reuse by the next insert, so a table that deletes as much as it inserts stays roughly flat. A soft delete is an UPDATE, and from Postgres’s point of view the row is still live, it just has a timestamp in a column now. VACUUM has nothing to reclaim, because nothing was removed. The row sits in the heap forever, every index on the table carries it forever, and every query that only wants the “active” rows has to filter it back out at query time instead of the table simply not having it anymore.
I built two identical order tables to put a number on that.
Two tables, same schema
hard_delete_orders and soft_delete_orders, same columns, same primary key, same created_at index, both with a deleted_at timestamptz null for schema parity. “Removing” a row means DELETE on one table and UPDATE ... SET deleted_at = now() on the other. Fifty churn cycles, each one inserting 5,000 rows and then removing 4,900 of those same freshly-inserted rows, chosen at random across the whole batch rather than only the oldest ones. That last part matters, if only old rows churned, a query for recent rows would never see the dead ones and the whole effect would hide. Both tables get a plain VACUUM ANALYZE after every cycle, the realistic autovacuum-equivalent, not a defrag.
Table heap size across 50 churn cycles
Twenty-seven times the disk for the same working set isn’t the whole story though, disk is cheap. The part that costs you is what a bigger table and a bigger index do to the query that has to walk them.
Where it actually shows up in a query
A primary-key lookup doesn’t care how bloated the table is, it’s one index descent either way. What every app actually does over and over is the other query, “give me the active rows,” the one that has to walk an index and skip past whatever’s dead along the way.
PK lookup vs active-rows query latency, p50
WHERE deleted_at IS NULL ORDER BY created_at DESC LIMIT 50) is where it shows up: 0.274ms on hard-delete vs 0.503ms on soft-delete, about 1.8x. EXPLAIN (ANALYZE, BUFFERS) on the soft-delete side reports Rows Removed by Filter: 1948, it walks past 1,948 dead rows in the index to find 50 live ones, versus 0 for hard-delete, whose index only ever contains live rows. Measured on PostgreSQL 16.14, 1,000 iterations per query, results in benchmarks/soft-delete-vs-hard-delete/results/query_latency.csv and explain_summary.csv.That’s the whole mechanism in one number. The index isn’t wrong, it’s doing exactly what a b-tree does, walking entries in order and testing the filter on each one. It’s just that on the soft-delete table, an increasing fraction of what it walks past is dead weight it has no way to skip, because from the index’s point of view a soft-deleted row looks exactly like a live one until the filter runs.
The fix: index only the rows you still call live
The column that’s making the index slow, deleted_at, is also the column that tells you exactly which rows to leave out of it.
CREATE INDEX soft_delete_orders_active_created_at_idx
ON soft_delete_orders (created_at DESC)
WHERE deleted_at IS NULL;
A partial index only ever contains rows matching its predicate, so it doesn’t grow with the soft-deleted rows at all, it grows with your active set, same as hard-delete’s index does.
Secondary index size, full vs partial
One honest wrinkle before the payoff: hard-delete’s index at 8,352 KB is a lot bigger than you’d expect for 5,034 rows, bigger even than it has any right to be next to the fresh 128 KB partial index covering roughly the same row count. That’s not a soft-delete effect, it’s a separate b-tree phenomenon. pgstatindex() on hard-delete’s own created_at index shows avg_leaf_density of 1.6% and leaf_fragmentation of 97%. A monotonically increasing key under heavy insert-and-delete churn bloats a b-tree through page fragmentation regardless of delete strategy, new inserts always land on a fresh page at the end, and a plain VACUUM reclaims dead entries but never rebalances the mostly-empty pages left behind. It’s a real Postgres gotcha worth knowing about on its own, but it doesn’t touch the partial index, which is freshly built and unfragmented, which is exactly why 128 KB is the clean number to trust here.
Active-rows p99 latency, before and after the partial index
EXPLAIN (ANALYZE, BUFFERS) execution goes from 1,993 shared buffers and 0.239ms to 39 shared buffers and 0.020ms, and Rows Removed by Filter drops from 1,948 to 0. Measured on PostgreSQL 16.14, 1,000 iterations, results in benchmarks/soft-delete-vs-hard-delete/results/partial_index_fix.csv and explain_active_rows_soft_partial.txt.51x fewer buffers touched on that sampled execution, 12x faster on it specifically. The partial index doesn’t just get smaller, it turns “scan past ~2,000 dead rows to find 50 live ones” into “read 39 buffers and you’re done.”
The takeaway
Soft delete isn’t free, it’s deferred. The cost doesn’t show up on the day you ship the deleted_at column, it shows up months later as a table that never gets smaller and an index that has to work around dead weight it can’t skip. The fix isn’t “stop soft-deleting,” an audit trail is usually the entire point of choosing it in the first place. The fix is a partial index on whatever query path actually reads your active rows, WHERE deleted_at IS NULL (or your equivalent) is a predicate Postgres can push down to the index instead of filtering row by row at query time. Add it when you add the column, not after the table’s 250,000 rows deep and someone’s asking why the dashboard got slow.
And know what it doesn’t fix: the partial index shrinks the index and speeds up the query, it does nothing about the heap itself. The table in this benchmark is still sitting at 42.3 MB carrying every row it’s ever held, that number doesn’t move unless something eventually hard-deletes or archives rows past their retention window. Soft delete without a purge job isn’t really a decision anymore, it’s just a table nobody ever finishes deleting from.
The harness, all three experiments and the raw CSVs, is on GitHub. These are laptop numbers meant to show the mechanism and the size of each effect, not capacity planning for your table, run it against your own schema and churn pattern before you quote a bloat ratio to anyone.
Comments