31 ms·

Soft deletion probably isn't worth it

• msie 4y ago
  I've used Soft Deletion so many times so I'll say it's been worth it. I believe using an audit table would have made recovery more difficult for me. Anyways take this advice with a grain of salt. It's only one guy's opinion. As is mine.
• jelkand 4y ago
  Soft deletion is certainly very situationally worth it. I've found the most value when 1. it is well supported at the ORM layer and 2. business requirements dictate strong auditability of data. While I have undeleted items on occasion, I've used soft deletes more frequently to debug and build a timeline of events around the data. For context, I've worked in fintech where I often needed to review backoffice approvals, transactions, offers, etc.
  • chomp 4y ago
    Yep, we have an abstraction layer on top of the ORM to provide common queries. "Give me all X" will always return stuff not soft deleted. Data people also like to go diving through old data, and without getting into data warehousing and stuff like that, it's not too complex to support a single flag to enable us to keep old stuff.
    • delusional 4y ago
      They might like to, but you should definitely consider if saving data no longer required for your business violates privacy regulation/ethical considerations.
      • chomp 4y ago
        Definitely. When a user "deletes" their account, we null out all identifying fields to "DELETED_PII_$user_id". We have running metrics we compute that would go off the rails if we dropped the row completely.
  • hinkley 4y ago
    In my limited experience, soft deletion also has better prospects where partial indexes are involved, since it reduces the size of the index and reduces search and insert time a little bit. If soft deletes are rare, you aren't going to see much of a payback for your investment in code complexity. And since you can never really be sure what you'll need 2 years from now, I imagine there are a lot of anecdotes out there of people who implemented it thinking it would be used a lot, and turned out to be wrong.
  • krstf13 4y ago
    Wouldn’t storing the deleted data in an immutable storage, with time stamp, be much better for auditability ? I mean how could you audit deleted, restored and deleted again data with that setup? Also, while I know it’s not really accurate, I tend to understand relations as sets, it makes me uncomfortable to have soft deleted data that are neither member or not member of the set.
• giantg2 4y ago
  "The concept behind soft deletion is to make deletion safer, and reversible." That's one part. The other part is that in many industries you have regulatory data retention and audit requirements. This is arguably the most valuable and common reason to perform Logical deletes.
  • dexwiz 4y ago
    I think billions have been spent bridging the gap between “ideal” software and what businesses actually need. Access control is another thing I see developers wanting to simplify or push to implement later, but is actually a key feature.
    • dubswithus 4y ago
      There's definitely perfect and perfect for the business. But when large companies have many developers they all have to do something. They will spend their time doing something unnecessary.
  • jewayne 4y ago
    I would argue that in many cases the concept behind soft deletion is to make deletion permanent. Hard deletes retain no memory of what you wanted to be gone, so any malfunctioning sync process will continuously recreate the deleted record soon after it's deleted. Soft deletes are often the only way to make sure deleted records don't reappear.
    • eeperson 4y ago
      Couldn't a malfunctioning sync process undo a soft delete as well?
      • deleted 4y ago
        [deleted]
      • jayd16 4y ago
        Null is more ambiguous than an explicit conflict. If there is literally no record, even of the delete action then there's no timestamp for last write wins.
• deleted 4y ago
  [deleted]
• llimos 4y ago
  Do any databases let you refer to constant values in foreign keys? Then you could do FOREIGN KEY (foreign_id, NULL) REFERENCES foreign_table(id, deleted_at)
  • munk-a 4y ago
    I don't believe that's possible in postgres at least - but I don't think it's a huge concern either - you can have deleted_at cascade via trigger or just use views to hide the data - both are extremely easy to implement at the DB level without the application devs ever needing to worry about what's what.
• matusp 4y ago
  The "Code leakage" problem can easily be solved by using views. Or am I missing something?
  • dboreham 4y ago
    Solved with "...and deleted = false"
• habibur 4y ago
  Which is why I don't add that extra deleted field. Rather duplicate all the tables into a new database called "archive" and then insert there before deleting from main. That works for updates too, by preserving the old data and showing you a time machine like backlog. But the archive database gets too large over time and you need to purge it periodically. You can create some delete triggers for automating this "save before delete" behavior.
  • tehbeard 4y ago
    How do you account for maintaining integrity in the archive? E.g. you have 3 users sign up with the same email (a unique field) one after the other with deletions in-between each sign-up?
    • habibur 4y ago
      No PK, FK or Unique constrains on the archive. Rather use simple index to speed up queries.
    • justin_oaks 4y ago
      I'm not who you asked the question of, but I do sometimes make use of archive/deleted/history tables. I'll refer to them as history tables from here on out. In short, I leave data integrity to the original table and drop it for the history table. The history table isn't identical to the original table. It has it's own primary keys that are separate from the original table. It doesn't include the original table's unique constraints or foreign key constraints. It also generally has a timestamp to know when the record was put there.
• bob1029 4y ago
  If you are going to think about this pattern, why not go one step further and simply event source everything with an append-only, immutable log? You could even sprinkle cryptographic guarantees into the mix. This would be very challenging to do with mutable DB rows.
• dafelst 4y ago
  Views are a simple solution to this problem. Pretty much all moderns RDBMSs support updatable views, so creating views over your tables with a simple WHERE deleted_at IS NULL solves the majority of the author's problems, including (IIRC) foreign key issues, assuming the deletes are done appropriately. I feel like a lot of developers underutilize the capabilities of the massively advanced database engines they code against. Sure, concerns about splitting logic between the DB and app layers are valid, but there are fairly well developed techniques for keeping DB and app states, logic and schemas aligned via migrations and partitioning and whatnot.
  • nousermane 4y ago
    > (...updatable view...) WHERE deleted_at IS NULL This is the way. Also, save record creation timestamp, and you can have very flexible "time-machine" selects/views of your table essentially for free.
  • firloop 4y ago
    Views can really bite you performance wise, at least with Postgres. If you add a WHERE against a query on a view, Postgres (edit: often) won't merge in your queries' predicates with the predicates of the view, often leading to large table scans.
    • dafelst 4y ago
      IIRC Postgres has supported predicate push down on trivial views like this for over a decade now, and possibly even more complex views these days (I haven't kept up with the latest greatest changes).
      • firloop 4y ago
        Postgres can do it, you're correct, but in my experience it rarely happens with any view that's even slightly non-trivial even on recent versions of Postgres. Most views with a join break predicate pushdown. It greatly reduces the usecases of views in practice.
• lowercased 4y ago
  "Instead, we rolled forward by creating a new app, and helping them copy environment and data from the deleted app to it. So even where soft deletion was theoretically most useful, we still didn’t use it." But... weren't you using all those env and data info from the soft-deleted set? I've typically been using soft-deletes for most projects for years. People have accidentally deleted records, and having a process to undelete them - manually or giving them a screen to review/restore - has usually been great. Yes, if there's a lot of related artefacts not in the database (files/etc) that were literally deleted, you may not be able to get them back. But that's an ever greater edge case in projects I work in as to not be a huge issue. We probably have some files in a backup somewhere, if it's recent. Trying to 'undelete' a record from years ago - yeah, likely ain't gonna happen. People are used to 'undo' and 'undelete'. Soft-deletes are one way to provide that functionality for some projects.
• GartzenDeHaes 4y ago
  Personally I like no delete designs, which give you a full audit history of changes. This is similar to generally accepted accounting principles. https://en.wikipedia.org/wiki/Generally_Accepted_Accounting_Principles_(United_States) https://en.wikipedia.org/wiki/Generally_Accepted_Accounting_...
  • munk-a 4y ago
    So if an account was active in your system and is active no longer... do you soft delete it (even if that means UPDATE ... SET active = 'f') or hard delete it?
    • GartzenDeHaes 4y ago
      That depends on the problem domain and how you design the system, since there are several way to do this. Typically in financial systems you would either have a start and end date on a summary record, or add an inactive record to a transaction table that has a record for each change to the account, or both.
  • znpy 4y ago
    Your taste in database design is probably nor gdpr compliant, i hope you don’t work in the eu.
    • munk-a 4y ago
      It's surprising but the EU tends to be one of the most stringent regions to work in both when it comes to totally permanently deleting things and when it comes to never ever deleting things - as with anything like this where there is a debate (rather than a settled best practice) there are some times when soft deletion is appropriate and necessary (i.e. to adhere to log retention requirements common in the EU) and some times when it's unnecessary... and the occasional fun time when it's both necessary to support soft deletes and hard deletes - when logs need to be retained for auditing purposes but also when some users can force a hard delete (leading to that data either being purged or moved to an archive storage if it's still needed for auditing purposes). The world is almost never as simple as it seems.
