Postgres in depth · Chapter 43 of 65
Why UPDATE writes a new row
By the end of this chapter you can read a row's version history on its page, explain how a snapshot decides which version a query sees, and make frequent updates cheap with HOT and fillfactor. Build it in your browser: a real editor and terminal, AI that explains, and tests that tell you when it works.
- Part: Safety, security and storage
- 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
The video player sends a heartbeat every 30 seconds: "this student is at minute 12 of this lesson". Kabir stored it in one small row per student and lesson, updated in place, so the table should stay small. Instead Priya watches it grow every hour, and its index grows faster.
Chapter 38 promised that readers never wait for writers, and chapter 42 showed that an UPDATE moved React from Zero from (0,3) to (0,25). Both come from one design decision, MVCC (multi-version concurrency control): Postgres never changes a row in place. It writes a new version.
What you build: 4 tasks
Each task has tests. You write the code in the lab's editor and press Check.
- Task 1
versions.sql: two versions of one coupon
Change WELCOME20's percent_off to 25, then, in the same file, list every line pointer on page 0 of coupons as the page holds it after the update.
Return four columns: lp, t_xmin, t_xmax and t_ctid, from pageinspect. Order: by lp.
You should see one more tuple than coupons has rows: the old WELCOME20 version is still on the page, with an xmax and a t_ctid pointing at the new one.
- Task 2
same-value.sql: an update that changes nothing
Set STUDENT10's percent_off to the value it already has, and show that its row moved anyway.
Return one row with two columns: old_ctid (STUDENT10's ctid before the update) and new_ctid (its ctid after it), from a single statement.
- Task 3
hot.sql: make the heartbeats HOT
video_heartbeats (the lesson makes it) gets an update every 30 seconds per student: position_seconds and updated_at change. Today none of those updates is HOT. Change the table so that they all are.
Keep its rows and its primary key. Set its fillfactor to 70, so 30% of every page stays free for new versions. The Check then updates every fifth heartbeat and counts the HOT updates: all of them must be.
Careful: two things stop HOT here, and setting fillfactor alone changes no existing page.
- Task 4 · interview question
Interview: in-flight.sql, a visible row with an xmax
Another session has started changing HOLI30 and has not committed. Return HOLI30 as your session sees it, with four columns: code, percent_off, xmin and xmax.
The Check runs your file while the other transaction is open. You should see the old percent_off, and an xmax that is not zero: the id of the transaction that is replacing this version.
What you will learn
- Tuple versions: what UPDATE and DELETE really write.
- xmin and xmax: the two transaction ids in every tuple.
- Snapshots: how a query decides which version it sees.
- HOT updates: the cheap kind of update, and when you get it.
- fillfactor: leaving room on each page for new versions.
More in Safety, security and storage
Build "Why UPDATE writes a new row" 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.