Under every COMMIT is a small set of storage mechanisms that decide whether your data survives a power cut and how fast the database can accept writes: fixed-size pages, a memory cache of those pages (the buffer pool), and an append-only log written before the pages are (the write-ahead log, WAL). Interviewers probe this because it is where "durability" in ACID stops being a word and becomes an engineering trade-off: what exactly is on disk when the client sees "committed", and what does it cost to make that true? For a Backend or AI engineer it explains commit latency, why bulk loads should batch, why a crash does not lose committed data, why replicas can be fed from the log, and what a setting like synchronous_commit = off really gives up.
Table file = a sequence of fixed-size PAGES (blocks)
PostgreSQL 8 KB InnoDB 16 KB SQLite 4 KB (default) SQL Server 8 KB
one page (slotted layout, simplified)
+-------------------------------------------------------+
| header: page LSN, checksum, free-space pointers |
| slot 1 -> | slot 2 -> | slot 3 -> (grow downward) |
| ... free space ... |
| row 3 data | row 2 data | row 1 data (grow upward) |
+-------------------------------------------------------+
slots = small indirection table, so a row can move inside the page
without changing the address (page, slot) that indexes point at
The B+Tree index nodes are ALSO pages: each node = one page.Disks and SSDs transfer data in blocks, so the database reads and writes whole pages, never single rows. Reading one 100-byte row costs a whole-page read; that is why locality matters (rows fetched together should live on the same pages) and why a covering index or a narrow table is faster: more useful rows per page means fewer page reads. Fixed-size pages also make everything else simple: a page is the unit of caching, of locking or latching, of checksums, of logging, and of B-tree nodes. In the SQLite experiment below, 2,000 rows of about 100 bytes each occupy 57 pages of 4,096 bytes, and the database file is 233,472 bytes, exactly 57 pages.
query needs page P
|
+--------v---------+ hit: return the in-memory copy (no disk I/O)
| BUFFER POOL |
| page table: | miss: pick a victim frame (LRU / clock-style eviction)
| P -> frame 17 | if victim is DIRTY -> its WAL must already be
| frames hold | flushed, then write it to disk
| page copies | read P from disk into the frame
+--------+---------+
|
dirty page = modified in memory, not yet written to the data file
pinned page = in use by a query, cannot be evicted right now
PostgreSQL: shared_buffers + OS page cache underneath (data can be cached twice)
InnoDB: innodb_buffer_pool_size, commonly the largest memory consumerMemory is orders of magnitude faster than storage, so the database keeps recently used pages in RAM and serves reads from there. Writes also go to memory first: an UPDATE modifies the cached page and marks it dirty, and a background process writes dirty pages out later. That last point is the whole reason WAL exists: if the data page is only in RAM when you commit, a crash would lose it. A buffer pool that is too small causes constant misses and I/O; one that is too big competes with the OS cache and the connections' own memory. The Buffers: shared hit=... read=... lines in EXPLAIN (ANALYZE, BUFFERS) in PostgreSQL are literally this: pages served from the pool versus pages it had to fetch from the OS or disk.
UPDATE accounts SET balance = balance - 100 WHERE id = 7; then COMMIT
1. modify page P in the buffer pool (P is now dirty)
2. append a WAL record describing the change to the log buffer (gets an LSN)
3. COMMIT: write the log buffer to the WAL file and fsync it <-- the durability point
4. return "committed" to the client
5. LATER, in the background: write dirty page P to the data file
Rule (the "write-ahead" part):
a dirty data page may reach disk only AFTER the WAL records up to that page's LSN
are safely on disk.
Why this is fast: Why this is safe:
one sequential append + one fsync after a crash, replay the log
instead of many RANDOM page writes and rebuild whatever pages
(a txn may touch table pages, had not reached the data file
index pages, and more)The insight is separating durability from data-file layout. Making a transaction durable does not require writing every page it touched to its final location; it only requires that a compact description of the change is on stable storage, which can then redo the change if needed. The log is written sequentially, which is cheap, and a single flush covers all the pages the transaction changed. The data pages can be written lazily and in batches. The price is that the same change is eventually written twice (log, then page), plus a recovery procedure to replay the log. WAL records are generally physical or "physiological" page-level changes rather than SQL text. The same log doubles as a replication stream (PostgreSQL streaming replication ships WAL) and, together with a base backup, supports point-in-time recovery. Real SQLite output shows the effect (WAL mode, 2,000 rows inserted in one transaction):
-- SQLite (real output): database file vs its -wal file
after CREATE TABLE db= 4096 wal= 8272
after COMMIT of 2000 rows db= 4096 wal= 243112 <- all 2000 rows are only in the log
after wal_checkpoint(TRUNCATE) db= 233472 wal= 0 <- pages copied into the data fileApplication --write()--> OS page cache --fsync()--> storage device (stable media)
(survives a process crash, (survives power loss ONLY if
NOT a power loss) the device honours the flush)
COMMIT latency ~ time of one fsync of the WAL (this is why commits/sec is bounded)
Group commit: many concurrent transactions share ONE fsync
T1 commit --+
T2 commit --+--> one WAL flush --> all three acknowledged
T3 commit --+
Knobs that trade durability for speed (engine specific):
PostgreSQL synchronous_commit = on | off off: ack before the WAL flush; a crash can lose
the most recent commits (a short window),
but does NOT corrupt the database
fsync = off unsafe: can corrupt after a crash
InnoDB innodb_flush_log_at_trx_commit = 1 flush the redo log every commit (default)
= 2 write at commit, flush about once a second
= 0 write and flush about once a second
SQLite synchronous = FULL | NORMAL (in WAL mode, NORMAL can lose the latest commits
on power loss but does not corrupt the database)write() only hands bytes to the operating system's cache; only fsync() (or opening the file with a synchronous flag) forces them to stable media. So "committed" in the strict sense means the WAL fsync has returned. Every commit therefore pays roughly one device flush, and that is the ceiling on single-stream commit rate; group commit lets concurrent transactions share it, and batching many rows into one transaction amortizes it further. The relaxed settings are legitimate for data you can afford to lose a few moments of (metrics, caches, derived data) but wrong for money, orders or anything you promised the user. Also note that the whole guarantee depends on the hardware honouring flushes: consumer drives with volatile write caches and no power-loss protection, or misconfigured virtualization, can silently break it.
WAL: ... [rec][rec][rec] | CHECKPOINT | [rec][rec][rec][rec] | (crash here)
^ ^
| |
all pages dirtied before this point recovery replays from
were flushed to the data files the checkpoint forward
Checkpoint = flush dirty pages (up to a known LSN) and record "recovery can start here".
After it, older WAL can be recycled or archived.
Recovery after a crash:
1. find the last checkpoint's redo start point
2. REDO: reapply logged changes to pages that were not yet on disk
3. changes of transactions that never committed must not be visible:
InnoDB rolls them back using undo logs
PostgreSQL needs no undo pass: uncommitted row versions are simply invisible
(commit status is recorded separately) and are cleaned later by VACUUM
Frequent checkpoints Infrequent checkpoints
short recovery, little WAL long recovery, more WAL kept,
more write I/O (and, in PG, fewer writes overall, but larger
more full-page images) I/O bursts when the checkpoint runsWithout checkpoints the log would grow forever and crash recovery would have to replay everything since the database was created. A checkpoint bounds both. It is a trade-off between recovery time and steady-state I/O: PostgreSQL's checkpoint_timeout and max_wal_size and InnoDB's redo log capacity control how far apart checkpoints can be. A related hazard is the torn page: an 8 KB or 16 KB database page is written as several 4 KB or 512-byte sector writes, so a power cut can leave half old and half new data that WAL replay cannot repair by itself. PostgreSQL handles this by writing a full copy of a page to the log the first time it is modified after a checkpoint (full_page_writes); InnoDB uses a doublewrite buffer. Here a simulated crash (the process is killed with an open, uncommitted transaction) recovers correctly in SQLite:
-- SQLite (real output): kill -9 style exit with WAL pending and an open transaction
db bytes: 4096 wal bytes: 16512 <- committed rows exist only in the WAL
after reopening: [(1, 'committed-1'), (2, 'committed-2')] <- 'never-committed' is goneCOMMIT is acknowledged.fsync), not from writing all the modified data pages, which is why WAL is both safe and fast.write() reaches the OS cache only; only fsync (with hardware that honours it) reaches stable media. Commit latency is bounded by that flush, and group commit lets concurrent commits share it.synchronous_commit = off, innodb_flush_log_at_trx_commit = 2) generally risk losing the most recent commits on a crash but not corrupting data; fsync = off in PostgreSQL can corrupt.COMMIT the log is flushed and fsynced up to the transaction's commit record (group commit may batch this with other transactions); only then is the client told the transaction is committed.# Python 3 (uses the built-in sqlite3 module). Output below is real from one run.
import sqlite3, os
DB = "demo.db"
def size(p): return os.path.getsize(p) if os.path.exists(p) else 0
def show(label):
print(f"{label:30} db={size(DB):>7} wal={size(DB+'-wal'):>7}")
db = sqlite3.connect(DB, isolation_level=None)
print("page_size :", db.execute("PRAGMA page_size").fetchone()[0])
print("journal_mode :", db.execute("PRAGMA journal_mode=WAL").fetchone()[0])
print("synchronous :", db.execute("PRAGMA synchronous").fetchone()[0], "(1 = NORMAL, 2 = FULL)")
db.execute("PRAGMA wal_autocheckpoint=0") # disable auto checkpoints so we can watch
db.execute("CREATE TABLE t(id INTEGER PRIMARY KEY, payload TEXT)")
show("after CREATE TABLE")
db.execute("BEGIN")
db.executemany("INSERT INTO t(payload) VALUES (?)", [("x"*100,) for _ in range(2000)])
db.execute("COMMIT")
show("after COMMIT of 2000 rows")
print("page_count :", db.execute("PRAGMA page_count").fetchone()[0])
print("checkpoint :", db.execute("PRAGMA wal_checkpoint(TRUNCATE)").fetchone())
show("after wal_checkpoint(TRUNCATE)")
print("page_count :", db.execute("PRAGMA page_count").fetchone()[0])
db.close()
# page_size : 4096
# journal_mode : wal
# synchronous : 2 (1 = NORMAL, 2 = FULL) <- default in this build; may differ elsewhere
# after CREATE TABLE db= 4096 wal= 8272
# after COMMIT of 2000 rows db= 4096 wal= 243112
# page_count : 57
# checkpoint : (0, 0, 0) <- (busy, log frames, checkpointed frames), log truncated
# after wal_checkpoint(TRUNCATE) db= 233472 wal= 0
# page_count : 57# Simulated crash: save as crash.py, run it, then reopen the database
import sqlite3, os
db = sqlite3.connect("crash.db", isolation_level=None)
db.execute("PRAGMA journal_mode=WAL"); db.execute("PRAGMA synchronous=FULL")
db.execute("PRAGMA wal_autocheckpoint=0")
db.execute("CREATE TABLE t(id INTEGER PRIMARY KEY, v TEXT)")
db.execute("INSERT INTO t(v) VALUES ('committed-1')")
db.execute("INSERT INTO t(v) VALUES ('committed-2')")
db.execute("BEGIN"); db.execute("INSERT INTO t(v) VALUES ('never-committed')")
print("db bytes:", os.path.getsize("crash.db"), " wal bytes:", os.path.getsize("crash.db-wal"), flush=True)
os._exit(1) # no close, no checkpoint, open transaction abandoned
# python3 crash.py -> db bytes: 4096 wal bytes: 16512
# then: sqlite3 crash.db "SELECT * FROM t"
# (or sqlite3.connect('crash.db').execute('SELECT * FROM t').fetchall())
# -> [(1, 'committed-1'), (2, 'committed-2')]
# Caveat: killing a PROCESS is not a POWER FAILURE. The OS page cache survives a process
# kill, so this shows redo and rollback of the open transaction, not the effect of fsync.
-- PostgreSQL (not run here; output depends on your server, so none is shown)
-- SHOW synchronous_commit; SHOW full_page_writes; SHOW checkpoint_timeout;
-- SELECT pg_current_wal_lsn(); -- note the LSN
-- INSERT INTO t VALUES (1); SELECT pg_current_wal_lsn(); -- LSN advances with each change
-- SELECT pg_wal_lsn_diff(pg_current_wal_lsn(), '0/0'); -- total WAL bytes generated so far
-- CHECKPOINT; -- force a checkpoint (superuser)
-- EXPLAIN (ANALYZE, BUFFERS) SELECT ...; -- shared hit = pages found in the buffer poolQ: Why do databases read and write in pages rather than individual rows?
A: Storage devices transfer fixed-size blocks, so reading one row costs at least one block anyway; a page groups many rows so one I/O serves many of them. Using a fixed size also makes caching, checksumming, logging and B-tree nodes uniform. The trade-off is read amplification for point lookups on wide rows and write amplification (changing one byte rewrites a whole page eventually), and page size itself is a compromise (larger pages favour scans, smaller pages favour random access and reduce write amplification).
Q: What is the buffer pool, and what happens when a dirty page must be evicted?
A: The buffer pool is the database's in-memory cache of pages; reads are served from it on a hit and fetched from disk on a miss, and writes modify the cached page and mark it dirty. To reuse a frame the database evicts a page: if it is clean it is just dropped, but if it is dirty it must first be written to disk, and under write-ahead logging the log records up to that page's LSN must already be durable. The cost of a pool that is too small is constant misses and forced dirty-page writes on the query's critical path.
Q: What is a write-ahead log, and why does it make commits durable without writing the data pages?
A: Before a transaction commits, the database appends a description of its changes to a sequential log and flushes it to stable storage; the data pages can then be written lazily in the background. If the server crashes, recovery replays the log and reconstructs any page that never reached the data file, so committed work survives. The trade-off is that data is written twice (log, then page), you need recovery logic and a checkpoint policy, and the commit path is limited by log flush latency, but you avoid many random synchronous page writes per transaction.
Q: What does fsync do, and why does it bound commit throughput?
A: write() only places data in the operating system's cache; fsync() forces it to stable storage, and a commit is not durable until the WAL flush returns. Each commit therefore pays about one device flush, which caps single-stream commits per second, and techniques like group commit and multi-row transactions amortize it. Turning it off (synchronous_commit = off, innodb_flush_log_at_trx_commit = 2) buys throughput at the cost of possibly losing the last moments of committed transactions on a crash, which is acceptable for metrics and wrong for payments.
Q: What is a checkpoint and why do we need it?
A: A checkpoint flushes dirty pages up to a known log position and records that recovery can start from there. Without it the log would grow indefinitely and crash recovery would have to replay all of history; with it, recovery time and retained WAL are bounded. The trade-off is I/O: frequent checkpoints shorten recovery but cause more write traffic (and, in PostgreSQL, more full-page images), while infrequent checkpoints reduce total writes but lengthen recovery and produce bigger I/O bursts.
Follow-up: A page is 8 KB but the disk writes in 4 KB units and power fails mid-write. How is that handled?
A: That is a torn page: half old, half new, and log replay assumes a consistent starting page so it cannot fix it alone. PostgreSQL logs a full image of a page the first time it is modified after each checkpoint (full_page_writes), so recovery can restore a clean copy and then apply later changes; InnoDB writes pages to a doublewrite area first. The cost is extra WAL volume right after each checkpoint, which is one reason checkpoint spacing affects write throughput.
Follow-up: How does recovery treat transactions that never committed?
A: Their effects must not be visible. InnoDB rolls them back using undo logs after redoing the log. PostgreSQL does not need an undo pass because MVCC keeps old row versions and the uncommitted transaction's row versions are invisible (its commit status is never recorded as committed) until VACUUM removes them. The design difference shows up as bloat and vacuuming in PostgreSQL versus undo-log growth and purge in InnoDB.
Follow-up: If the WAL is a full record of changes, is replication or the WAL a backup?
A: Replicas fed by WAL (PostgreSQL streaming replication) give availability, but they faithfully replay a bad DELETE too, so replication is not a backup. WAL becomes part of a backup strategy only when you archive it alongside periodic base backups, which lets you restore to a point in time before the mistake. The trade-off is storage for archived WAL and the operational discipline of testing restores.
Storage internals explain several design decisions at system level. Because commit latency is one WAL flush, a write-heavy service should batch related writes into one transaction and rely on group commit under concurrency rather than committing per row, while a service that must never acknowledge an unsafe write keeps full synchronous durability and puts its latency budget elsewhere. The same log is the foundation for replication and change-data-capture: PostgreSQL logical decoding (built on the WAL) and MySQL's binary log stream row changes to read replicas, search indexes, caches and event pipelines, which is why CDC and the outbox pattern can lean on the database's own durable log instead of risky dual writes. Capacity planning follows from the buffer pool: the working set that fits in RAM is served without I/O, so a slow endpoint may simply be reading pages that no longer fit, and the fix is memory, a narrower row, or an index that improves locality. For recovery targets, checkpoint settings set the crash-recovery time (RTO) of a single node, while archived WAL plus base backups set what point in time you can restore to (RPO), and both need restore drills rather than trust.
Write-ahead logging trades doubled writes and recovery complexity for cheap, safe commits: the log makes durability a single sequential flush, at the cost of writing data twice, keeping WAL around, and tuning checkpoints. Relaxing fsync behaviour is a direct bargain of speed for a window of possible data loss; it is defensible for derived or disposable data and the wrong choice for anything a user was told is saved. A bigger buffer pool reduces I/O but competes with the OS cache and per-connection memory, and can lengthen restarts and checkpoints because more dirty pages accumulate. Larger pages help scans and lower per-page overhead but increase write amplification and cache waste for random point access. Pages, buffer pool and WAL also assume hardware honours flush commands; on storage that lies about flushes, none of the guarantees hold, so the honest engineering answer includes verifying the storage stack, not only the database configuration.