• 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.
• dfee 4y ago
  My experience is that soft-deletes are blunt tools bridging the gap between hard deletes and event sourcing (capturing all the changes against the table, in a replay-worthy stream). Event sourcing is hard – because the engineers responsible for setting it up and managing it aren't generally well skilled in this domain (myself included) and there aren't a wealth of great tools helping engineers find their way into the pit of success. The downsides of soft-deletes (as identified in the article) are numerous. The biggest problem is that it appears "simple" at first blush (just add a deleted_at column!), but it rots your data model from the inside out.
  • zozbot234 4y ago
    An event store is just a special case of a temporal database. The whole point of temporal databases is to natively support the notion of historical vs. current data.
  • rodelrod 4y ago
    Or you can see it the other way around: soft-deletes are a pragmatic alternative to event sourcing that provides a lot of the value without requiring a team of super-humans and a radical redesign of the existing systems.
  • Terr_ 4y ago
    > My experience is that soft-deletes are blunt tools bridging the gap between hard deletes and event sourcing Agreed, sometimes it makes business-sense to implement it, but in the big picture it's still kludgy and not-ideal. While full-on event-sourcing isn't always the answer, once business-rules prevent you from un-deleting anything there's not much point of having all those dead-rows interspersed in your regular tables.
  • btown 4y ago
    The question for either of these systems IMO is: do you trust that a change from your upstream represents a true, everlasting intention, or is it something that may need to be reinterpreted or rolled back in the future? At my startup, soft deletes for our SKUs are critical, because we work with data sources where notoriously both the technical systems and the humans driving will all-too-frequently accidentally represent something to our connection as deleted. Or there might be an irrecoverable error when asking "what things are still active upstream" - but that doesn't mean the SKUs are deleted, we might just not have certain live details until a bugfix is made. So "error status" and "soft delete" are somewhat synonymous, and both require investigation into root causes and root intents. Yes, the concept of "unerrored and active" is peppered through our codebase and analytics - but our ability to recover from supplier technical mistakes is much higher as a result. And we could absolutely do this with an event sourced system - but the tooling for relational databases is so much better, it's night and day.
• satyrnein 4y ago
  We switched a lot of tables to soft deletes so we could replicate those deletes into our data warehouse. You can also use bin log replication for hard deletes, but every schema change would break it.
• justin_oaks 4y ago
  The author didn't mention it, but restoring data from a database backup is a perfectly reasonable way to handle undeletes. By this I mean the situations that are "Oh crap, we didn't mean to delete that!" instead of usual business operations. I've probably restored data from backup maybe 4 times in my career. I greatly prefer to do this on the rare one-off scenario than to deal with the overhead of soft deleting everything.
  • marcosdumay 4y ago
    The difference in framing one gets by looking around is amazing, even funny. > I've probably restored data from backup maybe 4 times in my career. Yet, I often use soft-deletes because it allows people to undelete things from the software interface and not call me all day long. But that's not the most common reason I have for them. Normally it is because the data just can not be gone, and the full table is still important somewhere.
• tgbugs 4y ago
  One use case that I think is not sufficiently considered in this is related to two comments I made about a year ago [0, 1]. If you can _actually_ delete something, then that means that a malicious actor can fabricate data an claim that you deleted it. GDPR may be well intentioned but systems that have the ability to remove any record of a thing lay the groundwork for systematic fabrication of data, because any record of the past has been erased. Operationally, I can totally see why soft delete might be considered to be problematic in certain cases, but from an information security point of view I think it is absolutely critic for protecting users against a whole class of attacks. 0. https://news.ycombinator.com/item?id=27249738 https://news.ycombinator.com/item?id=27249738 1. https://news.ycombinator.com/item?id=27691442 https://news.ycombinator.com/item?id=27691442
• deerIRL 4y ago
  As someone who has done development work with Class A data and specifically in the realm of justice, soft deletes aren't simply a good idea, they are required by law. Most of these downsides are easily mitigatable issues as well. As many users have stated, something like views solves the issue of forgetting the 'deleted' clause. Lastly, I'm not sure the issue with foreign keys/stray records really resonates with me. I'd be hard pressed to be comfortable allowing a developer or DBA who isn't fully comfortable with the data model to be hard deleting records, let alone flagging them as soft deleted.
• Apreche 4y ago
  I agree with the author that a separate table is the way to go, but I go one step further than the author and use database triggers to manage that second table. Alternatively, a combination of database views and triggers can do the same thing without having an actual extra table to manage. Either way, it allows you to have soft deletion and/or full activity logging functionality without the application having to know about it.
• codemac 4y ago
  Well, there are several problems with this analysis when you go very large (>10000 machines): - For many applications, it's easiest to put the state of the object in the primary key, and thus point reads will fail when something gets deleted. This has other problems though with hotspotting and compaction during deletes. The deleted table doesn't really solve this either. - For storage systems, GC is critical functionality to implement. Most systems whether they want to believe it or not are glorified storage systems. Garbage collection is hard to do at scale, and I've never seen it implemented as SQL statements rather than code. Especially for GDPR etc. - For large scale distributed systems, foreign key constraints are rare if impossible to implement with reasonable latency, so they don't exist either way. I haven't worked on a system in >15 years that had fk constraints. - For large scale restores where you need to undelete trillions of rows, keeping the rows basically pre-assigns the distribution of writes. When you have to re-create the rows, you tend to get intense hotspotting and failures along the way as you attempt to load balance on the keyspace of the writes. A deleted records table is good for smaller (<10000 machine) systems when latency between nodes can be kept within the same campus. It can really improve performance of your GC if reading by column isn't fast compared to reading by table.
  • mixmastamyk 4y ago
    Advice should be aimed at the 99% rather than the 1%, right? I guess Heroku and Stripe don’t have the biggest datasets in the world but they are probably larger than most folks will need to manage.
    • codemac 4y ago
      Sure, the analysis is not "wrong" or something. That cannot be judged without a context. I just hope that those building systems they desire to be very large do not follow this post's advice.
