find_batches_v2 (the diskless consumer read path) scaled with read-depth, not result size. It computed ROW_NUMBER() / SUM() OVER windows across the entire [starting_offset, high_watermark) range and only then trimmed to the fetch byte budget, so a single fetch cost O(high_watermark - starting_offset). A lagging consumer therefore paid for its whole lag on every fetch. This creates an unstable feedback loop: the further a consumer falls behind, the more expensive each fetch, so its drain rate drops below the produce rate and lag runs away rather than recovering.

Change

Rewrite find_batches_v2 (migration V19) as a per-partition plpgsql loop that streams batches in last_offset order and stops as soon as the per-partition (max_partition_fetch_bytes) or global (fetch_max_bytes) byte budget is crossed. Cost is now proportional to the bytes returned, not the backlog depth. The function signature and return type are unchanged, so the jOOQ binding and the FindBatchesJob caller are untouched; the only generated-code change is the schema-version stamp bump to 19.

Read results are identical to the previous version (V15): same error handling (unknown_topic_or_partition, offset_out_of_range), always at least one batch per partition, the budget-crossing batch is included, and responses stay in request order.

Why the plan is stable (and why an implicit loop, not an explicit cursor)

The O(result) win depends on the planner choosing an Index Scan on batches_by_last_offset_covering_idx (yielding last_offset order, no Sort) that the loop can abandon early. That plan is chosen reliably because the WHERE predicates are on domain-typed columns the query casts (topic_id::uuid, partition::integer, last_offset::bigint), which defeats column statistics: the planner estimates rows=1 and always picks the Index Scan + Nested Loop.

We evaluated an explicit OPEN/FETCH/CLOSE cursor (which adds cursor_tuple_fraction fast-start planning bias) as insurance, and rejected it: that bias is dormant today (the rows=1 estimate already forces the desired plan), while its single-row FETCH costs a consistent ~10-15% more than the implicit loop's 50-row batched fetch at large result sizes. Paying a live tax for a dormant benefit is not worth it. The migration comment records the full rationale and the exact future change (the rows=1 estimate becoming accurate: a PG upgrade, stats change, or dropping the domain casts) that would flip the plan to a Sort/Hash and make the explicit cursor worth revisiting.

Verification

• Equivalence: the existing FindBatchesJobTest (Postgres, testcontainers) runs against the new function and covers the >=1-batch, budget-crossing, combined per-partition/global-budget, max_batches_per_partition cap, ordering, and error paths. It is the regression oracle.
• Scan-depth benchmark (FindBatchesScanDepthBenchmarkTest, @Tag("benchmark")): at a fixed fetch budget, find_batches latency stays flat (~50-90 ms) as partition depth grows 25k -> 400k, versus a roughly linear rise before. EXPLAIN of the inner scan confirms Index Scan on the covering index with no Sort.
• Result-size sweep: at fixed depth, per-row cost is flat as the returned batch count grows to 128k (linear O(k) build, no O(k^2) array append; plpgsql mutates the array variable in place).
• Worst-case sizing (128 MiB request / 64 MiB-per-partition budget with 1 KiB batches ~= 131k metadata rows): measured ~0.9 s for the find_batches call, ~26 MB metadata array. This is metadata only; the record data never enters the control plane. O(result), no runaway.

Follow-ups (out of scope, separate items)

• Apply the same O(depth) -> O(result) treatment to the retention/write path (enforce_retention_v2), which additionally holds logs FOR UPDATE across a full-depth boundary scan (commit-latency tail): compute the boundary lock-free, take the row lock only for the DELETE.
• Optional covering-index widening: the scan is now O(result) rows, but each returned row still does a heap fetch + files lookup; index-only is a constant-factor win, benchmark-gated (costs commit-path WAL).
• A representative CI plan-guard (assert no Sort above the scan) as a stronger regression detector than the current benchmark-only EXPLAIN.

How to run the benchmark

./gradlew :storage:inkless:benchmarkTest --tests '*FindBatchesScanDepthBenchmarkTest*'