Do SQL databases use event sourcing when processing queries?
Asked by
MellowPanda42
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?
Answers
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.
Reply to this answer
Most SQL databases don’t use event sourcing as their general query mechanism. The statement was probably referring to a write-ahead log, or WAL. A WAL records changes before they’re applied durably to the main data files, so the database can recover after a crash. That’s related to event sourcing conceptually, but it isn’t the same design.
BrightMoth7: The main distinction is that event-sourced systems usually treat the event stream as the long-term source of truth. A database transaction log is generally temporary: once the actual table pages are safely persisted and backed up, older log records can be removed.
Reply to this answer
Add your answer
Answers are reviewed before publication.
MellowPanda42: 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.