• vyrotek 4y ago
  I've found SQL Server Temporal Tables are a good alternative to get the benefits of soft-deletes without some of the drawbacks. https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables https://docs.microsoft.com/en-us/sql/relational-databases/ta...
  • tfigment 4y ago
    Mysql also has this now. I've wanted to rewrite out apps to use it but haven't gotten around to it. Postgres has it as an addon but feels like it wouldn't work for us until its first class supported.
    • evanelias 4y ago
      MariaDB has this -- called system-versioned tables -- but MySQL actually does not. Although they share a common lineage, MySQL and MariaDB are somewhat distinct databases at this point, with each one having a number of features that the other lacks.
    • nicoburns 4y ago
      I believe first-class support is in development for Postgres. The article I read made it sound like it would probably land in either the next major version, or the one after that.
• ajuc 4y ago
  If you need this why reimplement it when you can use database history (dbms_flashback or SELECT AS OF in Oracle)?
• timomax2 4y ago
  We just have a history table (for each table) where all deleted and past versions of record are stored. Seems to solve all the issues. The history table is NOT part of the application, but is there for audit and diagnostics etc.
• ivank 4y ago
  https://github.com/xocolatl/periods https://github.com/xocolatl/periods implements SYSTEM VERSIONING for PostgreSQL and moves deleted rows to a history table.
• tehbeard 4y ago
  > Instead, we rolled forward by creating a new app, and helping them copy environment and data from the deleted app to it. So even where soft deletion was theoretically most useful, we still didn’t use it I don't get this statement. You wouldn't have had the env or data without soft delete? You did use it! I would say, soft delete isn't a tick the box and done solution as many ORMs make it. You need to consider the data model, and adjust your queries to that. It may make sense for a product to be deleted, but orderlines still able to access it to display product name etc. With blob data, I tend to move that to a "bin" with a 30-60 day grace period. Customers know quickly reporting, we can fully recover, while outside that time they'll have to provide images etc. It's a decent compromise. Reuse of unique fields is the sticking point I run into often, as mysql interprets null as not clashing with other nulls so composite uniques using the ID and deletion date don't work.
  • TylerE 4y ago
    > mysql interprets null as not clashing with other nulls Which is correct per SQL. Null is NaN, not zero (or negative infinity).
• willlll 4y ago
  For the control plane part of Crunchy Bridge, on day one I decided to go with the deleted_records table that is mentioned at the end of this post. It's been great. No need to keep around dead data that no one ever looks at. We don't need to have `where deleted_at is null` on every single query. But the best part though is our actual working data set of records we actually care about is tiny compared to the deleted cruft that would have otherwise been just sticking around forever. Backups and restores take no time at all. It's really cool that postgres lets you have conditional indexes on things, but it's even cooler not to need them.
• phibz 4y ago
  I've definitely seen soft delete work in practice. A couple things: for small data sets you can implement the naive deleted_at you can hide the records from your users by forcing them to use a view. You can also handle updates on the view to prevent data conflicting with deleted data if you need to. For foreign key constraints you can set the foreign key to null and orphan the records if the relation is deleted. You could also hard delete them in this case. It depend on your use case. When the data volume grows or the ratio of soft deleted to normal records is high, you should consider another solution. One solution you suggested, moving the record to a deleted table is a fine one. The other solution that I've used successfully is to journal your deletions in another table or system. For smaller volumes having an audit table Journaling the data and storing the pk, fkeys, and a serialized version of the record, json works great in postgres, works well. For large volumes or frequent deletions something like Kafka or PubSub work better. You may very well find others interested in consuming your audit journal to track changes. Updates and even inserts fit great in the more general case.
• deleted 4y ago
  [deleted]
• hn_throwaway_99 4y ago
  The deleted records table he mentions at the end is a good approach, but: 1. This can easily be done with a trigger, so that you just call a DELETE on the table and deleted tables are copied to the deletion table automatically. 2. I prefer, instead of having a jsonb column, that each table has a corresponding `deleted_original_table_name` table that exactly matches the schema of the base table, with the addition that the first column is a `deleted_at` timestamp. It's easy to use helper methods in schema migrations to always keep the table definitions in sync.
• kardianos 4y ago
  This poster misses the point completly. Soft delete is a must have for historical data, where you want to keep history, but keep the current set clean. Effectively, you don't check for the soft delete flag if you get to it from a an un-deleted record, but you do check for it if you access it the other way around.
• pgt 4y ago
  XTDB: xtdb.com
• 202206241203 4y ago
  It's something that a team of a PM, a QA and two developers can bill for at least a sprint. So, well worth it.
• adrianmsmith 4y ago
  > Instead of keeping deleted data in the same tables from which it was deleted from, there can be a new relation specifically for storing all deleted data The disadvantage of this is that if you ever do want to access this "deleted" data, e.g. in admin or compliance tools, you now have to do it in two different ways, one way for the main data and a different way in case the data has been "deleted". The article asserts you'll never need to "undelete" the data. So they're presenting a solution with that assumption, fair enough. Without that assumption, however, moving the data back from an archive table becomes a pain, and if there are any unique constraints e.g. on username or email address, you'll have a problem if you've moved the data out of the main table and another user has used that username or email address.
  • Terr_ 4y ago
    > The article asserts you'll never need to "undelete" the data. IMO it's worth distinguishing between (A) some kind of "click to undelete" feature versus (B) simply having that old-data conveniently exposed for a developer to manually-edit things or craft database-change scripts. In practice I've only ever seen the latter get used, because it requires a developer to figure out how the heck to get "the parts that matter" back while preserving the integrity of other newer data and obeying certain business-rules.
  • layer8 4y ago
    > now have to do it in two different ways Use a view. > if there are any unique constraints e.g. on username or email address Have those in a dedicated table where they aren’t deleted, and add a synthetic key referenced by the other tables.
• whack 4y ago
  We use soft-deletes extensively at our startup. Here's a couple reasons: - Feature creep. "Sometimes our users accidentally hit the delete button, or change their minds a minute later. We want to give them a way to undo the deletion." Or "I know we said last quarter that we users want to delete stuff, but they also want to see a list of everything they've deleted in the past." Soft-deletes handle feature-creep a lot better than hard-deletions - It simplifies foreign-keys management. If you want to hard-delete something that some other entity is referencing, you'll have to hard-delete or modify that other entity first. And potentially repeat this process recursively for their own references. This is a pain. One could argue that if you really want to delete something, you should be deleting all children as well. Such arguments are highly domain specific, and very bad universal claims. We've seen some use-cases where such pedantry is not necessary - It makes it easier to recover from mistakes and bugs. Customer deleted something accidentally and emailed you begging for help? Your code has a bug causing stuff to get deleted when it shouldn't be? You'll be thankful you did a soft-delete and not a hard-delete. Is it going to solve every single problem where the data has system-wide ripple effects in a unicorn sized organization? No. But it'll still solve a number of problems where the data impact is more localized - It makes debugging easier. You have a clear record of everything that used to exist. You don't have to go digging through your logs to find something that used to exist but has now been deleted - Speed. All of the above problems can be solved in other ways too. The author suggests putting all deleted data in a "deleted records table." So now you need to maintain a 2nd table for every table that you may want to delete stuff from. All schema updates will need to be mirrored on this 2nd table. And you'll need to write and maintain code to populate this deleted-records-table every time you delete stuff from the original table. All doable and straight-forward but takes time away from other things you could be doing instead The main benefit from hard-deletions is data compliance and liability. Ie, being able to tell privacy-conscious customers that you actually deleted their data. If you're handling any sensitive data, you should definitely do hard-deletions at some point for this reason. But the other reason the author gave - "it's annoying having to check for `deleted_at` when writing SQL queries" - seems pretty minor compared to the benefits.
  • waspight 4y ago
    It seems that it is too easy to delete things in your system. Rather than solving it with reversible soft deletes I would suggest to improve the UX. I don’t agree that it simplifies foreign key management, it is most often the opposite from my point of view.
