5 ms·

On the topic of data retention, though subtly different, can anyone succinctly explain to me when 'DELETE FROM' became a 4 letter word? Everyone wants soft del

On the topic of data retention, though subtly different, can anyone succinctly explain to me when 'DELETE FROM' became a 4 letter word? Everyone wants soft delete deleted_at instead. Everywhere. Many ORMs even force this behavior. Why? Why can't we just delete records anymore? I'm sure some have specific reasons for this, but I'm trying to find the broader reason that everyone has jumped on this bandwagon. Maybe I'm too YAGNI, but I prefer delete by default, unless justified. Not the inverse...

zimpenfish 4y ago
> Why? Why can't we just delete records anymore? I think there's a certain amount of "you must keep records pertaining to financial transactions for N years" going on combined with (more recently, I suppose) abuse/spam protection.
- bell-cot 4y ago
  > "you must keep records pertaining to financial transactions..." Old-school solution - keep the records, in off-line hard drives in a fire-proof safe, with each one labeled "physically destroy on $Date".
  - NoZebra120vClip 4y ago
    Simple, easy! Amazing! Now, what do you do with standard backups such as the ones which mingle data which is 1 day, 1 year, and 5, 10, and 20 years old?
mnsc 4y ago
One broader reason could be "event sourcing" at a buzzword level. The allure of having a full history of the system/data at every point in time. Combine this with ai at the buzzword level. You could then use the full history of the world as input data to large ai models. But why?
- onlypositive 4y ago
  Event sourcing allows business models and access to the data to change over time without having to modify the shape of historical data. The ability to change your interface is what's alluring, not the mere fact that you have a record of changes. Though the audit trail is nice too. Funnily enough you don't get to keep an audit trail while also losing the data. It's a trade off and businesses prefer the model that suits their business.
stouset 4y ago
Delete can’t reasonably be undone. If someone changes their mind, it’s too late.
- cuu508 4y ago
  Hoarding in a nutshell.
  - bottled_poe 4y ago
    Sometimes undoing a delete is reasonable?
masklinn 4y ago
In no small part because business wants undelete. Also managing “on delete” is complicated to hook everywhere, much easier to hook a flag and touch nothing else implicitly.
renewiltord 4y ago
Need retention now. But CPRA helps with this.
stareatgoats 4y ago
It's because database designers fear cascade delete and dangling references above all else.
mickeyp 4y ago
Deleting in a relational database often involves cascading to foreign keys to ensure data integrity is maintained. Delete a company row, and the employee rows may follow; that is probably not what most want. FK cascading and DELETE, when done poorly, can... well, cascade, and delete lots of things the end user did not foresee. Like nuking half the database levels of bad. `deleted_at' flags avoid that problem; combine it with row-level security in your DB (like postgres) and you can hide the rows permanently as well: from orms and non-superuser roles alike with a simple policy not to show any row where `deleted_at` is not null.
- silisili 4y ago
  I like cascading, but understand that risk. Why not just restrict everything, then? It forces the code to specify everything it wants to delete, no hidden nuking...
- noisy_boy 4y ago
  Use FK etc to ensure referential integrity but don't use it for cascading deletes. Speaking of Finance industry, stuff gets seldom deleted and when it does, more often than not, it is either manually orchestrated or the same orchestration is done via code. Sure it's more work but you are in full control of what is being removed at each stage.
  - deleted 4y ago
    [deleted]
MattGaiser 4y ago
Because someone always changes their mind and you get blamed if they cannot change their mind. We are used to a world of Ctrl-Z and customers expect that. Someone accidentally deletes their company profile as they thought they were deleting the duplicate. They expect to be able to get it back. Someone wipes out all the config files to get revenge on their employer. That employer is going to get in touch with support to get them back and they expect them back, even if you have to dig through backups and will make support's life difficult if they do not.
Prunkton 4y ago
I understand that this may not be the point you are trying to make, but it's important to note that deleting data can result in fines. As mentioned by others, regulated entities such as banks are required to retain data for a certain period of time. For example, a bank regulated by the EU needs to store most data for 10 years, which includes all emails sent by the bank, except for sensitive customer core data that is no longer needed. So what happens if a bank employee accidentally deletes an email in Microsoft Exchange? This is can not happen due to the policies in place. All emails are stored in a hidden folder for as long as necessary, ensuring that no data is lost.
gridspy 4y ago
Because many items don't make sense without showing old deleted data. Imagine you run a shop with a list of products. People can order and receive products from you. View past orders and get invoices and recepts. One day you have a product that has reached end of life, or isn't selling well. So naturally you delete it from the store. If you actually deleted this item, all the old invoices wouldn't be able to refer to it. Old customers couldn't claim warranty on it. You couldn't create old sales reports that made sense. Turns out that this problem extends to most tables. Old clients? Users? Products? Invoices? In most cases outright deletion makes many other things invalid that you want to keep. So it's nice when the ORM makes meeting this requirement easy for you. All this in addition to the ability to recover from an accidental or temporary deletion.
- hgsgm 4y ago
  "Don't delete stuff you are still using" has nothing to do with "delete stuff you shouldn't be using and in many jurisdictions cannot legally possess".
  - macintux 4y ago
    The latter category is typically small, a recent legal development, and absolutely impossible for an ORM to identify sans explicit rules.
rr808 4y ago
There are a lot of regulations about data retention in many industries, but now I think people making the decision aren't really sure and its easier to soft delete than get someone to approve a real deletion. In the old days hardware was a limiting factor so more likely to delete to keep performance up.

zimpenfish 4y ago

> Why? Why can't we just delete records anymore? I think there's a certain amount of "you must keep records pertaining to financial transactions for N years" going on combined with (more recently, I suppose) abuse/spam protection.

bell-cot 4y ago
> "you must keep records pertaining to financial transactions..." Old-school solution - keep the records, in off-line hard drives in a fire-proof safe, with each one labeled "physically destroy on $Date".
- NoZebra120vClip 4y ago
  Simple, easy! Amazing! Now, what do you do with standard backups such as the ones which mingle data which is 1 day, 1 year, and 5, 10, and 20 years old?

mnsc 4y ago

One broader reason could be "event sourcing" at a buzzword level. The allure of having a full history of the system/data at every point in time. Combine this with ai at the buzzword level. You could then use the full history of the world as input data to large ai models. But why?

onlypositive 4y ago
Event sourcing allows business models and access to the data to change over time without having to modify the shape of historical data. The ability to change your interface is what's alluring, not the mere fact that you have a record of changes. Though the audit trail is nice too. Funnily enough you don't get to keep an audit trail while also losing the data. It's a trade off and businesses prefer the model that suits their business.

stouset 4y ago

Delete can’t reasonably be undone. If someone changes their mind, it’s too late.

cuu508 4y ago
Hoarding in a nutshell.
- bottled_poe 4y ago
  Sometimes undoing a delete is reasonable?

masklinn 4y ago

In no small part because business wants undelete. Also managing “on delete” is complicated to hook everywhere, much easier to hook a flag and touch nothing else implicitly.

renewiltord 4y ago

Need retention now. But CPRA helps with this.

stareatgoats 4y ago

It's because database designers fear cascade delete and dangling references above all else.

mickeyp 4y ago

Deleting in a relational database often involves cascading to foreign keys to ensure data integrity is maintained. Delete a company row, and the employee rows may follow; that is probably not what most want. FK cascading and DELETE, when done poorly, can... well, cascade, and delete lots of things the end user did not foresee. Like nuking half the database levels of bad. `deleted_at' flags avoid that problem; combine it with row-level security in your DB (like postgres) and you can hide the rows permanently as well: from orms and non-superuser roles alike with a simple policy not to show any row where `deleted_at` is not null.

