5 ms·

I agree that data must never be "deleted and forever gone" unless you've already been very sure about it a few time. But I would like to warn people about cert

I agree that data must never be "deleted and forever gone" unless you've already been very sure about it a few time. But I would like to warn people about certain implementations of database "soft deletes" that I'm not a fan of. To be clear, I'm talking about the idea of having a "deleted" and/or a "date_deleted" column and using those columns in the WHERE clause to filter out rows that shouldn't be visible. That pattern complicates the table structure, queries, and indexes. It increases table and index size, thus more data has to be sifted through (either table data or index data) to ensure only non-deleted entries are returned. More data to go through means slower queries. It's also really easy for people to write SQL that accidentally leaves the "deleted" column out of the WHERE clause. Then old, irrelevant data is being returned. Accidentally deleting data that needs to be undeleted is usually rare so I don't think people should optimize for it. We should optimize for things that happen frequently. I have dealt with the rare "Oops! I deleted important data!" by restoring from backups and it has worked fine. I think it may be too strong to say you should never be in a position to restore data from a backup. In fact, I think it's important to streamline the restore process. For cases where we know ahead of time that we want to query deleted data I'll move deleted data to another database table that exists solely for maintaining history. For example, an ORDER table will have a DELETED_ORDER table, or an ORDER_HISTORY table. The HISTORY tables can also record data overwritten from updates. These tables take up disk space, but never affect the structure or size of the original table and its indexes. Queries to the original table don't need to be modified to account for soft deletes. To guarantee that things go to the delete/history tables, I'll usually put a trigger on the original table to move data over to the history tables. This way no application-specific code is needed.

bartread 5y ago
> Accidentally deleting data that needs to be undeleted is usually rare so I don't think people should optimize for it. That's very use case dependent. We've made it easy for people to undelete data they've accidentally deleted simply because they used to do it so often and the only people who could get it back were our tech team. We're a devops org so part of our job is of course to support the systems we build, but our time is better spent on building solutions to business problems than to repeatedly providing support for issues that come up all the time. Part of building those systems is of course engineering in solutions that make it hard to screw up, and easy to unscrew when things inevitably do go wrong. No mean feat given our platform dates back over 15 years and still includes a lot of legacy from the time when tech was just a couple of people. I suppose the object lesson here is that edge cases in one system or company can be part of core business in another so it's best not to make too many assumptions.
izacus 5y ago
A "deleted" field type deletion is also how you get a massive fine from a GDPR agency when they find out that you're not actually deleting PII properly.
- noasaservice 5y ago
  One smaller social media app I used to sysad over actually overwrote data to be purged as xX0-Deleted-oXx and similar (there were a few variants depending on data constraints). There was no "show_deleted_when" garbage. Then weekly, a task went in and then purged rows with those non-content placeholders to completely purge that user, if a user-purge was requested.
- saati 5y ago
  I don't see how moving it to another table is any way different from that perspective.
Trasmatta 5y ago
Agreed, soft deleting adds so much complexity to everything. And even has the potential for privacy related bugs. Like, say, accidentally forgetting to respect the deleted column in a query on a joining table that determines user permissions for some resource. Now people have access to something they had permissions revoked for. Whoops.
Gigachad 5y ago
>I have dealt with the rare "Oops! I deleted important data!" by restoring from backups and it has worked fine. Usually this goes along with "Oh and the other team did some important work at the same time" so you can't just restore a backup. You either tell them to deal with it or start writing custom scripts to copy out only the data you want to restore. A more sane solution would be soft delete for x days and after that it becomes a real delete.
sitharus 5y ago
If you're using MS SQL Server it natively supports temporal tables, which take care of this problem for you. There's also an extension for Postgres, and of course triggers. Temporal tables should really be used more often.
Too 5y ago
I guess one could make a view for each table that always includes the where deleted = false, to not bother about it in application code. Still yes, it adds complexity.

bartread 5y ago

> Accidentally deleting data that needs to be undeleted is usually rare so I don't think people should optimize for it. That's very use case dependent. We've made it easy for people to undelete data they've accidentally deleted simply because they used to do it so often and the only people who could get it back were our tech team. We're a devops org so part of our job is of course to support the systems we build, but our time is better spent on building solutions to business problems than to repeatedly providing support for issues that come up all the time. Part of building those systems is of course engineering in solutions that make it hard to screw up, and easy to unscrew when things inevitably do go wrong. No mean feat given our platform dates back over 15 years and still includes a lot of legacy from the time when tech was just a couple of people. I suppose the object lesson here is that edge cases in one system or company can be part of core business in another so it's best not to make too many assumptions.

izacus 5y ago

A "deleted" field type deletion is also how you get a massive fine from a GDPR agency when they find out that you're not actually deleting PII properly.

noasaservice 5y ago
One smaller social media app I used to sysad over actually overwrote data to be purged as xX0-Deleted-oXx and similar (there were a few variants depending on data constraints). There was no "show_deleted_when" garbage. Then weekly, a task went in and then purged rows with those non-content placeholders to completely purge that user, if a user-purge was requested.
saati 5y ago
I don't see how moving it to another table is any way different from that perspective.

Trasmatta 5y ago

Agreed, soft deleting adds so much complexity to everything. And even has the potential for privacy related bugs. Like, say, accidentally forgetting to respect the deleted column in a query on a joining table that determines user permissions for some resource. Now people have access to something they had permissions revoked for. Whoops.

Gigachad 5y ago

>I have dealt with the rare "Oops! I deleted important data!" by restoring from backups and it has worked fine. Usually this goes along with "Oh and the other team did some important work at the same time" so you can't just restore a backup. You either tell them to deal with it or start writing custom scripts to copy out only the data you want to restore. A more sane solution would be soft delete for x days and after that it becomes a real delete.

sitharus 5y ago

If you're using MS SQL Server it natively supports temporal tables, which take care of this problem for you. There's also an extension for Postgres, and of course triggers. Temporal tables should really be used more often.

Too 5y ago

I guess one could make a view for each table that always includes the where deleted = false, to not bother about it in application code. Still yes, it adds complexity.