Welcome to Grind Engineer , your guide to becoming a better engineer!
No fluff. Pure engineering insights.
Have you launched your creator affiliate program this holiday season? Don't miss out on building demand and landing the best partnerships. Levanta's 90-Day Holiday Sprint breaks it all down. Download the Guide.
AI Insights. Real Growth. Higher GMV, Better Profits
The difference between growing stores and stagnant ones isn't more effort. It's better insights. StoreClaw analyzes your Shopify and Amazon data, surfaces your biggest growth opportunities, and helps you increase GMV while protecting profit. Start free with bonus tokens. No credit card required.
The best voice models, now with full orchestration. Build real-time voice and chat agents on one low-latency stack: any LLM, your tools and knowledge, testing, Guardrails, and omnichannel deployment.
Pull the power cord on a Postgres box in the middle of a transaction. Boot it back up. The committed rows are all there, the half finished ones are gone, and nothing is corrupt. That works because of one append only file the database wrote to before it touched a single byte of your actual table.
The Rule Every Database Follows
The PostgreSQL 18 docs state it in one line: changes to data files must be written only after those changes have been logged. Log first. Touch the real data later.
Your rows don't sit loose on disk. They live inside a data page, a fixed size block, 8KB in Postgres and 16KB in InnoDB by default. Run an UPDATE and the database pulls that page into memory, edits it there, and marks it dirty, meaning memory and disk now disagree.
If the process died at that moment, your change would be gone. So before the transaction can report success, the database appends a small record to the write-ahead log describing what changed, then calls fsync to force it out of the OS cache onto the actual device.
The dirty page can then sit in memory for the next five minutes. Nobody cares. The log already knows what happened to it.
Appending Beats Updating In Place
A commit that flushed every modified page would be brutal. One small transaction can dirty a heap page, an index page, and a page holding a foreign key target, all in different places on disk. That's three scattered writes and three syncs for one row.
The WAL collapses all of that into one sequential append.
The WAL file is written sequentially, so the cost of syncing it is much less than flushing data pages. Under load, one fsync of the WAL can commit many transactions at once. (PostgreSQL 18 docs)
On spinning disks the win was mechanical. The head never had to move. On flash there's no head to move at all, and the win holds anyway. An SSD erases in large blocks, so a scattered 8KB page write turns into a read, modify, write cycle inside the controller and burns endurance doing it.
A steady stream of appends to the tail of one file is the friendliest access pattern you can hand a flash controller. Postgres writes that stream into 16MB segment files. RocksDB, etcd and Kafka all lean on the same shape.
What Recovery Actually Replays
Crash recovery is dumber than most engineers expect, and that's the whole trick.
On startup the database finds the most recent checkpoint record in the log. That record marks the redo point: everything logged before it is guaranteed to already be sitting in the data files. Recovery starts there and walks forward, reapplying every record it finds.
Postgres calls this roll forward recovery, or REDO. InnoDB does the same during initialization, starting from the checkpoint LSN in the redo log, and it finishes before the server accepts a single connection. Transactions that never committed get rolled back afterwards.
💡 Key Insight: A database doesn't keep your data safe by writing it carefully. It keeps your data safe by writing down what it's about to do, then doing it carelessly.
There's a nastier failure the log also covers. If the machine dies partway through writing an 8KB page, you get a torn page: half old bytes, half new. Replaying a delta onto a torn page produces garbage. Postgres solves this with full_page_writes, on by default, which copies the whole page image into the WAL the first time it's modified after each checkpoint.
If there's one Postgres setting I'd never let anyone touch on a production box, it's fsync. Turning it off makes benchmarks look great and makes your recovery story a coin flip.
Checkpointing Keeps the Log From Eating the Disk
Left alone, the log grows until the volume fills. Checkpointing is the cleanup pass.
A checkpoint flushes all dirty pages to the data files and writes a checkpoint record into the WAL. After that, every segment older than the new redo point is dead weight and gets recycled or deleted.
Postgres starts one every checkpoint_timeout seconds or when max_wal_size is about to be exceeded, whichever hits first. The defaults are 5 minutes and 1GB.
The tension here is permanent and nobody escapes it. Frequent checkpoints keep recovery fast and the log small, but they hammer the disk with page flushes. Rare checkpoints are cheap while things are running and expensive the moment you restart.
InnoDB shows the same tradeoff from the other end. It keeps around 32 redo files that together add up to innodb_redo_log_capacity, and a full redo log forces a checkpoint whether you wanted one or not.
Small redo log files cause many unnecessary disk writes. (MySQL 8.4 Reference Manual)
SQLite ships the most readable version. In WAL mode it checkpoints automatically once the WAL file crosses 1000 pages, moving those transactions back into the main database file.
One Log, Three Jobs
Everything so far is one machine surviving a bad night. Crash recovery doesn't own that log, though. Two other systems you probably already run read the exact same records.
A Postgres standby doesn't ask the primary for rows. It opens a TCP connection and receives WAL records as they're generated, then applies them locally. The docs put the lag under one second when the standby can keep up. Streaming replication is the crash recovery loop, run continuously, on a different box.
Point in time recovery is the same idea aimed at a clock. Take a base backup, archive the WAL, replay it forward, and stop wherever you want. The Postgres docs are clear that you can halt the replay at any point and hold a consistent snapshot of the database as it was at that instant. You name the stopping point with recovery_target_time.
Then there's change data capture, and this is where a database internals story turns into an architecture one. Logical decoding turns those same WAL records into row level change events, and an external consumer subscribes to them through a replication slot. Debezium's Postgres connector reads exactly that pipe.
Look at what that deletes from your system. No polling loop scraping a table every second. No outbox table someone forgets to write to. Your database already recorded every change it made, in order, because it had no choice. Subscribing to that log is the cheapest change stream you'll ever get, and most teams build a second one by hand without checking.
MySQL Splits the Job Across Two Logs
MySQL is where the tidy version of this story gets a wrinkle. Learn it before your next system design interview.
InnoDB's redo log handles crash recovery only. It's physical, it records page level changes, it's circular, and it gets truncated as checkpoints advance. It never leaves the server.
The binary log is a separate file recording logical events that describe what changed. Replicas consume it, and you replay it over a restored backup for point in time recovery. It's on by default in MySQL 8.4.
Two files, two jobs. MySQL ties them together with two phase commit on every transaction so the binlog can never advertise a change InnoDB rolled back. I've watched good engineers conflate the two in interviews and lose the thread. Knowing they're separate is a cheap way to sound like you've actually run the thing.
Takeaways
When any system claims durability, ask what it fsyncs and when. Postgres reports a commit once the WAL record is on disk, not once your row reaches the table. Every durability guarantee you've read resolves down to that one question.
Checkpoint frequency is a recovery time dial, not a throughput dial. Stretching max_wal_size buys you smoother writes today and charges you the difference during your next unplanned restart.
Before you build an outbox table or a polling job to ship changes out of your database, check whether the WAL already carries them. Postgres logical decoding and MySQL binlog CDC exist so you don't have to invent a second write path that can silently drift from the first.
Sources:
→ Find me on : Social Links
That’s it for today, keep learning!
Scortier, Signing Off!