• jandrewrogers 4y ago
  The complexity of soft deletes is that they implicitly introduce the difficult semantics of bi-temporality into the data model, typically without the benefit of a formal specification that minimizes the number of edge cases that have to be dealt with. Mechanically, I've typically supported soft deletes with audit tables that shadow the primary table, with a bunch of automation in the database to make management mostly automagic. It isn't too bad in PostgreSQL.
• vivegi 4y ago
  If you do want to retain the deleted records for any purpose (audit, compliance etc.,) it is better to design a DELETED table to maintain the history (just as suggested in the article towards the end). Once your main tables start getting to the order of tens of millions of records, the filtering by 'deleted_at is NULL' or 'deleted_at is NOT NULL' gets in the way of query performance. NULL is also not indexed. So, that throws the spanner in the works sometimes (depending on the query).
• sam_lowry_ 4y ago
  I do not understand the foreign keys issue. Do not use the deleted_at timestamp that is nullable by default. Instead, nullify the field when the line is deleted. Foreign keys on NULL values will be possible. In any case, soft deletion is usually a sign of incompetence. Whenever I saw it on a project, both soft deletion and the project turned sour.
• openthc 4y ago
  I use a delta-log table, so each INSERT/UPDATE/DELETE on objects I care about are captured (via trigger) -- but that one has to get date partitioned. So in my system a DELETE statement (and DELETE CASCADE) work as expected -- any history has to be discovered from the logs
• Pxtl 4y ago
  Trivial case I hit: 1) Client wants to remove user from the system who have left their org but 2) There are objects that were contributed by that user which are required to persist beyond the user's deletion. Those are ideal cases for soft deletion. We can still query information about the deleted user to explain who created this object, with the note that their account has been deleted. Probably I should be doing full event-sourcing for this case, but delete flag works well. MS offers temporal tables for this use case and I'm still considering the implications there -- AFAIK ORM support is WIP. And unlike the article author, I have used soft deletion to undelete things. Many times. Maybe he has better users than I do, I don't know.
  • mixmastamyk 4y ago
    Another way to handle is to set each obj.user to a “Deleted User” record.
• waspight 4y ago
  I use soft deletes to maintain insights. For instance I would like to know how many users that has been created in total even if some has been deleted later on. Is this a bad approach? Most of the other comments here seems to use it only to be able to restore deleted entries.
• khaledh 4y ago
  One reason we encourage keeping soft-deleted records at least for a while is synchronizing data across systems. We want to propagate deletions downstream. At some point when all downstream consumers have caught up, we can purge the soft-deleted records.
• wizofaus 4y ago
  The assumption seems to be that the undelete operation is performed by the vendor's support staff, rather than the end user. I've been involved in the implementation/ maintenance of systems with soft delete that was entirely for that purpose - it allowed the user to delete/undelete at will. In our case it also meant certain uniqueness constraints were kept in place effectively reserving things like email addresses or business registration numbers that couldn't be reused until a hard delete was issued. Arguably it's more like an "is active" flag in such a case, but it's debatable what the distinction is.
• dragonwriter 4y ago
  > The concept behind soft deletion is to make deletion safer, and reversible IME, as with “updated_by” and “last_modified_at” columns, it's usually hazy audit requirements, not making deletion reversible, that motivates it. A proper history store maintained by appropriate triggers solves this, and leaves the referential integrity constraints on the base table intact. (It can also be used for reversibility if you need that.) Views conceptually would work, but then you get bitten by all the ways that all relations are not equal in real-world RDBMSs.
• armchairhacker 4y ago
  Dumb solution: make soft deletes explicit in your backup system. Your company has a database backup system right? That system should be configured so that when it runs a backup, it will not remove deleted entries from the previous backup, instead just mark them as "deleted_since" the current backup time. Idk if any backup system actually support this, if there's some glaring problem (like you can't just overwrite parts of a database backup for some reason), or if most companies just don't have backups because they're too expensive (probably not), but this is the solution I would go with. It works for other sorts of data like file systems as well.
• danielrhodes 4y ago
  In a previous place I worked, we were programmatically using Box to store files. One day we were presented with a case study in Murphy's Law: a script went awry and deleted everything (10s of thousands of files). There was no clear way to recover these files, they were gone from what we could see. It was a disaster. We got a Box support person on the phone and described what had happened. There was a pause, some mouse clicking and then: "Ok, those files will be back in your account in an hour." It was 100% our fault. But soft deletes saved us that day. If you're in a situation where you or your customers could benefit from the same, it's wise to not only embrace them but also make sure they work.
  • pradn 4y ago
    The author agrees with you in principle. All the author is arguing against is the use of "deleted" bool column to indicate deletion. His solution of moving deleted objects to their own column gives you the ability to un-delete, just as before. Only now, your queries and indexes are simpler and you get to use foreign keys and other useful futures.
    • pradn 4y ago
      moving deleted objects to their own table* (not column)
      • mixmastamyk 4y ago
        Also timestamp column.
  • latchkey 4y ago
    That sounds more like a lack of backups and disaster recovery than it does soft deletes.
  • foolfoolz 4y ago
    Box has a well defined schedule for the various stages of trashing. some of them are user configurable. i would call this workflow expected behavior of the application. this sort of soft delete is something you design in intentionally knowing it’s a customer use case. there’s many other objects in Box that do not need this workflow. i think soft deletes don’t need to be available for all tables but some it’s immensely helpful
• scifibestfi 4y ago
  > When I worked at Heroku, we used soft deletion. When I worked at Stripe, we used soft deletion. At my job right now, we use soft deletion. > As far as I’m aware, never once, in ten plus years, did anyone at any of these places ever actually use soft deletion to undelete something. That's wild. So it seems the idea of needing undelete is largely an unfounded fear.
• pierrebai 4y ago
  The author claims pruning soft-deleted entries requires a complex query, but hard-deleting an entry would have required the same complexity. So it's really not an argument.
