▲ 275 points
back
102 comments
This is another solution to such a common problem that one might be surprised at there being no solution baked into the SQL standard. Instead, we have some vendor specific features like automatic audit tables and time travel, and a huge array of bespoked techniques like in the article: everything from adding a deleted_at column through to re-architecting your system around event-sourcing.
Why such diversity of solutions? I believe this is because the problem of adding time dimensions to your data model is heavily dependent on exactly what you intend to do with your data. Enabling theoretical audits is a very different requirement than enabling admins to rollback changes to data, which is a very different requirement to enabling users to undelete their data - and then there’s a performance angle to layer on top: your design will depend on whether you need your INSERTs/UPDATEs to be fast or your SELECTs.
It’s definitely not a case of one of these approaches being definitively better than another. You can’t shortcut talking to your users/clients/stakeholders about how the whole system is intended to work.
The SQL:2011 standard does describe a mechanism for solving this called System Versioned Tables, but the only database I've encountered that implements it so far is MariaDB: https://mariadb.com/kb/en/system-versioned-tables/
https://en.wikipedia.org/wiki/Temporal_database lists a few more - apparently there are versions of this in Oracle, DB2 and SQL Server now.
As many commenters pointed out, the ability to revert deletions is a highly desired feature. I cannot remember how many times our customers have deleted records by mistake and while deleted_at solution makes a revert trivial (update .. set deleted_at = null where undo_condition = true) it's much more complicated with the proposed condition. Why?
In any living system db schema is something that continuously evolves. New foreign keys, new columns, dropped foreign keys, dropped columns, new indexes. If soft deleted data remains in the tables it evolves with the rest of the system, but that's not the case if it all went into a json blob. Are you sure you want to remember all changes made to your data while trying to restore it?
The problem with queries is there, but I'm wondering about the scale of the problem. From my experience, tables do not go from hard deleted to soft deleted often, hence it's more a matter of habit to check the structure of a table you see for the first time and take deleted_at into account in case it's there.
As the author says, it's all about trade-offs, I would use an audit log for debug purposes (deleted_at does not answer who did it, adding deleted_by to every table adds a risk of split brain - what if deleted_at is null, but deleted_by is not?) and deleted_by to enable quick reverts to accommodate mistakes users and developers do.
A more problematic pattern for me is to trace the changes for different columns. Let's say you tag your uses as being a superhero. The moment you introduce this prop, analysts will immediately ask you who, why and how many times has changed this field and what was the value of the field at the time X. One can say that it's not necessary for all the fields, but I do observe that I have much less trouble in the development if I accommodate for it from the start rather then add hacks to support it later
I’ve been using this exact approach for years and strongly advocate for it.
The deleted_at approach is rife with problems since you have to ensure it’s checked in every single query, including joins where it may be referenced. Getting the record out of the table is critical.
You can get around the "check in every transaction" problem with an ORM, but now you're (more) coupled to your ORM which you will occasionally inevitable need to circumvent for something or other. And now you've made it (more of a) leaky abstraction.
Setting up row level security policies to exclude rows `where deleted_at is not null` solves most of the issues with the discarded solution. Of course it would be crazy to have a system where you will need any extra where clauses for the default queries being made. You can even make a simple function that sets a statement level variable checked in the rls policy so that deleted rows can be included or only deleted rows be returned. The only negative thing I see about using a deleted_at column is that cancelling the delete in the before delete trigger changes the resulting "deleleted rows" count to be 0 instead of the expected number.
Excluding the rows still doesn't solve problems with foreign keys (you can't DELETE CASCADE and instead have to iterate all relationships manually). It also means you still need to remember to consider deleted_at when doing things like setting up unique indexes.
What I like about this construct is the fact that it uses the looser JSON semantics for the typically ad-hoc nature of recovering deleted entries - which does not necessitate the full power of referntial consistency.
What I'm not sure about is how will it will behave with blob fields and other data types with "problematic" serializations.
In some cases a soft delete is the only option. Say I run a chain of stores and wish to close a particular store mid year. The existing sales data still needs to reference the now closed store, but any other attempt to interact with the store should be prevented.
The application and its use of data must take this into account. I don't believe there is some magic bullet here. However, audit tables are invaluable for tracking changes and culprits.
Eventually, the store and all its data can be deleted using cascade delete to maintain referential integrity.
I’ve found similar and settled on three distinct solutions that tackle different aspects of the problem that soft deleting covers.
The first is analytics/reporting, which can be used to aggregate or otherwise store data without being coupled to the main application (therefore, not a problem to delete data if it’s been processed already).
The second is auditing to maintain a paper trail.
The third is a soft delete backed by a TTL, basically so there’s a window of time to undo the operation, but the data doesn’t remain there forever (which might be a problem with GDPR and the like - depending on what your data is.)
Main reason I had for trying to break it down this way was because soft deletes in a relational database, with foreign key constraints, becomes unintuitive pretty fast. You need escape hatches to properly delete records, you need to remember to filter deleted ones from your results, and then there’s even more complication when trying to handle this across associations. So the final piece is only to soft delete things that require it, rather than enforcing it across all tables.
I wrote a specific library to do this (automatically generates a mixin for SQLAlchemy and installs a hook which rewrites all queries, removing soft-deleted items from queries and also relationships) so that the soft-delete problem becomes a non issue and you still have the data there if you want to revert/activate something
With a procedural language and SQL introspection features, you can programmatically generate triggers, functions and backing tables for these kinds of things. For Postgres, this SQL can generate temporal tables backed by JSONB for any table:
https://github.com/solidsnack/macaroon/blob/master/temporal....
It would be pretty easy to change this to perform deletion-with-interning like mentioned in the article.
In an older version, the code actually replicated the schema of the source table in the log table; but it can lead to problems during migrations.
There's at least one factor which I don't see consideration for here.
Under GDPR, CCPA/CCRA, and a number of other privacy laws, if you're going to retain data related to people you need to provide those people some way of retrieving that data or requesting that it be erased. Putting the data into a "deleted_record" table doesn't remove that obligation.
So, if you're going to have a bunch of "deleted" records hanging around, you need some way to figure out who they belong to. And I don't see any way to do that with this schema, short of rehydrating all the rows and following the original foreign key relations.
Can't you use json operators [1] for this?
-- Permanently delete message sent by jon
DELETE from deleted_record where table_name = 'messages' and data->>'sender' = 'jon';
This is an area where technologists and lawyers will end up disagreeing and fighting about boundaries etc.
Does it still count as your data if there is no normal way to retrieve/access it in the software?
If you say "yes", here's what this implies: if you have deleted the data, but it's still on the disk because the drive heads haven't wiped it yet (it's just been deallocated), then it's still accessible.
So, whenever there's a GDPR request, you should run disk recovery software? (The answer is no; you'd have to butt up against pretty thick lawyers and judges to be fined for this)
If you have an audit table that is automatically deleted after a while, and the audit table cannot be accessed as part of normal operations, then IMO you will be able to argue that it's not part of data that should be "reasonably accessed" via GDPR.
When you look at the spirit of the law, it also does make sense (disclaimer: I am a HUGE proponent of GDPR). What matters is that users have access to the data that the company has access to, and is able to correct and delete it. If the data is not normally accessible, and will soon be deleted, then it doesn't matter.
Something I’ve been meaning to play with is setting a “PII” comment on a table/field, then scanning over the schema if I need to find places to check for encryption etc
Perhaps something here that could mark it with a uuid so if it needed to be fully deleted, it could be found easily and removed or overwritten
I’ve recently put together an awesome list about temporality, including: soft delete, time travel, slowly changing dimensions, and bitemporality. https://github.com/daefresh/awesome-data-temporality
I did something similar to this many years ago but didn’t limit to deleted records and instead created an audit log with a very similar approach. It worked nicely and provided a view into how data was changing by our users.
I considered this for an audit log as well, but ran into a roadblock in terms of associating the operation with a logged in user who triggered the change..
how did solve that particular issue? or was it not a requirement in your case?
Reinventing standard database features like audit tables, sql server has such out of the box, same for any major databases.
Would “AS OF SYSTEM TIME” from SQL:2011[0] standard do the trick?
> I’ve spent the time migrating our code away from deleted_at, and we’re now at the point where it’s only left on a couple core tables where we want to retain deleted records for an exceptionally long time for debugging purposes.
Sounds like the problem is not soft deleting, but applying soft deleting to _everything_ without thought.
Then he goes on to suggest an alternative that is even more complicated.
Just include a where deleted at is null check. Hide it behind some interface in your ORM if you dont want to think about it.
It's a set-and-forget solution, where you only have to reason about it locally to the schema. As they point out, `deleted_at` is viral, you have to incorporate it into every query that touches a table with that column type, otherwise you might get into weird behavior or possibly vulnerabilities/disclosures.
A set and forget solution at the ORM level is begging trouble IMHO. I don't like ORMs, but I really don't like the idea of my ORM being even more magical than it already was.
This is addressed in the previous article:
> Some ORMs or ORM plugins make this easier by automatically chaining the extra deleted_at clause onto every query (see acts_as_paranoid for example), but just because it’s hidden doesn’t necessarily make things better. If an operator ever queries the database directly they’re even more likely to forget deleted_at because normally the ORM does the work for them.
I ran into this exact issue last month. Very common for places to have a general "soft delete unless you have a good reason not to" policy, and very common for people to forget about the deletion flag when writing joins by hand or doing reporting.
Actually this is what they are defending against: "dozens of bugs and countless hours of debugging time as people accidentally omit deleted_at IS NULL from production and analytical queries."
Those queries could be raw SQL or from different ORMs and applications, maybe written without a full understanding of the database.
However the claim is not substantiated: how many bugs of that type did they had before?
There is no reason to take on the tech debt of an ORM when you can use RLS to solve this be defining a policy that excludes records where deleted_at is not null.
What’s the actual use case for soft deletes. Audits? Have an audit log then. There’s little reason to have deleted but not really deleted items in your database.
‘Recycle bin’ type functionality for data is usually highly desirable for customers.
They want the ability to undelete stuff they accidentally delete, since accidental deletes happen all the time.
True for consumer users. Super super true for enterprise users.
What's a use case for not even allowing soft deletes?
In most case, i just see deletion as change status from `active` to `deleted`. You should never remove the actual record from database.
That's illegal in Europe, and unethical everywhere.
Often other tables have foreign keys to the record to be deleted.
What’s a good pattern for treating those related tables for every time a relationship row gets deleted/removed as per the OP blog post?
Really depends on the related data, but there‘s the option to use ON DELETE CASCADE|RESTRICT and set null or set default.
Cascade can be a bit of a footgun, as this can trigger a waterfall of deletes.
https://github.com/xocolatl/periods periods does this for PostgreSQL.
Great solution. Unfortunately only works on installations that support extensions, which excludes most managed database services like AWS RDS.
I like how you tried to track the standard as close as possible. I've seen (and written) ad hoc solutions that hard-code too much or mandate certain columns to be present.
That said, unlike the standard and most other RDBMSs, Postgres supports range types. Seems a shame to rely simply on two timestamptz columns when one tstzrange should suffice.
JSONB seems like something I need to put a few nights into understanding, but my time is short and filled with diapers and bottles.
Unless you’re really into the implementation details of individual postgres column types that seems of little interest.
Jsonb just means “parsed json” (lit. “json, binary”), meaning postgres parses the data to a binary representation upfront which allows for more efficient json operations.
However that comes at increased insert and storage costs. It also leads to postgres normalisation so values don’t round-trip textually (which can surprise).
jsonb is a viable alternative to mongodb. im not doing blow by blow feature parity...but in 90 percentile usecase, you can replace mongodb with jsonb
Good to see you have your priorities sorted.
Sincerely, a dad with 5 kids.
oh nice.
how do you recover deleted records?
It seems you should be able to do it fairly straightforwardly with dynamic sql with this structure, but, I don’t know why you wouldn’t just use proper history tables, rather than story only deleted records, but lumping them all into one table.
I’ve never encountered a database where I needed to know about non-current records but only ever the last-before-deletion state of deleted records, whether for data recovery or any other purpose.
Exactly. The whole point of soft delete is data recovery, as it’s not sufficient for anything else (like auditing).
My question when people implement a generic feature like this, is why doesn’t the database do it? And many tunes, as is the case with soft delete, it does. For example, Redshift tombstones records and you can choose when to “vacuum” them up (actually delete them).
Usually if you’re changing the way a primary function of the database works, like delete, it’s probably not a good move.
Sounds like a DSGVO/GDPR nightmare. And I really don't want to explain why the deleted data are part of some sold stolen data blob. There might be some technical use cases, but in general it has many culprit
If you want to delete something, delete it.
If you want to restore something, get it from a backup.
If you want to delete something, but you fear that it will ruin something in your db because the architecture is a mess and you are not really sure what references what and what will break, then soft-delete it.
But what is the point of this?