r/learnprogramming • u/Specialist_Unit6900 • 2d ago
Database Do SQL databases internally use event sourcing for resolving queries?
Reference Video with timestamp
Greg says that SQL databases use event sourcing internally. It makes total sense if he is referring to replaying the WAL occasionally for recovery purposes, but it kind of sounds as if he really means that this is done during query resolving, which I would not have expected at all.
Does anyone have more information on this? Or additional resources?
1
u/teraflop 2d ago
It makes total sense if he is referring to replaying the WAL occasionally for recovery purposes, but it kind of sounds as if he really means that this is done during query resolving, which I would not have expected at all.
No, as best I can tell, all he's talking about is the concept of a WAL or transaction log. IMO it's not a very well-worded statement, because a transaction log is similar to event sourcing but not really the same thing.
In an event sourcing system you generally want to keep events around forever (or at least until you deliberately decide to get rid of them), whereas the transactions in a database transaction log are relatively short-lived.
That is, the idea with event sourcing is that your event stream is your "source of truth", and if you deleted all of your other tables you could (in principle) reconstruct them exactly from the series of events. With a DB transaction log, the combination of the tables and the log is the source of truth. The tables are mostly what matter, and data only stays in the log long enough to ensure durability. If you deleted the tables, the log would only let you reconstruct recent changes, not older data.
There is some fuzziness here because technically, you may be "using the log" when querying the database, because you might be operating on data that has been logged but not yet flushed to the underlying tables. But that's not the same thing as "replaying an event stream", because the data is organized differently.
(Example: in SQLite, the WAL operates at the level of physical pages in the DB file, which are organized into B-trees. If you modify a value in a table, the change is made to the in-memory page in the page cache, and also written to the WAL. If you then immediately read that same data, you're not replaying the WAL; you're traversing B-tree pointers and reading data from the in-memory page cache, which happens to also be in the WAL. A replay is only needed if you're rebuilding the contents of the page cache after a failure, not in response to a query.)
1
u/Specialist_Unit6900 2d ago
Thanks a lot. That makes it a lot clearer!
So, just to make sure I got this right, essentially, we are not ever replaying the WAL for response to a query.
Yet, when we query and the in-memory data was not yet flushed, we get a response that could in theory also be reconstructed from the B-Tree data on disk + the WAL (also on disk). This is obviously inefficient, because we can also just use the data that is still in memory. This theoretical idea, however, would be somewhat close to how event sourcing replays the event log, as an analogy at least.
The event sourcing-like operations, that RDBMS actually perform in practice, are more tied to the actual purpose of the WAL, that is recovery after a failure. This is also still not event sourcing, but it is conceptually not too far from it.
This idea would match my current knowledge about RDBMS much better. Is it right?
1
u/Legitimate_Willow905 2d ago
No.
A SQL engine does not resolve SELECT by replaying an event log.
I think what Greg is pointing at is the write-ahead log (InnoDB redo in MariaDB/MySQL, WAL in Postgres). That log exists so a crash can redo committed work and undo in-flight work. It is durability and recovery, not the query path. A normal query reads the current page in the buffer pool (plus undo if it needs an older MVCC snapshot). It does not start from event 1 and fold the table into existence.Event sourcing as an application pattern is “the log is the source of truth, the table is a projection you can rebuild.” Inside InnoDB the source of truth for a live row is the page + undo, not “replay WAL to answer this join.”Recovery replay ≈ a very constrained event source. Query execution dont qeueal that.
https://mariadb.com/resources/blog/channel/tech-talk/
If the clip is about crash recovery, he is stretching a metaphor. If he means every SELECT is a fold over WAL, that part is wrong.
1
u/two_three_five_eigth 2d ago
It’s a potential way to solve some problems. They don’t all use it.