14 ms·

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 som

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
  For me it is simply that isn't open source (at least last I checked.)
- rjbwork 4y ago
  In the past I've used MSSQL's Temporal Tables (also called System-Versioned Tables) to implement this kind of functionality. This also gets you, for free, Type 2 SCD functionality for OLAP-style queries. I can't wait until Postgres has this kind of functionality baked in. It's such a nice feature.
  - dgb23 4y ago
    You likely know that there is an extension for PG that does this.
- synthc 4y ago
  Datomic is great but i think its missing some features that many enterprises need (access control and a query planner). Also it seems to be mostly built for DynamoDB/AWS.
- nightski 4y ago
  Personally looking at their pricing since it is so tied to AWS it is completely non-transparent how much it's going to cost us now or in the future. I really like the concept of datomic though.
- Scarbutt 4y ago
  Too niche of a technology, tied to clojure, not open source and very slow(doesn't matter for most internal apps though). For many, it's better to do the tedious thing here and there with postgres. SQL also has strong grip on databases and if you look at it the other way around, postgres has lots of features that datomic lacks, with datomic you almost always need a secondary database.
- tsuujin 4y ago
  It’s hard to get adoption for expensive toys. I think Datomic is neat, and I’d like to use it, but it is prohibitively expensive for a personal or hobby project. Personal projects are where I get excited about tech, and when I’m excited I’m more likely to adopt it in my day job. They’re really shooting themselves in the foot by not having a one-click install free tier or a self hosted option.
corrral 4y ago
I'd add tagging, for anything that could conceivably use it, when you're doing DB design. May as well start with support, even if the functionality's initially dormant. Someone will ask for it, directly or indirectly, and it won't take long before they do.
- JohnBooty 4y ago
  I personally would not agree. But... your experiences are also real and I believe you when you say that your experiences DO support your conclusion. :) The "soft delete" design decision is usually pretty impactful and is in my experience potentially much more of a pain in the butt to implement later if you haven't included it from day 0. Audit trails and soft-deletes are also crazy useful for developers (both for debugging and for general cover-thine-ass utility) even if end users never touch them. Whereas, tags are easier to tack on later and are not intrinsically useful to developers.
  - deleted 4y ago
    [deleted]
  - dgb23 4y ago
    I agree. But if you’re asked to implement hierarchical categories, you implement them as tags underneath. This doesn’t cost you any time. When eventually they realize that categories don’t work (they never do) you can just turn a switch.
rjzzleep 4y ago
In rails you get these things for free. What I don't get is why everyone rolls their own framework with node.js. It's basically 90s PHP all over again. EDIT: Soft delete is a trivial piece of code when the framework has a well defined transaction system for its ORM. It's not really related to Rails per se. Your statement is extremely disingenuous, while trying to look smart. Audit trails _can_ be(but don't have to be) more complex, especially when the framework uses a lot of stored procedures to handle operations. But other than that these frameworks are specifically designed to REDUCE complexity of such operations, dependency costs - which are huge in node.js, specifically because you can mix and match anything into everything. Node.js people tend to stitch together XSS solutions, random templating solutions based on their frontend work, even basic salting of auth passwords becomes unpredictable because you have 30 options on minimal auth libraries. But yes nothing is ever free. If you want to use Rails you still have to learn ruby and the framework and a basic understand of how ActiveRecord builds queries if you want to be writing performant code. And the same applies to Laravel, Django, or whatever of the 50 patchwork node.js solutions you want to base your code on.
- sky_rw 4y ago
  Nothing is free. In Rails you have _currently well maintained libraries_ for this. There are still complexity costs, dependency costs, data costs, performance cots, etc, etc, etc.
- joshmanders 4y ago
  I don't know why you're ragging on Node.js users or even PHP for that matter as both ecosystems have this stuff covered too. Also you're comparing language/runtime with an actual framework and then dogging those users... If you want to compare Rails with Node/PHP then I'd suggest comparing with things like Laravel (PHP), Adonis (Node) and you'll find everything you can do in Rails is done in Node/PHP too.
  - nightpool 4y ago
    What percentage of production Node.js systems use Adonis? It's probably vanishingly low. Laravel is nice, and it's been gaining a lot of adoption in new applications, but the GP said "90s PHP" for a reason. Modern Node JS backends are often littered with hand rolled SQL queries, poor MVC separation, and lots of shoddy, half baked model layers—often without using any library whatsoever. Which is a real shame, because I personally really love programming in JavaScript, and the performance is great, but the strength of Rails as a framework draws me back in every time.
