Row groups above the 32 MB fetch-cache cap have a 100 percent miss rate: a full group re-read per fetched row

[ChronicallyJD]

From an external validation report (2026-08-05, two EC2 architectures, PGDG 18.4). Their
headline finding, which I reproduced independently and which the code confirms with a
different mechanism than they proposed.

The symptom

Fetching rows through an index on a blob-heavy columnar table costs a flat per-row price
equal to a whole row-group decode, so total time is linear in rows fetched at a very
large constant. My reproduction, 500,000 rows, 9 columns, 1 KiB incompressible payload,
511 MB on disk, index path forced, plan asserted to be an Index Scan before each timing:

rows fetched	plan	total	per row
1	Index Scan	346.0 ms	346.0 ms
10	Index Scan	3,138.7 ms	313.9 ms
100	Index Scan	31,541.9 ms	315.4 ms
1,000	Index Scan	>300 s, cancelled	-

The fetches are in perfect key order, which is the friendliest possible access pattern.
Heap on the same data does a point lookup in 1.39 ms; we take 245 ms, 176x slower.

The reporter measured the same shape at a different scale: ~25 ms/row, 100k rows killed
after 33 minutes.

The mechanism, and it is not what the report said

The report concluded there is "no caching across consecutive fetches". There is a fetch
cache: static PgColumnarFetchGroup columnarFetchCache[4] (src/columnar_reader.c:1932),
keyed on (storageId, groupNumber) plus CommandId (:2078-2135). It works, and below
the cap a control table shows a flat 16-18 ms and about 6 extra buffers per row.

The real trigger is a whole-entry reset at the end of every fetch when the group's raw
stored size exceeds the cap:

/* src/columnar_reader.c:2583-2584 */
if (rg->byteLength > COLUMNAR_FETCH_CACHE_MAX_BYTES)
    pgcolumnar_fetch_entry_reset(entry);

COLUMNAR_FETCH_CACHE_MAX_BYTES is 32 MB (src/columnar.h:192). Above it the hit rate is
not merely low, it is zero by construction: every fetch populates the entry, uses it
once, and discards it. Measured on a 60,909,712-byte group in perfect key order:
+7,476 shared_hit per row, which is the whole 60 MB group re-read per fetched row.

That is a better bug than the one reported, because it is a cliff rather than a slope, and
it has a threshold to move.

Two details the report did not have:

• The test at :2583 uses the group's raw stored bytes, so a narrow projection gets no
  protection. Selecting one small column from a wide table still trips it.
• The DML path is worse. columnar_tableam.c:1186 reaches columnar_reader.c:2600 with
  allColumns = true, about 63 ms per row version fetched, which is what the reporter saw
  as UPDATE ... WHERE id % 10 = 1 running at ~5.7 ms/row on their smaller chunks.

Why no test caught it

test/native_fetch_cache.sh builds (id int, v int, t text) with repeat('a',150). That
compresses far below 32 MB, so the :2583 branch never executes anywhere in the suite.
The regime with a 100 percent miss rate is untested, which is why it survived #143, #157
and #359.

Any fix should come with a fixture whose row groups genuinely exceed the cap, and the
fixture must assert its own on-disk size. Mine did not on the first two attempts: the
payload compressed 17x and then 25x, because repeat(md5(...), 32) repeats one string and
an uncorrelated scalar subquery is evaluated once for all rows. Both would have produced a
green test that measured nothing.

Not the same as #359

#359's per-column overflow release (:2542-2550) is real and separate. This table falls
into the whole-entry carve-out that #359 deliberately left alone.

Severity

High. It is silent, it is on the ordinary index-scan path, and the shape that triggers it
(wide rows, an index, point lookups) is the shape a user reaches for when they add an index
to a columnar table. Combined with the planner issue filed alongside this, the database
chooses this path on its own.

[ChronicallyJD]

Claiming this, but the fix has a design choice I do not think is mine to make alone.

The mechanism is settled. What is not settled is what should replace it, and #359
deliberately left this carve-out, so there is a reason behind the current shape that I want
before I change it.

The constraint

/* src/columnar_reader.c:2583-2584 */
if (rg->byteLength > COLUMNAR_FETCH_CACHE_MAX_BYTES)
    pgcolumnar_fetch_entry_reset(entry);

