Postgres in depth · Chapter 45 of 65
Never lose a payment
By the end of this chapter you can explain how Postgres keeps a committed payment safe through a crash, read the write-ahead log with pg_walinspect, and say where crash recovery starts. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.
- Part: Inside Postgres
- 4 tasks
- PostgreSQL
- In the all labs plan
The first 2 chapters of Postgres in depth are free. This one comes with the all labs plan.
Why you need this
Sana has a refund dispute: the payment gateway says a payment of 1,999 rupees was captured, and LCO's server rebooted a second later. Did the payment survive? Priya wants a real answer, not "probably". So far you know that a row lives in an 8 KB page of a table file (chapter 42). But Postgres does not write that page to disk when you commit: it keeps it in memory and writes it minutes later. So what makes the commit safe?
What you build: 4 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
lsn-math.sql: LSN arithmetic
Sana's payment gateway logged the database position (LSN) 0/1A2B3C4D next to a payment it is unsure about. Return one row: the WAL file that holds that position (on the lab's timeline 1), how many bytes into that file it is, and how many bytes of WAL lie between 0/19000000 and 0/1A2B3C4D.
Return three columns: wal_file, offset_bytes and bytes_after. Let Postgres work all three out from the two LSNs.
- Task 2
wal-records.sql: the records behind 50 refunds
The Check builds refund_log and wal_marks exactly as the lesson's last code block does: 50 refunds inserted, 10 of them updated, 5 deleted, and the WAL insert position saved after each step (labels start, inserted, updated, deleted).
Between the marks start and deleted, count the WAL records that touched the table refund_log or its index refund_log_pkey, per resource manager and record type.
Return three columns: resource_manager, record_type and records. Order by records, most first, then by record_type.
Careful: the WAL holds every database's changes, so keep only the blocks of those two files (by their file node), and count a record that touched two blocks once.
- Task 3
wal-phases.sql: what each step cost in WAL
With the same wal_marks, measure each step on its own: from start to inserted, from inserted to updated, and from updated to deleted. For each step return the label of the mark that ends it, the number of WAL records that touched refund_log or refund_log_pkey in that range, and the sum of their record lengths in bytes.
Return three columns: phase, records and wal_bytes, one row per step, in WAL order (inserted, updated, deleted).
Careful: a record that touched two blocks appears twice in the block list; count it, and its length, once.
- Task 4 · interview question
Interview: recovery.sql, where would recovery start?
"If the server crashed right now, where would crash recovery begin, and how much WAL would it replay?" Return one row: the WAL file that holds the last checkpoint's redo point, and the number of bytes from that redo point to the current WAL insert position.
Return two columns: redo_wal_file and replay_bytes.
Careful: recovery starts at the redo point, not at the checkpoint record, and both numbers change all the time, so ask Postgres.
What you will learn
- The write-ahead log (WAL): every change is written to a log first, and the log is what makes a commit durable.
- LSNs: positions in the WAL, and the arithmetic on them.
- pg_walinspect: reading the WAL records your own SQL wrote.
- Checkpoints and crash recovery: the redo point, and replaying the WAL.
- synchronous_commit and fsync: what the lab switches off, and what production must keep on.
More in Inside Postgres
Build "Never lose a payment" yourself
LCO, an edtech company, moves its platform onto a single Postgres server you run. A real editor and terminal in your browser, AI that explains, and a Check that says when it works.
The first 2 chapters of Postgres in depth are free. This one comes with the all labs plan.