Layer 3 · Go deeper · 6 min
Go deeper
Size it, build it, then make the calls an interviewer will push you on.
Build-up · 6 steps
One heap page through a plain VACUUM
Six tuples on one 8 kB page, three of them dead. Follow the phases you can watch in pg_stat_progress_vacuum.
Step 1 of 6
Three dead tuples, still in place
Updates left versions 2 and 5 behind; a delete left 3. No snapshot can see them, yet scans still read them and three index entries still point at them.
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
“Your 500-million-row payments table takes 2,000 updates a second. Autovacuum runs on default settings. What do you change?”
Your move. Pick an approach:
Deep dive 2 of 4
Interviewer
“Your Postgres table is 400 GB but holds 50 GB of data, and it serves traffic all day. What do you do?”
Your move. Pick an approach:
Deep dive 3 of 4
Interviewer
“Autovacuum runs on your orders table again and again, yet dead tuples never fall. No session is old. What next?”
Your move. Pick an approach:
Deep dive 4 of 4
Interviewer
“A hot shard hits the transaction ID limit; only read-only transactions start. Two terabyte-scale tables hold data the app can live without. What now?”
Your move. Pick an approach:
Build-up · 6 steps
The alarms as unfrozen IDs age
The age of a database’s oldest unfrozen transaction ID climbs with every write transaction. PostgreSQL 18 defaults, drawn as rungs, not to scale.
Step 1 of 6
From now to the wrap
XIDs are 32 bits, compared on a circle. A row stays in the past for about 2.1 billion transactions, then looks like the future and becomes invisible.