• scott_w 4y ago
  The example the author gives is… frankly awful. I can’t think of a single case where you’d want to remove the invoices of a customer you delete. Ever. In fact, the opposite is more likely to be a big problem, accidentally cascading your delete to your financial records! Using a soft delete, your invoices won’t “disappear” because your app WILL have a view for looking at just the invoices. Source: I built a bookkeeping system and soft deletes is a necessary feature.
  • decebalus1 4y ago
    > I can’t think of a single case where you’d want to remove the invoices of a customer you delete. Ever. CCPA will require you to delete the invoices. And I would love for all platforms to support deleting everything, including invoices, considering some things are illegal in other places and if there's proof of you buying said illegal thing, you can get in serious trouble (think gay dating apps in the UAE). But I don't really agree with the author on his take about soft deletions.
    • mixmastamyk 4y ago
      You don’t say why (last paragraph).
• outworlder 4y ago
  I wish Datomic was made open-source (with maybe some features available as an 'enterprise' offering) so that we could actually have a decent alternative for this 'soft-delete' problem.
• JohnBooty 4y ago
  I've been a software dev since the 90s and at this point, I've learned to basically do things like audit trails and soft deletion by default, unless there's some reason not to. Somebody always wants to undelete something, or examine it to see why it was deleted, or see who changed something, or blah blah blah. It helps the business, it helps you as developer by giving you debug information as well as helping you to cover your ass when you are blamed for some data loss bug that was really user error. Soft deletion has obvious drawbacks but is usually far less work than implementing equivalent functionality out-of-stream, with verbose logging or some such. Retrofitting your app and adding soft deletion and audit trails after the fact is usually an order of magnitude more work. Can always add it pre-launch and leave it turned off. If performance is a concern, this is usually something that can be mitigated. You can e.g. have a reaper job that runs daily and hard-deletes everything that was soft-deleted more than n days ago, or whatever.
  • pg_1234 4y ago
    This 100%
  • nicoburns 4y ago
    +1 on audit trails. And one should always store audit trails in machine readable format. That way you can not only manually inspect what happened, but you can query it too (and reconstruct the entire state as it existed in the past if necessary).
  • augustl 4y ago
    This is why I don't understand why Datomic isn't more popular. Pretty much every system I've worked on never needed to scale past 100s of writes per second due to hard limits on the system (internal backoffice stuff, fundamenally scoped/shardable to defined regions, etc etc). And since Datomic is built with that in mind, you get the trade-off of full history, first class transactions and being able to query for things like "who changes this attribute to its current value, when, and why" is such as super power!
    • th0ma5 4y ago
• ThePhysicist 4y ago
  I don't get what the problem is with cascading deletes. I mean you typically only use them for foreign keys where deletion of the parent object makes the referencing object simply invalid, so there would be no reason to leave the referencing object in the database. The point that is true is that queries get more complicated as you'll have to add a "WHERE deleted_at IS NULL" to every SELECT (once for each table you refer to), but that can be automated if you use an ORM. A paradigm that I often use is that all objects in the database belong to a role object that determines who can read/write/delete the given object. So before doing anything with an object I always check the role object (e.g. the "user" referencing an "invoice", to stay with the example OP gives), and as part of this I check whether the user object still exists. Alternatively, you could automate most of the required update logic using triggers as well. But otherwise I agree, soft deletes often don't seem to be a worthwhile tradeoff, not sure if I would use them again when designing a relational schema. They are very useful for auditing and undo though: In a current project, whenever a set of objects gets updated I soft-delete the old versions and create new objects, keeping the UUIDs intact. That allows me to display the entire version history of each object to the user, which can be necessary e.g. for compliance reasons. You can achieve this with an audit log as well but that would require more logic and different queries, whereas querying soft-deleted objects just requires a slight modification of existing queries.
• jtwebman 4y ago
  The bigger reason to use soft deletes is to keep history. Just because someone does not access doesn't mean we should report on the things they did months ago.
• justin_oaks 4y ago
  For those who are expressing favor with soft deletes, do you default to soft deletes on every table unless you know you won't need them? Or do you only apply them where you know you'll need them? I think people arguing for and against soft deletes both understand that there are cases where you want to use them and when you don't.
  • baq 4y ago
    soft delete everywhere by default. true deletes only after retention policy expires, if FK constraints allow it (best if you can drop whole partitions).
• spfzero 4y ago
  I like the deleted-items-table suggestion the author makes. It's useful though, to think about the cases where you'd want to delete, say a customer with existing invoices. In one situation, you may have made a mistake and want to start over, say an operator creates the customer and order, but then the customer changes their mind. In that situation a hard delete is in order; you want to "undo" the _creation_ of the customer and invoice, and nothing further has happened as far as referencing their key In other situations though, you may have some reason to treat the customer as if they were deleted, but better to examine the reason for that, and use an attribute more relevant to that reason, such as active/inactive etc. Would be different for different entities of course.
• lolsal 4y ago
  In my 20 years of software experience the soft delete is not so often used to undelete something, but more often used to know what has been deleted. If you delete a record from a table, did it ever exist? Can you reference that customer/user/product ever again? Not to mention the one-in-a-million case where a customer had their account erroneously or fraudulently deleted - undeleting saves time/money/bacon when it's needed and is relatively inexpensive to maintain.
• pilgrimfff 4y ago
  All you need is a layer of abstraction to get past the downsides of soft deletion. You can use views or your ORM (if you use one) In Django, it's really easy to create almost seamless soft deletion logic in the model manager or in your querysets. Over the last decade, I find myself using soft deletion more and more - usually to accommodate user/client requests.
• jacobsenscott 4y ago
  Deletion is never worth it full stop. How do you delete from a backup? You can't delete all your backups. Effective dates and app level encryption to allow for cryptographic "deletes" is the way to go.
• nwah1 4y ago
  If you have a lot of stored procs then the argument makes some sense. If you do most things in code, then I would argue these complaints are moot. In your code you can isolate all soft-deleting from business logic in the ORM layer or data layer, so the complaint about littering your codebase is moot for me. For instance, using Entity Framework, you can change deletes to soft deletes in a centralized place for all records matching a particular interface, then add a query filter that applies in the background for all queries. The complaint that soft deleting is never done is maybe valid since you can review things with audit logging or backups without risking unknown effects of an un-delete. But if you need a recycle bin feature then you get that for free if you just build that in from the start, and it is one more guarantee. The risk of orphaned records is real, although you could probably handle most cases generically in the data layer or ORM as well. It seems like there's just tradeoffs to the various approaches. Do you want to err on the side of deleting data, or on the side of keeping it? Do you worry more about orphaned records or data loss?
• revskill 4y ago
  In realworld, there's no concept as deletion from DB ! There's only deactivate account, archive a legacy product,... Because there's no such thing as delete something from real world.
• dcdc123 4y ago
  If you are using a state manager with models in something like rails/django/etc then it is trivial to support soft deletion without it infecting your entire code base.
• jonstaab 4y ago
  If you implement soft delete, you should surface it to your user. That's who is accidentally deleting things, and that's who will want to un-delete them. As for side effects like spinning up/down servers, build that into your data model (of course, in a case like Heroku's that can be prohibitively expensive, so don't). Source: I write back of house software for resale store owners, and accidental deletes happen occasionally. Being able to restore things instills a lot of confidence for our customers.
• kache_ 4y ago
  wait until this guy finds out about financial regulations
• encoderer 4y ago
  Even if you don’t “undelete” something, soft deletes make it possible to instantly hide something while saving the expensive sql delete for processing later.
