Fsync and Durability Modes

"Written" doesn't mean "durable"

Here's a subtlety that trips up a lot of people building their first storage engine: calling write() on a file does not guarantee the bytes are actually on the physical disk. The OS buffers writes in its own page cache and lazily flushes them to the storage device later, for performance. If the machine loses power between your write() call and the OS's actual flush, those bytes are gone — even though your program "wrote" them and moved on.

fsync() (and its Windows equivalent, FlushFileBuffers) is the system call that closes this gap: it blocks until the OS confirms the data is physically on the storage device, not just sitting in a buffer somewhere. This is the real mechanism behind the "D" in ACID — durability isn't free, it's a sync() call, and every sync call costs real latency (a full round trip to a physical disk, or at least to the drive's own onboard cache in the SSD case).

The tradeoff every database exposes as a knob

Because fsync is slow, and not every application needs the same durability guarantee, real databases expose the tradeoff as a tunable. SQLite's actual pragma for this is PRAGMA synchronous, and this exercise models exactly that idea with three levels:

Note the pattern: as you go from OFF → NORMAL → FULL, you sync more things, more often, trading throughput for a stronger durability guarantee. This directly connects to the Write-Ahead Log chapter — the fsync mode governs exactly when the durability promise that WAL relies on ("this write is safely logged before we call it committed") is actually backed by a real flush to storage rather than just sitting in an OS buffer.

What this exercise asks you to build

This is a simulation of the semantics, not real disk I/O — there's no actual filesystem sync happening in your REPL. What you're modeling is the state machine around the setting:

• PRAGMA sync = OFF|NORMAL|FULL → validate the mode is one of the three recognized values, store it, output OK.
• PRAGMA sync (no =) → output whatever mode is currently set, as plain text (OFF, NORMAL, or FULL).
• The default, before any PRAGMA sync = ... has been issued, is NORMAL — matching real SQLite's default.
• Every other statement — CREATE TABLE, INSERT INTO ... VALUES (...), .count, etc. — must behave exactly as in earlier lessons, printing its normal output regardless of the current sync mode. The sync setting changes an internal durability guarantee in this exercise's model, not the REPL's visible output for unrelated statements.

function pragma_sync(arg):
    if arg is None:
        return current_sync_mode
    if arg not in {"OFF", "NORMAL", "FULL"}:
        return ERR "invalid sync mode"
    current_sync_mode = arg
    return "OK"

Edge cases

• Case sensitivity — decide whether off/Off/OFF should all be accepted, or only the exact casing shown in the examples, and be consistent; the given example only ever uses uppercase (FULL), so match that unless your test suite says otherwise.
• Querying before ever setting — PRAGMA sync on a fresh database should report the default (NORMAL), not an error or empty string.
• An invalid mode (PRAGMA sync = WEIRD) — should be rejected cleanly rather than silently accepted or crashing the REPL.
• Sync mode must not leak into unrelated command output — a common mistake is accidentally prefixing or suffixing other commands' output with sync-related text; every command's output format stays exactly what it was before this lesson.