Visualizing PostgreSQL Storage Internals
Thursday, October 22 at 09:25–10:15
Most PostgreSQL developers tune autovacuum thresholds, set fillfactor, and decide when to REINDEX without ever seeing what they are actually affecting. That gap between what you configure and what is physically there is where a lot of bad intuitions live.
PostgreSQL stores everything in 8KB pages. Each page has a 24-byte header, line pointers growing from the front, and tuples growing from the back. Seeing the layout once makes a lot of configuration decisions click in a way documentation does not quite manage.
Index pages surprise people. VACUUM removes dead index entries but does not compact the tree. Empty leaf pages stay in the structure, the file does not shrink, and that is why REINDEX CONCURRENTLY exists.
HOT updates are one of the most effective storage optimizations in PostgreSQL and most developers have never heard of them. When a new tuple lands on the same page and no indexed column changed, PostgreSQL skips the index write entirely. Fillfactor controls how much room you leave for this to happen.
MVCC sits underneath all of it. Every tuple carries t_xmin and t_xmax. An UPDATE does not modify a row, it writes a new version and stamps the old one dead. That dead tuple sits on the page until VACUUM can safely remove it. A long-running transaction blocks that from happening. Seeing the dead tuple next to the live one, in the same page, makes the whole thing land differently than reading about it.
Everything is shown live using pg-storage-visualizer. The next time autovacuum misbehaves or an index bloats unexpectedly, you will know what to look at.