• Minor49er 4y ago
  It's interesting that the author notes that, as far as he's aware, nobody's ever undeleted something. It could be true. But I'm wondering if maybe he simply hasn't seen it first-hand since the action of recovering something is often handled by a customer-facing team and not by a developer.
• Ensorceled 4y ago
  I use soft deletes in our system and literally used it to restore an accidentally deleted item about 3 hours ago. Took a second to toggle the deleted item. I don't get how this rocket science. Almost every query in the system is some kind of where clause on a fk to account or user or project or some other critical object ... so there are only a few places in the ORM where I need to support this.
• mrinterweb 4y ago
  For audit trails in rails, I still like papertrail. https://github.com/paper-trail-gem/paper_trail https://github.com/paper-trail-gem/paper_trail. It provides the ability to restore records as well as auditing abilities.
• agentultra 4y ago
  Soft deletion by storing the row in JSON won’t survive months of schema migrations. If restoring a record is rare you don’t want to have to find out that there’s no way to map the old data to the new table when it matters. There are cases where you shouldn’t be deleting or updating data; auditable and non-repudiation systems for some regulatory compliance come to mind. Best to use patterns that don’t require those operations. Soft deletion does come at a cost. Choose carefully!
  • deleted 4y ago
    [deleted]
• Smoosh 4y ago
  DB2 has implemented temporal tables which can automatically capture all changes to the primary table. https://www.ibm.com/docs/en/db2/10.1.0?topic=tables-history https://www.ibm.com/docs/en/db2/10.1.0?topic=tables-history
• jacksnipe 4y ago
  The ONLY reason that you should avoid soft deletion is that deleting things permanently in a soft-deletion-based system is hard and error prone. GDPR, among other regulations, requires that you be able to do this sometimes; and it requires that the data REALLY BE GONE. But I really think that soft deletion should be the default unless you think you’ll be fielding user data deletion requests.
• duxup 4y ago
  > All our selects look something like this: SELECT * FROM customer WHERE id = @id AND deleted_at IS NULL; Solution… a whole other table of deleted stuff… in a new structure. Man soft deletes just look better to my eye.
• dunkelheit 4y ago
  This brings back memories... Some time ago I was an intern in a team working on a UGC map editor. We were using this soft-delete pattern and for some task I needed to deploy a database migration that fiddled with the "deleted" status field. It was quite late and after the migration finished I almost went home but for some reason decided to check community forums. There users were having a time of their life taking screenshots of deleted objects that suddenly became visible (many of them quite amusing, including swear words written in 500km letters). Dunno how this escaped testing, but horror of what I have done brought clarity of mind and I quickly found an error and devised another migration that fixed the data. That worked and I was able to finally go home. So yeah, be careful with the soft-delete pattern :)
• AdrianB1 4y ago
  Soft deletes are really worth in the right scenario. There are cases when they can be avoided, cases when they are not worth and cases when they are worth, for the problems presented in the article there are solutions or workarounds.
• muhaaa 4y ago
  Always use a temporal database (datomic, postgres with temporal_tables extension). You get out of the box the full history of your data. That is really helpful for business intelligence and analytics, auditing / audit log (security, accountability), live sync & real-time features and as a bonus easy recovery after application fails. If disk gets to full, project the latest time slice into a new database and move the old database onto a cold storage.
  • hu3 4y ago
    MariaDB as well: https://mariadb.com/kb/en/system-versioned-tables/ https://mariadb.com/kb/en/system-versioned-tables/
• rubyist5eva 4y ago
  One thing that I could find in the article: performance. At least for our use case, soft deletes made everything slower because it's much harder to index. For our database we basically had to do an audit of all of our WHERE clauses and create partial indexes on "not yet deleted" records. Of course, this bloats your indexes/disk and hurts write performance so it's not a silver bullet. We've also taken to inserting into "delete records tables" for records we may want to recover or for historical reasons. You still lose foreign keys but indexing and query optimization is a lot easier, and your old data is just still a simple query away.
• krascovict 4y ago
  If it's the case of deleting files safely, I recommend shared, it's very good... https://wiki.archlinux.org/title/Securely_wipe_disk https://wiki.archlinux.org/title/Securely_wipe_disk
• jasonhansel 4y ago
  It's pretty easy to solve the foreign key issue (where you need to write elaborate DELETE queries to avoid breaking foreign keys) in Postgres using deferrable constraints. Just start a transaction, run "SET CONSTRAINTS ALL DEFERRED," delete rows from various tables in any order, then commit the transaction. The DELETE statements will effectively ignore the foreign key constraints, but any remaining "broken" foreign keys will be caught when the transaction commits.
• joshuanapoli 4y ago
  Atlassian's deletion-related outage demonstrates why soft deletion should be the default. Use hard deletion after a grace period for data that truly needs to be expunged. Even if undelete is not part of the normal workflow, experience shows that swift recovery from bugs and operator errors is a universal part of serving users. The less data motion involved in deletion (and recovery) the better for both the original deletion process and also any recovery process. https://news.ycombinator.com/item?id=31015813 https://news.ycombinator.com/item?id=31015813
• deleted 4y ago
  [deleted]
• gigatexal 4y ago
  There’s a lot wrong with this write up. Why would anyone want to delete corresponding invoices when you “delete” the corresponding user? And GDPR provides a caveat that if you need the data for a biz usecase like legacy reporting you can keep the data (I think it has to be masked or something but it’s not insane to say you must delete data on request that could materially affect a company like removing transactions). Just put a filtered index on the column to better query non deleted data. On the whole I don’t think in practice the author’s take makes much sense.
• kleer001 4y ago
  Yea, it is.
• viiralvx 4y ago
  I don't know if I 100% agree with this blog post. Additionally, having foreign key constraints isn't a "catch-all" solution and breaks at scale. There's frameworks like Rails that can still handle these discards for the user via the `dependent` option on the model with some extra code. At my current employer, we noticed that `acts_as_paranoid`'s default behavior was not what we wanted, so we migrated over to `discard`. We also added a concern that reflects on dependent associations, finds if they are discardable, and discards them if possible. And that cascades down, easing those concerns. This `Discardable` concern is automatically added to every single soft-deletable model and it has been working out great for us. [1]: https://github.com/jhawthorn/discard https://github.com/jhawthorn/discard
• astura 4y ago
  >so you can be left with your customer being “deleted”, but its invoices still live. This not a problem, its is almost always what's desired, otherwise you have no records for, for example, the tax auditor. Obviously when, say, an employee leaves basically all things they did on a corporate system can't disappear. Any documents they created/updated still need to be accessed, their git history/commits can't disappear. When you switch classrooms you don't want all the events that ever happened in the old classroom to disappear. This sort of systems are the kinds of systems I've worked with my entire career. Undeletion happens all the time too (employees get rehired, for example). Most computer systems aren't B2C free social media sites where you CAN just delete anything you want because no data is important.
• mizzao 4y ago
  The most famous example is perhaps the recent weeks-long Jira outage, right?