COLUMNAR_FETCH_CACHE_MAX_BYTES is 32 MB and there are four entries, so the cache is
bounded at 128 MB per backend. That bound is the reason the reset exists: without it a
backend fetching from a 2 GB row group would hold 2 GB.

So the bug is not the cap. The bug is that exceeding it degrades to the worst possible
behaviour rather than a merely bad one: we pay the full decode, use one row, and throw it
away, every time. A cache that refused to cache at all would be no slower and would not
allocate.

Four ways out, and they are not equivalent

1. Raise the cap. Cheapest, and wrong: it moves the cliff rather than removing it, and
   the memory bound is real.
2. Cache only the projected columns. :2583 tests rg->byteLength, the group's whole
   raw size, so a query touching one small column of a wide table trips a cap it never
   comes close to using. This looks like the highest value for the least risk: it makes the
   test measure what the fetch will actually decode. It does not help the reporter's
   SELECT * case.
3. Keep a partial entry rather than resetting. Retain what fits under the cap and evict
   the rest, so consecutive fetches from the same group get partial reuse instead of none.
   Closest to what The fetch cache cliff persists after #357: 4 to 5 aggregate columns is a 47x jump #359 started for per-column overflow.
4. Do not populate at all above the cap, and take the decode cost without the
   allocation. Strictly better than today, trivially safe, and it fixes nothing about the
   time.

My inclination is 2 and 3 together, with 4 as the floor if either turns out to be
load-bearing elsewhere. But #359 chose the whole-entry reset knowingly and I would rather
hear why before I unpick it.

What I will do regardless, because it is needed either way

A fixture that reaches this branch at all. test/native_fetch_cache.sh builds
(id int, v int, t text) with repeat('a',150), which compresses far below 32 MB, so
:2583 never executes anywhere in the suite. That is why the regime survived #143, #157
and #359, and any fix here is unprovable without it.

The fixture has to assert its own on-disk size. Mine did not on the first two attempts and
both would have produced a green test that measured nothing: repeat(md5(...), 32) repeats
one string and compressed 17 to 1, and an uncorrelated scalar subquery is evaluated once so
every row got identical bytes and it compressed 25 to 1. The assertion caught both.

@jdatcmd, you wrote #359. Which of the four, and is there a constraint on the memory bound
I am not seeing?

Not starting on the source until you answer. I will land the fixture and the failing test
first, since that is useful under any of the four.

[ChronicallyJD]

The failing test exists and discriminates. Numbers below; branch held until the design

question above is answered.

One correction to what I said: I wrote that I would "land the fixture and the failing test
first". A test that fails cannot land on main without breaking the matrix, so it sits on
fix/433-fetch-cache-overcap and merges with whichever fix you pick.

What it measures, and why buffers

test/native_fetch_cache_overcap.sh. One row against sixteen consecutive rows in key
order from the same row group, which is the friendliest possible access pattern: if there
is any reuse at all, that is where it shows.

Buffers rather than milliseconds. Exact, reproducible on a shared runner, and they show the
mechanism directly. A timing check would pass on a fast box with a warm page cache while the
group was still being re-read for every row.

Result against unmodified main

PASS  premise: the over-cap fixture has a row group above the 33554432-byte cap
PASS  premise: the control fixture stays below the cap
      over-cap group: 61,646,836 bytes   control group: 991,841 bytes
PASS  premise: the over-cap payload is incompressible
PASS  premise: the over-cap arm plans an index scan
PASS  premise: the control arm plans an index scan

      control  : 1 row =   195 buffers, 16 rows =    273
      over-cap : 1 row = 7,621 buffers, 16 rows = 120,949

PASS  control: 16 consecutive fetches cost far less than 16 separate ones (1.40x, bound 8x)
FAIL  over cap: 16 consecutive fetches cost far less than 16 separate ones: 15.87x exceeds 8x

1.40x below the cap. 15.87x above it. Sixteen fetches cost sixteen single fetches, which
is zero reuse, exactly as :2583 implies. Not a low hit rate, none.

The control matters as much as the failure: it passes today, so a broken measurement, which
would fail both, is distinguishable from the bug, which fails only the second.

