An API can return success after a database transaction commits, but that does not mean every modified data page has already been written to its final location on disk.
A simplified view is:
Transaction → WAL → Memory/Data Pages → Disk
1. Transaction
A transaction represents a logical unit of work.
For example:
Create an order and reduce inventory.
The database ensures the transaction either commits according to its rules or is rolled back.
2. WAL
WAL (Write-Ahead Log) records database changes before the corresponding data pages are written to their final storage locations.
The WAL allows the database to recover committed changes after a crash.
This is a key part of database durability.
3. Data Pages
Databases generally organize table and index data into pages.
When a row changes, the database can modify the relevant page in memory and write the modified page to storage later.
This avoids requiring every individual row change to become an immediate random disk write.
Why This Design Matters
Imagine an application processing thousands of writes per second.
Writing every modified data page synchronously and immediately could create significant latency and storage pressure.
WAL allows the database to make durable transaction progress while data pages can be flushed separately.
After a crash, the database can use the WAL during recovery to bring data pages to the correct state.
The Important Trade-Off
More aggressive flushing can reduce recovery work but may increase I/O and latency.
Less frequent page flushing can improve write efficiency, but recovery may need to process more WAL after a failure.
The exact behavior depends on the database engine, storage system, and durability configuration.
Common Misconception
"Commit successful means every changed row is already sitting in its final disk location."
Not necessarily.
A commit primarily establishes the transaction's durability according to the database's configured guarantees. WAL, cached data pages, and later page flushing are separate parts of the storage process.
Key Takeaway
A database commit is not simply "write the row to disk."
WAL, memory, data pages, and storage work together to provide both durability and performance.
Share your perspective on how databases move data from memory to durable storage in the comments.
