5 ms·

I just wanted to touch on the fact that eliding soft-deleted rows from queries is really, really easy - this article makes it out to be a constant headache but

by munk-a 4y ago

I just wanted to touch on the fact that eliding soft-deleted rows from queries is really, really easy - this article makes it out to be a constant headache but here's my suggested approach. ALTER TABLE blah ADD COLUMN deleted_at NULL TIMESTAMP; ALTER TABLE blah RENAME TO blahwithdeleted; CREATE VIEW blah (SELECT * FROM blahwithdeleted WHERE deleted_at IS NULL); And thus your entire application just needs to keep SELECTing from blah while only a few select pieces of code related to undeleting things (or generating reports including deleted things) need to be shifted to read from blahwithdeleted.

bjourne 4y ago
This is not a solution. It introduces a leaky abstraction which sooner or later will lead to errors. Sure, all code you write will access the view and not the table. But how can you ensure all other code in the organisation uses the view? Perhaps you add some access control to the table so that only authorized users can read directly from it, but that's even more technical overhead. Then you have foreign keys. If you have a "deleted" column in the Customer table you need to remake the Invoice table as a view so that it hides invoices for deleted customers. The same goes for the InvoiceItem table (foreign key of a foreign key) and all author auxiliary information related to the soft-deleted customers. Furthermore, the cost of an error is potentially massive. Someone new at the company makes a revenue report based in the billed Invoices and does not realize they should query the view and not the table... Not great if 90% of all invoices belong to soft-deleted customers! The author is right; soft-deletes are probably most definitely not worth it. There are many better ways to solve the problem.
- munk-a 4y ago
  I don't really agree with that. Within an organization you have documentation and instruction as tools - but you're also making the dumb approach (SELECT * FROM blah) the correct approach. If a user is writing a query against the DB, has no idea what the layout of the data is, and decides to prefer blahwithdeleted over blah then I'd really question whats going on at your organization - blahwithdeleted is pretty clearly self-documenting and it's likely a lot of your other domain specific tables with be much harder to naively discover your way through. I, personally, would in no way restrict access to blahwithdeleted, but I have made a pattern of it in our DB, there are about a dozen blahwithdeleted tables - each with a corresponding blah view... I usually get about one question per every two new employees about which table to use which I can answer in less than a minute with a helpful little explanation. I'd also mention I've not made a specific value statement on soft deletions in a general case since, if there was a clear general case solution we'd just all do that. This is a decision that needs to be made on a per table basis - it's a rather trivial decision in most cases, but it's very specific to the problem at hand.
- croes 4y ago
  If you mention the costs of reporting error you should consider the costs of wrongful hard delete too. And reports are developed and tested before they are used for crucial purposes.
- tshaddox 4y ago
  > Furthermore, the cost of an error is potentially massive. Someone new at the company makes a revenue report based in the billed Invoices and does not realize they should query the view and not the table... Not great if 90% of all invoices belong to soft-deleted customers! I'm not sure I buy this argument. It's certainly conceivable for that to happen, but no more so than any other case of "the engineer queried the wrong table and thus got incorrect results." There's never going to be any technical way of preventing this: if you have access to multiple sets of numbers, and you want to sum up one set of numbers but mistakenly sum up the other set of numbers, you're going to get the wrong answer!
  - bjourne 4y ago
    > "the engineer queried the wrong table and thus got incorrect results." The difference is that the path of least resistance, the most obvious method - just query the damn table - is incorrect. Bad design can certainly make a system more error prone.
    - tshaddox 4y ago
      In the example from this thread the names of the table and view are reasonably clear, so even in a hypothetical project without any external documentation of naming conventions or engineering processes (code review, etc.) the most obvious thing would be to query blah instead of blahwithdeleted. Of course, this is extremely hypothetical, and in any real project where you're generating a financial report you absolutely must have a detailed understanding of the relation that you're aggregating over. Even if your project has very strict naming conventions for tables, views, etc. you've gotta put in more work than "this short string sounds like a plausible label for the relation I want to aggregate over."
      - bjourne 4y ago
        "reasonably clear" are famous last words. It ignores tons of evidence on how commercial software development works. Everything is "reasonably clear" in isolation, but not when you throw in thousands of other "reasonably clear" things developers are supposed to keep track while not missing tight deadlines. Fact is that if you add opportunities for people to screw up then you will make people screw up, regardless of how "reasonably clear" or "obvious" the system is. Maybe you need more work experience because believing that it is implausible for someone to accidentally query the customer_with_deleted table over the customer view is incredibly naive. Likewise, people generating financial reports can have detailed understanding of data modelling but scarily often don't. Give them extra opportunities to fuck up and they will take them every time. KISS
- bpicolo 4y ago
  Give it the ol obvious name - table_including_soft_deleted_rows_dont_query_directly.
