I heard someone say that SQL databases use event sourcing internally. That would make sense if they were referring to replaying a write-ahead log or transaction log during crash recovery, but it sounded like the event history might be replayed while resolving ordinary queries, which I wouldn't expect. Is that what was meant? How do transaction logs and event sourcing actually differ inside a database, and where could I learn more about this kind of database internals question?
1 Answer
During a normal query, the database usually reads table or index structures such as B-trees, along with any newer changes still held in memory. It doesn’t replay the entire transaction log to answer each query. For example, a database may have a modified page in its buffer cache that has also been written to the WAL; a read uses the current page in memory, not an event-by-event reconstruction from the log. Log replay is mainly needed to rebuild the database state after a failure or to support replication and similar features.
So the tables and indexes are normally the queryable state, while the log mainly protects that state and helps recover recent changes. That clears up the ambiguity.