JSONB seems like something I need to put a few nights into understanding, but my time is short and filled with diapers and bottles.
Easy, alternative soft deletion: `deleted_record_insert`
21–30 of 109 posts
Re: Easy, alternative soft deletion: `deleted_record_insert`
#22> I’ve spent the time migrating our code away from deleted_at, and we’re now at the point where it’s only left on a couple core tables where we want to retain deleted records for an exceptionally long time for debugging purposes.
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.
Re: Easy, alternative soft deletion: `deleted_record_insert`
#23I’ve been using this exact approach for years and strongly advocate for it. 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.
You can get around the "check in every transaction" problem with an ORM, but now you're (more) coupled to your ORM which you will occasionally inevitable need to circumvent for something or other. And now you've made it (more of a) leaky abstraction.
Bwttwr yet, encode the logic in a view and run your queries against the view. DRY
Re: Easy, alternative soft deletion: `deleted_record_insert`
#24JSONB seems like something I need to put a few nights into understanding, but my time is short and filled with diapers and bottles.
Unless you’re really into the implementation details of individual postgres column types that seems of little interest.
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).
Re: Easy, alternative soft deletion: `deleted_record_insert`
#25Earlier quoted context omitted.
You can get around the "check in every transaction" problem with an ORM, but now you're (more) coupled to your ORM which you will occasionally inevitable need to circumvent for something or other. And now you've made it (more of a) leaky abstraction.
Bwttwr yet, encode the logic in a view and run your queries against the view. DRY
Sure, but you need an "undeleted" view per table now - I haven't seen an ORM offer maintaining those views capability out of the box - any to look at?
Re: Easy, alternative soft deletion: `deleted_record_insert`
#26JSONB seems like something I need to put a few nights into understanding, but my time is short and filled with diapers and bottles.
jsonb is a viable alternative to mongodb. im not doing blow by blow feature parity...but in 90 percentile usecase, you can replace mongodb with jsonb
Re: Easy, alternative soft deletion: `deleted_record_insert`
#27I did something similar to this many years ago but didn’t limit to deleted records and instead created an audit log with a very similar approach. It worked nicely and provided a view into how data was changing by our users.
I considered this for an audit log as well, but ran into a roadblock in terms of associating the operation with a logged in user who triggered the change..
how did solve that particular issue? or was it not a requirement in your case?
Re: Easy, alternative soft deletion: `deleted_record_insert`
#28I’ve been using this exact approach for years and strongly advocate for it. 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.
You can get around the "check in every transaction" problem with an ORM, but now you're (more) coupled to your ORM which you will occasionally inevitable need to circumvent for something or other. And now you've made it (more of a) leaky abstraction.
or you can create view for every table that supports soft deletion and ensure all of your read-only queries are using those table
CREATE VIEW current_customers AS
SELECT * FROM customers where deleted_at is null;
SELECT * FROM current_customers JOIN ...
Re: Easy, alternative soft deletion: `deleted_record_insert`
#29I did something similar to this many years ago but didn’t limit to deleted records and instead created an audit log with a very similar approach. It worked nicely and provided a view into how data was changing by our users.
I considered this for an audit log as well, but ran into a roadblock in terms of associating the operation with a logged in user who triggered the change.. how did solve that particular issue? or was it not a requirement in your case?
We did something like this, by using postgresql's `set_config()` before every transaction. The trigger functions can read that value using `current_setting()`. It works for us, but for a relatively low-traffic internal application.
Re: Easy, alternative soft deletion: `deleted_record_insert`
#30> I’ve spent the time migrating our code away from deleted_at, and we’re now at the point where it’s only left on a couple core tables where we want to retain deleted records for an exceptionally long time for debugging purposes. 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 includ…
It's a set-and-forget solution, where you only have to reason about it locally to the schema. As they point out, `deleted_at` is viral, you have to incorporate it into every query that touches a table with that column type, otherwise you might get into weird behavior or possibly vulnerabilities/disclosures.
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.