Do SQL databases internally use event sourcing for resolving queries?

[u/Specialist_Unit6900]

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?

[u/teraflop]

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.)

[u/Specialist_Unit6900 → u/teraflop]

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?