Easy, alternative soft deletion: `deleted_record_insert`
brandur.org
brandur.org
Why such diversity of solutions? I believe this is because the problem of adding time dimensions to your data model is heavily dependent on exactly what you intend to do with your data. Enabling theoretical audits is a very different requirement than enabling admins to rollback changes to data, which is a very different requirement to enabling users to undelete their data - and then there’s a performance angle to layer on top: your design will depend on whether you need your INSERTs/UPDATEs to be fast or your SELECTs.
It’s definitely not a case of one of these approaches being definitively better than another. You can’t shortcut talking to your users/clients/stakeholders about how the whole system is intended to work.
https://en.wikipedia.org/wiki/Temporal_database lists a few more - apparently there are versions of this in Oracle, DB2 and SQL Server now.
I got real excited about that feature because I could think of a few tables at work that could use it. Sadly, PostgreSQL doesn't implement it [3] (I can't permalink to the feature but you can search for "T180", "System-versioned tables", "T181", or "Application-time period tables").
[1] https://news.ycombinator.com/item?id=34182433
[2] https://www.youtube.com/watch?v=xEDZQtAHpX8&t=2276s
[3] https://www.postgresql.org/docs/current/unsupported-features...
In any living system db schema is something that continuously evolves. New foreign keys, new columns, dropped foreign keys, dropped columns, new indexes. If soft deleted data remains in the tables it evolves with the rest of the system, but that's not the case if it all went into a json blob. Are you sure you want to remember all changes made to your data while trying to restore it?
The problem with queries is there, but I'm wondering about the scale of the problem. From my experience, tables do not go from hard deleted to soft deleted often, hence it's more a matter of habit to check the structure of a table you see for the first time and take deleted_at into account in case it's there.
As the author says, it's all about trade-offs, I would use an audit log for debug purposes (deleted_at does not answer who did it, adding deleted_by to every table adds a risk of split brain - what if deleted_at is null, but deleted_by is not?) and deleted_by to enable quick reverts to accommodate mistakes users and developers do.
A more problematic pattern for me is to trace the changes for different columns. Let's say you tag your uses as being a superhero. The moment you introduce this prop, analysts will immediately ask you who, why and how many times has changed this field and what was the value of the field at the time X. One can say that it's not necessary for all the fields, but I do observe that I have much less trouble in the development if I accommodate for it from the start rather then add hacks to support it later
The deleted_at approach is rife with problems since you have to ensure it’s checked in every single query, including joins where it may be referenced. Getting the record out of the table is critical.
There is a hack of sorts. You create a duplicate of the primary key column, named e.g. id_active. You create a CHECK constraint which says something like "(status = 'Deleted' AND id_Active IS NULL) OR (status <> 'Deleted' AND id_active = id)". You create a unique index on "id_active", and point your foreign key to that. When you create a record, populate both id and id_active to same value; when you soft-delete it, set id_active to NULL. Actually, maybe a simpler solution is to make id_active a "GENERATED ALWAYS AS ... STORED" column–although I'm not sure if Postgres supports them for foreign keys? That's a relatively new Postgres feature and I haven't done much yet with the more recent versions in which that feature was added.
As an aside, they’re a great feature. We’re using them to generate columns that we can index for efficient joins between tables and also for creating text strings for searching over using trigram indexes. The whole thing is really seamless.
It was one of the many things that impressed me so much when I was a user that made me want to hack on the tool for a living.
So, this problem of soft-deletes becomes trivial
CREATE VIEW current_customers AS
SELECT * FROM customers where deleted_at is null;
SELECT * FROM current_customers JOIN ...
Considering soft deleted for uniqueness can be a feature, especially if one has a restore feature. Though I agree it can be easily overlooked if you want to exclude them, such as forgetting to use COALESCE with some reserved value. (Otherwise null will make the whole constraint always unique.)
Especially when you consider foreign keys, restoration of deleted data is essentially never just setting the deleted field to null. You need a more robust system to fill in missing details or rectifying the deleted data with new data.
What I'm not sure about is how will it will behave with blob fields and other data types with "problematic" serializations.
The application and its use of data must take this into account. I don't believe there is some magic bullet here. However, audit tables are invaluable for tracking changes and culprits.
Eventually, the store and all its data can be deleted using cascade delete to maintain referential integrity.
The first is analytics/reporting, which can be used to aggregate or otherwise store data without being coupled to the main application (therefore, not a problem to delete data if it’s been processed already).
The second is auditing to maintain a paper trail.
The third is a soft delete backed by a TTL, basically so there’s a window of time to undo the operation, but the data doesn’t remain there forever (which might be a problem with GDPR and the like - depending on what your data is.)
Main reason I had for trying to break it down this way was because soft deletes in a relational database, with foreign key constraints, becomes unintuitive pretty fast. You need escape hatches to properly delete records, you need to remember to filter deleted ones from your results, and then there’s even more complication when trying to handle this across associations. So the final piece is only to soft delete things that require it, rather than enforcing it across all tables.
https://github.com/solidsnack/macaroon/blob/master/temporal....
It would be pretty easy to change this to perform deletion-with-interning like mentioned in the article.
In an older version, the code actually replicated the schema of the source table in the log table; but it can lead to problems during migrations.
Under GDPR, CCPA/CCRA, and a number of other privacy laws, if you're going to retain data related to people you need to provide those people some way of retrieving that data or requesting that it be erased. Putting the data into a "deleted_record" table doesn't remove that obligation.
So, if you're going to have a bunch of "deleted" records hanging around, you need some way to figure out who they belong to. And I don't see any way to do that with this schema, short of rehydrating all the rows and following the original foreign key relations.
-- Permanently delete message sent by jon
DELETE from deleted_record where table_name = 'messages' and data->>'sender' = 'jon';
It also requires you to scan the entire table and decode JSON for every row you've ever deleted, which may become prohibitively expensive for large databases.
To give an example for your 1st point: If you have the schema users->messages->message_attachments and message_attachments does not store the user_id, you'll have trouble finding all attachments from a specific user.
Perhaps something here that could mark it with a uuid so if it needed to be fully deleted, it could be found easily and removed or overwritten
Does it still count as your data if there is no normal way to retrieve/access it in the software?
If you say "yes", here's what this implies: if you have deleted the data, but it's still on the disk because the drive heads haven't wiped it yet (it's just been deallocated), then it's still accessible.
So, whenever there's a GDPR request, you should run disk recovery software? (The answer is no; you'd have to butt up against pretty thick lawyers and judges to be fined for this)
If you have an audit table that is automatically deleted after a while, and the audit table cannot be accessed as part of normal operations, then IMO you will be able to argue that it's not part of data that should be "reasonably accessed" via GDPR.
When you look at the spirit of the law, it also does make sense (disclaimer: I am a HUGE proponent of GDPR). What matters is that users have access to the data that the company has access to, and is able to correct and delete it. If the data is not normally accessible, and will soon be deleted, then it doesn't matter.
Moreover, technical backup solutions, where only a very limited set of people have access, are fine. If you store DB backups, you don't have to rewrite or delete them because a customer that used your service for a week decided to ask for deletion under GDPR.
how did solve that particular issue? or was it not a requirement in your case?
I got it working in a PoC, and with the right configuration of roles, this pattern would give you user identity and audit right down to the database itself.
Sadly I haven’t pulled it off in production environment yet, but if I ever get the chance to work at this level in an enterprise app again, I’ll definitely do it.
I don’t typically favor DB level solutions because bleeding app logic beyond basic schema and integrity means you lose some portability and/or your architecture messes separation of concerns pretty quick. Maybe that means you don’t get the full benefit of the DB but it’s a trade I usually make.
Sounds like the problem is not soft deleting, but applying soft deleting to _everything_ without thought.
Then he goes on to suggest an alternative that is even more complicated.
Just include a where deleted at is null check. Hide it behind some interface in your ORM if you dont want to think about it.
A set and forget solution at the ORM level is begging trouble IMHO. I don't like ORMs, but I really don't like the idea of my ORM being even more magical than it already was.
Understanding the schema is important I agree - a great way to achieve that is to make the schema simpler to understand & to eliminate the need to for special case logic as much as possible.
In my view triggers like this are great, they're keeping the integrity rules you've established for your data ("though shalt not delete a record without creating a copy for observability and manual recovery by incident responders") out of your application code (where shifting requirements and much higher change rate put them in jeopardy) and enshrining them in the schema (where that can stay for decades, through rewrites etc.).
Those queries could be raw SQL or from different ORMs and applications, maybe written without a full understanding of the database.
However the claim is not substantiated: how many bugs of that type did they had before?
> Some ORMs or ORM plugins make this easier by automatically chaining the extra deleted_at clause onto every query (see acts_as_paranoid for example), but just because it’s hidden doesn’t necessarily make things better. If an operator ever queries the database directly they’re even more likely to forget deleted_at because normally the ORM does the work for them.
I ran into this exact issue last month. Very common for places to have a general "soft delete unless you have a good reason not to" policy, and very common for people to forget about the deletion flag when writing joins by hand or doing reporting.
They want the ability to undelete stuff they accidentally delete, since accidental deletes happen all the time.
True for consumer users. Super super true for enterprise users.
In finance or medicine, it's often illegal[0] to delete customer information before the end of an expiration period (minimum several years).
There are ways to comply without soft-deletion, but in practice they are much more expensive to implement and never used.
0: "Illegal" is the wrong word, really "non-compliant with regulations, resulting in significant fines but except in case of gross negligence or fraud, probably not criminal charges".
There are also laws that require you to delete customer information. For instance patient data in situations where consent is withdrawn. So either way you can’t rely on “it’s often”. And on the flip side of things the legal requirements you are referring to are typically the audit requirements, which are still better served using audit tables, since it’s not enough to have a “current data” and “data that is currently not active”, since you need logs of who changed the data when, and you can easily run into situations where something is soft deleted, then restored, then soft deleted again.
Just pointing out that in some common cases, the goals of consumer data control are impossible to implement, due to conflicting requirements for record retention.
Cascade can be a bit of a footgun, as this can trigger a waterfall of deletes.
I like how you tried to track the standard as close as possible. I've seen (and written) ad hoc solutions that hard-code too much or mandate certain columns to be present.
That said, unlike the standard and most other RDBMSs, Postgres supports range types. Seems a shame to rely simply on two timestamptz columns when one tstzrange should suffice.
Jsonb just means “parsed json” (lit. “json, binary”), meaning postgres parses the data to a binary representation upfront which allows for more efficient json operations.
However that comes at increased insert and storage costs. It also leads to postgres normalisation so values don’t round-trip textually (which can surprise).
Sincerely, a dad with 5 kids.
how do you recover deleted records?
I’ve never encountered a database where I needed to know about non-current records but only ever the last-before-deletion state of deleted records, whether for data recovery or any other purpose.
jsonb_populate_record(…) was made precisely for this kind of scenario. Combine with an INSERT…ON CONFLICT DO UPDATE statement and Bob's your uncle.
Usually if you’re changing the way a primary function of the database works, like delete, it’s probably not a good move.
This is one of those things where business requirements trump the technical implementation details. Prevailing theory is that actual deletes are bad because you can’t do historical analysis, recovery etc on the data. Say a customer stops using a service for a year but comes back: it’s a big win if you can (at least optionally) restore their data, so the theory goes.
That’s why tricks like this exist.
The alternative is to write the data to a separate database and/or table that’s meant for archive purposes. I think it’s better than these soft delete tricks but it’s got complexity too. More resilient IMO and let’s production systems run leaner over time IMO
AND NOT is_deleted
INSERT INTO mytable
SELECT recovered.\*
FROM myaudittable audit
, jsonb_populate_record(
audit.jsoncolumn
, NULL::mytable
) recovered
WHERE audit.id = 8675309
-- optionally merge if new data added
ON CONFLICT DO UPDATE
SET field1 = EXCLUDED.field1
, field2 = …etc…
It's around historical data, especially in a work scenario.
For example a worker might create a thread and then 38 other workers reply to it. There could be a lot of great information in this thread. It could also be referenced in 5 other threads and external sources (docs, etc.).
If the worker leaves the company, should you really delete them in such a way where all of the threads they've created get deleted in a cascading fashion? I'm all for privacy and I would want to see that happen in most public communities but for private work, I don't know. That changes everything.
I've seen a number of tools keep the user around and visibly label them as "Jane Smith (Deactivated)". I think that's a lot cleaner than having a special reserved "Deactivated" user and then you change all of the user_id FKs to that user before deleting the person who left. If you do it that way you lose the context of who posted the original thing which has a lot of value in a long running project.
But all of the above hints at using deleted_at most likely. It keeps everything in tact at the DB level and then the app layer chooses what to do for each resource type. What other options do you really have if you want to keep things working exactly the same after deleting someone except for maybe showing an indicator that they're not around anymore?
I feel like you are solving a different problem than the one presented in the article.
Marking it as inactive with a boolean makes you lose the context on when the inactive toggle happened.
Chances are you want to use deleted_at on the user in my use case because that provides you the timestamp of when it happened and also lets you have application logic which prevents that user from being able to login and do things, but still lets you show their previous information to everyone else as if they didn't leave and all you have to do is have a tiny bit of template logic that appends " (Deactivated)" to their name if `deleted_at` is not NULL.
It prevents the overload of what deletion means as to preserve proper separation of concepts too
This isn't a soft delete -- you can't use the data for undeletion and it's not visible to an application that can see that table (as it would be in the case of a `deleted_at` column). It's only for analytical purposes, as the author says.
They do - almost all databases will offer change data capture integration either via making the write ahead log readable or by suggesting triggers, like the article.
If you wanted to read the deletion journal in another system (maybe because of transaction rate), you might prefer CDC to Triggers. For example: https://www.postgresql.org/docs/current/logical-replication-...
If you want to restore something, get it from a backup.
If you want to delete something, but you fear that it will ruin something in your db because the architecture is a mess and you are not really sure what references what and what will break, then soft-delete it.
But what is the point of this?
This seems like a clever solution that simply deletes rows but provides a cumbersome mechanism to see history of delete data if they need it.