The title here is a little misleading, because the author proposes a different way of implementing soft deletes (a deleted table vs a deleted_at column).
A better name might be: “rethink your deleted_at column”
This is (mostly) the way we did it at my last gig. The differences were:
Some tables had a corresponding history table that were a mirror of the core table with some extra metadata
Data was inserted on any change using a trigger
It was mostly used for auditing purposes. However, I do know of at least one case where someone restored rows from the history table because of a bad predicate on a update query.
Unfortunately, it was difficult to maintain because you had to keep the history table, trigger, and core tables in sync. It was a SQL Server so the JSON support was not fabulous and people generally avoided using JSON for columns.
The system was low volume so it mainly didn’t cause performance issues, but there was a period where an errant job caused tons of updates and ballooned the storage used by the server. Easy enough to fix, but annoying nonetheless.
It’s interesting that the article cites the possibility of foreign-key references as a downside of soft deletion. I usually only turn to soft deletion when I specifically want existing foreign-key references to keep pointing to the soft-deleted row. Undeletion hasn’t been a motivation at all when I’ve used soft deletion.
An example from the service I work on now, which manages inventories of seeds for reforestation projects: there’s a table that lists all the seed species a given project works with. Sometimes a project no longer wants to work with a given species, so naively we’d delete it. But we still want project-specific information about the species to show up when users browse past activity.
One option would be what the article suggests: moving deleted species to a separate table. But then what do we do with the foreign keys? We’d either have to have a separate “deleted species ID” foreign key column in all the tables that reference the species table, and modify all the queries in an even more intrusive way than adding deleted_time IS NULL, or we’d need a sprawling web of shadow tables for every table that could refer, even indirectly, to a deleted species, and add UNION clauses to any queries that could legitimately return both deleted and non-deleted data.
I’m surprised the article doesn’t mention an approach that works pretty well: views that automatically exclude the deleted rows. Give those views obvious, easy names and the underlying tables less-convenient names and people will generally use the views instead of the tables for ad-hoc queries. Using my example, you might call the view species and the table species_all or even species_including_deleted if you wanted to get very explicit about it. Write operations do get less convenient but not hugely so.
What you’re doing sounds perfectly reasonable and sensible and this isn’t a criticism, but… the operation you’re doing there doesn’t sound like a deletion. It sounds like the rows record that “a given project once worked with this species”, and the flag that you are calling “deleted_at” could be called “stopped_working_with_at”.
There are two different aspects to soft deletion, and this article only discusses one.
The article focuses entirely on customer action driven soft deletes. At AWS many teams decide to not do this, to their regret. At our scale customers regularly nuke their own production assets and cry loudly for help. In my team, DynamoDB Backup and Restore, we have automatic and well tested mechanisms to support this. It goes without saying as a SaaS you need customer-driven soft-deletes.
The other angle is system-driven deletes. As a database service we prune old data. This is frightening. We have layers upon layers upon layers of redundancy, reconciliation, bit-rot detection, and using e.g. S3 tagging with lifecycles to mark data as to be deleted. These mechanisms have caught bugs. We have not lost data. So system-driven soft-deletes are even more mandatory than customer-driven soft deletes.
The article seems obsessed with relational databases, these are prohibited for AWS services, but the same challenges of data consistency exist for NoSQL databases.
Soft deletion is definitely worth it, and if you don’t have it you may not care about your customers.
Hmm. To add another perspective, Discourse uses soft deletes for post content but hard deletes for user accounts, for several reasons.
Undeleting a post is done on a regular basis. Reviewing deleted post content in the context it was originally posted is a critical part of moderation workflows.
On the other hand, a user account has so many foreign keys that it’s not worth attempting to recover it, and they just tell people to create a new account and transfer the posts over (this part is manual work for the admins).
All the other database tables with soft deletes? I don’t think anyone has really thought about the design very hard, or ever attempted to recover anything. Definitely a “toxic data” risk.
I don’t think anyone has really thought about the design very hard, or ever attempted to recover anything.
I feel like this is a weird statement, but I’ve also spent a decent chunk of my career working in regulated industries with rules about what you could delete, what you couldn’t delete, and what you had to keep even if you were asked to delete it.
I am quite glad that online web forums are not what people typically call “regulated industries” (outside of China I suppose, I’ve heard of heavy regulations there).
On the other hand, a user account has so many foreign keys that it’s not worth attempting to recover it, and they just tell people to create a new account and transfer the posts over (this part is manual work for the admins).
At a previous job, we did the reverse because of the many foreign keys. Deleting entities created by an inactive user (ie. a former employee) was not an option for most of our customers, so we would instead “scrub” the user down to have only the UUID and a default name (“deleted user”).
I think making the foreign keys linking to the user nullable/optional is a dangerous path (and could result in critical bugs), and also makes any kind of “data forensics” near-impossible. Having a separate table with deleted users is only good for avoiding the “AND deleted_at IS NONE”-missing query pitfall, in exchange for having another foreign key column and additional checks to see if either the “normal” foreign key is set OR the deleted-user foreign key.
We did have to implement hard-deletion (for GDPR reasons when a customer churned with all their data), and it was a PITA, so I am not saying soft-deletion is easy. But there’s a lot of upfront work in handling hard deletion well if you need to support retention of related data (ie. user posts).
I have used soft deletes for many, many years largely as a “recycle bin” with the back office system having a toggle for showing deleted items. It’s helped on more than one occasion where a user has accidentally deleted something and allows for the deletion confirmed modal to have an undo button.
Most of the systems I worked on had a data retention policy stating that deleted items would remain recoverable for no longer than 30 days post deletion. Some had different data retention policies based upon data type, with user accounts tending to have the shortest.
Yes, I think that the article isn’t very clear about the intent of the flag. If it’s like in your case, a recycle-bin, then it’s actually a feature. Then you “can’t” forget this by accident, because “deleted” isn’t a flag, it’s one of the possible states for the item and you design it intentionally. If it is meant as a disaster -recovery type of thing like the article seems to imply, then it is implemented on the totally strange level. In other words, of course it’s not worth it and even the “workaround” suggested (different table) is still not a very good way to solve the problem.
We’ve been using soft deletes on my system at work for 5 years now and I’d never go back. But we’ve never once used it as an undelete; it’s been hugely valuable as an immutable historical record instead. Every “update” is actually just an update of the “stop-date” of the current row and an insertion of a replacement row with a nil stop date.
You do need to make a distinction in your head between “row-id” vs “stable-id” that didn’t previously exist so it adds a bit of complexity but anyone who’s used to FP patterns should pick this up pretty easily.
It’s funny, I would be almost on board with this article, except a couple of weeks ago an old client called me up in a pretty big panic. One of their customers had gone through and deleted a ton of historical data, and a month later the deleter’s boss discovered that deleting that data had an unintended (to them) side effect. The deleted_at field was absolutely perfect because I could query which data had been deleted on that specific day and with a single update query I restored it all for them. They were overjoyed!
I’m a bit late to the party, but at GitLab we reached similar conclusions: soft deletion using a deleted flag is a pain. If I recall correctly we did discuss using a separate foo_deleted table, but felt that keeping its structure in sync with the corresponding foo table was too annoying. We ended up removing soft deletions entirely. We didn’t really need it either: there was no way in the UI to restore data, and we already had audit logging and what not.
In the six years I worked there I think we only needed to restore some accidentally removed data once, I think that was due to somebody accidentally scheduling their project for removal. I think we ended up just copying data back from a backup, but I could be mistaken. I think this also lead to us implementing a feature where a project scheduled for removal isn’t removed immediately, instead it takes a few hours for the work to get picked up.
Point being: data that is removed should either be physically removed, or moved to a different table. Keeping it around with a flag opens up the door for all sorts of trouble.
At work, we have a kind of soft deletion we call “hide”/“hidden” in the UI. It’s used on tables with under a million rows so we haven’t had to think much about subtle effects on scaling. We subclassed Django’s “managers” so that searching for lists of items, by default, excludes hidden objects, with an escape hatch for when we want the hidden stuff. (Around the same place, we also add in some timestamping, etc.) Users can navigate to a list of the hidden objects of a given type and unhide them–it’s much more Move to Trash than rm.
Hiding is non-cascading, and by default hidden objects still show up when searched for by ID or accessed via a relation, which has usually worked out to be a feature. For example, you can hide outdated webpage templates so they’re no longer offered when creating new pages, but not affect the pages already published. And hiding a page with a form doesn’t undo/lose submissions that came in through it (not something everyone would want, but we do).
It helps to have had this from early on, and that some UI bits are factored so some of the trickiness of e.g. hidden-aware item lists only needs to be happen once. I recall we may’ve had to hack in some stuff to show the right UI when unhidden objects reference hidden ones.
It’s just one strategy and we use others depending on context. For some other tables, certain values of status columns can have soft-delete-like effects. We track history for some tables, but would like it for more. We also have hard deletes in various places: for some non-hideable things of course, but also via the API for most (all?) hideable stuff, and in the GUI where not having the data really is the goal (e.g. “delete my data” requests from users).
It has created some work: the combo of hiding and unique indexes can confuse users (“it says I can’t reuse this unique name, but I don’t see anything using it!”). But overall this particular feature has been pretty low-regret after a number of years with it. I do want to repeat the caveat that we don’t use it everywhere and don’t think it would make sense to.
I really wish there was a SQL database that could provide a queryable log along side the other tables. That feature would enable so much goodness and largely obviate the need for soft deletes in the usecases I most often see soft deletes used for.
I’ve also had to implement soft delete many times in projects. The biggest problem we always had was figuring out when to fetch deleted items and when to ignore them in the frontend. For example, if you’re looking at a deleted invoice, you’d want to show the invoice lines (possibly including deleted ones). But if you’re looking at an active invoice, you would not want to show any deleted lines.
When calculating total revenue, you want to look at non-deleted lines for non-deleted invoices.
And let’s say you have a forum post by a deleted user, the post itself should still be shown, probably with the deleted user’s name and avatar. But if you want to address a user in a message on that thread, should the auto-complete show the deleted user as well? He was a participant, but he’s been deleted…
These sort of semantic issues mean you have to carefully decide this on a case-by-case basis. This means you can’t have one simple rule for deleted items, because the idea of “soft deletion” is rather ill-defined. Essentially “soft deleted” means “hide by default, but show when and where it makes sense”. I remember having to tweak such specific situations many times from complaints by customers. Just this aspect, having an annoyed customer who has to report “the same issue” many times is also something to consider.
The title here is a little misleading, because the author proposes a different way of implementing soft deletes (a deleted table vs a deleted_at column).
A better name might be: “rethink your deleted_at column”
This is (mostly) the way we did it at my last gig. The differences were:
It was mostly used for auditing purposes. However, I do know of at least one case where someone restored rows from the history table because of a bad predicate on a update query.
Unfortunately, it was difficult to maintain because you had to keep the history table, trigger, and core tables in sync. It was a SQL Server so the JSON support was not fabulous and people generally avoided using JSON for columns.
The system was low volume so it mainly didn’t cause performance issues, but there was a period where an errant job caused tons of updates and ballooned the storage used by the server. Easy enough to fix, but annoying nonetheless.
It’s interesting that the article cites the possibility of foreign-key references as a downside of soft deletion. I usually only turn to soft deletion when I specifically want existing foreign-key references to keep pointing to the soft-deleted row. Undeletion hasn’t been a motivation at all when I’ve used soft deletion.
An example from the service I work on now, which manages inventories of seeds for reforestation projects: there’s a table that lists all the seed species a given project works with. Sometimes a project no longer wants to work with a given species, so naively we’d delete it. But we still want project-specific information about the species to show up when users browse past activity.
One option would be what the article suggests: moving deleted species to a separate table. But then what do we do with the foreign keys? We’d either have to have a separate “deleted species ID” foreign key column in all the tables that reference the species table, and modify all the queries in an even more intrusive way than adding
deleted_time IS NULL, or we’d need a sprawling web of shadow tables for every table that could refer, even indirectly, to a deleted species, and addUNIONclauses to any queries that could legitimately return both deleted and non-deleted data.I’m surprised the article doesn’t mention an approach that works pretty well: views that automatically exclude the deleted rows. Give those views obvious, easy names and the underlying tables less-convenient names and people will generally use the views instead of the tables for ad-hoc queries. Using my example, you might call the view
speciesand the tablespecies_allor evenspecies_including_deletedif you wanted to get very explicit about it. Write operations do get less convenient but not hugely so.What you’re doing sounds perfectly reasonable and sensible and this isn’t a criticism, but… the operation you’re doing there doesn’t sound like a deletion. It sounds like the rows record that “a given project once worked with this species”, and the flag that you are calling “deleted_at” could be called “stopped_working_with_at”.
There are two different aspects to soft deletion, and this article only discusses one.
The article focuses entirely on customer action driven soft deletes. At AWS many teams decide to not do this, to their regret. At our scale customers regularly nuke their own production assets and cry loudly for help. In my team, DynamoDB Backup and Restore, we have automatic and well tested mechanisms to support this. It goes without saying as a SaaS you need customer-driven soft-deletes.
The other angle is system-driven deletes. As a database service we prune old data. This is frightening. We have layers upon layers upon layers of redundancy, reconciliation, bit-rot detection, and using e.g. S3 tagging with lifecycles to mark data as to be deleted. These mechanisms have caught bugs. We have not lost data. So system-driven soft-deletes are even more mandatory than customer-driven soft deletes.
The article seems obsessed with relational databases, these are prohibited for AWS services, but the same challenges of data consistency exist for NoSQL databases.
Soft deletion is definitely worth it, and if you don’t have it you may not care about your customers.
Hmm. To add another perspective, Discourse uses soft deletes for post content but hard deletes for user accounts, for several reasons.
Undeleting a post is done on a regular basis. Reviewing deleted post content in the context it was originally posted is a critical part of moderation workflows.
On the other hand, a user account has so many foreign keys that it’s not worth attempting to recover it, and they just tell people to create a new account and transfer the posts over (this part is manual work for the admins).
All the other database tables with soft deletes? I don’t think anyone has really thought about the design very hard, or ever attempted to recover anything. Definitely a “toxic data” risk.
I feel like this is a weird statement, but I’ve also spent a decent chunk of my career working in regulated industries with rules about what you could delete, what you couldn’t delete, and what you had to keep even if you were asked to delete it.
I am quite glad that online web forums are not what people typically call “regulated industries” (outside of China I suppose, I’ve heard of heavy regulations there).
At a previous job, we did the reverse because of the many foreign keys. Deleting entities created by an inactive user (ie. a former employee) was not an option for most of our customers, so we would instead “scrub” the user down to have only the UUID and a default name (“deleted user”).
I think making the foreign keys linking to the user nullable/optional is a dangerous path (and could result in critical bugs), and also makes any kind of “data forensics” near-impossible. Having a separate table with deleted users is only good for avoiding the “AND deleted_at IS NONE”-missing query pitfall, in exchange for having another foreign key column and additional checks to see if either the “normal” foreign key is set OR the deleted-user foreign key.
We did have to implement hard-deletion (for GDPR reasons when a customer churned with all their data), and it was a PITA, so I am not saying soft-deletion is easy. But there’s a lot of upfront work in handling hard deletion well if you need to support retention of related data (ie. user posts).
I have used soft deletes for many, many years largely as a “recycle bin” with the back office system having a toggle for showing deleted items. It’s helped on more than one occasion where a user has accidentally deleted something and allows for the deletion confirmed modal to have an undo button.
Most of the systems I worked on had a data retention policy stating that deleted items would remain recoverable for no longer than 30 days post deletion. Some had different data retention policies based upon data type, with user accounts tending to have the shortest.
Yes, I think that the article isn’t very clear about the intent of the flag. If it’s like in your case, a recycle-bin, then it’s actually a feature. Then you “can’t” forget this by accident, because “deleted” isn’t a flag, it’s one of the possible states for the item and you design it intentionally. If it is meant as a disaster -recovery type of thing like the article seems to imply, then it is implemented on the totally strange level. In other words, of course it’s not worth it and even the “workaround” suggested (different table) is still not a very good way to solve the problem.
Good distinction.
We’ve been using soft deletes on my system at work for 5 years now and I’d never go back. But we’ve never once used it as an undelete; it’s been hugely valuable as an immutable historical record instead. Every “update” is actually just an update of the “stop-date” of the current row and an insertion of a replacement row with a nil stop date.
You do need to make a distinction in your head between “row-id” vs “stable-id” that didn’t previously exist so it adds a bit of complexity but anyone who’s used to FP patterns should pick this up pretty easily.
It’s funny, I would be almost on board with this article, except a couple of weeks ago an old client called me up in a pretty big panic. One of their customers had gone through and deleted a ton of historical data, and a month later the deleter’s boss discovered that deleting that data had an unintended (to them) side effect. The
deleted_atfield was absolutely perfect because I could query which data had been deleted on that specific day and with a singleupdatequery I restored it all for them. They were overjoyed!I’m a bit late to the party, but at GitLab we reached similar conclusions: soft deletion using a
deletedflag is a pain. If I recall correctly we did discuss using a separatefoo_deletedtable, but felt that keeping its structure in sync with the correspondingfootable was too annoying. We ended up removing soft deletions entirely. We didn’t really need it either: there was no way in the UI to restore data, and we already had audit logging and what not.In the six years I worked there I think we only needed to restore some accidentally removed data once, I think that was due to somebody accidentally scheduling their project for removal. I think we ended up just copying data back from a backup, but I could be mistaken. I think this also lead to us implementing a feature where a project scheduled for removal isn’t removed immediately, instead it takes a few hours for the work to get picked up.
Point being: data that is removed should either be physically removed, or moved to a different table. Keeping it around with a flag opens up the door for all sorts of trouble.
At work, we have a kind of soft deletion we call “hide”/“hidden” in the UI. It’s used on tables with under a million rows so we haven’t had to think much about subtle effects on scaling. We subclassed Django’s “managers” so that searching for lists of items, by default, excludes hidden objects, with an escape hatch for when we want the hidden stuff. (Around the same place, we also add in some timestamping, etc.) Users can navigate to a list of the hidden objects of a given type and unhide them–it’s much more Move to Trash than
rm.Hiding is non-cascading, and by default hidden objects still show up when searched for by ID or accessed via a relation, which has usually worked out to be a feature. For example, you can hide outdated webpage templates so they’re no longer offered when creating new pages, but not affect the pages already published. And hiding a page with a form doesn’t undo/lose submissions that came in through it (not something everyone would want, but we do).
It helps to have had this from early on, and that some UI bits are factored so some of the trickiness of e.g. hidden-aware item lists only needs to be happen once. I recall we may’ve had to hack in some stuff to show the right UI when unhidden objects reference hidden ones.
It’s just one strategy and we use others depending on context. For some other tables, certain values of status columns can have soft-delete-like effects. We track history for some tables, but would like it for more. We also have hard deletes in various places: for some non-hideable things of course, but also via the API for most (all?) hideable stuff, and in the GUI where not having the data really is the goal (e.g. “delete my data” requests from users).
It has created some work: the combo of hiding and unique indexes can confuse users (“it says I can’t reuse this unique name, but I don’t see anything using it!”). But overall this particular feature has been pretty low-regret after a number of years with it. I do want to repeat the caveat that we don’t use it everywhere and don’t think it would make sense to.
I really wish there was a SQL database that could provide a queryable log along side the other tables. That feature would enable so much goodness and largely obviate the need for soft deletes in the usecases I most often see soft deletes used for.
MS SQL has temporal tables, not sure if that covers what you were thinking of.
I’ve also had to implement soft delete many times in projects. The biggest problem we always had was figuring out when to fetch deleted items and when to ignore them in the frontend. For example, if you’re looking at a deleted invoice, you’d want to show the invoice lines (possibly including deleted ones). But if you’re looking at an active invoice, you would not want to show any deleted lines.
When calculating total revenue, you want to look at non-deleted lines for non-deleted invoices.
And let’s say you have a forum post by a deleted user, the post itself should still be shown, probably with the deleted user’s name and avatar. But if you want to address a user in a message on that thread, should the auto-complete show the deleted user as well? He was a participant, but he’s been deleted…
These sort of semantic issues mean you have to carefully decide this on a case-by-case basis. This means you can’t have one simple rule for deleted items, because the idea of “soft deletion” is rather ill-defined. Essentially “soft deleted” means “hide by default, but show when and where it makes sense”. I remember having to tweak such specific situations many times from complaints by customers. Just this aspect, having an annoyed customer who has to report “the same issue” many times is also something to consider.
If you like this, you’ll like historical modeling.