101–110 of 157 posts
Earlier quoted context omitted.
No, more like why you'd use a more expensive filter to hide soft deleted data, instead of just a flag.
Checking whether `deleted_at is null` should be extremely cheap, and it avoids the duplication and desynchronisation of having both “deleted” and “deleted_at”.
This might stem from the domain I work in (banking), but I have the opposite take. Soft delete pros to me: * It's obvious from the schema: If there's a `deleted_at` column, I know how to query the table correctly (vs thinking rows aren't DELETEd, or knowing where to look in another table) * One way to do things: Analytics queries, admin pages, it all can look at the same set of data, vs having separate handling for h…
Earlier quoted context omitted.
> DELETEs are likely fairly rare by volume for many use cases All your other points make sense, given this assumption. I've seen tables where 50%-70% were soft-deleted, and it did affect the performance noticeably. > Undoing is really easy Depends on whether undoing even happens, and whether the act of deletion and undeletion require audit records anyway. In short, there are cases when soft-deletion works well, and i…
> I've seen tables where 50%-70% were soft-deleted, and it did affect the performance noticeably. I think we largely need support for "soft deletes" to be baked into SQL or its dialects directly and treated as something transparent (selecting soft deleted rows = special case, regular selects skip those rows; support for changing regular DELETE statements into doing soft deletes under the hood). https://news.ycombinat…
(In my opinion, replicating this via a `validity tstzrange` column is also often a sane approach in PostgreSQL, although OP's blog post doesn't mention it.)
[1]: https://learn.microsoft.com/en-us/sql/relational-databases/t...
This might stem from the domain I work in (banking), but I have the opposite take. Soft delete pros to me: * It's obvious from the schema: If there's a `deleted_at` column, I know how to query the table correctly (vs thinking rows aren't DELETEd, or knowing where to look in another table) * One way to do things: Analytics queries, admin pages, it all can look at the same set of data, vs having separate handling for h…
I have worked with databases my entire career. I hate triggers with a passion. The issue is no one “owns” or has the authority to keep triggers clean. Eventually triggers become a dumping ground for all sorts of nasty slow code. I usually tell people to stop treating databases like firebase and wax on/wax off records and fields willy nilly. You need to treat the database as the store of your business process. And you…
We found that strict CQRS/Decoupling is the only way to scale this. Let the operational DB keep the soft-deletes for audit/integrity (as mentioned by others), but the Search Index must be a clean, ephemeral projection of only what is currently purchasable.
Trying to filter soft-deletes at query time inside the search engine is a recipe for latency spikes.
Earlier quoted context omitted.
Checking whether `deleted_at is null` should be extremely cheap, and it avoids the duplication and desynchronisation of having both “deleted” and “deleted_at”.
Yes, if your database has null. I know this is about postgres, but a lot of stuff is nosql now.
Earlier quoted context omitted.
> I've seen tables where 50%-70% were soft-deleted, and it did affect the performance noticeably. At that point you should probably investigate partitioning or data warehousing.
What would be the benefit of data warehousing in this case?
The correct thing to do if your retention policy is causing a performance problem is to sit down and actually decide what the data is truly needed for, and if you can make some transformations/projections to combine only the actual data you really use to a different location so you can discard the rest. That's just data warehousing.
Data warehouse doesn't only mean "cube tables". It also just means "a different location for data we rarely need, stored in a way that is only convenient for the old data needs". It doesn't need to be a different RDBMS or even a different database.
Earlier quoted context omitted.
> Soft delete isn’t language used by users so it should not be used by engineers when making product facing decisions. Users generally don’t even know what a database record is. There is no reason that engineers should limit their discussions of implementation details to terms a user might use. > “Delete” “archive” “hide” are the type of actions a user typically wants, each with their own semantics specific to the pr…
> Users might say they want “delete”, but then also “undo”, and suddenly we’re talking about soft delete semantics. I've worked for a company where some users managed very personal informations on behalf of other users, like, sometimes, very intimate data and I always fought product on soft deletion. Users are adults, and when part of their job is being careful with the data _they_ manage and _they_ are legally respo…
So users have been taught that the term "delete" means "move somewhere out of my sight". If you design a UI and make "delete" mean something completely different from what everyone already understands it to mean, the problem is you, not the user.