gmiller123456 4y ago
The author uses the "no one ever undeleted anything" as the primary justification. I think this is the part they miss. I've never undeleted a user either, but there have been many times I've gone back to look at something. Either a complaint finally gets around to me as to why the user wanted their account deleted (e.g. feature not working) and it helps to figure out why. Or they're returning and want things set up like they were. Or someone is taking over their roll and needs to be set up like the last person who's already gone. Though you really shouldn't be relying on a database for an audit trail. It might help find some issues, but things actually used for security shouldn't be writable so easily.
- alerighi 4y ago
  It may be convenient, but under the GDPR is illegal. When an user deletes an account, all the personal data associated with that user must be deleted (or anonymize it in a way that it's no longer possible to associate it back to the particular user). You cannot just keep user information forever "just in case" they are useful again.
  - changoplatanero 4y ago
    User deleting an account is just one way that stuff gets deleted. There are other deletion scenarios where it is appropriate to keep the information after its deleted.
    - Akronymus 4y ago
      Yeah. Basically anything that an employee can mess up, should be reversible IMO. But actions such as deleting an account should have the option of "YES, DELETE IT PERMANENTLY, THIS CAN'T BE UNDONE"
      - closeparen 4y ago
        Often it’s mark for deletion after some number of days.
      - 4y ago
wnevets 4y ago
This is something that I was forced to learn the hard way more than once. Literally today I needed to undelete a record because a customer was confused by what the "delete" button did and wanted their record back.
- wst_ 4y ago
  Isn't it the problem of UI, though. If the user would be informed about the consequences (possibly with bold red font and with a confirmation checkbox) would they still click that button?
  - robertlagrant 4y ago
    One will, one day.
  - wnevets 4y ago
    All of that exist.
  - doctor_eval 4y ago
    File this under "falsehoods programmers believe about users": they act rationally.
    - Aeolun 4y ago
      Delete “I’m sorry sir, I didn’t think that clicking that button would actually delete something!” “What exactly did you think that button would do?”
      - doctor_eval 4y ago
        "falsehoods programmers believe about users #2": they read button labels and dialog box text.
  - thrashh 4y ago
    As a user, I would still sometimes and then regret my decision Soft deletions are awesome
pradn 4y ago
Soft deletion is just one way to achieve undeletion. The author's proposed solution of moving the resource to another table works just as well. You can move it back to the non-deleted table to perform the undeletion. You can keep around these deleted objects as long as you want; they work as a subset of a proper audit trail. The cost of course is you have more tables, but that is less of a cost than having to add "deleted=False" predicates in all of your queries. Also note, if you use a soft-deleted column, indexes need to be keyed by that column as well if you want to access non-deleted objects quickly. That's extra complexity.
- mjevans 4y ago
  Even more important; the deleted records don't need to live in your cache / RAM / etc. Potentially faster queries.
- smackeyacky 4y ago
  What seems to be missing here is the DB tech he is using. On a proper database you can do your "undeleted" with triggers and it's relatively trivial. Nonsense like a "deleted" column on your main data table just seems silly.
  - icedchai 4y ago
    I worked at a place that kept all the deleted stuff in their main tables. It turned out over 90% of the rows were deleted. I'm not sure how often something was undeleted, but it was not very frequent. Some of these soft deleted rows were 5+ years old. Archive that crap.
    - JohnBooty 4y ago
      Yeah, for sure, if deletion is going to be the exception rather than the rule.... keeping deleted stuff in the main tables is not ideal. Of course, you can have your cake and eat it too. You can e.g. keep your deleted crap in the main table (which makes certain things easier) and then hard-delete or archive it to another table after N days.
    - 4y ago
augustiine 4y ago
deleted 4y ago
[deleted]
efsavage 4y ago
Also if people know that deletion is reversible, they're more likely to actually do it, which can keep things generally tidier. I don't actually like using a "deleted" column, my standard table has a status column, and deleted is one of those states, along with active/pending/suspended/etc, as the needs dictate. This way I get soft deletes for basically free both in the schema, but also in the queries (which would generally default to active), so it's not really the spaghetti that the author discusses.
- robertlagrant 4y ago
  That still has the same issues. You have to remember to set every linked table's records to the same state, or remember to query every linked table through the table that has the lifecycle column on it.
  - jwarden 4y ago
    Forgetting to include the lifecycle column in your query may mean not understanding what question you are trying to answer with your query.
  - dubswithus 4y ago
    There are some Rails gems that take care of this by scoping associations (that also have the deleted_at column).
- JohnBooty 4y ago
  For some applications this is fine, depending on your app/business logic but for a lot of applications states like active/pending/suspended and "deleted" are not mutually exclusive. Suppose I soft-delete an active, pending, or suspended user using your scheme. Now I need to un-delete the user. What status should they have? We don't know. This is another "best practice" I've learned over the years. Those flags/statuses you think are mutually exclusive? Maybe they aren't. Or maybe they are now but won't always be. It's usually easier in the long run to give each status its own column even if you think they'll always be mutually exclusive. Because I mean, what are you really saving? A few bytes per record? In most cases that's not worth it.
jesseryoung 4y ago
I was getting ready to disagree with you - but then I tried to think of any time I've actually pushed code to production with the "DELETE" keyword in it. The problems that I've had to solve in my career very rarely call for deleting something. "Soft deletion" and "audit trail" are technical terms we developers come up for solutions the business wants but maybe hasn't asked for yet. It's not really a soft deletion it's a "deactivate" or "hide". Likewise, it's not an audit trail it's a "history" or "undo". Most of the time your stakeholders and users actually want these features, but don't ask because they perceive this as more expensive to build then just a "delete" button.
- pwm 4y ago
  > Likewise, it's not an audit trail it's a "history" or "undo". Depends on the industry. The one I work in audit trail is a well-defined and mandatory business concern.
makeitdouble 4y ago
> unless there's some reason not to. Yes. To note, with GDPR there's now legal reasons to do so regarding user personal data. That can be the moment the devs realize they actually can't delete the data, because they soft deleted for so long, many relations are now interlocked and the data model needs to be changed to give a starting point to the deletion cascade. My lesson from that was to at least have one test deleting a mock user that spans the maximum data breadth of the service . We caught a bunch of these loops in test at dev time and that was pretty great.
- JohnBooty 4y ago
  My lesson from that was to at least have one test deleting a mock user that spans the maximum data breadth of the service . We caught a bunch of these loops in test at dev time and that was pretty great. Thank you. That is SUPER insightful. If it wasn't too late to edit my initial post I would add this.
- Falkon1313 4y ago
  Good point. That could be tricky when it crosses the boundaries of schema changes and data migrations.
femiagbabiaka 4y ago
Having multiple use cases for data is normal and okay. Treating your primary data store that is designed for one set of use cases and using it for them all is very very bad, even before you hit scale. Know which data store to use and when for a use case is a super power than can allow you to scale to much higher magnitudes than one would intuit.
cpursley 4y ago
Can you recommend any best practices, design patterns, etc - for implementing soft delete in SQL systems?
- JohnBooty 4y ago
  I think the best answer is to optimize for the expected data and use cases. - How many records are there? Enough that performance will be an issue? If I'm planning on keeping things in the same table, can I utilize database features like indexes and partitions to mitigate any perf issues? (For some access patterns, partitions might solve 100% of your perf concerns) - How common is deletion? Are we expecting 1% of the records to be soft-deleted, or more like 90%? If it's the latter, you may not want all those records clogging up your main table. - Is this greenfield development, or am I adding soft-delete to an existing app? Greenfield favors "same table" soft delete; if you're retrofitting an existing app it may be better to keep deleted stuff in a separate table so that you don't break existing functionality. - What do you want to do with the soft-deleted records? Are there times when you want to treat them just like regular records, e.g. "Give me a list of all the users who joined last week, even if we've deleted their accounts?" If the answer is "yes" then a lot of those things will probably be easier if you keep deleted and non-deleted in a single table.
- tomschlick 4y ago
  The biggest one is to use a timestamp instead of a boolean. For instance Laravel used `deleted_at` as the field name with the type of TIMESTAMP (for mysql at least). This allows you to do a `select * from users where deleted_at is null` to get the active records, but also know when the user was deleted if you need to audit / rollback.
aboodman 4y ago
> Somebody always wants to undelete something > or examine it to see why it was deleted > or see who changed something, or blah blah blah People for whom this resonates should look into Dolt: https://dolthub.com/ https://dolthub.com/. It's a mysql-compat database that is versioned and forkable - basically imagine git and mysql had a baby. Every transaction creates an entry in the commit log that lets you see when, how, and why the database changed. And just like git you can `dolt checkout <hash>` to see what the data was like at some point in time, or `dolt checkout -b temp-branch <hash>` to make exploratory changes to some historical version, or `dolt revert` to revert a transaction... etc. There is a lot more power that comes with making the entire database versioned and forkable by default. For example it makes it much easier to recover from catastrophic bad code pushes, etc. note: Dolt was forked from Noms, my previous project, but I don't work for Dolt or have a stake. Just a fan.
brightball 4y ago
Agreed. The key to soft deletes is to actually move the record out of the original table to ensure they don’t accidentally end up in a query join. But there’s always something that needs to be undeleted. You can either have an easy way to do it or restore an entire DB backup and cross query the missing records. Soft deletes are a lot easier.
4m1rk 4y ago
It's audit log enough for that? As deletion should also be logged.
xxs 4y ago
GDPR compliance and all - we encrypt and delete stuff... or at the very least "lose" encryption keys 1st.
dcow 4y ago
What's wrong with the author's proposed compromise/solution?
ReptileMan 4y ago
The proper thing is database snapshots or something like that. I don't think soft delete is wrong per se, but it is something that should be native to the database engine.
cal85 4y ago
> Somebody always wants to undelete something, or examine it to see why it was deleted, or see who changed something, or blah blah blah. In my experience this happens “rarely”, not “always”. It can happen, and in some ultra-rare cases the impact of not being able to recover some data might be huge (company-ending, even), and engineers are good at worrying about such edge cases. That’s why we habe protective measures like soft deleting and event sourcing - because of nightmare edge cases, not because we are always having to actually use them. It’s driven by engineers avoiding their worst nightmare: having to say “I’m sorry, I cannot solve this problem for you. The data is gone.” It’s a peace-of-mind thing, not an everyday-need thing.
- nivertech 4y ago
  > That’s why we have protective measures like soft deleting and event sourcing IMO soft deletion is a hack trying to fix problems in CRUD, which is a hack in itself. CRUD attempts to model everything in the universe as a collections of mutable items, loosely based on RDBMS/SQL. Event Sourcing is more realistic: it models everything as a append-only logs of immutable events/facts, which preserves both the historical data and, more importantly, the original intent. Unlike CRUD, Event Sourcing is technology agnostic.

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
For me it is simply that isn't open source (at least last I checked.)
rjbwork 4y ago
In the past I've used MSSQL's Temporal Tables (also called System-Versioned Tables) to implement this kind of functionality. This also gets you, for free, Type 2 SCD functionality for OLAP-style queries. I can't wait until Postgres has this kind of functionality baked in. It's such a nice feature.
- dgb23 4y ago
  You likely know that there is an extension for PG that does this.
synthc 4y ago
Datomic is great but i think its missing some features that many enterprises need (access control and a query planner). Also it seems to be mostly built for DynamoDB/AWS.
nightski 4y ago
Personally looking at their pricing since it is so tied to AWS it is completely non-transparent how much it's going to cost us now or in the future. I really like the concept of datomic though.
Scarbutt 4y ago
Too niche of a technology, tied to clojure, not open source and very slow(doesn't matter for most internal apps though). For many, it's better to do the tedious thing here and there with postgres. SQL also has strong grip on databases and if you look at it the other way around, postgres has lots of features that datomic lacks, with datomic you almost always need a secondary database.
tsuujin 4y ago
It’s hard to get adoption for expensive toys. I think Datomic is neat, and I’d like to use it, but it is prohibitively expensive for a personal or hobby project. Personal projects are where I get excited about tech, and when I’m excited I’m more likely to adopt it in my day job. They’re really shooting themselves in the foot by not having a one-click install free tier or a self hosted option.

corrral 4y ago

I'd add tagging, for anything that could conceivably use it, when you're doing DB design. May as well start with support, even if the functionality's initially dormant. Someone will ask for it, directly or indirectly, and it won't take long before they do.

JohnBooty 4y ago
I personally would not agree. But... your experiences are also real and I believe you when you say that your experiences DO support your conclusion. :) The "soft delete" design decision is usually pretty impactful and is in my experience potentially much more of a pain in the butt to implement later if you haven't included it from day 0. Audit trails and soft-deletes are also crazy useful for developers (both for debugging and for general cover-thine-ass utility) even if end users never touch them. Whereas, tags are easier to tack on later and are not intrinsically useful to developers.
- deleted 4y ago
  [deleted]
- dgb23 4y ago
  I agree. But if you’re asked to implement hierarchical categories, you implement them as tags underneath. This doesn’t cost you any time. When eventually they realize that categories don’t work (they never do) you can just turn a switch.

rjzzleep 4y ago

In rails you get these things for free. What I don't get is why everyone rolls their own framework with node.js. It's basically 90s PHP all over again. EDIT: Soft delete is a trivial piece of code when the framework has a well defined transaction system for its ORM. It's not really related to Rails per se. Your statement is extremely disingenuous, while trying to look smart. Audit trails _can_ be(but don't have to be) more complex, especially when the framework uses a lot of stored procedures to handle operations. But other than that these frameworks are specifically designed to REDUCE complexity of such operations, dependency costs - which are huge in node.js, specifically because you can mix and match anything into everything. Node.js people tend to stitch together XSS solutions, random templating solutions based on their frontend work, even basic salting of auth passwords becomes unpredictable because you have 30 options on minimal auth libraries. But yes nothing is ever free. If you want to use Rails you still have to learn ruby and the framework and a basic understand of how ActiveRecord builds queries if you want to be writing performant code. And the same applies to Laravel, Django, or whatever of the 50 patchwork node.js solutions you want to base your code on.