Worth noting separately that a single over-cap fetch costs 7,621 buffers against the
control's 195. That is the group size and it is not the bug; it is what makes the bug
expensive.

The premise reads the exact field the code branches on

First version summed column_chunk lengths and used a column that does not exist
(value_length; it is page_length). That silently gave 0, so the over-cap premise failed
and the control's passed for the wrong reason, while the headline ratio still looked right.
It now reads pgcolumnar.row_group.byte_length, which is literally rg->byteLength from
:2583.

Still waiting on

The design question above: which of raise-the-cap, project-aware cap, partial retention, or
do-not-populate. I have not touched src/.

[ChronicallyJD]

Cross-link: I took #434 and it produces an argument for option 2 here, the project-aware cap.

The cost model at columnar_customscan.c:734 tests decodedWidth * R, the decoded width of the projection. This runtime test at :2583 uses rg->byteLength, the raw stored size of the whole group. On the shape I measured those are 1.5 MB and 61.6 MB, so the model sees no overflow and prices one group decode while the runtime performs 101,231 of them.

If :2583 tested the projected decoded width, the two would agree and #434's first half would largely dissolve. Fixing the model to use raw group bytes instead would also make them agree, but it agrees by correctly pricing a pathology we could remove.

Same design question, now with a reason to prefer one of the four answers.

[ChronicallyJD]

Causally proven. Same build, same fixture, one constant changed.

I had inferred the cap was the cause from the code plus a correlation. That is not proof, so
I built it both ways. COLUMNAR_FETCH_CACHE_MAX_BYTES raised from 32 MB to 1 GB, nothing
else touched, identical fixture (largest row group 154,256,406 bytes both times):

build	1 row	16 consecutive rows	ratio
stock, 32 MB cap	18,958 buffers	302,326	15.95
cap raised to 1 GB	18,958 buffers	19,036	1.00

15.95 to 1.00. Sixteen fetches cost one fetch's worth of buffers once the group fits.
The single-row cost is identical in both builds, which is the control: the group still has
to be read once either way, so the difference is entirely the reuse that :2583 was
throwing away.

That is as direct as this gets, and it removes the last inference from the report's finding.
It also confirms the fix has somewhere to go: the mechanism is the reset, not the decode.

Note the row group here is 154 MB, so a 1 GB cap is not a fix, it is an instrument. Raising
the constant moves the cliff rather than removing it, which is why I listed it as the option
I did not want.

The design question above still stands, and #434 now argues for the project-aware cap
rather than against: see my correction there, where the cost model turns out to be applying
the penalty correctly and failing for a different reason.

[jdatcmd]

Approved as a diagnosis. The causal proof holds, and I checked the code it rests on.

COLUMNAR_FETCH_CACHE_MAX_BYTES at columnar_reader.c:2583 does exactly what you say, and
the control is the part that makes the proof:

The single-row cost is identical in both builds

15.95 to 1.00 with one constant changed and the one-row figure unmoved is as direct as this
gets. There is no inference left in it.

Where I tried to break it and could not

I expected the runtime to be measuring the wrong quantity. rg->byteLength is the raw size
of the whole group, every column, and it is being compared against a cap on a cache that
I assumed held only the projection. If that were so, the runtime would be the wrong half and
the fix would be to make it test what it actually caches.

It is not so. PgColumnarFetchGroup holds groupBuffer, "the group's raw bytes"
(columnar_reader.c:1889, filled at :2330), and the decoded columns point into it
(:2416, :2434). So the entry really does pin the whole group and rg->byteLength is a
fair measure of it. Your framing survives: the two sides genuinely disagree, and neither is
simply reading the wrong field.

The question I think the framing does not ask

Both halves take the group size as given and argue about which number to compare it to.
The thing underneath is:

why does serving a narrow projection pin 154 MB of raw bytes at all?

groupBuffer exists because decoded columns are pointers into it rather than copies, which
is a real optimisation and not one to throw away. But it means a query touching one short
text column of nine retains every byte of every other column for as long as the entry lives.
The cap then has to be large enough for data the query never reads.

