mediumDesignScenarioMidSeniorStaff
Every table in this schema soft-deletes with a deleted_at column. A year in, what has that cost you?
5 min readUpdated 2026-08-15Target archetype: Big Tech, Enterprise Captive, Product Startup
Answer
Short answer
Soft deletion is a modelling decision rather than a convenience: it silently breaks uniqueness, leaves foreign keys pointing at rows that are logically gone, and adds a predicate every future query has to remember.
Was this answer helpful?
Likely follow-ups
- A customer exercises a right to erasure. What does this design have to do that it currently cannot?
- How would you migrate a table that already holds two years of soft-deleted rows into your model?
- Who is permitted to read the deleted rows, and at which layer do you enforce that?
- Half the table is dead rows. What does that do to the planner's estimates and to your indexes?
Related questions
- We sell to trade customers who each negotiate their own prices, and those prices change. Design the schema.hardAlso on data-modelling and constraints5 min
- Half the catalogue has attributes the other half does not, and merchants add new ones weekly. How would you store them?hardAlso on data-modelling and schema-design7 min
- A table has forty million rows, thirty-five million of them soft-deleted, and every query for active rows has got slower. Would a partial index help, and what would you have to be careful about?hardAlso on soft-delete and postgresql5 min
- Your team says the database is backed up nightly. How would you establish whether you could really restore it?hardAlso on postgresql5 min