131–140 of 157 posts
Of course, in a system with 1000s of tables, I would not likely do this. But for simpler systems, it's been quite a boon.
I just long for DBs to evolve from "stateful" to "stateless". CQRS at the DB level. * All inserts into append only tables. ("UserCreatedByEnrollment", "UserDeletedBySupport" instead of INSERT vs UPDATE on a stateful CRUD table) * Declare views on these tables in the DB that present the data you want to query -- including automatically maintained materialized indices on multiple columns resulting from joins. So your "…
I just long for DBs to evolve from "stateful" to "stateless". CQRS at the DB level. * All inserts into append only tables. ("UserCreatedByEnrollment", "UserDeletedBySupport" instead of INSERT vs UPDATE on a stateful CRUD table) * Declare views on these tables in the DB that present the data you want to query -- including automatically maintained materialized indices on multiple columns resulting from joins. So your "…
This is just… event sourcing? https://martinfowler.com/eaaDev/EventSourcing.html
My point is that event sourcing would have been a lot less painful if popular DBs had builtin support for it in the way I describe.
If you go with event sourcing today you end up with having to do a lot of things that the DB could have been able to handle automatically, but there's an abstraction mismatch.
(I've worked with 3-4 different strategies for doing event sourcing in SQL DBs in my career)
Earlier quoted context omitted.
> 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…
Right, but I think that the Recycle Bin is exactly what is causing the issue here. Users have been taught for decades that if they delete something, it is not really gone, as they can always just go back to their Recycle Bin or Deleted Items folder and restore it. (I have worked with clients that used the Deleted Items folder in Outlook as an archive for certain conversations, and would regularly reference it.) So us…
There are stories all over the internet involving people who leave stuff in their recycle bin or deleted items and then are shocked when it eventually gets purged due to settings or disk space limits or antivirus activity or whatever.
Storing things you care about in the trash is stupid behavior and I hope most of these people learned their lessons after the one time. But recycle bin behavior is beneficial to a much larger set of people, because accidental deletion is common, especially for bulk actions. “Select all these blurry photos, Delete, Confirm, Oh, no! I accidentally deleted the last picture of my Grandma!”
Recycle bin behavior can also make deletion smoother because it allows a platform to skip the Confirm step since it’s reversible.
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…
> I think we largely need support for "soft deletes" to be baked into SQL I think web and GUI programmers must stop expeting the database to contain the data already selected and formatted for their nice page.
So a widespread, common and valid practice shouldn't be made better supported and instead should rely on awkward hacks like "deleted_at" where sooner or later people or ORMs will forget about those semantics and will select the wrong thing? I don't think I agree. I also don't think that it has much to do with how or where you represent the data. Temporal tables already do something similar, just with slightly different semantics.
Earlier quoted context omitted.
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…
What you describe is basically event sourcing, which is definitely popular. However, for OLAP, you will still want a copy of your data that only has the actual dimensions of interest, and not their history - and the easiest way to create that copy and to keep it in sync with your events is via triggers.
Earlier quoted context omitted.
Datomic can use various storage services. Yes, pg is one option, but you can have DynamoDB, Cassandra, SQLServer and probably more. > Also, it doesn't support non-immutable use cases AFAIK What do you mean? It's append only but you can have CRUD operations on it. You get a view and of the db at any point in time if you so wish, but can support any CRUD use case. What is your concern there? It will work well if you're…
Also good real-world use case talk: https://www.youtube.com/watch?v=A3yR4OlEBCA
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…
The core system at my previous employer (an insurance company) worked along the lines of the solution you outline at the end: each table is an append only log of point in time information about some object. So the current state is in the row with the highest timestamp, and all previous stars can be observed with appropriate filters. It’s a really powerful approach.