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.
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.
They might like to, but you should definitely consider if saving data no longer required for your business violates privacy regulation/ethical considerations.
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.
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.
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.
"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.
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.
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.
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.
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.
Assuming you're using a modern database, replication is done with paxos/raft and they are formally proven to not allow this to happen as both edits/deletions are both just entries in distributed event log.
An audit table is a similar, but not entirely equivalent tool. There are circumstance where both audit tables / soft deletes are appropriate, and where only one of the two is. Other systems that work are append-only tables, event driven systems, and I'm sure there are more that I'm not aware of.
In banking and bookkeeping, there’s no such thing as a “delete”. Once something is in the ledger you can’t undo it - you have to make a new entry that negates the old one.
Yes, but banks tend to have websites with accounts and those accounts need to be deactivated when a customer should no longer have access (or, even more finicky, specific accounts for a client need to be deactivated or activated as they change their usage).
All this essentially forces the use of some sort of soft deletion. (Activation flags are sort of just a more complicated form of soft deletion).
Status (active/inactive/other) and existence (present or deleted) are very different things, they may be separated most of the time. Legal requirements can prevent deletion of inactive data, so in some cases one may have a lot of inactive but must keep data. Soft delete can help in some scenarios, for example you want to give people a way to re-activate accounts, but hide the ones marked as deleted.
Well you see, you need a complete record of when it was created, every change that occurred, everyone who could view it and also log every access attempt. But it. You're not actually supposed to keep it. Just everything surrounding it.
Not quite. GDPR (and equivalents) have clear escape hatches to allow you to store data if you have good reason (even if the data subject requests its removal).
Invoices, from the article, is a great example. That record must remain unchanged in most financial regulations. I’d wager a customer sending a deletion request for invoices will be met with raucous laughter from the legal and finance teams.
In some industries the retention regulations trump the deletion ones. I believe finance is one area where they will delete some of your data, but are still required to maintain 7 years of specifically listed data.
Then there's always the joy of a situation where your client is being sued by one of their clients and now needs your help recovering everything possible from your platform. And you're going to help them because you'd like to keep them as a client rather than let them be sued into oblivion.
My experience at a few start-ups has been that account deletion just isn't prioritized. It's not a focus when building an MVP. If the application ever gains traction, everyone is then terrified they'll accidentally delete customer data that they never delete anything. It's a shame. As a user, when I delete my account or data in my account, I want you to permanently delete it, not keep it around and just make inaccessible to me.
If it makes you feel better, the startups that don't have time to delete your data probably don't have a viable disaster recovery plan either.
As we learned from the Atlassian snafu, even giant companies with billions in revenue often can't recover from disasters. (I try to test mine every 6 months.
I've never had a test go perfectly.)
I've also seen businesses retain "deleted" data in order to support legitimate data analysis work in the future. And it actually can help significantly. Maybe scrubbing PII from deleted accounts is a good idea, but those deleted accounts are perfectly good data points, especially in smaller/newer companies with lower-volume data streams.
If you want to retain anonymous statistics, fine. I'm not keen on you retaining my actual data so you can monetize it after our business relationship has concluded. The biggest thing a small team can learn is why they failed to retain a customer or a trial that didn't convert. For that, usage metrics are considerably more valuable than user data.
And yet another part is making deletes (appear) instantaneous: useful when it involves cleaning up a bunch of "related" data possibly living on different services (eg. S3, ES...).
This also helps with the original goal of making them safer by manually implementing "eventual consistency" for data living outside the transactional world.
Don't make deletes appear instantaneous? If you have heavy weight systems, then it makes sense for provisioning and deleting entities is a process, that should be open to monitoring.
Exposing details of long lived background processes, and especially deletion processes to users (who in many cases couldn't care less) is a lot of work that's probably not worth it for the rare "hey, something went wrong" case — it's usually perfectly acceptable to raise that with the support team and let them use internal tools to debug and investigate.
I am sure there are cases where it is worthwhile, but most of the times I've hit this in common web apps, it wasn't.
I disagree. Any system that doesn't transition an entity to "deleting" is almost certainly going to be a pain point for me when I go to delete something.
This can be seen as related to soft deletes. But I consider it more marking intent in the system. Lets you make "delete" a simple field update that will be carried out by the backend.
Say you are attempting to delete a tweet you made: you click on the delete button, and you want to see that your tweet is in the process of being deleted, but not really deleted yet (as the platform goes and updates all the retweet references and such)?
Really? What value do you as a user get from knowing the details of this transitory state? Do you also want to see a progress bar of how many references have been updated, what's the progress of evicting the tweet from search indexes, etc? What's wrong with all this appearing instantaneous and actual delete happening in the background over 5 minutes or 5 hours?
It seems you are in favour of this approach, just don't want to call it "soft delete" (since it may be followed by a hard out-of-band delete).
I'd rather see acknowledgement that it is having to do more work, than be surprised when it still shows up on another timeline for a bit longer.
I don't necessarily care on a progress bar, as those typically have their own woes.
And don't get me wrong, there are easy deletion cases that I am ok with appearing to happen immediately. I just find hiding processes from me is usually more annoying than it is worth. Worse, when the implementation didn't do it as a process, and now they typically just have more edge cases that won't delete cleanly.
What you missed from my original post is that it is a lot of effort to expose transitory state: nobody is going the extra mile to annoy you, they are just not investing in implementing extra UI to expose it.
This is almost certainly going to bite you if you don't push all customer identification data out of your main data stores.
And it will go a long way to making your services harder to use if you don't allow users to associate friendly names with things. And to assume that the same friendly name will be used for a future item. (For example, if you name devices based on the room you put them in. Is reasonable to think that when you replace a device, that you are likely to want to reuse the name.)
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.
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.
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.
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.
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.
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).
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.
Based on my experience, I like the author's approach since it makes things pretty clear-cut and optimized the storage in the core table (in my experience as well, deletes happen frequently and the soft deletes are rarely touched). In large, row-oriented tables that that storage can add up and even with views/materialized views there's a cost to using/maintaining those as well.
Views are such a powerful concept I’m honestly disheartened by how hard it is to use, replicate or leverage that functionality outside of dropping straight into the db shell
This is one gripe I have with soft-deletion. Since I can no longer rely on ON DELETE CASCADE relationships, I need to re-defined these relationship between objects at the application layer. This gets more and more difficult as relationships between objects increase.
If the goal is to keep a history of all records for compliance reasons or "just in case", I tend to prefer a CDC stream into a separate historical system of record.
You may end up doing this anyways if you have any application code that needs access to delete hooks, or access control varies across objects. At this point, you are probably using a ORM instead of direct queries, and place logic that could be in the db instead at the app layer.
It's not a standard (I think) but it'd let you do a cascading delete and then be able to go and look at the old objects as they were at time of deletion too.
You'd need to do things very differently to show a list of deleted objects though.
How closely implementations follow the standard I don't know, but something close exists in several DBMSs: I use them regualrly in MS SQL Server, there are plug-ins for postgres, MariaDB has them, and so forth.
You lose traditional FK constraints with temporal tables since there's multiple copies of a row. One workaround is to partition current rows separately from historical rows and only enforce FK constraints on the current partition.
If you're using PostgreSQL, you can implement cascading soft-deletes yourself.
The information schema table holds all foreign key relationships, so one can write a generic procedure that cascades through the fkey graph to soft-delete rows in any related tables.
However in practice this usually dramatically slows down reads if you have to constantly skip over the historic rows so you probably don't want to keep garbage round longer than absolutely necessary. The concept of a historic table mentioned below could be interesting though - especially if it could be offloaded to cold storage.
If we're assuming you're using a view based approach which elides the soft deleted rows automatically then you'll get a lot of these dependent objects correctly updated for free assuming you're pulling them out of the DB with JOINs - SELECT FROM foo JOIN bar (assuming bar is a view into barwithdeleted) will automatically filter out the invalid rows from foo... if you're using this information to populate a CRUD interface it's likely you'll be JOINing bar already to get some metadata for display (like maybe bar.name instead of the surrogate bar.id key you use for joining).
Yes, but other queries (any aggregate queries that don't join the soft deleted table, any joins to other tables) will now return rows that would have been deleted under hard deletion with cascade.
This is definitely something to watch out for, but in practice (as someone that migrated a system without soft deletes to one that had them) I found that it doesn't tend to come up nearly as much as you might think - usually the table being transitioned to support soft deletes is a relatively core table (since ancillary tables usually aren't worth the complexity to transition) so a lot of your reporting queries will already be pulling in that table. You definitely need to check to make sure you're not missing anything - and sometimes CRUD interfaces will need to be completely revamped to include the table in question - but it's usually not that hard.
You could use a trigger to cascade soft-delete flag toggles, provided all the relevant tables have such a column. Still have to factor that into your other queries, but at least you wouldn't have to make potentially-impossible-to-exhaustively-check joins to figure out if a given row was "deleted".
Being unable to effectively use foreign key relationships is definitely a downside of using soft deletes. But it's also worth asking if these types of behaviors, which would also include a feature like triggers, really belongs in a database or whether it's better to have at the application level (or at least at a layer above the data layer). I'd argue that ultimately you probably don't want these things at the DB level because you get into a situation where you're sharing business logic between two (or more places).
I'm less likely to use triggers, but I'll say I pretty much always want proper foreign key relationships set up in the database. Unique and other constraints too. In principal I might agree with you that it's an application level concern, but being able to setup these kind of invariants and trust that they will be enforced even in the face of bugs in my code (and there will be bugs in my code) is just too powerful to let go of in the name of purity. I'd much rather let a bit of business logic creep between multiple layers that discover that I have a whole bunch of orphaned records and no sensible way to reconcile them.
My perspective is DB level triggers are the absolute very best place to put cascading update/delete logic so it only ever needs to be written once and is consistent regardless of any future frontend clients that might be written in a different language and/or framework than the original codebase.
Right now in $dayjob I am converting an old non-DRY codebase from NoSQL data layer format to proper relational SQL backend.
This old front-end was created by verbosely coding up a relational cascading update/delete system for the NoSQL backend, in numerous places redundantly with subtle differences and inconsistencies, making the code brittle.
My current estimate is some front end functions will be reduced in LOC size by 95% once we use the power of SQL in the backend.
And the backend SQL Triggers+StoredProcedure required to replace these long NoSQL frontend functions doing cascading updates/deletes is only around 10% the size of the replaced front-end code.
And now future new frontends can reuse this without exploding the size of their codebase where complex data operations are required. And no need to reinvent the same data handling algorithm all over again (and risk subtle variation creeping in from the different front-end implementation of data algorithms)
The DB's responsible for maintaining the integrity of data in it, unless there's some very good reason you can't let it do that. It's faster and better at it than your application, 99% of the time, and it can keep doing that even when/if a second application starts using the same database, or when being used from a SQL prompt, or whatever.
Presumably you have a schema defining the tables, the columns, and the types at the least, along with things like unique indexes. So you already have data constraints in your database design. And that's where they belong, to ensure the data integrity, since the database's concern is the data.
If you're doing everything as one big table of entity-attribute-value with generic blobs for the values, then yes you'll have to re-implement all the normal constraints (that the database would handle) in your application and do all your data integrity handling there. And you'll also have to duplicate that logic across every application that accesses that database now and in the future.
Data usually lives longer and has more uses than just one program. So I think it's generally better to put integrity constraints in the database, rather than having to re-implement and duplicate that logic several places.
Often you don't have to rely on ON DELETE CASCADE relationships. Because you are never deleting anything, you will never have any orphaned records. If you don't want to see say Invoices for a deleted Customer then that's just another filter feature.
Mostly I use soft-delete because for auditing requirements we pretty much can't remove anything but also because nothing ever truly goes away. If we have an Invoice or Order then, from our perspective, we must have those forever even if the corresponding client is deleted and can never place another one.
> Since I can no longer rely on ON DELETE CASCADE relationships
Cascaded deletes scare me anyway. It only takes one idiot to implement UPSERT as DELETE+INSERT because it seems easier, and child data is lost. You could always use triggers to cascade you soft-delete flags as an alternative method, though that would be less efficient (and more likely to be buggy) than the built-in solution that cascaded deletes are.
If you look at how system-versioned (or “temporal”) tables are implemented in some DBMSs, that is a good compromise. The history table is your audit, containing all old versions of rows even deleted ones, and the base table can be really deleted from, so you don't need views or other abstractions away from the base data to avoid accidentally resurrecting data. You can also apply different storage options to the archive data (compression/not, different indexes, ... depending on expected use cases) without more manaully setting up partitioning based on the deleted/not flag. It can make some query times less efficient (you need to union two tables to get the latest version of things including deleted ones, etc.) but they make other things easier (especially with the syntactic sugar like AS AT SYSTEM_TIME <when> and so forth) and yet more things are rendered possible (if inefficient) where they were not before.
> I tend to prefer a CDC stream into a separate historical system of record.
This is similar, though with system versioned tables you are pretty much always keeping the history/audit in the same DB.
---
FWIW: we create systems for highly regulated finance companies where really deleting things is often verboten, until it isn't and then you have the other extreme and need to absolutely purge information, so these things are often on my mind.
> It only takes one idiot to implement UPSERT as DELETE+INSERT because it seems easier, and child data is lost.
Seems unfortunate to miss out on all the referential integrity benefits of a serious database when hiring standards, training and code reviews should all be preventing idiotic changes.
If I’m making a shopping cart system, I want to know every order line belongs to an order, every order belongs to a user and so on. Anyone who can’t be trusted to write an update statement certainly can’t be trusted to avoid creating a bunch of orphan records IMHO.
> Seems unfortunate to miss out on all the referential integrity benefits of a serious database when hiring standards, training and code reviews should all be preventing idiotic changes.
If you don't use ON DELETE CASCADE, the actual foreign key constraint gives you a meaningful error-- that you need to delete some stuff to have referential integrity.
ON DELETE CASCADE --- you're telling it "eh, if you need to delete some stuff to avoid an error, go ahead, don't bother me, do what I mean".
You don't lose any referential integrity without cascades. Foreign keys are still enforced, just with an error if an action would break integrity rather than automatic deletes to satisfy the constraint that way.
> when hiring standards, training and code reviews should all be preventing idiotic changes
I was burned by this sort of thing early on, in companies where I had few such luxuries. Even though things are done better now, I'm still paranoid of that one day someone skips procedure and somehow lets a problem through all the QA loops.
> If I’m making a shopping cart system, I want to know every order line belongs to an order, every order belongs to a user and so on.
I take it from the other side: I want to know that if something is referred to elsewhere it can't be deleted until that is resolved.
If a top-level manager leaves I want an error if the hierarchy hasn't been updated before deleting his record¹, rather than his underlings, their underlings, their underling's underlings, … , being deleted when that one person is!
----
[1] Obviously this would normally be a soft-delete, there may be a lot of records referring to such an individual not just other person records. If you actually need to delete them (right to be forgotten etc.) then you need to purge the PII but keep the record so other things still hang together.
> This is one gripe I have with soft-deletion. Since I can no longer rely on ON DELETE CASCADE relationships
If you use soft deletes on all tables, you can also cascade them as long as you either cascade updates to the real keys as well, or prevent such updates, by having a deleted flag column on each table, including it in a unique constraint with the actual key column(s), and including it in the foreign key.
I had the same reaction to the 'code leakage' section, but 'foreign keys'? You can't reference a view; so you either don't use them (fks) or they point at the underlying table and you have the problem described.
You could have views that say 'thing I have a foreign key to is not deleted' of course, but that sort of seems like 'code leakage' again, just in SQL this time.
Also in Postgres, you cannot have a foreign key constraint that references a view, not even a materialised view.
I'm with the author on this one. Any soft delete logic does have a tendency to bleed into other systems and make your systems more complicated, for very little gain.
The main problem with views for this use case in practice is that they ossify your schema. Views and matviews are effectively a dependency tree, and many common types of schema evolution become substantially more difficult when the system forces you to wrap your DDL in a series of view drop/recreation steps.
This is merely annoying when dealing with regular views because recreating even a large number of views is fast, but can be catastrophic if you have any matviews in your table dependency tree. A matview can easily turn what should be an instantaneous DDL operation into a partial outage while the matview is being regenerated.
(this is all postgres specific, it may be untrue for other systems)
dbt is great, but I'm not sure it's appropriate to manage objects in a production transactional database. It's designed for analytical, columnar databases.
Thank you for this. I had no idea this existed either and it may solve some points for us on MySQL where we've really been yearning for materialized views... which I'm now reading my be a foot gun.
> The main problem with views for this use case in practice is that they ossify your schema. Views and matviews are effectively a dependency tree, and many common types of schema evolution become substantally more difficult when the system forces you to wrap your DDL in a series of view drop/recreation steps.
You're not wrong, especially with the second part. I.e., deeply nested or convoluted dependencies between views can definitely make it awkward or painful to make adjustments near the root of the tree.
When I started this reply I was going to say "I hear you, but it's not an issue I run into very often". But that's not true. I've actually been burned by that moderately often, and have sometimes avoided or redesigned the root-level table change to avoid having to propagate all those changes to the rest of the dependency tree.
That said, in my experience (also mostly with postgres for this context) I feel like that's usually been more of a developer laziness issue (my own laziness that is), rather than an "ossified schema" issue. It's definitely a PITA when some simple change is going to break a dozen inter-connected views, but that's a coding issue not a deployment issue almost all of the time.
To be fair I don't really use matviews very often, but for true basic views I am guessing that the actual execution of the DDL to rebuild changed views is manageable in all but the most extreme cases. Even then there _should_ be a maintenance window of some sort available.
Thinking this thru a little bit, I believe the "anti-pattern" you're warning against isn't really views themselves but deeply nested/interconnected views (views that query other views, etc). I use views often (for this logical-delete type idiom for example) and I have rarely regretted it. I have often regretted creating complicated view-base dependency trees however, so I think I'm wholeheartedly in agreement on that point.
How would a view solve the foreign key issue? Are you suggesting coding specific deletion triggers into the view such that appropriate foreign keys are "cascade" deleted when a row in the view is deleted?
Views can be used to implement pretty much any kind of automated inference, reasoning, rules etc. on the "raw" table data. The example of filtering out deleted records is just one of the simplest. That one single feature can easily transform a simple DB platform into a fully-featured knowledge base system, easily usable to support even complex reasoning tasks.
At least in Postgres, having a huge amount of "dead" data in large tables is problematic because vacuum always has to read the full data set.
Even with conditional indexes where you exclude deleted data you take a significant performance hit reading dead blocks because there is no way to quickly vacuum them. You accumulate hours of bloat until your vacuum finishes.
You can't beat a separate insert only archive table which you never have to vacuum.
Vacuum does not have to read the full data set every time. The visibility map tracks, on a block level, whether all rows in a page are known to be visible to everyone (starting in 8.4 or such) and whether the page is "frozen", i.e., does not contain visibility information that might need vacuuming (starting in 9.6 IIRC).
However, indexes do currently have to be scanned as a whole. But that's only done by autovacuum if there's enough row versions for that to be worth it (in recent versions).
This is practically the #1 use-case for partitions imo. Partitions are tables with syntactic sugar (that Postgres understands in its query planner) so partitions maintain their own indexes and are vacuumed individually. If you structure your partitions into hot/cold, or hot/cold_day1/cold_day2/etc then you get several advantages:
* hot and cold do not churn each others indexes or tables, you effectively have only one set of indexes (and data) that's actually churning and the others are stable.
* hot and cold can be treated differently - you can perform more aggressive indexing on data once you know it's out of the hot-set, or partition your hot data onto a tablespace with dedicated hardware while cold data is on bulk hardware, etc. Since queries are planned onto each partition individually, postgres can often select "the right way" for each partition.
* "deleted_at" is a special case of cold table. Dropping any partition is basically free, so if you partition into date ranges, then at the end of your data retention period you just drop the partition for that date range, which doesn't churn indexes or induce vacuuming
If data can never exit the cold-data state, then it's effectively an append-only table too, it just exists as a supplement to your online/hot-data table but it doesn't require special attention/etc. So we're in agreement on that point, that's a good design feature if you can swing it!
(note that for audit logging, I think it's simpler to just do the separate table. But the partition strategy does have some advantages for "cold" or "dead" data as a more generic concern imo)
This is one of those situations where a good ORM can simplify things greatly. For example, with EF Core you can add a global filter which will filter out soft-deleted rows in all queries automatically (unless you add .IgnoreQueryFilters()).
It couples nicely with some hackery which turns removes into soft-deletes. You can remove objects as usual and they get soft-deleted in the database.
I've used this in a few projects and it's fantastic.
A problem (unless something has changed, my context is Oracle from some time ago) is that NULL values are not indexed. So the "WHERE deleted_at IS NULL" could trigger a full table scan. It can also cause row migration when the NULL value is eventually filled in. Unless you explicitly need the deleted date, it's probably better to use a non-nullable Y/N for this.
> developers underutilize the capabilities of the massively advanced database engines
So true. There are so many amazing, powerful features in all of the major players.
Also: updatable views are amazing. With query rewriting (whatever you vendor calls it) you can affect some truly material changes to the system without any changes to the client applications. An example would be implementing temporal relations.
I was going to chime in with this. thanks. One issue with views however is that a lot of these features require more and more nuanced knowledge of RDBMSes where these days unless you have a veteran architect, most of the team just knows the various library/tooling that interacts with "a variety of databases" so there is often less effort to go deeper.
I've seen so much torturously complex code to coerce an ORM or query builder to generate a query that would've been simple just to write. Because "that's the way it's done" - Spend 5 minutes writing a query and then 5+ hours trying to figure out the convoluted code necessary to coerce something to generate that same query.
Of course, there are times when query builders are necessary to create the queries dynamically depending on parameters. But it's all too common to see them used and abused even for static queries.
People are just plain afraid of writing SQL. Or they think it's 'best practice' to use an ORM or query builder and using actual SQL is somehow 'wrong'.
Even people who know a decent amount about the database do it anyway because "that's the way that it's supposed to be done" or "that's 'best practice'".
Seriously. That "Downsides: Code leakage" point is nonsensical.
```
CREATE OR REPLACE VIEW active_customer AS
SELECT *
FROM customer
WHERE
deleted_at IS NULL
OR deleted_at <= NOW()
;
```
There, I fixed it.
Just use `active_customer` instead of `customer ... deleted_at IS NULL`.
In fact, since the deleted_at column is a timestamp, the original "leakage" query:
```
SELECT *
FROM customer
WHERE id = @id
AND deleted_at IS NULL;
```
is actually broken. A non-null `deleted_at` timestamp that's in the future implies the record hasn't been deleted yet, right?
I've often had junior devs assert that views are some kind of code smell, but these sorts of "canned query/filter that you want to apply very very often" seem like the perfect use case for a view to me. It's DRY, and the fact that your standard "query" is in the database" means you can change it more readily than trying to make sure you hit all the points it might be embedded in the application code.
> I feel like a lot of developers underutilize the capabilities of the massively advanced database engines they code against
Early-ish in the JDBC days a senior dev I was working with at the time (as a junior dev myself) made a pretty good case that "the database is part of the application" that's always stuck with me. Full database independence via software level abstractions is a pretty silly goal outside of library code. If you have a service that makes extensive use of the database, don't throw away the database features in the interest of some abstract "we could swap out oracle with mysql without changing anything" objective. If you want it to be generic, use the SQL standard, but don't be afraid to have a few db-specific bits in the app code if that's a subsystem you might replace once a decade or something.
I blame the DBA/Dev divide for a lot of this. A lot of the impedance between these layers is social/procedural. If you can change the DB as easily as the code, there's a lot less fear of using the right tool for the specific job.
I'd argue that the simple `deleted_at IS NULL` check is not broken - unless your product / domain specifically allows and requires scheduled future deletions adding such logic can easily introduce bugs. For example, you could to get the comparison flipped by accident, and if it's only in one place out of many that bug could go unnoticed for a while.
You don't put the check in your code, you put it in the view, and access the data exclusively through that view. In that way, the check is defined exactly once.
And now your view is based on an unstable function, so it's impossible to write partial indexes for (very important for performance!) and impossible to use foreign keys with. Just add a check constraint that deleted at is never in the future and move on with your life. If you really, really need to schedule a delete, make it its own concern (UserVersions table with applicable_at?), don't mix it with your soft delete logic.
If any non-null value for deleted_at indicates a logical delete, it seems like TIMESTAMP is the wrong data type for that column.
I mean I guess I can imagine a scenario where you want to know _when_ a record was logically deleted in addition to a flag that says is_deleted, but maybe that should be two different columns.
Or at the very least, `deleted_at` is a misleading name in that case. Interpreting `deleted_at` as "is deleted after but not before" is definitely a reasonable _enough_ interpretation. I agree it doesn't unambiguously mean that, but it's at best ambiguous enough to make for a bad column name if `IS NOT NULL` is the way you intend to check for a logical delete.
> you could to get the comparison flipped by accident, and if it's only in one place out of many that bug could go unnoticed for a while.
That's exactly why I proposed a database view as the proper solution to both whole `deleted_at IS NULL` "code leakage" issue. If you have a table that uses a logical-delete idiom that you intend to query from a bunch of different places in the code, a database view that filters out the logically deleted rows is a much more appropriate solution. It's a universally DRY solution. The single simple view covers all tech stacks, all services, etc., up to and including interacting with the database via some sort of REPL console.
If you need to add `deleted_at IS NULL` to, say, 90% of the queries against the `customer` table and you're afraid someone is going to forget to do that somewhere somehow, it seems pretty obvious kind of "extract common WHERE clause" refactoring is warranted. This is especially true in the actual "business meaning" context here. You are only going to be interested in the logically deleted rows in small number of specific circumstances. Having some way to extract away that detail from the 90% of the use cases where "logical deleted" is meant to be functionally equivalent to "actually deleted" seems extremely warranted. It's a perfect counter-example to overcome an aversion to using database views. I get that the `active_customer` abstraction is probably awkward to implement in a lot of application code contexts (depending on your ORM/DB-query framework I guess), but the database view is a simple and elegant way to address that really clearly defined need.
That's fair. It's what I was trying to acknowledge when I wrote "I can imagine a scenario where you want to know _when_ a record was logically deleted".
That said, I'm genuinely curious about how often and in what way that time-based information is actually used.
For example, I often see created_at and modified_at columns that are completely ignored by the actual business logic and application code that are only used (if ever) for ad hoc diagnostic purposes like reconstructing the sequence of events after some catastrophic error in the app logic screwed up the database. If run-away-logic-screwing-up-the-database is a legitimate cause for concern, that's probably reason enough to include that extra meta-data, but I can easily count on one hand the number of times that's come up in practice across my long career. I mean, I also include that sort of column often. And there are certainly entities for which those data are relevant for application logic and/or reporting purposes. But there are definitely cases in which those data are never used at all too.
If this `deleted_at` timestamp-as-boolean convention is popular enough in the Rails community to be universally known, is there some common use for knowing _when_ a record was logically deleted (outside of reconstructing of the database state after something goes wrong) that I'm missing?
I.e., is this something that's actually used in the typical business logic and application code, or is it primarily used for ad hoc debugging?
To be clear I'm not _doubting_ that such a use case exists, it's just that off the top of my head I can't think of commonly occurring one.
Every Rails developer knows what “deleted_at” means. I’m absolutely repulsed at the thought of turning the users table into a view. What’s next? No code and we only use triggers?
> I’m absolutely repulsed at the thought of turning the users table into a view. What’s next? No code and we only use triggers?
Assuming there's a justifiable need to apply the logical-delete idiom to the users table, what's the Rails-native, ActiveRecord-based approach to filtering out the logically deleted rows?
100% this. If you accept that the database is part of the application, you give yourself permission to use the full feature set of the database, and life becomes a lot simpler. Using views, stored procedures and other features lets you implement things like soft delete trivially, without it infecting all your application code.
In my entire career I've changed backend databases for an application exactly twice. It's not easy, and no amount of abstraction is likely to make it easier.
> Using views, stored procedures and other features lets you implement things like soft delete trivially, without it infecting all your application code.
That’s great but some of us actually like to write code. Especially Ruby on Rails where soft delete is a breeze if you don’t overthink it and build something the business doesn’t need.
Well, less code means less bugs, but go nuts. I'm just saying that the database is part of the stack. You wouldn't avoid Ruby features "just in case we stop using Ruby" - so why would we avoid using database features? It's up to you how best to assemble the features from your stack.
There is a reason we avoided database features in the 90's, which was to avoid database vendor lock-in. This was almost entirely a financial decision - you didn't want to be at the mercy of a vendor who could 10x the license fees and put you out of business, so you needed to have leverage over the vendor, and you needed to be able to credibly say you could change databases -- and in fact this exact scenario actually happened to me.
But that kind of behaviour is not really needed any more, and there's no need to avoid the great database features that systems like PG provide. In my experience, using database primitives to implement features tends to perform much faster (10x - 100x) and more consistently than the equivalent implementations higher up the stack.
The use case for using the database here is overblown. I've never experienced of heard of anyone using a view for the users table. I suppose it may exist in the wild somewhere.
I wouldn't avoid Ruby features because the applications I develop are in Ruby and it would require a complete rewrite anyway. I avoid the database because there are development speed advantages to using Rails features. And everything works together nicely assuming you do things the Rails way.
> If a record has has_many associations defined AND those associations have dependent: :destroy set on them, then they will also be soft-deleted if acts_as_paranoid is set, otherwise the normal destroy will be called.
The main reason was indeed vendor lock-in. Databases (like compilers) were hugely expensive back before open source got big.
But also some of the features were a bit flaky back then. I remember views in MySQL always caused problems - if you dumped and restored (ex: staging to dev), the special handling of permissions for views and such ("Access denied; you need the SUPER privilege for this operation") would break stuff. So we just didn't use them.^1
However, I'm still in favor of them, and think it's worth finding workarounds for things like that. They're not as flaky now, and people have found workarounds for the remaining flakiness. Nowadays the fear of using them is just tradition from 30 year old problems.
But now we have one or two generations of developers who were taught "Don't use the database features! 'Best practice' is to use it as a dumb datastore and re-implement them all in your application." But they don't know why, so wouldn't even know that those reasons are no longer applicable.
>^1 There exists a shortcoming with the current implementation of views. If a user is granted the basic privileges necessary to create a view (the CREATE VIEW and SELECT privileges), that user cannot call SHOW CREATE VIEW on that object unless the user is also granted the SHOW VIEW privilege.
>That shortcoming can lead to problems backing up a database with mysqldump, which may fail due to insufficient privileges. This problem is described in Bug #22062.
>The workaround to the problem is for the administrator to manually grant the SHOW VIEW privilege to users who are granted CREATE VIEW, since MySQL doesn't grant it implicitly when views are created.
The query isn't broken. In the Rails community at least it is very common to use a nullable frobbed_at column to indicate both "was it frobbed" and "when was it frobbed". In that context, the boolean check is always NULL/NOT NULL, rather than a time comparison.
I can see that use case and I guess if that's a popular idiom within a particular dev community it's not indefensible, but from a first-principles code-complete/pragmatic-programmer kind of lens, that seems to me like a really terrible naming convention if that's your intent.
It seems to me that having a date-type column named `is_frobbed` is highly preferable to `frobbed_at` if your intent is for `IS NOT NULL` to mean frobbed.
Sure, it's a little weird to have boolean-sounding `is_frobbed` name for a date column, but the number of times that `frobbed_at IS [NOT] NULL` appears in the code is likely to dwarf the number of times you're inserting a date type into the `is_frobbed` column. I feel like there's always going to be someone (like me in this case) that's going to come across a `frobbed_at IS NOT NULL` case, notice that it's a date column and make the same flawed assumption I did. A similar thing will happen to the `is_frobbed` column too, but in that case trying to treat the date type as a boolean is going to make it obvious that you're not understanding the full context.
Frankly, if I was naming a column for this idiom and didn't have other constraints (like the Rails context), I would probably try to find a more exact and direct way to express it, if that's a little clunky. Maybe something like `frobbed_when_not_null`? But I don't love that. Honestly it may make more sense to have a actual boolean-valued `is_frobbed` column and an independent `frobbed_at` timestamp column that's populated by a trigger when the value of `is_frobbed` changes. I feel like "clunky but direct" beats out "elegant but misleading" in the long run, especially given the degree of "clunky" and "elegant" we're talking about here.
I don't expect to talk you out of it, and I'd fall in line with this in a Rails context too if that's the convention, but I think it's objectively poor design.
For what it's worth, if the `frobbed_at` convention usually intended to track the timestamp at which the frobbing happened, the good news is that my `AND frobbed_at <= NOW()` check would be unlikely to break anything in practice. Assuming that there's nothing weird going on with the timestamps, the frobbed_at dates will always be in the past anyway.
I would use "deleted_after" for the use case you describe (scheduled deletions). "deleted_at" is saying that an event occurred (deletion) at a specific time. There is never going to be a case where you have a deletion time for a record that is not deleted, nor should there ever be a time where a record is deleted but a time is not recorded. So a single timestamp column is a parsimonious solution.
I agree `deleted_after` is a more appropriate name than `deleted_at`, but it starts to run into naming conventions again. For example `published_at` is often used in editorial systems (newspapers, blogs, etc.) for things that "embargoed until" some future date. `published_after` or even just `pub_dt` would probably be more appropriate, but I feel like the `_at` suffix is well established in some contexts.
For what it's worth, I have built and managed systems where "delete/disable this thing in the future" is a valid use case, but I'm wondering whether/how often/how the date aspect of the Rails-style `deleted_at` is actually used. Does anyone ever care when the record was deleted, or is it just extra metadata that is occasionally used in an ad hoc way for debugging or diagnostics. If the typical rails app replaced `deleted_at` with boolean-valued column, would it actually matter?
I'm glad to see this thread. I've been mulling over this exact issue of deleted_at code leakage with a naive soft-delete implementation. My immediate thought was to use views, so its nice to see this is not yet another case of me using crack-brain ideas out of inexperience.
What's an appropriate naming convention?
Should I do it universally and put transparent views in front of all my tables so I don't have to refactor my code to point to new views whenever I do suddenly need to put in a model constraint that isn't 1:1 with my data layer? Is a transparent view a no-op in terms of perf? if it matters, this is being done in Postgres
I will probably make my constraints partial over the not-deleted records, particularly for unique constraints used for upserts. Am I about to footgun myself? Is it even necessary with the new uniqueness behavior with NULLs being implemented in postgres? Will my performance characteristics be better one way or the other in particular circumstances? It sounds like if I have a high ratio of deleted to not-deleted records a partial index becomes necessary.
"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.
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?
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.
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).
GDPR is probably the worst piece of law ever written.
If an account is linked to invoices, it is perfectly reasonable to keep personally idenfitiable information for up to 10 years (would depend on each member state, but I don't think any have laws requiring you to keep invoices for more than 10 years). And because of that legal requirement you can justify keeping an audit trail of everything relevant for those transactions.
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.
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.
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.
> Furthermore, the cost of an error is potentially massive. Someone new at the company makes a revenue report based in the billed Invoices and does not realize they should query the view and not the table... Not great if 90% of all invoices belong to soft-deleted customers!
I'm not sure I buy this argument. It's certainly conceivable for that to happen, but no more so than any other case of "the engineer queried the wrong table and thus got incorrect results." There's never going to be any technical way of preventing this: if you have access to multiple sets of numbers, and you want to sum up one set of numbers but mistakenly sum up the other set of numbers, you're going to get the wrong answer!
> "the engineer queried the wrong table and thus got incorrect results."
The difference is that the path of least resistance, the most obvious method - just query the damn table - is incorrect. Bad design can certainly make a system more error prone.
In the example from this thread the names of the table and view are reasonably clear, so even in a hypothetical project without any external documentation of naming conventions or engineering processes (code review, etc.) the most obvious thing would be to query blah instead of blahwithdeleted.
Of course, this is extremely hypothetical, and in any real project where you're generating a financial report you absolutely must have a detailed understanding of the relation that you're aggregating over. Even if your project has very strict naming conventions for tables, views, etc. you've gotta put in more work than "this short string sounds like a plausible label for the relation I want to aggregate over."
"reasonably clear" are famous last words. It ignores tons of evidence on how commercial software development works. Everything is "reasonably clear" in isolation, but not when you throw in thousands of other "reasonably clear" things developers are supposed to keep track while not missing tight deadlines. Fact is that if you add opportunities for people to screw up then you will make people screw up, regardless of how "reasonably clear" or "obvious" the system is.
Maybe you need more work experience because believing that it is implausible for someone to accidentally query the customer_with_deleted table over the customer view is incredibly naive. Likewise, people generating financial reports can have detailed understanding of data modelling but scarily often don't. Give them extra opportunities to fuck up and they will take them every time. KISS
But that's my point. If you're relying on only the label of tables and views to determine what relation they represent, then you're already in an extremely unrealistic scenario, but in that scenario the names of the relevant tables and views in this example are as reasonable as one might expect.
> Fact is that if you add opportunities for people to screw up then you will make people screw up, regardless of how "reasonably clear" or "obvious" the system is.
But again, my point is that it's impossible to implement a technical solution to the problem that if one has multiple sets of numbers they can choose from then it is physically possible for them to choose the wrong one.
> believing that it is implausible for someone to accidentally query the customer_with_deleted table over the customer view is incredibly naive.
I was clear that I think it's possible to choose the wrong table. Just like it would be possible to select count(*) from blah where is_deleted = true when you intended to select count(*) from blah where is_deleted = false. Just like it would be possible to select count(*) from movies instead of select count(*) from television_series, in which case you'd get the wrong answer if what you wanted was a count of television series! Of course you should name things as best as you can, have naming conventions, have documentation of your schema, etc. so that your engineers can be informed, but the goal isn't to make it physically impossible for someone to make a mistake.
> Someone new at the company makes a revenue report based in the billed Invoices and does not realize they should query the view and not the table... Not great if 90% of all invoices belong to soft-deleted customers!
This is not a great example of what you mean. If a customer purchased a product or subscription, paid for it, then later deleted their account, the company has received revenue so that data absolutely should be on revenue reports.
This is a really scenario specific question - sometimes it's needed, sometimes it isn't. At my shop we have customers that will suspend their account but our sales team is pretty damn awesome so usually they end up renewing after going a while without our product - so being able to easily restore a large swath of former customers with all their permissions and preferences intact with a simple click of a button is a huge win compared to having a dev try to piece the data together out of backups that are six months out of date (a little secret... we never did this and just put the obligation on the CS team to manually recreate the records since that cost the company less).
If I was a tech lead and also wanted to advocate for hard deletion, I would ask the question for this scenario: "What's the cost of keeping all this data unnecessarily, modifying most queries to filter for deleted data, and dealing with other various consequences of soft deletion, and how does this cost compare with the cost of building a bespoke tool to restore deleted data at a customer's request within a certain time frame and compare with the benefit of a customer being able to restore their data at the 'click of a button'?".
Having dealt with systems that have hundreds of millions of records or more, many of which reference deleted data and are therefore useless, I lean towards hard deletion more and more and on the off chance that deleted data really needs to be recovered you build a separate system/infrastructure to support that, rather than building your _entire_ system around the small likelihood you really need to restore it.
As your data architect I'd probably answer your question "Well, it'll take a little while longer to vet all the indices - since we'll probably want most indices to filter on WHERE deleted_at IS NULL but we'll likely want a few without that constraint for managing undeletion. We'll use more space on disk which, honestly, is pretty much a non-issue in the modern world - and if it gets bad enough we can always partition the table on deletion status and dump the soft deleted rows on a secondary server... but I wouldn't worry about that until we hit facebook scale. In terms of application developer time - I'll probably need an hour of their time to explain it once and all existing queries keep working as they're working now... and as for that undelete tool, well, we don't actually have to build it - if we don't build it we can just ask devs to submit manual queries to undelete data rows as needed via our migration, or oneoff or console interface. We'll want to do a sweep for any queries that reference tables dependent on the soft-delete having table - just to make sure they're throwing an INNER JOIN against the view but, honestly, we could just bop those bugs on the head if they ever come up."
Like, I'm definitely not saying soft-deletion is always the answer, it takes additional effort and planning, but it's really, really easy to do safely.
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.
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.
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.
> 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.
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.
Just want to add that the downsides as identified in the article make little sense. Deleting a customers invoices should be a very rare thing. I can't imagine any accountant or auditor is going to be happy with an IT guy deciding when to delete invoices.
If accidentally writing the wrong query is a problem, then writing the wrong query is your problem.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
513 comments
[ 3.3 ms ] story [ 308 ms ] threadFor context, I've worked in fintech where I often needed to review backoffice approvals, transactions, offers, etc.
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.
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.
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.
All this essentially forces the use of some sort of soft deletion. (Activation flags are sort of just a more complicated form of soft deletion).
Invoices, from the article, is a great example. That record must remain unchanged in most financial regulations. I’d wager a customer sending a deletion request for invoices will be met with raucous laughter from the legal and finance teams.
As we learned from the Atlassian snafu, even giant companies with billions in revenue often can't recover from disasters. (I try to test mine every 6 months. I've never had a test go perfectly.)
This also helps with the original goal of making them safer by manually implementing "eventual consistency" for data living outside the transactional world.
I am sure there are cases where it is worthwhile, but most of the times I've hit this in common web apps, it wasn't.
This can be seen as related to soft deletes. But I consider it more marking intent in the system. Lets you make "delete" a simple field update that will be carried out by the backend.
Really? What value do you as a user get from knowing the details of this transitory state? Do you also want to see a progress bar of how many references have been updated, what's the progress of evicting the tweet from search indexes, etc? What's wrong with all this appearing instantaneous and actual delete happening in the background over 5 minutes or 5 hours?
It seems you are in favour of this approach, just don't want to call it "soft delete" (since it may be followed by a hard out-of-band delete).
I don't necessarily care on a progress bar, as those typically have their own woes.
And don't get me wrong, there are easy deletion cases that I am ok with appearing to happen immediately. I just find hiding processes from me is usually more annoying than it is worth. Worse, when the implementation didn't do it as a process, and now they typically just have more edge cases that won't delete cleanly.
And in most cases, it's the right balance.
If you just want to make it hidden, that is fine. But can lead to it's own problems. Usually when you have uniqueness constraints on things.
And it will go a long way to making your services harder to use if you don't allow users to associate friendly names with things. And to assume that the same friendly name will be used for a future item. (For example, if you name devices based on the room you put them in. Is reasonable to think that when you replace a device, that you are likely to want to reuse the name.)
Then you could do
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.
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?
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.
You could even sprinkle cryptographic guarantees into the mix. This would be very challenging to do with mutable DB rows.
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.
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.
Are you using CTEs in your views?
This is one gripe I have with soft-deletion. Since I can no longer rely on ON DELETE CASCADE relationships, I need to re-defined these relationship between objects at the application layer. This gets more and more difficult as relationships between objects increase.
If the goal is to keep a history of all records for compliance reasons or "just in case", I tend to prefer a CDC stream into a separate historical system of record.
It's not a standard (I think) but it'd let you do a cascading delete and then be able to go and look at the old objects as they were at time of deletion too.
You'd need to do things very differently to show a list of deleted objects though.
They were introduced in ANSI SQL 2011.
How closely implementations follow the standard I don't know, but something close exists in several DBMSs: I use them regualrly in MS SQL Server, there are plug-ins for postgres, MariaDB has them, and so forth.
The information schema table holds all foreign key relationships, so one can write a generic procedure that cascades through the fkey graph to soft-delete rows in any related tables.
https://www.db-fiddle.com/f/n3ux4s7mcZ2554738T14QR/2
However in practice this usually dramatically slows down reads if you have to constantly skip over the historic rows so you probably don't want to keep garbage round longer than absolutely necessary. The concept of a historic table mentioned below could be interesting though - especially if it could be offloaded to cold storage.
Right now in $dayjob I am converting an old non-DRY codebase from NoSQL data layer format to proper relational SQL backend.
This old front-end was created by verbosely coding up a relational cascading update/delete system for the NoSQL backend, in numerous places redundantly with subtle differences and inconsistencies, making the code brittle.
My current estimate is some front end functions will be reduced in LOC size by 95% once we use the power of SQL in the backend.
And the backend SQL Triggers+StoredProcedure required to replace these long NoSQL frontend functions doing cascading updates/deletes is only around 10% the size of the replaced front-end code.
And now future new frontends can reuse this without exploding the size of their codebase where complex data operations are required. And no need to reinvent the same data handling algorithm all over again (and risk subtle variation creeping in from the different front-end implementation of data algorithms)
If you're doing everything as one big table of entity-attribute-value with generic blobs for the values, then yes you'll have to re-implement all the normal constraints (that the database would handle) in your application and do all your data integrity handling there. And you'll also have to duplicate that logic across every application that accesses that database now and in the future.
Data usually lives longer and has more uses than just one program. So I think it's generally better to put integrity constraints in the database, rather than having to re-implement and duplicate that logic several places.
Mostly I use soft-delete because for auditing requirements we pretty much can't remove anything but also because nothing ever truly goes away. If we have an Invoice or Order then, from our perspective, we must have those forever even if the corresponding client is deleted and can never place another one.
Exactly. Unless you're doing something silly like adding deleted at to bridge tables ... which, you probably don't need even in 1:many.
Cascaded deletes scare me anyway. It only takes one idiot to implement UPSERT as DELETE+INSERT because it seems easier, and child data is lost. You could always use triggers to cascade you soft-delete flags as an alternative method, though that would be less efficient (and more likely to be buggy) than the built-in solution that cascaded deletes are.
If you look at how system-versioned (or “temporal”) tables are implemented in some DBMSs, that is a good compromise. The history table is your audit, containing all old versions of rows even deleted ones, and the base table can be really deleted from, so you don't need views or other abstractions away from the base data to avoid accidentally resurrecting data. You can also apply different storage options to the archive data (compression/not, different indexes, ... depending on expected use cases) without more manaully setting up partitioning based on the deleted/not flag. It can make some query times less efficient (you need to union two tables to get the latest version of things including deleted ones, etc.) but they make other things easier (especially with the syntactic sugar like AS AT SYSTEM_TIME <when> and so forth) and yet more things are rendered possible (if inefficient) where they were not before.
> I tend to prefer a CDC stream into a separate historical system of record.
This is similar, though with system versioned tables you are pretty much always keeping the history/audit in the same DB.
---
FWIW: we create systems for highly regulated finance companies where really deleting things is often verboten, until it isn't and then you have the other extreme and need to absolutely purge information, so these things are often on my mind.
Seems unfortunate to miss out on all the referential integrity benefits of a serious database when hiring standards, training and code reviews should all be preventing idiotic changes.
If I’m making a shopping cart system, I want to know every order line belongs to an order, every order belongs to a user and so on. Anyone who can’t be trusted to write an update statement certainly can’t be trusted to avoid creating a bunch of orphan records IMHO.
If you don't use ON DELETE CASCADE, the actual foreign key constraint gives you a meaningful error-- that you need to delete some stuff to have referential integrity.
ON DELETE CASCADE --- you're telling it "eh, if you need to delete some stuff to avoid an error, go ahead, don't bother me, do what I mean".
You don't lose any referential integrity without cascades. Foreign keys are still enforced, just with an error if an action would break integrity rather than automatic deletes to satisfy the constraint that way.
> when hiring standards, training and code reviews should all be preventing idiotic changes
I was burned by this sort of thing early on, in companies where I had few such luxuries. Even though things are done better now, I'm still paranoid of that one day someone skips procedure and somehow lets a problem through all the QA loops.
> If I’m making a shopping cart system, I want to know every order line belongs to an order, every order belongs to a user and so on.
I take it from the other side: I want to know that if something is referred to elsewhere it can't be deleted until that is resolved.
If a top-level manager leaves I want an error if the hierarchy hasn't been updated before deleting his record¹, rather than his underlings, their underlings, their underling's underlings, … , being deleted when that one person is!
----
[1] Obviously this would normally be a soft-delete, there may be a lot of records referring to such an individual not just other person records. If you actually need to delete them (right to be forgotten etc.) then you need to purge the PII but keep the record so other things still hang together.
If you use soft deletes on all tables, you can also cascade them as long as you either cascade updates to the real keys as well, or prevent such updates, by having a deleted flag column on each table, including it in a unique constraint with the actual key column(s), and including it in the foreign key.
You could have views that say 'thing I have a foreign key to is not deleted' of course, but that sort of seems like 'code leakage' again, just in SQL this time.
I'm with the author on this one. Any soft delete logic does have a tendency to bleed into other systems and make your systems more complicated, for very little gain.
This is merely annoying when dealing with regular views because recreating even a large number of views is fast, but can be catastrophic if you have any matviews in your table dependency tree. A matview can easily turn what should be an instantaneous DDL operation into a partial outage while the matview is being regenerated.
(this is all postgres specific, it may be untrue for other systems)
if you want view depending on mat view - materialize it yourself in a table, and refresh it yourself controllably.
You're not wrong, especially with the second part. I.e., deeply nested or convoluted dependencies between views can definitely make it awkward or painful to make adjustments near the root of the tree.
When I started this reply I was going to say "I hear you, but it's not an issue I run into very often". But that's not true. I've actually been burned by that moderately often, and have sometimes avoided or redesigned the root-level table change to avoid having to propagate all those changes to the rest of the dependency tree.
That said, in my experience (also mostly with postgres for this context) I feel like that's usually been more of a developer laziness issue (my own laziness that is), rather than an "ossified schema" issue. It's definitely a PITA when some simple change is going to break a dozen inter-connected views, but that's a coding issue not a deployment issue almost all of the time.
To be fair I don't really use matviews very often, but for true basic views I am guessing that the actual execution of the DDL to rebuild changed views is manageable in all but the most extreme cases. Even then there _should_ be a maintenance window of some sort available.
Thinking this thru a little bit, I believe the "anti-pattern" you're warning against isn't really views themselves but deeply nested/interconnected views (views that query other views, etc). I use views often (for this logical-delete type idiom for example) and I have rarely regretted it. I have often regretted creating complicated view-base dependency trees however, so I think I'm wholeheartedly in agreement on that point.
Say I have a simple view `select * from foo join bar on foo.foo_id = bar.foo_id where foo.deleted_at is null`
I never have to worry about deleting from bar, because I should never grab a child when the parent is 'deleted'.
Even with conditional indexes where you exclude deleted data you take a significant performance hit reading dead blocks because there is no way to quickly vacuum them. You accumulate hours of bloat until your vacuum finishes.
You can't beat a separate insert only archive table which you never have to vacuum.
However, indexes do currently have to be scanned as a whole. But that's only done by autovacuum if there's enough row versions for that to be worth it (in recent versions).
* hot and cold do not churn each others indexes or tables, you effectively have only one set of indexes (and data) that's actually churning and the others are stable.
* hot and cold can be treated differently - you can perform more aggressive indexing on data once you know it's out of the hot-set, or partition your hot data onto a tablespace with dedicated hardware while cold data is on bulk hardware, etc. Since queries are planned onto each partition individually, postgres can often select "the right way" for each partition.
* "deleted_at" is a special case of cold table. Dropping any partition is basically free, so if you partition into date ranges, then at the end of your data retention period you just drop the partition for that date range, which doesn't churn indexes or induce vacuuming
If data can never exit the cold-data state, then it's effectively an append-only table too, it just exists as a supplement to your online/hot-data table but it doesn't require special attention/etc. So we're in agreement on that point, that's a good design feature if you can swing it!
(note that for audit logging, I think it's simpler to just do the separate table. But the partition strategy does have some advantages for "cold" or "dead" data as a more generic concern imo)
It couples nicely with some hackery which turns removes into soft-deletes. You can remove objects as usual and they get soft-deleted in the database.
I've used this in a few projects and it's fantastic.
https://docs.microsoft.com/en-us/ef/core/querying/filters
https://www.thereformedprogrammer.net/ef-core-in-depth-soft-...
I agree that one should make use of RDBMS capabilities. A check constraint may be practical instead of (or in addition to) the foreign-key constraint.
So true. There are so many amazing, powerful features in all of the major players.
Also: updatable views are amazing. With query rewriting (whatever you vendor calls it) you can affect some truly material changes to the system without any changes to the client applications. An example would be implementing temporal relations.
Everywhere I have worked people know a decent amount about their data store. Not architects, just mid devs and higher.
Of course, there are times when query builders are necessary to create the queries dynamically depending on parameters. But it's all too common to see them used and abused even for static queries.
People are just plain afraid of writing SQL. Or they think it's 'best practice' to use an ORM or query builder and using actual SQL is somehow 'wrong'.
Even people who know a decent amount about the database do it anyway because "that's the way that it's supposed to be done" or "that's 'best practice'".
Hi, <1 yr experience swe here. Would HN mind unpacking "whatnot" with specific names of some these techniques?
``` CREATE OR REPLACE VIEW active_customer AS SELECT * FROM customer WHERE deleted_at IS NULL OR deleted_at <= NOW() ; ```
There, I fixed it.
Just use `active_customer` instead of `customer ... deleted_at IS NULL`.
In fact, since the deleted_at column is a timestamp, the original "leakage" query:
``` SELECT * FROM customer WHERE id = @id AND deleted_at IS NULL; ```
is actually broken. A non-null `deleted_at` timestamp that's in the future implies the record hasn't been deleted yet, right?
I've often had junior devs assert that views are some kind of code smell, but these sorts of "canned query/filter that you want to apply very very often" seem like the perfect use case for a view to me. It's DRY, and the fact that your standard "query" is in the database" means you can change it more readily than trying to make sure you hit all the points it might be embedded in the application code.
> I feel like a lot of developers underutilize the capabilities of the massively advanced database engines they code against
Early-ish in the JDBC days a senior dev I was working with at the time (as a junior dev myself) made a pretty good case that "the database is part of the application" that's always stuck with me. Full database independence via software level abstractions is a pretty silly goal outside of library code. If you have a service that makes extensive use of the database, don't throw away the database features in the interest of some abstract "we could swap out oracle with mysql without changing anything" objective. If you want it to be generic, use the SQL standard, but don't be afraid to have a few db-specific bits in the app code if that's a subsystem you might replace once a decade or something.
I blame the DBA/Dev divide for a lot of this. A lot of the impedance between these layers is social/procedural. If you can change the DB as easily as the code, there's a lot less fear of using the right tool for the specific job.
I mean I guess I can imagine a scenario where you want to know _when_ a record was logically deleted in addition to a flag that says is_deleted, but maybe that should be two different columns.
Or at the very least, `deleted_at` is a misleading name in that case. Interpreting `deleted_at` as "is deleted after but not before" is definitely a reasonable _enough_ interpretation. I agree it doesn't unambiguously mean that, but it's at best ambiguous enough to make for a bad column name if `IS NOT NULL` is the way you intend to check for a logical delete.
> you could to get the comparison flipped by accident, and if it's only in one place out of many that bug could go unnoticed for a while.
That's exactly why I proposed a database view as the proper solution to both whole `deleted_at IS NULL` "code leakage" issue. If you have a table that uses a logical-delete idiom that you intend to query from a bunch of different places in the code, a database view that filters out the logically deleted rows is a much more appropriate solution. It's a universally DRY solution. The single simple view covers all tech stacks, all services, etc., up to and including interacting with the database via some sort of REPL console.
If you need to add `deleted_at IS NULL` to, say, 90% of the queries against the `customer` table and you're afraid someone is going to forget to do that somewhere somehow, it seems pretty obvious kind of "extract common WHERE clause" refactoring is warranted. This is especially true in the actual "business meaning" context here. You are only going to be interested in the logically deleted rows in small number of specific circumstances. Having some way to extract away that detail from the 90% of the use cases where "logical deleted" is meant to be functionally equivalent to "actually deleted" seems extremely warranted. It's a perfect counter-example to overcome an aversion to using database views. I get that the `active_customer` abstraction is probably awkward to implement in a lot of application code contexts (depending on your ORM/DB-query framework I guess), but the database view is a simple and elegant way to address that really clearly defined need.
Not if I also want the timestamp of when it was deleted
That said, I'm genuinely curious about how often and in what way that time-based information is actually used.
For example, I often see created_at and modified_at columns that are completely ignored by the actual business logic and application code that are only used (if ever) for ad hoc diagnostic purposes like reconstructing the sequence of events after some catastrophic error in the app logic screwed up the database. If run-away-logic-screwing-up-the-database is a legitimate cause for concern, that's probably reason enough to include that extra meta-data, but I can easily count on one hand the number of times that's come up in practice across my long career. I mean, I also include that sort of column often. And there are certainly entities for which those data are relevant for application logic and/or reporting purposes. But there are definitely cases in which those data are never used at all too.
If this `deleted_at` timestamp-as-boolean convention is popular enough in the Rails community to be universally known, is there some common use for knowing _when_ a record was logically deleted (outside of reconstructing of the database state after something goes wrong) that I'm missing?
I.e., is this something that's actually used in the typical business logic and application code, or is it primarily used for ad hoc debugging?
To be clear I'm not _doubting_ that such a use case exists, it's just that off the top of my head I can't think of commonly occurring one.
Assuming there's a justifiable need to apply the logical-delete idiom to the users table, what's the Rails-native, ActiveRecord-based approach to filtering out the logically deleted rows?
100% this. If you accept that the database is part of the application, you give yourself permission to use the full feature set of the database, and life becomes a lot simpler. Using views, stored procedures and other features lets you implement things like soft delete trivially, without it infecting all your application code.
In my entire career I've changed backend databases for an application exactly twice. It's not easy, and no amount of abstraction is likely to make it easier.
That’s great but some of us actually like to write code. Especially Ruby on Rails where soft delete is a breeze if you don’t overthink it and build something the business doesn’t need.
There is a reason we avoided database features in the 90's, which was to avoid database vendor lock-in. This was almost entirely a financial decision - you didn't want to be at the mercy of a vendor who could 10x the license fees and put you out of business, so you needed to have leverage over the vendor, and you needed to be able to credibly say you could change databases -- and in fact this exact scenario actually happened to me.
But that kind of behaviour is not really needed any more, and there's no need to avoid the great database features that systems like PG provide. In my experience, using database primitives to implement features tends to perform much faster (10x - 100x) and more consistently than the equivalent implementations higher up the stack.
I wouldn't avoid Ruby features because the applications I develop are in Ruby and it would require a complete rewrite anyway. I avoid the database because there are development speed advantages to using Rails features. And everything works together nicely assuming you do things the Rails way.
https://github.com/rubysherpas/paranoia
> If a record has has_many associations defined AND those associations have dependent: :destroy set on them, then they will also be soft-deleted if acts_as_paranoid is set, otherwise the normal destroy will be called.
It's convenient.
Well, now you have heard of people doing this.
But also some of the features were a bit flaky back then. I remember views in MySQL always caused problems - if you dumped and restored (ex: staging to dev), the special handling of permissions for views and such ("Access denied; you need the SUPER privilege for this operation") would break stuff. So we just didn't use them.^1
However, I'm still in favor of them, and think it's worth finding workarounds for things like that. They're not as flaky now, and people have found workarounds for the remaining flakiness. Nowadays the fear of using them is just tradition from 30 year old problems.
But now we have one or two generations of developers who were taught "Don't use the database features! 'Best practice' is to use it as a dumb datastore and re-implement them all in your application." But they don't know why, so wouldn't even know that those reasons are no longer applicable.
>^1 There exists a shortcoming with the current implementation of views. If a user is granted the basic privileges necessary to create a view (the CREATE VIEW and SELECT privileges), that user cannot call SHOW CREATE VIEW on that object unless the user is also granted the SHOW VIEW privilege.
>That shortcoming can lead to problems backing up a database with mysqldump, which may fail due to insufficient privileges. This problem is described in Bug #22062.
>The workaround to the problem is for the administrator to manually grant the SHOW VIEW privilege to users who are granted CREATE VIEW, since MySQL doesn't grant it implicitly when views are created.
> -- https://dev.mysql.com/doc/refman/8.0/en/view-restrictions.ht...
It seems to me that having a date-type column named `is_frobbed` is highly preferable to `frobbed_at` if your intent is for `IS NOT NULL` to mean frobbed.
Sure, it's a little weird to have boolean-sounding `is_frobbed` name for a date column, but the number of times that `frobbed_at IS [NOT] NULL` appears in the code is likely to dwarf the number of times you're inserting a date type into the `is_frobbed` column. I feel like there's always going to be someone (like me in this case) that's going to come across a `frobbed_at IS NOT NULL` case, notice that it's a date column and make the same flawed assumption I did. A similar thing will happen to the `is_frobbed` column too, but in that case trying to treat the date type as a boolean is going to make it obvious that you're not understanding the full context.
Frankly, if I was naming a column for this idiom and didn't have other constraints (like the Rails context), I would probably try to find a more exact and direct way to express it, if that's a little clunky. Maybe something like `frobbed_when_not_null`? But I don't love that. Honestly it may make more sense to have a actual boolean-valued `is_frobbed` column and an independent `frobbed_at` timestamp column that's populated by a trigger when the value of `is_frobbed` changes. I feel like "clunky but direct" beats out "elegant but misleading" in the long run, especially given the degree of "clunky" and "elegant" we're talking about here.
I don't expect to talk you out of it, and I'd fall in line with this in a Rails context too if that's the convention, but I think it's objectively poor design.
For what it's worth, if the `frobbed_at` convention usually intended to track the timestamp at which the frobbing happened, the good news is that my `AND frobbed_at <= NOW()` check would be unlikely to break anything in practice. Assuming that there's nothing weird going on with the timestamps, the frobbed_at dates will always be in the past anyway.
For what it's worth, I have built and managed systems where "delete/disable this thing in the future" is a valid use case, but I'm wondering whether/how often/how the date aspect of the Rails-style `deleted_at` is actually used. Does anyone ever care when the record was deleted, or is it just extra metadata that is occasionally used in an ad hoc way for debugging or diagnostics. If the typical rails app replaced `deleted_at` with boolean-valued column, would it actually matter?
What's an appropriate naming convention?
Should I do it universally and put transparent views in front of all my tables so I don't have to refactor my code to point to new views whenever I do suddenly need to put in a model constraint that isn't 1:1 with my data layer? Is a transparent view a no-op in terms of perf? if it matters, this is being done in Postgres
I will probably make my constraints partial over the not-deleted records, particularly for unique constraints used for upserts. Am I about to footgun myself? Is it even necessary with the new uniqueness behavior with NULLs being implemented in postgres? Will my performance characteristics be better one way or the other in particular circumstances? It sounds like if I have a high ratio of deleted to not-deleted records a partial index becomes necessary.
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.
The world is almost never as simple as it seems.
If an account is linked to invoices, it is perfectly reasonable to keep personally idenfitiable information for up to 10 years (would depend on each member state, but I don't think any have laws requiring you to keep invoices for more than 10 years). And because of that legal requirement you can justify keeping an audit trail of everything relevant for those transactions.
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.
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.
And reports are developed and tested before they are used for crucial purposes.
I'm not sure I buy this argument. It's certainly conceivable for that to happen, but no more so than any other case of "the engineer queried the wrong table and thus got incorrect results." There's never going to be any technical way of preventing this: if you have access to multiple sets of numbers, and you want to sum up one set of numbers but mistakenly sum up the other set of numbers, you're going to get the wrong answer!
The difference is that the path of least resistance, the most obvious method - just query the damn table - is incorrect. Bad design can certainly make a system more error prone.
Of course, this is extremely hypothetical, and in any real project where you're generating a financial report you absolutely must have a detailed understanding of the relation that you're aggregating over. Even if your project has very strict naming conventions for tables, views, etc. you've gotta put in more work than "this short string sounds like a plausible label for the relation I want to aggregate over."
Maybe you need more work experience because believing that it is implausible for someone to accidentally query the customer_with_deleted table over the customer view is incredibly naive. Likewise, people generating financial reports can have detailed understanding of data modelling but scarily often don't. Give them extra opportunities to fuck up and they will take them every time. KISS
But that's my point. If you're relying on only the label of tables and views to determine what relation they represent, then you're already in an extremely unrealistic scenario, but in that scenario the names of the relevant tables and views in this example are as reasonable as one might expect.
> Fact is that if you add opportunities for people to screw up then you will make people screw up, regardless of how "reasonably clear" or "obvious" the system is.
But again, my point is that it's impossible to implement a technical solution to the problem that if one has multiple sets of numbers they can choose from then it is physically possible for them to choose the wrong one.
> believing that it is implausible for someone to accidentally query the customer_with_deleted table over the customer view is incredibly naive.
I was clear that I think it's possible to choose the wrong table. Just like it would be possible to select count(*) from blah where is_deleted = true when you intended to select count(*) from blah where is_deleted = false. Just like it would be possible to select count(*) from movies instead of select count(*) from television_series, in which case you'd get the wrong answer if what you wanted was a count of television series! Of course you should name things as best as you can, have naming conventions, have documentation of your schema, etc. so that your engineers can be informed, but the goal isn't to make it physically impossible for someone to make a mistake.
This is not a great example of what you mean. If a customer purchased a product or subscription, paid for it, then later deleted their account, the company has received revenue so that data absolutely should be on revenue reports.
Having dealt with systems that have hundreds of millions of records or more, many of which reference deleted data and are therefore useless, I lean towards hard deletion more and more and on the off chance that deleted data really needs to be recovered you build a separate system/infrastructure to support that, rather than building your _entire_ system around the small likelihood you really need to restore it.
Like, I'm definitely not saying soft-deletion is always the answer, it takes additional effort and planning, but it's really, really easy to do safely.
Do you reach into it often? No.
But when you do it absolutely saves the day and makes you a hero.
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.
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.
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.
If accidentally writing the wrong query is a problem, then writing the wrong query is your problem.
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.
> 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.
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 1. https://news.ycombinator.com/item?id=27691442
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.
Either way, it allows you to have soft deletion and/or full activity logging functionality without the application having to know about it.
- 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.
https://docs.microsoft.com/en-us/sql/relational-databases/ta...
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.