• wruza 4y ago
  But with soft deletion, this goes out the window. A customer may be soft deleted with its deleted_at flag set, but we’re now back to being able to forget do the same for its invoices. What? You do not delete invoices, unless you’re trying to take revenge on your accountant. This is what soft deletion is (partially) for: you don’t want to see Alice in a customer list for some reason, but her invoices are the accomplished fact. You can even visit her card from there, but it is unlisted everywhere else. Of course that depends on which sense you put into deletion, e.g. you may put obsolete cards into a special group instead and only use deletion to remove data completely with all references. But then deletion is useless, because only a programmer to the bone can imagine deletion of a customer together with all historical (legal) documents they participated in.
• sfink 4y ago
  I don't really have enough experience with this stuff for my opinion to have value, but a lot of the opinions I see here appear to me to be dancing around the real question. I disagree with the terminology in the article. "Soft deletion" suggests that the complexity is in the "soft" part, and that a common way of implementing it is problematic. I disagree. There isn't some orthogonal "soft vs hard" dimension to a generic concept of deletion. The complexity is in the meaning of deletion. In accounting, you don't simply delete. Or when you do, you really do, and the two operations aren't the same in any meaningful sense. If you want data to still be available—whether it's for debugging or analysis or auditing or whatever—you should be thinking about the semantics of what you need, and structure your data model accordingly. The `deleted_at` column approach is a DB design smell if it isn't supporting application logic (where "application" may include auditing or whatever). It works against the DB's mechanisms to maintain data integrity. FKs are just one example. An example: consider the place where you want to keep historical data, but you're also going to be modifying your schema. If you use a `deleted_at` column, your migrations will start inventing more and more things that were simply not true at the time a deleted row was alive. It will lie to you. It's the same if you move data to a single deleted data table and then migrate that table repeatedly. For maximal semantic purity, you probably ought to leave "deleted" data in a historical table matching the historical schema, and then use views to glue things together for convenience. If you update the live data schema in an incompatible way, you might even be saved by the FK constraints on the archive tables. But that's a pain, and whether or not it's less pain than the other approaches depends again on what deletion semantics you are targeting. Crossing your fingers and closing your eyes and hoping that your chosen mechanism's semantics are close enough to the semantics you need is going to bite you. A `deleted_at` column can absolutely be the right solution if your rows have a status that changes over time, and one of the statuses that you're willing to support (with potentially brittle code) is "archived".
• rtpg 4y ago
  I believe you can get most of the advantages of soft deletion through a notion of archival. Usually archival does the main thing (“get this out of my main resource list”) without breaking audit trails or resource links. For people for whom this is insufficient, you can of course offer hard deletion.
• jb3689 4y ago
  Dealing with a separate table is still hard (I know because we do this). What happens when you do a migration or need to shard something and want consistent partitioning across your data? You have to consider your one off table that everyone inevitably forgets about. I agree that a deleted_at column is too big of a liability for compliance reasons though
• ccleve 4y ago
  Someday we'll have a database that handles this for us. We'll specify whether a particular table should have an audit trail. The system will know about foreign keys and related tables, and save them as well. Everything will be configurable, of course. Internally, the system will save the relevant data using the write-ahead log. Restoring deleted data will be easy, a simple command. Purging data that should disappear forever will be another command. This is all very possible. Someday. I'm embarrassed to admit how many decades I've been waiting for this.
• Gurgler 4y ago
  There's a very legitimate case that I've seen made for soft-deletion in several different situations: foreign keys related to "created-by" columns. Hard-deleting a user who created an object that remains in use after they're gone would trigger referential integrity complaints on those columns. Without being able to reference a "deactivated" user's primary key in such a situation, you'd have to come up with some counterintuitive system for revisiting such objects. And the result (short of removing the foreign key) would be to give you inaccurate information about who created the object. Maybe one of you smarter people has already thought of an elegant way to handle this, but I've never seen one that satisfies my taste.
  • dubswithus 4y ago
    There are Rails gems that can handle this in various ways. But the easiest way is to deactivate the user account (is_active boolean) and continue to reference the user in internal records.
    • whoomp12342 4y ago
      thats exactly their point, is_active or deleted_at represents the same thing(just inverse). However, if you DONT use an active/deleted flag, and instead do what the author suggests, I dont know the right way to support deleting said user If you set the deleted_record table as part of a trigger on delete of other tables, you could turn on cascading delete and hope for the best. Outside of that I dont have any plan for using this with referential integrty. It would be easy enough if you decided NOT to use referential integrity, but then you save the space of ONE user record and retain how many orphan records, making them all effectively soft deleted anyways... whats the point?
  • unemployable 4y ago
    Yeah that is the main problem with not using soft deletes. The question is though, if you delete a user, should the user's personal information still exist in your database, or does that violate some kind of privacy regulations? The idea of the deleted user's table is that it can be kept around and then pruned after x number of days to satisfy both privacy and undeleting. To keep the references around, I think one way might be to create two tables, so one table is used for all of the references and it stays around, and the other one gets deleted. Eg Account and User tables, or something.
• amerine 4y ago
  Brandur will know what I mean when I say, it’s always worked out in our favor at Heroku to soft-delete logicals, but hard-delete physicals. It’s not hard to remember to append a “where deleted_at is null” to some sql, or build into higher order UI’s. However, GDPR/customer data demands across regimes makes me agree with him and would suggests folks listen. <3
• mst 4y ago
  Soft delete has always caused me more trouble than it was worth. Keeping a deleted recrords table via app code or triggers has always been more trouble than it took to build.
• n4jm4 4y ago
  I can't even tell you how much political capital I lost at a major retailer recommending against wasting time implementing soft deletions... on an internal portal that babysat linter configurations. Don't ask me why the linter configurations weren't simply persisted in git.
• whoomp12342 4y ago
  good idea but only if you dont use foreign keys. If you do use foreign keys, then you must create custom deletion logic for each relationship. Yuck!
• Pakdef 4y ago
  not worth it for short term profits... which is why most of today's internet will disappear
• rplst8 4y ago
  • yomkippur 4y ago
• galaxyLogic 4y ago
  Couldn't soft deletion be happening behinds the scenes by the database engine? Then have a statement like RESTORE * FROM ...
• magundu 4y ago
  We use soft deletion by moving all related rows into different archive database which will be cleaned for 60 days older entries. For accidental delete, we will undelete from archive.
