The Staff Engineer Storage Engine Interview: WAL, Checkpoints, and Compaction

A database reports that a write succeeded. A moment later, its host loses power. When the service returns, is the write still there? A storage-engine interview turns that small sequence into an architectural argument: what became durable, which files can be reconstructed, and how much unfinished maintenance the system can tolerate.

For senior and staff engineers, the useful skill is tracing a promise across memory, operating-system buffers, persistent storage, and recovery. This guide uses PostgreSQL, SQLite, and RocksDB documentation to explain concrete mechanisms. They are different engines with different protocols; their terminology is not interchangeable. The design exercise and practice rubric below are illustrative preparation tools, not a company's hiring criteria.

1. Define the failure behind “durable”

Begin with a small service: an ordered key-value store supporting puts, gets, deletes, and range scans. Ask whether a successful response must survive a process crash, an operating-system crash, loss of power, or permanent loss of the storage device. Also clarify atomic batches, concurrent readers, storage limits, and acceptable restart time. These choices determine what evidence you need before acknowledging a request.

PostgreSQL's reliability documentation follows writes through operating-system, controller, and device caches. A durable design depends on that stack honoring synchronization requests. A successful write system call alone does not establish persistence through power loss. Device failure is a separate boundary: a correct local write protocol cannot recover bytes from a permanently lost disk without another copy.

Put the proposed contract on the whiteboard: after a successful synchronous write, reopening the store on intact persistent media must recover that write; a failed or timed-out request may require the caller to resolve an unknown outcome. Then identify what remains outside the contract, such as regional loss. This gives later tuning decisions a clear test: does the setting preserve the promise, or deliberately weaken it?

2. Trace the write-ahead rule before the response

The core ordering matters more than the acronym. In PostgreSQL's WAL explanation, log records describing changes reach permanent storage before the corresponding data-file changes. Recovery can redo changes whose data pages were not persisted before a crash. Commit therefore need not synchronously flush every affected table and index page. Concurrent transactions can also share a WAL synchronization, reducing the number of synchronization operations.

For the interview's simplified store, propose a versioned record format with length, operation identity, sequence number, payload, and integrity check. Explain how the format identifies a complete committed unit; do not assume that arbitrary appended bytes are a transaction. Draw the acknowledgment after the required durable boundary. If you propose grouping requests into one flush, discuss the throughput benefit alongside queueing delay, batch limits, and what happens when the flush fails.

A faster response can change the contract

RocksDB's basic operations guide distinguishes synchronous writes from its default nonsynchronous behavior. It also distinguishes disabling the WAL from leaving it enabled without synchronizing each write. Those settings do not provide the same crash guarantees. Name the exact options in an implementation discussion instead of saying that an LSM engine “always persists to the log.”

SQLite's synchronous documentation offers another precise example: in WAL mode, NORMAL can lose a committed transaction after power loss or a system crash, while FULL adds a WAL synchronization after each commit. That tradeoff may be acceptable for reconstructible data. It needs explicit product approval when the service promises to preserve acknowledged changes.

3. Rehearse recovery at each interruption point

Walk through a write that stops before log synchronization, after synchronization but before updating in-memory state, and after updating state but before responding. In your proposed protocol, say which records recovery accepts and which outcomes the client can distinguish. Separate recovery correctness from request deduplication: replaying a durable storage record and accepting a second business request are different operations.

SQLite's atomic-commit description explains a rollback-journal protocol that preserves original page content before changing the database. This is a useful counterexample to a universal “recovery replays new values” story. Establish whether the chosen engine undoes original changes, redoes logged changes, or uses another documented mechanism before borrowing its recovery vocabulary.

For the simplified engine, propose rejecting incomplete trailing records according to the documented record format and failing safely on unexpected interior corruption. Do not silently skip damaged records and claim the database is correct. Preserve diagnostic evidence, distinguish an uncommitted tail from missing committed history, and define when operator intervention or restoration is required.

Checksums detect damage; recovery needs intact information

PostgreSQL documents a separate risk: a crash during a data-page write can leave only part of the page updated. Its full_page_writes setting records a full page image on the first modification after a checkpoint, helping recovery repair that condition. A checksum can reveal damage but cannot independently reconstruct missing data. Explain which intact copy or log information supplies the repair.

4. Treat checkpoints as a recovery-work budget

A checkpoint establishes progress that lets recovery avoid replaying the entire history. In PostgreSQL's checkpoint documentation, dirty pages are written and a checkpoint record identifies the redo starting point. More frequent checkpoints can reduce crash-recovery work while increasing page-write activity and subsequent full-page-image WAL traffic. The engine spreads checkpoint writes to limit bursts of I/O.

Translate that mechanism into an operational question: what restart delay will the service tolerate after a busy hour? Measure replay under representative data and cache conditions, rather than dividing log bytes by the disk's advertised bandwidth. Recovery may involve parsing, applying changes, fetching pages, and initializing other structures. A byte count is a useful input to an experiment, not proof of recovery duration.

SQLite's WAL documentation describes another checkpoint mechanism: copying committed changes from WAL into the database file. A long-running reader can prevent checkpoint progress beyond the state it still needs. For an interview follow-up about a growing WAL, investigate reader lifetimes and checkpoint progress before merely increasing the disk limit.

