3 ms·

But, assuming you don't really need the data, why make your queries more complex and instead just actually delete the data?

by yladiz 4y ago

munk-a 4y ago
This is a really scenario specific question - sometimes it's needed, sometimes it isn't. At my shop we have customers that will suspend their account but our sales team is pretty damn awesome so usually they end up renewing after going a while without our product - so being able to easily restore a large swath of former customers with all their permissions and preferences intact with a simple click of a button is a huge win compared to having a dev try to piece the data together out of backups that are six months out of date (a little secret... we never did this and just put the obligation on the CS team to manually recreate the records since that cost the company less).
- yladiz 4y ago
  If I was a tech lead and also wanted to advocate for hard deletion, I would ask the question for this scenario: "What's the cost of keeping all this data unnecessarily, modifying most queries to filter for deleted data, and dealing with other various consequences of soft deletion, and how does this cost compare with the cost of building a bespoke tool to restore deleted data at a customer's request within a certain time frame and compare with the benefit of a customer being able to restore their data at the 'click of a button'?". Having dealt with systems that have hundreds of millions of records or more, many of which reference deleted data and are therefore useless, I lean towards hard deletion more and more and on the off chance that deleted data really needs to be recovered you build a separate system/infrastructure to support that, rather than building your _entire_ system around the small likelihood you really need to restore it.
  - munk-a 4y ago
    As your data architect I'd probably answer your question "Well, it'll take a little while longer to vet all the indices - since we'll probably want most indices to filter on WHERE deleted_at IS NULL but we'll likely want a few without that constraint for managing undeletion. We'll use more space on disk which, honestly, is pretty much a non-issue in the modern world - and if it gets bad enough we can always partition the table on deletion status and dump the soft deleted rows on a secondary server... but I wouldn't worry about that until we hit facebook scale. In terms of application developer time - I'll probably need an hour of their time to explain it once and all existing queries keep working as they're working now... and as for that undelete tool, well, we don't actually have to build it - if we don't build it we can just ask devs to submit manual queries to undelete data rows as needed via our migration, or oneoff or console interface. We'll want to do a sweep for any queries that reference tables dependent on the soft-delete having table - just to make sure they're throwing an INNER JOIN against the view but, honestly, we could just bop those bugs on the head if they ever come up." Like, I'm definitely not saying soft-deletion is always the answer, it takes additional effort and planning, but it's really, really easy to do safely.
TylerE 4y ago
Becauase that's a huge assumption. From my experience, it's sorta like the giant box of old cables most techies keep around. Do you reach into it often? No. But when you do it absolutely saves the day and makes you a hero.

But, assuming you don't really need the data, why make your queries more complex and instead just actually delete the data?

by yladiz 4y ago

But, assuming you don't really need the data, why make your queries more complex and instead just actually delete the data?

munk-a 4y ago

This is a really scenario specific question - sometimes it's needed, sometimes it isn't. At my shop we have customers that will suspend their account but our sales team is pretty damn awesome so usually they end up renewing after going a while without our product - so being able to easily restore a large swath of former customers with all their permissions and preferences intact with a simple click of a button is a huge win compared to having a dev try to piece the data together out of backups that are six months out of date (a little secret... we never did this and just put the obligation on the CS team to manually recreate the records since that cost the company less).

yladiz 4y ago
If I was a tech lead and also wanted to advocate for hard deletion, I would ask the question for this scenario: "What's the cost of keeping all this data unnecessarily, modifying most queries to filter for deleted data, and dealing with other various consequences of soft deletion, and how does this cost compare with the cost of building a bespoke tool to restore deleted data at a customer's request within a certain time frame and compare with the benefit of a customer being able to restore their data at the 'click of a button'?". Having dealt with systems that have hundreds of millions of records or more, many of which reference deleted data and are therefore useless, I lean towards hard deletion more and more and on the off chance that deleted data really needs to be recovered you build a separate system/infrastructure to support that, rather than building your _entire_ system around the small likelihood you really need to restore it.
- munk-a 4y ago
  As your data architect I'd probably answer your question "Well, it'll take a little while longer to vet all the indices - since we'll probably want most indices to filter on WHERE deleted_at IS NULL but we'll likely want a few without that constraint for managing undeletion. We'll use more space on disk which, honestly, is pretty much a non-issue in the modern world - and if it gets bad enough we can always partition the table on deletion status and dump the soft deleted rows on a secondary server... but I wouldn't worry about that until we hit facebook scale. In terms of application developer time - I'll probably need an hour of their time to explain it once and all existing queries keep working as they're working now... and as for that undelete tool, well, we don't actually have to build it - if we don't build it we can just ask devs to submit manual queries to undelete data rows as needed via our migration, or oneoff or console interface. We'll want to do a sweep for any queries that reference tables dependent on the soft-delete having table - just to make sure they're throwing an INNER JOIN against the view but, honestly, we could just bop those bugs on the head if they ever come up." Like, I'm definitely not saying soft-deletion is always the answer, it takes additional effort and planning, but it's really, really easy to do safely.

TylerE 4y ago

Becauase that's a huge assumption. From my experience, it's sorta like the giant box of old cables most techies keep around. Do you reach into it often? No. But when you do it absolutely saves the day and makes you a hero.