DB Caching: Where exactly?


When a change is "not yet committed" to the DB, where exactly is that info stored? Is it in some temporary table? Written to a file? Or directly in RAM?

Typically, the DBMS will put the requested changes in a kind of "queue" in memory, but also write these changes to permanent storage (disk) in the background. At the same time, it's keeping track of what has changed, in case a ROLLBACK is needed.

Having the in-memory queue minimizes blocking on each individual SQL command, yet not waiting too long before writing changes to permanent storage minimizes blocking on COMMIT1.

That's the reason why one larger transaction tends to be faster than a bunch of smaller ones - it gives the DBMS more chance to do things in the background, before being forced to block the client on COMMIT.

1 A "D" in "ACID transaction" stands for "durable", which essentially means that when transaction COMMITs, its effects are guaranteed to already be in permanent storage (and not just in volatile memory that can be lost in case of power failure or other problem).