sky_rw 4y ago
Nothing is free. In Rails you have _currently well maintained libraries_ for this. There are still complexity costs, dependency costs, data costs, performance cots, etc, etc, etc.
joshmanders 4y ago
I don't know why you're ragging on Node.js users or even PHP for that matter as both ecosystems have this stuff covered too. Also you're comparing language/runtime with an actual framework and then dogging those users... If you want to compare Rails with Node/PHP then I'd suggest comparing with things like Laravel (PHP), Adonis (Node) and you'll find everything you can do in Rails is done in Node/PHP too.
- nightpool 4y ago
  What percentage of production Node.js systems use Adonis? It's probably vanishingly low. Laravel is nice, and it's been gaining a lot of adoption in new applications, but the GP said "90s PHP" for a reason. Modern Node JS backends are often littered with hand rolled SQL queries, poor MVC separation, and lots of shoddy, half baked model layers—often without using any library whatsoever. Which is a real shame, because I personally really love programming in JavaScript, and the performance is great, but the strength of Rails as a framework draws me back in every time.

gmiller123456 4y ago

The author uses the "no one ever undeleted anything" as the primary justification. I think this is the part they miss. I've never undeleted a user either, but there have been many times I've gone back to look at something. Either a complaint finally gets around to me as to why the user wanted their account deleted (e.g. feature not working) and it helps to figure out why. Or they're returning and want things set up like they were. Or someone is taking over their roll and needs to be set up like the last person who's already gone. Though you really shouldn't be relying on a database for an audit trail. It might help find some issues, but things actually used for security shouldn't be writable so easily.

