Layer 3 · Go deeper · 8 min
Go deeper
Size it, build it, then make the calls an interviewer will push you on.
Back of the envelope
Size it up
Tap through each step of the arithmetic.
How far can InnoDB’s checkpoint lag behind with the default redo log?
Build-up · 5 steps
Crash recovery, one LSN at a time
Five log records, two pages on disk, then a crash. Recovery needs no memory of what happened before; comparing LSNs is enough.
Step 1 of 5
The log at the crash
T1 changed pages 7 and 9 and committed at LSN 340. T2 changed pages 9 and 7 but never committed. The crash lands just after LSN 420.
4 interview moments
You’re the candidate
The interviewer pushes on the hard parts. Pick an approach and see how it holds up.
Deep dive 1 of 4
Interviewer
“Crash recovery on your PostgreSQL primary should take about a minute. How do you set checkpoints?”
Illustrative workload: 10 MB/s of WAL, 50,000 hot pages changed per interval, replay at 100 MB/s.
Your move. Pick an approach:
Deep dive 2 of 4
Interviewer
“You retired a CDC pipeline last month. Now the primary’s pg_wal disk is nearly full. What do you do?”
Your move. Pick an approach:
Deep dive 3 of 4
Interviewer
“Your archive_command ships WAL segments for point-in-time recovery. Sometimes the copy to storage fails. What should the command do?”
Your move. Pick an approach:
Deep dive 4 of 4
Interviewer
“WAL volume, and with it replica and archive bandwidth, spikes after every checkpoint. A colleague wants full_page_writes off. Agree?”
Your move. Pick an approach:
Build-up · 6 steps
One WAL, four readers
Crash recovery, a standby, the archiver and a CDC slot all read the same WAL, each from its own position.
Step 1 of 6
Segments, oldest first
PostgreSQL keeps WAL as 16 MB segment files. A segment can be recycled only once every reader with a claim on it has moved past.