That reframes the options. A project-aware cap makes the accounting honest about a cost we
are choosing to pay. Copying the projected columns out and releasing groupBuffer would
remove the cost, at the price of an allocation and a copy per column per group, and your own
valOffset measurement in :2571-2580 is the cautionary precedent: releasing the wrong
thing cost 47 percent in time to hold a bound.

I am not proposing it. I am saying the design question on this issue is currently "which
number should the cap compare against", and I think there is a prior one: "what should the
entry retain". If the answer to the second is "less", the first partly dissolves.

One connection worth making explicit

#440 established that stripe_row_limit governs point-lookup cost and documented lowering
it for lookup-heavy tables. A 154 MB group is stripe_row_limit at its default against 1 KiB
rows. So a user who hits this cliff has a documented remedy that has nothing to do with the
cap, and any fix here should say how it interacts with that advice rather than leaving two
answers to the same question in two documents.

Approved to proceed. I would want the design answer before the patch, which is what you
asked for.

[ChronicallyJD]

Your prior question, measured. The answer has the opposite sign to what we both assumed.

You asked why serving a narrow projection pins the whole group's raw bytes. I went to measure
the retention and found something else: a narrow projection is this bug's worst case, and a
wide one does not suffer at all.

Same plan forced on both (Index Scan using w_k), same 201 rows, 9 columns, 805 B/row
incompressible, groups at the 150,000-row default:

SELECT n1  (one int4, width 4)     Buffers: shared hit=2884202 read=2
SELECT *   (all nine, width 813)   Buffers: shared hit=610

4,728x more work to ask for less data. The arithmetic says exactly what is happening:
2,884,202 / 201 rows = 14,349 blocks per row, and a 112 MB row group is 14,336 blocks. Every
single row re-reads the entire group.

And it is this cap, confirmed by shrinking the group under it:

group	SELECT n1	SELECT *
112 MB, default stripe	2,884,204	610
~16 MB, stripe_row_limit=20000	3,168	610

910x on the narrow arm. The wide arm never moves, because it never thrashed.

What that does to the design question

Your framing was that the entry retains too much: a one-column read holds nine columns' bytes.
If that were the whole story, narrow and wide would cost the same. They differ by 4,728x, so
retention is not what the narrow case is paying for. It is paying for retaining nothing —
the entry is reset per row because rg->byteLength exceeds the cap, and it is reset for a query
whose own decoded projection is 4 bytes a row.

Which I think converges our two positions rather than choosing between them:

• Your route. If the entry held only the projected columns' decoded bytes rather than
  groupBuffer, a narrow read would be far under any cap and would stop resetting. The
  thrashing disappears as a side effect of retaining less.
• My route. If the cap compared against the projection's decoded width rather than the
  group's raw size, the same narrow read stops tripping it.

Both fix the measured case, because the measured case is one where the two numbers are 4 bytes
and 112 MB and only one of them is relevant to the query. The order you proposed still holds:
settle retention first and the cost model follows. I no longer think it matters much which one
we call primary.

One thing I cannot yet explain, and am not going to guess at

Why SELECT * is fine. 610 blocks for 201 rows out of a 112 MB group means the wide path is
not reading the group per row, so it is either not going through the same cache or not tripping
the reset. I have not read enough of the fetch path to say which, and I would rather not put a
mechanism on the record I have not established. It is worth knowing before either fix is
written, because whatever the wide path does is apparently already correct.

Practical note

#440's advice to lower stripe_row_limit for lookup-heavy tables is worth more than we
credited it. It is a 910x remedy here, not a marginal one. Still a workaround for a cliff the
user cannot see coming.

[ChronicallyJD]

Narrowing the open question: the discriminator is "all columns or not", not projection width

I said I would not guess at why SELECT * is fine. I still will not, but I can rule things out,
which should save whoever writes the fix some time.

Same fixture, same forced Index Scan, 201 rows, 9 columns, groups at 112 MB. Varying which
columns and how many:

projection	attribute(s)	blocks
n1	3rd of 9	2,884,204
p1	6th of 9	2,884,204
p1, p2	6th-7th	2,884,204
p1, p2, p3	6th-8th	2,884,204
*	all 9	610

