Hacker News Reader

> What goes wrong with the classic soft-delete approach? The one where every table has a boolean is_deleted column and hopefully the deleting code updates the correct subgraph of records at the same time?

> It typically rots as you expand your model and add features. It's not clear what significance different tables have, and you you can't "restore" things with any reliability or safety. That may be because of relational/index conflicts, or because you don't have enough information about how it was originally deleted to actually "restore" something that is both correct and useful, or because there are actually 3 different kinds of restoration schemes/scopes but current problem calls for a fourth.

The long and short of soft deletes is that it breaks my data's correctness guarantees WRT referential integrity: i.e. I 'delete' a record while there are still references to it, and the RDBMS cannot even warn me, much less stop me!

If I wanted broken data, I'd use a NoSQL DB, or flat files even.

> I 'delete' a record while there are still references to it, and the RDBMS cannot even warn me, much less stop me!

Can't you do multi column FKs ( (otherid, active) references other(id, active) )?

Also makes UNIQUE indices much harder to use. You can't just delete and later re-insert the same data, you need to "undelete" it instead, but that can actually mess up the chronology of the data, so then you need a separate table to track dates... and things just get over-engineered quickly...

> For example, you don't soft-delete a customer row, you "deactivate" a customer

I'm all for naming things more precise, but functionally speaking at that point it's really just semantics, you still end up with some DB column acting as a flag, and from then on you need to take it into account in every single query that touches that table in the whole app. Since most modern ORMs know how to handle soft-delete internally, it's far easier to just stick to the defaults and use `deleted_at` if you really need a way to keep the records around. And you often need, for the referential integrity of the historical data.

> at that point it's really just semantics

Nah, There's a structural difference between (A) adding a status column to one table and changing your queries and joins to care/not care about it, versus (B) adding is_deleted to every table and layering get in as a bonus where clause everywhere.

In other words, The thing being solved is not suitable for a cookie-cutter table/row-level design. It's a real business-logic quality of your data model that lives in particular places and mean slightly different things in those different places.

This is a problem but IMO, the bigger problem is that you create a pit of failure rather than a pit of success because you now have to remember to add "and deleted_at is null" everywhere and forgetting to do so often doesn't look like a bug until the day some user sees something they thought had been deleted still appear.

Perhaps it's a scale thing, but I get more "oh crap, I just deleted this thing, can you get it back for me?" requests than I do bug reports about soft deleted records showing up incorrectly. Being able to restore records immediately is a big win imo.

Being able to contact a dev to undelete a record from a database does sound like a thing that's hard to scale. If this happens a lot, it could be worth examining if there's a UX problem.

So implement a trash can?

And how would you implement one, if not with soft-delete?

No, it’s still a soft delete, but do it so restoration doesn’t require manual action by a dev.

Author here.

This can be addressed with PG views. You create a view where deleted_at IS NULL and now you query the view instead. Furthermore, if the view is “updatable” (which is the case when it only has filters), you can also do updates and deletes directly against it, and it all just works. So as long as your ORM/query builder can work with views, you are good to go.

I understand many devs want to avoid moving more logic to the database (I also personally avoid stored procedures and similar) but views are quite declarative and simpler to manage than indexes.

I haven't used row level security, but wouldn't it be easy to set the table up such that only a dedicated recovery role could select rows that have deleted_at set? Then the view wouldn't even be necessary.

Postgres supports rules for rewriting queries

too much magic for my taste. I think the more proper thing would be to simply use views, and expose the natural absurdity of the design with one-view-per-table.

Right. I have never tried those but they sound unappealing to me. I would expect edge cases where they don't work and unpredictable performance implications. But as I said, I have no experience so this is just my imagination.

IDK. I think people can overthink the problem a bit.

I done the is_deleted flag with a deleted_at timestamp that gets cascaded down the table relations. Restoring is an admin job and the logic is to follow the table relations down again restoring linked objects with the same deleted_at timestamp.

I have this in a metadata database that controls a data warehouse. Accidentally knocking out a metadata record could nuke large parts of the warehouse so i have to have this and a locked flag that prevents records from being removed or even soft deleted once data is in the warehouse.

> I think people can overthink the problem a bit.

> I have this in a metadata database that controls a data warehouse. Accidentally knocking out a metadata record could nuke large parts of the warehouse

I think you might be underthinking your problem.

I don't have problems with that, we have effective controls. But the warehouse data requires context, and that's supplied via our metadata db. Once metadata starts having actual records associated with it you can't remove it, or you just end up storing junk.

Nullable deleted_at suffices. There is no need for an is_deleted flag.