silisili 4y ago
I like cascading, but understand that risk. Why not just restrict everything, then? It forces the code to specify everything it wants to delete, no hidden nuking...
noisy_boy 4y ago
Use FK etc to ensure referential integrity but don't use it for cascading deletes. Speaking of Finance industry, stuff gets seldom deleted and when it does, more often than not, it is either manually orchestrated or the same orchestration is done via code. Sure it's more work but you are in full control of what is being removed at each stage.
- deleted 4y ago
  [deleted]

MattGaiser 4y ago

Because someone always changes their mind and you get blamed if they cannot change their mind. We are used to a world of Ctrl-Z and customers expect that. Someone accidentally deletes their company profile as they thought they were deleting the duplicate. They expect to be able to get it back. Someone wipes out all the config files to get revenge on their employer. That employer is going to get in touch with support to get them back and they expect them back, even if you have to dig through backups and will make support's life difficult if they do not.

Prunkton 4y ago

I understand that this may not be the point you are trying to make, but it's important to note that deleting data can result in fines. As mentioned by others, regulated entities such as banks are required to retain data for a certain period of time. For example, a bank regulated by the EU needs to store most data for 10 years, which includes all emails sent by the bank, except for sensitive customer core data that is no longer needed. So what happens if a bank employee accidentally deletes an email in Microsoft Exchange? This is can not happen due to the policies in place. All emails are stored in a hidden folder for as long as necessary, ensuring that no data is lost.

gridspy 4y ago

Because many items don't make sense without showing old deleted data. Imagine you run a shop with a list of products. People can order and receive products from you. View past orders and get invoices and recepts. One day you have a product that has reached end of life, or isn't selling well. So naturally you delete it from the store. If you actually deleted this item, all the old invoices wouldn't be able to refer to it. Old customers couldn't claim warranty on it. You couldn't create old sales reports that made sense. Turns out that this problem extends to most tables. Old clients? Users? Products? Invoices? In most cases outright deletion makes many other things invalid that you want to keep. So it's nice when the ORM makes meeting this requirement easy for you. All this in addition to the ability to recover from an accidental or temporary deletion.

hgsgm 4y ago
"Don't delete stuff you are still using" has nothing to do with "delete stuff you shouldn't be using and in many jurisdictions cannot legally possess".
- macintux 4y ago
  The latter category is typically small, a recent legal development, and absolutely impossible for an ORM to identify sans explicit rules.

rr808 4y ago

There are a lot of regulations about data retention in many industries, but now I think people making the decision aren't really sure and its easier to soft delete than get someone to approve a real deletion. In the old days hardware was a limiting factor so more likely to delete to keep performance up.