- adrianmsmith 4y ago
  > Someone new at the company makes a revenue report based in the billed Invoices and does not realize they should query the view and not the table... Not great if 90% of all invoices belong to soft-deleted customers! This is not a great example of what you mean. If a customer purchased a product or subscription, paid for it, then later deleted their account, the company has received revenue so that data absolutely should be on revenue reports.
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.

by munk-a 4y ago

bjourne 4y ago

This is not a solution. It introduces a leaky abstraction which sooner or later will lead to errors. Sure, all code you write will access the view and not the table. But how can you ensure all other code in the organisation uses the view? Perhaps you add some access control to the table so that only authorized users can read directly from it, but that's even more technical overhead. Then you have foreign keys. If you have a "deleted" column in the Customer table you need to remake the Invoice table as a view so that it hides invoices for deleted customers. The same goes for the InvoiceItem table (foreign key of a foreign key) and all author auxiliary information related to the soft-deleted customers. Furthermore, the cost of an error is potentially massive. Someone new at the company makes a revenue report based in the billed Invoices and does not realize they should query the view and not the table... Not great if 90% of all invoices belong to soft-deleted customers! The author is right; soft-deletes are probably most definitely not worth it. There are many better ways to solve the problem.

munk-a 4y ago
I don't really agree with that. Within an organization you have documentation and instruction as tools - but you're also making the dumb approach (SELECT * FROM blah) the correct approach. If a user is writing a query against the DB, has no idea what the layout of the data is, and decides to prefer blahwithdeleted over blah then I'd really question whats going on at your organization - blahwithdeleted is pretty clearly self-documenting and it's likely a lot of your other domain specific tables with be much harder to naively discover your way through. I, personally, would in no way restrict access to blahwithdeleted, but I have made a pattern of it in our DB, there are about a dozen blahwithdeleted tables - each with a corresponding blah view... I usually get about one question per every two new employees about which table to use which I can answer in less than a minute with a helpful little explanation. I'd also mention I've not made a specific value statement on soft deletions in a general case since, if there was a clear general case solution we'd just all do that. This is a decision that needs to be made on a per table basis - it's a rather trivial decision in most cases, but it's very specific to the problem at hand.
croes 4y ago
If you mention the costs of reporting error you should consider the costs of wrongful hard delete too. And reports are developed and tested before they are used for crucial purposes.
tshaddox 4y ago
> Furthermore, the cost of an error is potentially massive. Someone new at the company makes a revenue report based in the billed Invoices and does not realize they should query the view and not the table... Not great if 90% of all invoices belong to soft-deleted customers! I'm not sure I buy this argument. It's certainly conceivable for that to happen, but no more so than any other case of "the engineer queried the wrong table and thus got incorrect results." There's never going to be any technical way of preventing this: if you have access to multiple sets of numbers, and you want to sum up one set of numbers but mistakenly sum up the other set of numbers, you're going to get the wrong answer!
- bjourne 4y ago
  > "the engineer queried the wrong table and thus got incorrect results." The difference is that the path of least resistance, the most obvious method - just query the damn table - is incorrect. Bad design can certainly make a system more error prone.
  - tshaddox 4y ago
    In the example from this thread the names of the table and view are reasonably clear, so even in a hypothetical project without any external documentation of naming conventions or engineering processes (code review, etc.) the most obvious thing would be to query blah instead of blahwithdeleted. Of course, this is extremely hypothetical, and in any real project where you're generating a financial report you absolutely must have a detailed understanding of the relation that you're aggregating over. Even if your project has very strict naming conventions for tables, views, etc. you've gotta put in more work than "this short string sounds like a plausible label for the relation I want to aggregate over."
    - bjourne 4y ago
      "reasonably clear" are famous last words. It ignores tons of evidence on how commercial software development works. Everything is "reasonably clear" in isolation, but not when you throw in thousands of other "reasonably clear" things developers are supposed to keep track while not missing tight deadlines. Fact is that if you add opportunities for people to screw up then you will make people screw up, regardless of how "reasonably clear" or "obvious" the system is. Maybe you need more work experience because believing that it is implausible for someone to accidentally query the customer_with_deleted table over the customer view is incredibly naive. Likewise, people generating financial reports can have detailed understanding of data modelling but scarily often don't. Give them extra opportunities to fuck up and they will take them every time. KISS
bpicolo 4y ago
Give it the ol obvious name - table_including_soft_deleted_rows_dont_query_directly.
adrianmsmith 4y ago
> Someone new at the company makes a revenue report based in the billed Invoices and does not realize they should query the view and not the table... Not great if 90% of all invoices belong to soft-deleted customers! This is not a great example of what you mean. If a customer purchased a product or subscription, paid for it, then later deleted their account, the company has received revenue so that data absolutely should be on revenue reports.

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.