alt Hacker NewsI don't get why all of the big RDBMSes (PostgreSQL, MariaDB/MySQL, SQL Server, Oracle, ...) don't seem to have built in support for soft deletes up front and center?
CREATE TABLE ... WITH SOFT DELETES
If we like to talk about in-database processing so much, why don't we just put the actual common features in the DB, so that toggling them on or off doesn't take a bunch of code changes in app, or that you'd even be able to add soft deletes to any legacy app that knows nothing of the concept, on a per table basis or whatever.
Replies
The whole model of RDBMS is based on mutable tuples; soft deletes don't make much sense as an intrinsic part of that model. If you want soft deletes, you create an application layer or use a different data model.
Most of the time if you want "soft deletes", you really want an immutable log so that you time travel to any point in the history. XTDB and Datomic are worth looking at if you want to solve the problem at the data model level.
Because it's too dependent on business logic.
Different products will handle soft deletes differently. Which queries need to include soft-deleted rows and which don't? What about different levels of soft deletes, e.g. done by the user (can be undone by user) vs. done by an admin (can't be undone by user)?
Implementing soft deletes yourself isn't hard. Yes you'll have to make a bunch of decisions about how they work in every circumstance, but that's the point.
Temporal tables in SQL server fit this use-case[0], I think.
0: https://learn.microsoft.com/en-us/sql/relational-databases/t...
It's just not bothersome enough to deviate from the standard.
If they did this, nobody would use it. They do lots of more useful things that people don't use because it's not portable.
There's a sibling comment about temporal databases. Those solve a very bothersome problem, so a few people use them. That means that there's a chance soft deletes get adopted as a side effect of a much more complex standard.
old problem. in postgresql many ways already exist to keep deleted data hanging around, not the least of which is explicity archiving the delete transaction in an archive table. for legacy code that can not be changed: triggers, row level security, tailing logical replication log.
Oracle has this already. SELECT ... AS OF timestamp.
It needs to be enabled of course and it's not free.
Maybe my intuition is wrong, but to me this sounds like a violation of the principle of least power.
"Soft deletes" is just a name for a regular write operation, with specific semantics.
Adding a layer of magic to the DB for this doesn't seem right to me.
And applications could have many different requirements for soft deletes, like the article points out. For example, the simplest version would be just a boolean "deleted" column, but it could also be "deleted_at", "deleted_by", etc.
All of these cases require an bunch of code changes anyway, and the more complex ones could interfere with an implementation of this feature at the database level: such a transparent implementation couldn't access app-specific concerns such as user data, for example.
Adding soft deletes to a legacy app that knows nothing about it would only work for a boolean flag and a maybe date-time value, unless the DBMS would also offer triggers for soft deletes etc?
Seems to me to that this capability would make a DBMS much more complicated.