Interview question. A database modifies pages in memory, records changes in a log, and writes data pages later. Its API returned success for a transaction. The process then crashes. In a second test, the entire machine loses power. Explain what must be true at the success boundary, what recovery reconstructs, and why the two tests need not give the same result.
Assume a local database using write-ahead logging, a correctly configured filesystem and storage device, and no synchronous replica in another failure domain. This is a question about a local commit guarantee, not regional failover.
Think first
A database acknowledges transactions after the WAL is written into the operating system buffer cache, but before it is synchronized. A process kill test passes. A machine power-cut test sometimes loses acknowledged rows. Explain why, then design a correct local acknowledgment boundary and recovery test.
Take a few minutes to form an approach, then compare it with the worked explanation.
Reveal the worked explanation
Success is a statement about a boundary
A developer sees this sequence:
UPDATE inventory SET available = available - 1 ...
COMMIT
success
They assume every modified database page must already be on disk. That is not necessary. If every transaction had to force all its table and index pages to permanent storage before replying, many small transactions would cause substantial random I/O.
Instead, a write-ahead-log engine records enough information to recover committed changes. Its central ordering rule is that the required log records reach stable storage before the corresponding modified data pages may safely reach disk. PostgreSQL documents this write-ahead rule and the use of redo after a crash. 1
Imagine that an inventory transaction changes a data page in a buffer pool. It also creates log records describing the change and a commit decision. The data page can remain dirty in memory after the reply, provided recovery can replay the durable log into the database files later.
The durable log boundary and data pages that can be flushed after commit
The crucial interview question is not “is WAL enabled?” It is what had actually become durable before the client received success?
Walk through three failure points
First, the process dies before the commit decision becomes durable. Recovery must not expose a committed outcome merely because a data buffer had been modified. Depending on the database's transaction and recovery protocol, incomplete work is discarded or undone.
Second, the log records and commit decision are durably flushed, the client receives success, and the process dies before the dirty data page is written. Recovery reads the last valid checkpoint and replays the necessary WAL records. The accepted inventory change reappears even though the pre-crash data file might still contain the older page. PostgreSQL calls this REDO or roll-forward recovery. 1
Third, the data page is being written when power disappears. A page write can be torn, with only part of the page reaching stable storage. WAL ordering by itself does not mean every damaged page image can be understood. PostgreSQL uses full-page images in WAL when configured for that protection, along with recovery rules for restoring such pages. 2
This last point matters when a design says “just append a log.” Crash safety is a protocol involving log contents, ordering, durable metadata, page integrity, and recovery. A checksum may detect bad bytes but cannot reconstruct missing ones without another valid representation.
A process crash is not a power loss
A process crash may leave operating-system buffers intact. A whole-machine outage can erase volatile operating-system and device caches.
A successful write() to a file does not generally mean its bytes are on nonvolatile media. The database's sync operation, such as fsync or its platform-specific equivalent, is part of enforcing the intended persistence contract. Even then, the storage stack must honor flush requests. PostgreSQL's reliability documentation discusses the multiple caching layers and assumptions involved. 2
Do not generalize one database's acknowledgment behavior to every engine. In RocksDB, the default WAL path protects against a process crash differently from the stronger power-loss guarantee obtained with a synchronized WAL write. Its official recovery discussion separates those cases. 3
In an interview, I would explicitly ask whether “committed” means visible in the process, written to the operating system, durably flushed on one machine, or durably recorded across multiple failure domains. Those are not interchangeable milestones.
What about group commit?
Imagine 500 transactions arriving in a short interval. If each needs a durable WAL flush, the database can sometimes batch their log records and let one flush satisfy many commits. That is group commit. It reduces repeated sync overhead without necessarily weakening the durability contract for transactions whose acknowledgments wait for the shared durable flush.
Asynchronous commit is a different decision. It may reply before the necessary flush, accepting that an outage could lose some recently acknowledged transactions. PostgreSQL documents this distinction in its synchronous commit settings. 4
If the product requires zero loss of acknowledged orders after machine failure, relaxing the acknowledgment boundary to improve benchmark throughput is not a harmless tuning change. A fast average commit is useful only if the system still meets the requested failure guarantee.
What I would measure and test
I would collect log insertion, write, and flush positions where supported, log sync latency, transaction commit latency, dirty-page backlog, checkpoint duration, and restart recovery duration. I would verify the storage device's flush behavior and run controlled crash testing at relevant persistence boundaries. A process kill test alone would not prove survival of a machine power cut.
For a replicated database, I would separately ask which copies contain the acknowledged commit. A transaction durably present on one primary may still disappear from a promoted asynchronous standby. A safe failover policy must establish which replacement contains the acknowledged transaction; a local WAL alone does not provide that evidence.
The local durability protocol now explains why a database can modify pages without flushing every page at commit. But how are those pages organized so that a point lookup can find one row among millions of entries?
What actually happens when a B-tree leaf runs out of room?
Go deeper
Recovery requires a durable starting point
A checkpoint records enough information to avoid reconstructing all history from the beginning. That does not imply the checkpoint contains every newer committed data page. The recovery protocol combines checkpoint state with subsequent records, observing transaction commit boundaries. Log truncation must not discard records still needed by recovery or replicas.
PostgreSQL can record a complete image of a page the first time it is changed after a checkpoint, which protects against a torn write of that page when full-page writes are enabled. This increases WAL volume. The tradeoff is between additional durable log bytes and recoverability, not merely CPU work.
Not every storage engine uses an identical WAL design. SQLite's rollback-journal mode illustrates atomic commit by preserving old page content for rollback rather than using SQLite's optional WAL mode. That is a concrete counterexample to the claim that every serious database must use write-ahead logging. 5
A log on a separate physical device can reduce contention, but the database still needs the correct write ordering and metadata durability. Losing the log device may make recent dirty pages unrecoverable, regardless of whether the table device survived. Separate placement changes failure domains and performance, not the definition of a committed transaction.
Check your reasoning
Distinguish dirty data pages, WAL creation, operating-system writes, and durable WAL flush.
Explain why a process kill can appear safe even when a machine power loss is unsafe.
Require the appropriate durable log boundary before acknowledging under a strict contract.
Describe crash recovery from checkpoints and valid committed WAL, including torn-page protection.
Distinguish group commit from asynchronous commit and local persistence from cross-region durability.
Common mistake: Saying WAL enabled means power-loss durability, or that all table pages must be flushed before every COMMIT.
Read the answer explanation
Yes, if every acknowledged commit is covered by the same completed durable flush. — Group commit can amortize the cost of one WAL sync across several transactions, with acknowledgments after the durable log boundary.
Apply it elsewhere
A locally durable payment decision disappears after an asynchronous standby is promoted. Was the local WAL broken?
Compare your approach
No. The local commit could have been correct while the promoted replica lacked the accepted transaction. A regional guarantee requires replication and promotion rules beyond local crash recovery.
One more constraint
The WAL device fails but the table device survives. Which committed changes can you still prove?
Quick revision
A conventional WAL database can acknowledge a committed transaction before flushing its modified table pages, because it has durably recorded the information needed to redo that work. I would distinguish the log insertion, write, durable flush, and later page flush boundaries. A process crash and power loss are different tests because volatile OS and device caches may survive only the former. On recovery, the engine starts from a valid checkpoint and replays required log records while preserving transaction outcomes. Torn pages need additional recovery protection. Group commit shares durable flush work without inherently relaxing the acknowledgment rule. Asynchronous commit explicitly changes that rule. Local WAL durability does not promise survival of losing an asynchronously replicated primary region.