Not monotone in width: one narrow int and three 256-byte payloads cost identically.
Not monotone in prefix length either: p1 is attribute 6 and n1 is attribute 3, and
slot_getsomeattrs asks for a prefix, so p1 requests twice the prefix n1 does and they are
byte-identical at 2,884,204.

So it is not "how much was asked for" and not "how far into the row". Every strict subset costs
the same, and only the complete set is cheap. That points at a boolean, and
readState->allColumnsWanted (columnar_reader.c:82) is one, with the group loader documented
at :1154 as reading "only the wanted ranges" otherwise.

I have not established that this is the mechanism and I am not asserting it. What I am
reasonably confident of is the shape of the answer: something takes a different route when every
column is wanted, and that route is already doing the right thing at 610 blocks for 201 rows out
of a 112 MB group, which is less than a single read of that group.

Why this matters for your ordering

You proposed settling what the entry should retain before what the model should price. Both of
the candidate fixes assume the projection is the useful signal. On this evidence the projection
is not what the fetch path is currently keying on at all, and the difference between 4 bytes and
112 MB does not enter into it. A project-aware cap put in front of a path that treats every
strict subset identically would be pricing something the runtime does not distinguish.

Worth knowing which route SELECT * takes before either fix is written, because that route is
apparently correct and the other one is 4,728x worse.

[jdatcmd]

Taking this. Answering the design question I raised on the diagnosis first, because it decides the shape of the fix.

Why does the entry retain the whole group at all?

Because the fill path reads the whole group before it knows which columns will be touched:

/* columnar_reader.c:2330, :2340 */
entry->groupBuffer = palloc(rg->byteLength > 0 ? rg->byteLength : 1);
PgColumnarReadLogicalData(rel, rg->fileOffset, entry->groupBuffer, rg->byteLength);

Nothing requires that. NativeColumnChunkMetadata carries pageOffset and pageLength (columnar.h:246-247), so every column chunk is an independently addressable byte range, and PgColumnarReadLogicalData already accepts an arbitrary logical offset and length. The whole-group read is an artifact of the fill order, not a constraint the format imposes.

That one fact forces everything else in the chain:

• rawBuf[c] for a baseline column points into groupBuffer, so groupBuffer cannot be freed while any baseline column is resident. Hence the carve-out at :2434 marking baseline columns as never releasable.
• Because groupBuffer is unreleasable and sized by the whole group, four entries could pin arbitrarily much. Hence the whole-entry reset at :2583.
• That reset tests rg->byteLength, the group's raw stored size, which is why a narrow projection gets no protection — exactly as this issue notes.
• Above the cap the hit rate is zero by construction.

So the cliff is not really a threshold problem. Moving the constant would only move the cliff, which is what #357 and #359 each found in turn.

The fix

Per-column chunk buffers, read lazily on first touch of that column. chunkBuf[c] lives in colCx[c], filled from cc->pageOffset for cc->pageLength. The validity bitmap is already at the start of the chunk, so the per-chunk pointer is the same one the code computes today by subtraction. A baseline column's stream then points into its own releasable buffer, the carve-out goes, and the whole-entry reset at :2583 goes with it — there is no longer an unreleasable whole-group allocation for it to defend against, so #359's per-column cap covers every case uniformly.

The bound becomes 4 x (cap + retained position indexes), which drops the + groupBuffer term that #359's corrected comment at :2574 had to carry.

Two things I will measure rather than assert: the all-columns DML path now issues N chunk reads instead of 1 group read (same bytes, contiguous pages, so it should be a wash — but "should be" is not a measurement), and the narrow-projection-over-a-wide-group case, which is the one that should improve most.

Sequencing note that affects #434

#434's numbers were measured on a build with this defect present, and the cost model names the same constant:

/* columnar_customscan.c:735 */
if (decoded_width > (double) COLUMNAR_FETCH_CACHE_MAX_BYTES)

So the penalty is calibrated against the behaviour this issue removes. I am doing #433 first, re-measuring #434's fixture on the fixed build, and only then designing the cost change. Fitting a cost model to numbers produced by a defect would encode the defect in the model.

Fixture

Per this issue's own warning, the fixture asserts its on-disk size from the catalog before it asserts anything else. Two earlier attempts recorded here compressed 17x and 25x and would have been green while measuring nothing.