We use analytics cookies.We use cookies to analyze site traffic and improve your experience.
Loading...
System Design and AI Engineering, by Roni Das. 984 interactive lessons.
Start LearningIntermediate · Data Replication & Distribution
A technique where changes are written to a log before being applied to the database. Ensures durability and crash recovery.
In short
A write-ahead log (WAL) is a durability technique where every change is appended to a sequential on-disk log and forced to stable storage before the change is applied to the main data files. If the system crashes, it replays the log on restart to recover any committed changes that had not yet reached the data files, guaranteeing no committed write is ever lost.
In short
A write-ahead log (WAL) is a durability technique where every change is appended to a sequential on-disk log and forced to stable storage before the change is applied to the main data files. If the system crashes, it replays the log on restart to recover any committed changes that had not yet reached the data files, guaranteeing no committed write is ever lost.
Learn it in the course
This page is the free part.
₹1,111 in India$25 everywhere elseonce, for the whole course
The System Design course covers Write-Ahead Log across a run of lessons, not one page. These 4 alone are about 176 minutes of step-by-step reading, 1 of them with code you run in the browser, all with a quiz.
Write changes to a sequential log before applying them, the foundation of database crash recovery.
Postgres WAL, the foundation of crash recovery, replication, and point-in-time restore.
What does a catalog like Unity Catalog do that a folder cannot? I put 3,475,226 NYC taxi trips behind three real catalogs: an Iceberg SQL catalog, the Apache Iceberg REST catalog and Unity Catalog OSS 0.6.0. A rename sent one SQL UPDATE and moved 0 of 9 files, while moving the folder broke a reader after 1 of 10 files. A writer that crashed before its swap left a newest metadata file with 123,646 trips nobody committed. Two catalogs on one folder both accepted writes and disagreed. Unity Catalog refused a user with no grants, but the same user read every row straight from the folder.
Design a cloud file storage system - chunking, deduplication, sync protocols, conflict resolution, and efficient storage at scale.
all part of
System Design Masterclass
770 lessons · about 287 hours · 18 free to read
₹1,111in India, by UPI
$25everywhere else, by PayPal
It is really nice and good Please join the course
You own this course
Continue with Write-Ahead Loggingpay once,
yours for life
A write-ahead log is an append-only file that records the intent to change data before the change touches the actual table or page on disk. The rule it enforces is in the name: write to the log ahead of writing to the data. Postgres calls this the WAL, MySQL InnoDB calls it the redo log, SQLite calls it WAL mode, and SQL Server calls it the transaction log. They are all the same idea.
The reason it exists is that random writes to data files are slow and a crash in the middle of one leaves the file half-updated. Appending to a log is fast because it is sequential, and the log is the source of truth for what was supposed to happen. As long as the log entry made it to durable storage, the database can always finish or redo the work later.
Each entry usually describes a physical or logical change: page 4012 byte offset 80 changed from this to that, or this row was inserted into this table. Entries carry a monotonically increasing sequence number, called the LSN in Postgres and SQL Server, so recovery knows the exact order to replay them.
When a transaction modifies data, the database first writes the change into an in-memory log buffer and updates the page in the shared buffer cache. The actual data file on disk is not touched yet. When the transaction commits, the database calls fsync on the log file so the log records are physically on disk. Only after that fsync returns does the commit report success to the client.
The dirty data pages stay in memory and get written to the data files later, in the background, by a checkpoint process. A checkpoint flushes all dirty pages up to a known log position and records that position, so on recovery the database only needs to replay log entries after the last checkpoint instead of the entire history.
Recovery after a crash is a replay. The database reads the log from the last checkpoint forward, reapplies every committed change to the data files (redo), and rolls back any transaction that was in flight but never committed (undo). This is the ARIES algorithm that most relational engines follow. Because commit only depends on a sequential append plus one fsync, a write-ahead log also lets many transactions group their commits into a single disk flush, which raises throughput a lot.
You get a write-ahead log for free inside any serious database, so the real decisions are about tuning and about reusing the log. The big win beyond crash recovery is replication: the same log that recovers a crash can be streamed to replicas, which is exactly how Postgres streaming replication and MySQL binlog replication work. Change data capture tools like Debezium read the log to feed Kafka, so downstream systems see every change in order.
The cost is write amplification. Every change is written twice, once to the log and once to the data files, so a busy system does roughly double the disk writes. The commit fsync also adds latency, often a few milliseconds per commit on spinning disks, which is why group commit and fast SSDs matter. If you set the log to flush less aggressively, for example Postgres synchronous_commit off or MySQL innodb_flush_log_at_trx_commit set to 2, you trade a small window of possible data loss on a crash for much higher throughput.
Logs also need management. They grow until a checkpoint lets old segments be recycled or archived, and if archiving falls behind, the log can fill the disk and stall the database. A long-running transaction or a stuck replica can pin old log segments and cause the same problem.
Imagine a bank transfer that debits account A by 100 and credits account B by 100. Inside one transaction the database writes two log records, one for each row change, then a commit record, and fsyncs the log. At this point the data pages for A and B may still be sitting in memory only.
Now the power fails before those pages reach disk. On restart, the database finds the last checkpoint, reads forward, sees the commit record for the transfer, and redoes both row changes against the data files. The money moved exactly once and the books balance. If instead the crash happened after the debit record but before the commit record, recovery sees no commit, so it discards the partial work and account A keeps its 100. Either way the database is consistent, which is the whole point.
In SQL Server, the write-ahead log is the transaction log. Microsoft's documentation says SQL Server uses a write-ahead logging algorithm that guarantees no data change is written to disk before its log record is. Log records go to disk when a transaction commits or the log buffer fills. Each record has a log sequence number (LSN), and the log file is split into virtual log files (VLFs) that are reused once they are no longer needed. Under the full recovery model that reuse, called truncation, happens after a log backup. Without log backups, or with a transaction left open for a long time, the log keeps growing until it fills the disk.
PostgreSQL states the rule plainly: changes to data files must be written only after WAL records describing them have been flushed to permanent storage. Its WAL lives in the pg_wal folder as segment files, normally 16 MB each. The LSN is a byte position in the WAL, and comparing two LSNs tells you how far a replica or a recovery has progressed. Archiving WAL files lets you restore a backup and replay up to any moment, called point-in-time recovery. MySQL's InnoDB engine calls its write-ahead log the redo log. Since MySQL 8.0.30 it is stored in the #innodb_redo folder and its size is set by innodb_redo_log_capacity.
SQLite offers WAL as a journal mode, turned on with PRAGMA journal_mode=WAL. The original content stays in the database file and changes are appended to a separate -wal file, alongside a -shm index file. A commit is a commit record appended to the WAL, so readers keep reading the unchanged database while a writer commits. By default SQLite runs a checkpoint, copying changes back into the database file, when the WAL reaches 1000 pages.
The append-first idea also scales up into whole systems. Change data capture tools read a database's WAL to stream every change in order, and the Kafka design guide shows a broker built around an append-only log. Checkpointing explains how databases bound recovery time, and the lesson on WAL-based replication shows how replicas apply the log.
PostgreSQL
The WAL is the core durability mechanism and also the source for streaming replication and point-in-time recovery via archived WAL segments.
MySQL InnoDB
Uses a redo log for crash recovery plus a separate binlog that drives replication and tools like Debezium.
SQLite
Offers WAL journal mode so readers and a writer can run concurrently and commits are fast appends instead of rewriting the database file.
Apache Kafka
The entire broker is built as a durable append-only commit log, the same write-ahead idea promoted to be the primary storage abstraction.
Appending to a log is sequential and fast, and it gives the database a durable record of intent. A crash partway through a slow random update to a data file would leave it corrupt, but if the log entry survived, recovery can always finish or redo the change. The log, not the data file, is the source of truth at commit time.
The WAL records every change as it happens. A checkpoint is a periodic event that flushes the dirty in-memory pages to the data files and records how far the log has been applied. Checkpoints bound recovery time so you only replay log written after the last checkpoint, not the entire log history.
It adds write amplification because each change is written to both the log and the data files, and a commit normally costs one fsync. On fast SSDs this is small, and group commit batches many commits into a single flush. You can relax the flush policy for more throughput at the cost of a small data-loss window on a crash.
Because the log already contains every change in order, the database can stream those log records to replica servers that apply them to stay in sync. Postgres streaming replication ships WAL, MySQL ships its binlog, and change-data-capture tools read the same log to publish events to systems like Kafka.
Once a checkpoint confirms that all changes up to a log position are in the data files, older log segments are no longer needed for crash recovery and can be recycled or archived. If archiving or a replica falls behind, those segments must be kept, which can fill the disk and stall writes.
Yes. MySQL's InnoDB storage engine writes every change to its redo log before the changed pages reach the data files, and replays that log after a crash: write-ahead logging under another name. MySQL also keeps a binary log (binlog) of committed changes, but that one is for replication and point-in-time recovery, not crash recovery. LSM-tree engines such as RocksDB do the same: each write goes to a WAL before the in-memory table, as the LSM trees lesson shows.
In SQL Server, WAL refers to write-ahead logging through the transaction log. Every change is recorded in the log, and the log record must reach disk before the changed data page does. If the server crashes, SQL Server replays committed work from the log and rolls back unfinished transactions.
Write-ahead log means the database writes a record of each change to a log first, ahead of changing the data itself. Once the log record is safely on disk, the change can be recovered even if the data files were never updated before a crash. The name describes the order of the two writes.
PostgreSQL keeps its WAL in the pg_wal folder inside the data directory. The log is split into segment files that are normally 16 MB each. Old segments are recycled after a checkpoint unless archiving or a replica still needs them.
Run PRAGMA journal_mode=WAL; on the database. It returns wal when the switch worked, and the setting stays with the database file. SQLite then creates -wal and -shm files next to the database while it is in use.
Usually because the log cannot be truncated. Under the full recovery model, space is reused only after a log backup, so a database with no log backups grows without limit. A long-running open transaction or replication that has fallen behind also holds log space until it finishes.
The industry examples in “The next question”:
ACID
Four guarantees for database transactions: Atomicity (all or nothing), Consistency (valid states only), Isolation (no interference), Durability (changes persist).
Replication
Keeping copies of the same data on multiple servers. Improves read performance and provides fault tolerance if one server goes down.
Event Sourcing
Storing every state change as an immutable event instead of just the current state. You can rebuild any past state by replaying events.
Database Partitioning
Dividing a large table into smaller, more manageable pieces while keeping them in the same database. Sharding is partitioning across servers.
Read Replica
A copy of your database that handles read queries, reducing load on the primary database. Writes still go to the primary and replicate out.
Consistent Hashing
A hashing technique where adding or removing servers only moves a small fraction of keys. Used by Amazon DynamoDB and Cassandra for data distribution.
This page explains the idea. The "Write-Ahead Logging" lesson builds it up step by step, with diagrams. It is part of the System Design Masterclass course, which has 770 lessons. 18 of them are free to read. You pay once and keep it for life, with no subscription.
Open the Write-Ahead Logging lessoncourse 1
From absolute beginner to principal engineer, drawn step by step.
Asked about Write-Ahead Log in an interview?
Each question has why it is asked, a full answer, what the interviewer listens for, the follow-ups, the common mistakes and a diagram (268 diagrams in all). A PDF you keep, junior to staff level.
India: UPI, cards or netbanking through Razorpay. Elsewhere: card or PayPal. Download at once from My packs.
