The Row Is Still There
Soft delete is the first schema decision most teams make and the one they are least likely to revisit. Marking a row deleted instead of removing it sounds free: nothing is lost, the audit trail is intact, and the undelete button works.
The row is still there. Everything below follows from that one sentence.
A million users in a real PostgreSQL, seventy percent of them soft deleted, which is what a table looks like after a few years of a delete button that does not delete.
Soft delete is often the right decision. The point of this lesson is that it has a price, the price grows with the table, and two partial indexes stop most of it.
What The Deleted Rows Cost
Every number is measured against a PostgreSQL this lab started on a scratch directory and tore down.
Both hold exactly the same live data. Seventy percent of the rows in the first are invisible to every query the application makes, and every one of them is on disk, in the page cache, and in every index.
The ratio moves with the share deleted and the shape does not. Whatever fraction of a table is dead, that fraction of every scan, every index page and every cached block is being spent on rows nothing will ever return.
The measured point is the last one. The rest of the curve is arithmetic on the same table, and its shape is why this problem is invisible for years and then suddenly is not.
Plot live rows over total rows on every table with a deleted flag. It is one query, it costs nothing, and it is the only number that tells you the schema has quietly committed to storing every row the product has ever created.