On 2026-07-29, Andres Freund wrote:
> I don't believe this algorithm will actually work well even without
> concurrent bulk access [...] the likelihood of repeat accesses to the
> same index page won't be high enough to keep those pages warm.
Andres,
Me again. I like testable predictions, so I tested it. :)
I'll keep this to just this one question: under real cache pressure, with the
index resident and the heap much larger than shared_buffers, does the
HOT/COOL+batching fail to protect index pages relative to today's 0..5 clock?
I hope I got that objection correctly framed, otherwise my tests might be
invalid. I'm sure you'll (bluntly) let me know if I have. ;-)
Setup:
- i4i.metal (128 vCPU, 2 NUMA nodes), data on local NVMe RAID0.
- shared_buffers = 4GB, huge_pages on.
- pgbench scale 2000: pgbench_accounts heap = 25GB (6.25x s_b),
primary key index = 4.3GB (~1.1x s_b). So the index does not fit
with room to spare; its leaf pages genuinely compete for the pool
and do get evicted.
As you pointed out, a tiny always-resident index would prove
nothing.
- 96 clients, 10-minute measured runs, autovacuum off.
- Two access distributions:
a) uniform pgbench -S (the case you named)
b) a zipfian point lookup (s=1.2) so the hot subset of rows is
re-read often.
- I compare stock master against the two-patch series, both built
identically, with a small throwaway instrument patch that counts
evictions per relfilenode.
- Using that I classified each evicted relation as heap or index
via pg_class. The metric is the share of evictions that fall on
index pages. If the HOT/COOL+batch protects index pages worse
than stock, it should evict them at a higher share (right?).
Results (I had an LLM format this table):
index heap-evict index-evict
workload build evict % (buffers) (buffers)
------------------ ----- ------- ----------- -----------
uniform -S stock 38.41% 616,467,240 384,451,330
uniform -S bcs 35.06% 639,240,582 345,178,854
zipfian s=1.2 stock 30.17% 12,266,976 5,299,490
zipfian s=1.2 bcs 29.28% 11,665,582 4,828,986
Change in index-eviction share (bcs - stock):
uniform -3.35 pt
zipfian -0.89 pt
Throughput, same runs:
uniform stock 1.126M tps -> bcs 1.309M tps (+16%)
zipfian stock 1.153M tps -> bcs 1.305M tps (+13%)
So the proposed new algorithm (HOT/COOL+batch) evicts index pages at a slightly
lower rate than the existing one (0..5 clock) not a higher one which refutes
your claim in this particular benchmark. That results in higher throughput
with these two patches for that workload, I'll take that win.
I'm guessing that a primary-key leaf page backs on the order of a few hundred
heap rows, so any given leaf is re-touched far more often per-unit time than
any single heap page. Under COOL admission that reuse arrives well before the
leaf drains out of the COOL stage, so the leaf promotes to HOT and survives.
I don't disagree with the promotion race you described, I think it is real.
However, in this workload the index-leaf reuse rate simply clears the bar.
I've not added any form of "ghost directory" which could potentially improve
this even more, to your point. A page whose second access lands after it has
already been evicted from COOL is gone with no way to learn/remember that. Or,
it may turn out that the maintenance of a ghost directory could outweigh the
benefits I can't say from this one benchmark.
I don't know if S3-FIFO is a better approach than the one I propose, no data
yet. To say it is "trivially going to fail" is a statement worth testing and
my next target for the next email on this thread. My bet is that it won't, but
the only way to find out (and convince you and others that this approach is
worth consideration) is to try it out and post numbers.
If I missed something or you'd like the test adjusted let me know and I'll give
it a whirl.
best.
-greg
