151–157 of 157 posts
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.
Exactly, partition the table vertically by month. Surprised no one else seems to be mentioning this.
Earlier quoted context omitted.
> 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…
Well, Microsoft SQL Server has built-in Temporal Tables [1], which even take this one step further: they track all data changes, such that you can easily query them as if you were viewing them in the past. You can not only query deleted rows, but also the old versions of rows that have been updated. (In my opinion, replicating this via a `validity tstzrange` column is also often a sane approach in PostgreSQL, althoug…
This has, at least with current MariaDB versions, the annoying property that you really cannot ever again modify the history without rewriting the whole table, which becomes a major pain in the ass if you ever need schema changes and history items block those.
Maria still has to find some proper balance here between change safety and developer experience.
Earlier quoted context omitted.
Soft deletes in banking are just a Band-Aid to the much bigger problem of auditability. You may keep the original record by soft deleting it, but if you don't take care of amends, you will still lose auditability. The correct way is to use EventSourcing, with each change to an otherwise immutable state being recorded as an Event, including a Delete (both of an Event and the Object). This is even more problematic from…
Isn't this, essentially, backing into double-entry accounting for all things banking? Which, fair, it makes sense.
Earlier quoted context omitted.
Soft deletes in banking are just a Band-Aid to the much bigger problem of auditability. You may keep the original record by soft deleting it, but if you don't take care of amends, you will still lose auditability. The correct way is to use EventSourcing, with each change to an otherwise immutable state being recorded as an Event, including a Delete (both of an Event and the Object). This is even more problematic from…
> The correct way is to use EventSourcing, with each change to an otherwise immutable state being recorded as an Event, including a Delete (both of an Event and the Object). Another great (and older) approach is adding temporal information do your traditional database, which gives immutability without the eventual consistency headaches that normally comes with event sourcing. Temporal SQL has their own set of challen…
Earlier quoted context omitted.
Isn't this, essentially, backing into double-entry accounting for all things banking? Which, fair, it makes sense.
Good analogy, double-entry book keeping, generalized. (Nothing specific to banking btw)
At Firezone we started with soft-deletes thinking it might be useful for an audit / compliance log and quickly ran into each of the problems described in this article. The real issue for us was migrations - having to maintain structure of deleted data alongside live data just didn't make sense, and undermined the point of an immutable audit trail. We've switched to CDC using Postgres which emits into another (non-rep…
In simple projects where database is only changed via an API, we just audit the API instead. It's easier to display and easier to store than tracking each DB change a single transaction does
But what happens if you need to manually update a record?
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 think this is likely unnecessary for most use cases and is mostly a RAM saving measure, but could help in some cases.