Make log retirement an explicit dependency. Ask whether a log segment is still needed for local recovery, a backup workflow, replication, or another consumer. In your design, delete it only when every relevant retention requirement has advanced. “Checkpoint completed” should trigger a documented eligibility check, not an unconditional deletion of every old file.

5. Follow an LSM write into background work

Now change the design to a log-structured merge tree. Explain a write entering a memory table, a flush creating a sorted on-disk table, and compaction reorganizing sorted files. Keep the WAL's recovery role separate from the sorted tables' serving role. An in-memory write buffer and an on-disk search structure solve different parts of the problem.

RocksDB's leveled-compaction guide describes potentially overlapping files in level zero and nonoverlapping key ranges within later levels. Merging files changes the work required to serve reads and writes. Do not describe compaction as free housekeeping that happens after the important request path; it consumes resources that foreground requests also need.

The RocksDB compaction overview explains tradeoffs among compaction strategies, including write, read, and space amplification. Use workload questions to choose among them: overwrite frequency, scan needs, ingestion bursts, available memory, disk capacity, and latency goals. Avoid declaring either an LSM tree or a page-oriented tree universally faster without specifying the workload and durability settings.

State the amplification boundary in your arithmetic

Illustrative calculation: assume the service accepts 10 MB/s of logical values, and measured table-file writes are 60 MB/s under the target workload. At that boundary, table write amplification is 6×. If measured WAL writes add another 12 MB/s, those two categories total 72 MB/s before filesystem or device-internal effects. These invented numbers demonstrate accounting; they are not RocksDB benchmark results or a sizing recommendation.

Carry the example into capacity planning: the device must also support reads, temporary files, and bursts. State whether measurements count compressed bytes, deleted values, metadata, and background rewrites. Without a consistent denominator, comparing two amplification ratios can hide more than it reveals.

6. Give maintenance debt an admission policy

RocksDB's write-stall guide explains why the engine slows or stops writes when flushes or compaction cannot keep up. Triggers include accumulated memory tables, too many level-zero files, and pending compaction work. Unbounded ingestion would otherwise increase resource pressure and damage read performance or exhaust space.

In your design, expose separate signals for log synchronization, memory-table flushes, compaction backlog, stall time, free space, and foreground latency. When p99 writes rise, ask which queue grew first. Raising a stall threshold can postpone visible throttling while allowing more maintenance work to accumulate; propose it only with a reasoned capacity and recovery argument.

Define a response before storage becomes completely full: bound accepted work, reject or defer new ingestion, reserve room for required maintenance, and preserve readable committed state. Discuss whether ingestion can be replayed from an authoritative upstream source. Extra background threads may help a CPU-bound bottleneck, but propose measuring the limiting resource before increasing concurrency against an already saturated disk.

7. Keep crash recovery separate from restoration

A store that reopens correctly after a crash is not automatically protected against losing its volume. PostgreSQL's continuous-archiving documentation pairs a base backup with the required sequence of archived WAL for point-in-time recovery. The presence of some log files on the same device does not establish a restorable backup.

Keep this interview boundary brief and concrete. Identify where independent recovery material lives, what must remain mutually consistent, and how a restore is tested. Then return to the storage-engine question: which local files can be recreated, which metadata selects the current state, and which corruption causes opening to stop? This connects the low-level protocol to service continuity without substituting a disaster-recovery diagram for a crash-recovery explanation.

8. Practice one design and three adversarial follow-ups

Use an illustrative 40-minute rehearsal. Spend five minutes defining operations and failure assumptions, ten tracing write acknowledgment and recovery, ten explaining checkpoints and log retention, and ten testing sustained ingestion against compaction capacity. Use the final five to identify an experiment that could disprove your design. The schedule is a preparation aid, not a prediction of an employer's interview format.

Ask a practice partner to interrupt with three scenarios: the process dies immediately after returning success; a long reader survives an ingestion burst; and the disk approaches capacity while compaction falls behind. Answer each with an invariant, a diagnostic signal, and a bounded corrective action. If your only response is to add machines, revisit the exact local dependency that scaling is supposed to remove.

Illustrative self-review rubric: mark each explanation as missing, asserted, or demonstrated. Review five items: acknowledgment boundary, crash replay, damaged-page handling, safe log retirement, and maintenance backpressure. “Demonstrated” means you can trace a concrete interruption and explain the surviving state. This rubric measures the specificity of your practice answer; it is not validated hiring guidance and does not predict an offer.

For a local experiment, use immutable per-operation keys, keep an external ledger of acknowledged operation IDs, and inject failures at the proposed persistence boundaries. After restart, check those acknowledgments against recovered state. Include incomplete and reordered storage writes in the test plan; killing the database process alone does not reproduce every machine-failure condition. SQLite describes simulated storage failures in its atomic-commit testing documentation.

Finish with a concise recommendation tied to the assumed workload. Describe the chosen durability mode, the checkpoint or flush policy, the resource bound that triggers admission control, and the recovery test you would run before rollout. A defensible answer makes its assumptions inspectable and gives the interviewer a clear way to challenge them.