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-...