• rzwitserloot 4y ago
  > But the technique has some major downsides. The first is that soft deletion logic bleeds out into all parts of your code. All our selects look something like this: A view solves that problem. Make a view that only has the non-deleted stuff. Give it ON UPDATE and ON INSERT triggers so that it walks, talks, swims, and quacks like a table. Voila, no more code bleeding. > Another consequence of soft deletion is that foreign keys are effectively lost. Bit trickier, you have a few options: * Make the constraint include that the foreign object has `deleted_at IS NULL`. * Add a trigger that automatically marks as deleted (I'm assuming a setup where you'd ordinarily use ON DELETE CASCADE) anything that refs row X when you mark row X as deleted. If you prefer commit failure ON DELETE instead, triggers can do that too. > GDPR scaremongering You don't have to delete records from backup tapes either (SOURCE: I read the whole thing). Using soft delete is actually making life easier for you - you presumably _do_ have certain data storage requirements (for audit trails and the like), and now you can just have the one canonical database that contains it all. When its time to prune an entire customer/user into oblivion, it's simpler to do that then - just `DELETE` the right rows away (actual DELETE, not UPDATE SET deleted_at). Yes, GDPR has something to say about keeping data around where you have no feasible auditing or any other reason to have it, but that's a red herring: You don't want your database tables to grow humongous with 99% of the rows 'deleted'. That view with some indexes can do a lot but it isn't magic. Presumably you want a cleanup task that, every month or so, DELETEs anything with a deleted_at value that's older than a month or what not. This fully takes care of your GDPR requirements as far as unreasonable data retention goes: A script automatically runs to wipe out all rows in all tables whose deleted_at is too long ago, and then reports that it did this so that you have the audit trail. So, for your requirement to delete specific records upon request, it's easier. For your requirement to not keep unneccessary data around beyond reasonable bounds, it's a simple script. > Here’s a snippet from one that I wrote recently which keeps all foreign keys satisfied by removing everything as part of a single operation If you set your constraints explicitly to checking only at the end of a commit this isn't at all difficult the way the author says it is. Just delete what you wanna delete, commit at the end, and poof - all is well. You can force postgres specifically into a 'yeah yeah do not check any constraints until I commit' mode if you don't want to change your ON DELETE clauses. > data deletion has non-data sideeffects That depends on the use case. It feels like a bit of a strawman argument - obviously if the delete action does irreversible damage, marking the database row using a soft delete is rather silly. Of course. Most delete operations are nothing like that though. > Alternative: A deleted records table Author's previous point about non-data sideeffects kills this just as badly. But, sure, this isn't a bad idea. However, most of the complaints about soft deletion apply in a different fashion to this model. For example, if you have reference constraints, and using ON DELETE CASCADE, you need to do a heck of a lot of copying. You don't just 'copy' the row you want to delete, you also have to copy every row of every table that refs your table with ODCascade constraints to its 'deleted' variant first, and only then can you delete the lot. > Hard deleting old records for regulatory requirements gets really, really easy: DELETE FROM deleted_record WHERE deleted_at < now() - '1 year'::interval. It is _exactly_ as simple to do this if you use soft-delete. Bit of an own goal. Author's got the right idea (soft delete needs some thought), but the technical aspects are a swing and a miss, I think. However, some database make some of these solutions hard. As far as I remember, they don't all support ON UPDATE/ON INSERT rules on views, for example. Fortunately, postgres supports all of this stuff.
• unemployable 4y ago
  Everybody always did soft deletes with the is_deleted column at companies I once worked for so that is what I would do. I noticed that a lot of bugs would occur this way because you would forget to add the is_deleted to the query somewhere. The queries were also longer due to the longer where clause and so on. These days I use a deleted table as per the article as I decided it would be better to deal with the more complex undelete process. It keeps that process to a single section instead of spreading it all throughout your database. Some of the suggestions here like "use views" don't really work for two reasons - sometimes the is_deleted check must be performed in the ON clause, not in the WHERE clause, and sometimes you want to count the deleted or show the deleted, while other times you don't.
• kleebeesh 4y ago
  Maybe a more accurate take: Half-assed soft deletion definitely isn't worth it. If you're just going to throw in some deleted bool or deleted_at timestamp without thorough testing, you might as well just skip it. It's virtually certain to go wrong.
• nikanj 4y ago
  ”Here’s my argument for why airbags are useless: In my 15 years of driving, I haven’t needed them once” Remember the Attlassian outage from earlier this year. They sure would have appreciated a soft delete
• rvr_ 4y ago
  TFA is nonsense. Hard deletes should almost never be used, period. The application credentials should not even have permission to issue delete statements, thus reducing potential damage from bad actors. Things like ON CASCADE DELETE should not even exist. Anyone using them must stop and rethink their life decisions.
• OOPMan 4y ago
  Nice way to get on HN. Post a daft hot take.
• radu_floricica 4y ago
  Is nobody using log tables? Pretty much every time I touch something in my db, there's a log call that records who did it, when, IP, URL and a (JSON) snapshot of the changed record, which in a pinch can be used for undelete. It's surprisingly manageable. I mean, yes, it's definitely the largest table in the db, but: 1. it's well worth it 2. most of the stuff in it isn't the main scenario above (a human does something and I record the change) but various automated processes I also want to track, like API calls. which leads to: 3. it's easy to prune - both in time period kept, and by selectively deleting the automated stuff earlier But it mostly helps by localizing things. It's just one meta-data log table, and everything related to logging actions is there. Not very elegant to keep adding fluff fields to every table, like "add_date" or "deleted_at". When I decided I want to also track the URL of the request I had to change things in just one place, and now I have it for every action everywhere. Note: don't fall into the "everything is a nail" mistake. Some other dedicated log tables may be necessary, for high-volume or distinct stuff. I also have a mail_log, a sms_log and a separate table for events coming from mobile users (like location history).
• dudeinjapan 4y ago
  At my company, the soft-deleted items in our DB were a source of massive confusion for our data engineers. "How can a row be both deleted and undeleted at once, like Schroedinger's cat?" they puzzled. We renamed "deleted_at" to "archived_at". And there was much rejoicing.
• AtNightWeCode 4y ago
  ”The concept behind soft deletion is to make deletion safer, and reversible.” Well, that is one reason. To keep the actual data can be done for many reasons. Audits, reports, laws and so on. Edit: Deletion is always reversible btw since there are backups.
• mmmuhd 4y ago
  I remember when a rouge employee of a client went ahead to do stupid deletions on students' and staff data, soft delete saved the day and made us some money.
• qxxx 4y ago
  in one project I was working on, we used a similar version of the 2nd method from the article: Every table had the same table with _del suffix (eg. users_del). If a record was deleted, it was simply moved to _del table. We used code for this but later we started to use db triggers. It worked quite well, and yes, there was always someone who wanted to undelete things. One downside was, if the schema changed on the source table, we needed to also change the schema in _del table. I like the approach with storing the data as json. That way there could be only 1 deleted_stuff table because it was looking quite strange having all the _del tables.
• marginalia_nu 4y ago
  Including "deleted_at IS NULL" is surely something that you'd solve using a view, rather than explicitly entering it into the queries. GDPR is the big thing to consider, I think.
• BatteryMountain 4y ago
  The purpose of soft deleting is not to be reversible...that's just a free side effect if you really need it.
• jaitsu 4y ago
  Very similar thoughts to an article I wrote back in 2014: https://jameshalsall.co.uk/posts/why-soft-deletes-are-evil-and-what-to-do-instead https://jameshalsall.co.uk/posts/why-soft-deletes-are-evil-a... Excuse the dramatic title of the post
• runeks 4y ago
  This article touches on something I’ve always wondered: how do I determine whether to add a BOOLEAN column to a table or create a new table instead? For all tables containing a BOOLEAN column it’s always possible to simply split this table into two separate tables with the same columns, where the name of the table signals whether the factored-out BOOLEAN column would be TRUE or FALSE. My gut instinct says it’s cleaner to have two separate tables, but I’ve never found a definite answer.
• jarek83 4y ago
  I wonder how author handles relations that have to stay even when origin needs to be gone. Like in the given example with invoices - they have to stay otherwise your accounting people will visit you quite a lot. Whenever we thought we can do hard delete it almost always proved wrong.