Build1 publisher2 min readPublished
Ordinary reads prune Postgres heap pages with autovacuum switched off
Franck Pachot's page-level demo disables autovacuum, updates one row eight ways, and shows hint bits, opportunistic pruning and LP_DEAD marks doing cleanup that the chain-walking model of MVCC credits to vacuum alone.
The Engineer · Build desk
What happened
- A dev.to post published under the account franckpachot uses page inspection to test the belief that Postgres reads walk lengthening version chains until VACUUM clears them.
- The demo table disables autovacuum in its own storage parameters, indexes columns a and b, and is loaded with eight rows before a vacuum analyze.
- The post makes three claims about scans: sequential scans skip invisible tuples in place, index scans follow HOT chains only within one page, and bitmap scans behave like either depending on losiness.
- It also reports that ordinary reads set hint bits and prune heap pages with autovacuum off, and that an index scan marks a dead entry LP_DEAD so later scans skip the heap visit.
Compiled by The EngineerSomething wrong?How this is made
Why it matters
- decision A team tuning autovacuum thresholds to fix read latency on an update-heavy table is acting on a different mechanism from the one described here; the number that moves scan cost in this account is pages read per query.
- cost The maintenance bill lands on whoever queries next: the first index scan pays a heap visit to discover the entry is dead, and read-mostly traffic absorbs work the writer deferred.
- constraint Read-path cleanup only reaches pages that get read, so dead tuples in cold pages sit there whatever the query volume, and only vacuum removes them.
- capability Turning autovacuum off for a table during a bulk update window still leaves space reuse available in the pages the workload actually touches.
Seven of the eight rows landed on page 0, and the eighth sat alone on page 1 [10]. Each row carries a filler column defaulting to repeat('x',1000), so roughly a kilobyte of text per row [8]. Seven of those filled the page. When row 1 was updated, the new version could not be written in place: the post reports it went to another page because there was no space on the same page [12], [15]. The old tuple at (0,1) kept xmin 697, took xmax 740, and its t_ctid now points at (1,2) [12].
That pointer is what the misconception is built on [1]. Pachot's account is that no scan traverses it. The two versions of row 1 sit on different pages, and an index scan follows the HOT chain only within a single page [4], [16].
The cleanup is done by whoever reads next. Hint bits and opportunistic heap pruning happen on the read path [5], so a page no query touches keeps its dead tuples until vacuum, which in this framing is background garbage collection for space [14]. The LP_DEAD skip is a saving on the second visit to an index entry, because the first visit is the one that pays the heap access to learn the entry is dead [6].
What the excerpt measures is page contents. The output is heap_page_items filtered to line pointers with lp_len>0 [13], before and after a single update statement [12]. There are no timings, no buffer counts and no query plans in it [19].
The claim the demo does support is a proportionality claim. PostgreSQL "can build up dead tuples and index entries, but read amplification doesn't increase proportionally", Pachot wrote [7]. Proportionally is the load-bearing word there. A sequential scan examines heap tuples directly and skips the invisible ones [4]. It still reads the page each dead tuple sits on [17]. So a bigger heap still means more pages for a scan to cross. The cost that does not grow one hop per update is chain traversal, so a team watching dead tuple count and complaining about latency is looking at two things connected by page count [20].
"Databases are built to read only the data they need from a larger dataset," Pachot wrote [3].
Two conditions have to hold for the mechanism to transfer to a table you care about. The pages have to be read, because the reader is the one setting hint bits and pruning [5]. And the same index entries have to be scanned more than once, because the skip only applies after a scan has already marked the entry LP_DEAD [6].
What to watch
- A follow-up with EXPLAIN (ANALYZE, BUFFERS) across a genuinely bloated table would test whether page count, not chain length, is what moves latency.