alerighi 4y ago
It may be convenient, but under the GDPR is illegal. When an user deletes an account, all the personal data associated with that user must be deleted (or anonymize it in a way that it's no longer possible to associate it back to the particular user). You cannot just keep user information forever "just in case" they are useful again.
- changoplatanero 4y ago
  User deleting an account is just one way that stuff gets deleted. There are other deletion scenarios where it is appropriate to keep the information after its deleted.
  - Akronymus 4y ago
    Yeah. Basically anything that an employee can mess up, should be reversible IMO. But actions such as deleting an account should have the option of "YES, DELETE IT PERMANENTLY, THIS CAN'T BE UNDONE"
    - closeparen 4y ago
      Often it’s mark for deletion after some number of days.
    - 4y ago

wnevets 4y ago

This is something that I was forced to learn the hard way more than once. Literally today I needed to undelete a record because a customer was confused by what the "delete" button did and wanted their record back.

wst_ 4y ago
Isn't it the problem of UI, though. If the user would be informed about the consequences (possibly with bold red font and with a confirmation checkbox) would they still click that button?
- robertlagrant 4y ago
  One will, one day.
- wnevets 4y ago
  All of that exist.
- doctor_eval 4y ago
  File this under "falsehoods programmers believe about users": they act rationally.
  - Aeolun 4y ago
    Delete “I’m sorry sir, I didn’t think that clicking that button would actually delete something!” “What exactly did you think that button would do?”
    - doctor_eval 4y ago
      "falsehoods programmers believe about users #2": they read button labels and dialog box text.
- thrashh 4y ago
  As a user, I would still sometimes and then regret my decision Soft deletions are awesome

pradn 4y ago

Soft deletion is just one way to achieve undeletion. The author's proposed solution of moving the resource to another table works just as well. You can move it back to the non-deleted table to perform the undeletion. You can keep around these deleted objects as long as you want; they work as a subset of a proper audit trail. The cost of course is you have more tables, but that is less of a cost than having to add "deleted=False" predicates in all of your queries. Also note, if you use a soft-deleted column, indexes need to be keyed by that column as well if you want to access non-deleted objects quickly. That's extra complexity.

mjevans 4y ago
Even more important; the deleted records don't need to live in your cache / RAM / etc. Potentially faster queries.
smackeyacky 4y ago
What seems to be missing here is the DB tech he is using. On a proper database you can do your "undeleted" with triggers and it's relatively trivial. Nonsense like a "deleted" column on your main data table just seems silly.
- icedchai 4y ago
  I worked at a place that kept all the deleted stuff in their main tables. It turned out over 90% of the rows were deleted. I'm not sure how often something was undeleted, but it was not very frequent. Some of these soft deleted rows were 5+ years old. Archive that crap.
  - JohnBooty 4y ago
    Yeah, for sure, if deletion is going to be the exception rather than the rule.... keeping deleted stuff in the main tables is not ideal. Of course, you can have your cake and eat it too. You can e.g. keep your deleted crap in the main table (which makes certain things easier) and then hard-delete or archive it to another table after N days.
  - 4y ago

augustiine 4y ago

deleted 4y ago

[deleted]

efsavage 4y ago

Also if people know that deletion is reversible, they're more likely to actually do it, which can keep things generally tidier. I don't actually like using a "deleted" column, my standard table has a status column, and deleted is one of those states, along with active/pending/suspended/etc, as the needs dictate. This way I get soft deletes for basically free both in the schema, but also in the queries (which would generally default to active), so it's not really the spaghetti that the author discusses.

robertlagrant 4y ago
That still has the same issues. You have to remember to set every linked table's records to the same state, or remember to query every linked table through the table that has the lifecycle column on it.
- jwarden 4y ago
  Forgetting to include the lifecycle column in your query may mean not understanding what question you are trying to answer with your query.
- dubswithus 4y ago
  There are some Rails gems that take care of this by scoping associations (that also have the deleted_at column).
JohnBooty 4y ago
For some applications this is fine, depending on your app/business logic but for a lot of applications states like active/pending/suspended and "deleted" are not mutually exclusive. Suppose I soft-delete an active, pending, or suspended user using your scheme. Now I need to un-delete the user. What status should they have? We don't know. This is another "best practice" I've learned over the years. Those flags/statuses you think are mutually exclusive? Maybe they aren't. Or maybe they are now but won't always be. It's usually easier in the long run to give each status its own column even if you think they'll always be mutually exclusive. Because I mean, what are you really saving? A few bytes per record? In most cases that's not worth it.

jesseryoung 4y ago

I was getting ready to disagree with you - but then I tried to think of any time I've actually pushed code to production with the "DELETE" keyword in it. The problems that I've had to solve in my career very rarely call for deleting something. "Soft deletion" and "audit trail" are technical terms we developers come up for solutions the business wants but maybe hasn't asked for yet. It's not really a soft deletion it's a "deactivate" or "hide". Likewise, it's not an audit trail it's a "history" or "undo". Most of the time your stakeholders and users actually want these features, but don't ask because they perceive this as more expensive to build then just a "delete" button.

pwm 4y ago
> Likewise, it's not an audit trail it's a "history" or "undo". Depends on the industry. The one I work in audit trail is a well-defined and mandatory business concern.

makeitdouble 4y ago

> unless there's some reason not to. Yes. To note, with GDPR there's now legal reasons to do so regarding user personal data. That can be the moment the devs realize they actually can't delete the data, because they soft deleted for so long, many relations are now interlocked and the data model needs to be changed to give a starting point to the deletion cascade. My lesson from that was to at least have one test deleting a mock user that spans the maximum data breadth of the service . We caught a bunch of these loops in test at dev time and that was pretty great.

JohnBooty 4y ago
My lesson from that was to at least have one test deleting a mock user that spans the maximum data breadth of the service . We caught a bunch of these loops in test at dev time and that was pretty great. Thank you. That is SUPER insightful. If it wasn't too late to edit my initial post I would add this.
Falkon1313 4y ago
Good point. That could be tricky when it crosses the boundaries of schema changes and data migrations.

femiagbabiaka 4y ago

Having multiple use cases for data is normal and okay. Treating your primary data store that is designed for one set of use cases and using it for them all is very very bad, even before you hit scale. Know which data store to use and when for a use case is a super power than can allow you to scale to much higher magnitudes than one would intuit.

cpursley 4y ago

Can you recommend any best practices, design patterns, etc - for implementing soft delete in SQL systems?

JohnBooty 4y ago
I think the best answer is to optimize for the expected data and use cases. - How many records are there? Enough that performance will be an issue? If I'm planning on keeping things in the same table, can I utilize database features like indexes and partitions to mitigate any perf issues? (For some access patterns, partitions might solve 100% of your perf concerns) - How common is deletion? Are we expecting 1% of the records to be soft-deleted, or more like 90%? If it's the latter, you may not want all those records clogging up your main table. - Is this greenfield development, or am I adding soft-delete to an existing app? Greenfield favors "same table" soft delete; if you're retrofitting an existing app it may be better to keep deleted stuff in a separate table so that you don't break existing functionality. - What do you want to do with the soft-deleted records? Are there times when you want to treat them just like regular records, e.g. "Give me a list of all the users who joined last week, even if we've deleted their accounts?" If the answer is "yes" then a lot of those things will probably be easier if you keep deleted and non-deleted in a single table.
tomschlick 4y ago
The biggest one is to use a timestamp instead of a boolean. For instance Laravel used `deleted_at` as the field name with the type of TIMESTAMP (for mysql at least). This allows you to do a `select * from users where deleted_at is null` to get the active records, but also know when the user was deleted if you need to audit / rollback.

aboodman 4y ago

> Somebody always wants to undelete something > or examine it to see why it was deleted > or see who changed something, or blah blah blah People for whom this resonates should look into Dolt: https://dolthub.com/ https://dolthub.com/. It's a mysql-compat database that is versioned and forkable - basically imagine git and mysql had a baby. Every transaction creates an entry in the commit log that lets you see when, how, and why the database changed. And just like git you can `dolt checkout <hash>` to see what the data was like at some point in time, or `dolt checkout -b temp-branch <hash>` to make exploratory changes to some historical version, or `dolt revert` to revert a transaction... etc. There is a lot more power that comes with making the entire database versioned and forkable by default. For example it makes it much easier to recover from catastrophic bad code pushes, etc. note: Dolt was forked from Noms, my previous project, but I don't work for Dolt or have a stake. Just a fan.

brightball 4y ago

Agreed. The key to soft deletes is to actually move the record out of the original table to ensure they don’t accidentally end up in a query join. But there’s always something that needs to be undeleted. You can either have an easy way to do it or restore an entire DB backup and cross query the missing records. Soft deletes are a lot easier.

4m1rk 4y ago

It's audit log enough for that? As deletion should also be logged.

xxs 4y ago

GDPR compliance and all - we encrypt and delete stuff... or at the very least "lose" encryption keys 1st.

dcow 4y ago

What's wrong with the author's proposed compromise/solution?

ReptileMan 4y ago

The proper thing is database snapshots or something like that. I don't think soft delete is wrong per se, but it is something that should be native to the database engine.

cal85 4y ago

> Somebody always wants to undelete something, or examine it to see why it was deleted, or see who changed something, or blah blah blah. In my experience this happens “rarely”, not “always”. It can happen, and in some ultra-rare cases the impact of not being able to recover some data might be huge (company-ending, even), and engineers are good at worrying about such edge cases. That’s why we habe protective measures like soft deleting and event sourcing - because of nightmare edge cases, not because we are always having to actually use them. It’s driven by engineers avoiding their worst nightmare: having to say “I’m sorry, I cannot solve this problem for you. The data is gone.” It’s a peace-of-mind thing, not an everyday-need thing.

nivertech 4y ago
> That’s why we have protective measures like soft deleting and event sourcing IMO soft deletion is a hack trying to fix problems in CRUD, which is a hack in itself. CRUD attempts to model everything in the universe as a collections of mutable items, loosely based on RDBMS/SQL. Event Sourcing is more realistic: it models everything as a append-only logs of immutable events/facts, which preserves both the historical data and, more importantly, the original intent. Unlike CRUD, Event Sourcing is technology agnostic.