`PagedChunkVectorStorage` pages with OFFSET: `sec ask` is 7.3× slower at 400k chunks than the unbounded read it replaced

[sroussey]

6d9ee18 replaced SqliteVectorStorage.similaritySearch's unbounded SELECT * FROM kb_chunk
with a paged scan, to stop one question hydrating the whole index. The memory bound is real and
the reasoning is right. The paging form is not: it is OFFSET-based, and SQLite walks the
primary-key index from the start on every page, so the total traversal is O(N²/page).

src/kb/PagedChunkVectorStorage.ts:86-101:

for (let offset = 0; ; offset += SCAN_PAGE) {
  const page = (await this.getAll({
    orderBy: [{ column: "chunk_id", direction: "ASC" }],
    limit: SCAN_PAGE,
    offset,
  })) ?? [];

Measured

node:sqlite, in-memory kb_chunk (uuid TEXT primary key, JSON-encoded vector, metadata),
scanning the whole table the way one question does. Three forms, same data, same process:

rows     OFFSET paging   keyset paging   unbounded (what this replaced)
 50,000      566 ms          189 ms          214 ms
100,000    1,885 ms          791 ms          383 ms
200,000    6,040 ms          993 ms          651 ms
400,000   21,482 ms        3,083 ms        2,925 ms

Doubling the corpus multiplies OFFSET time by ~3.3×. At 400k chunks the change costs
+18.6 s per question against the read it replaced and +18.4 s against the keyset form.
All three issue the same 782 statements at that size; only the work per statement differs.

That matters here specifically because the commit message's own framing is that sec index is
"a build measured in hours to days" — i.e. the index this has to read is the large one.

Fix

The ordering is already on the primary key, which is exactly the column a keyset cursor needs, so
the memory bound costs nothing if the page is selected by key instead of by position:

SELECT * FROM kb_chunk WHERE chunk_id > ? ORDER BY chunk_id ASC LIMIT 512

carrying the last row's chunk_id forward, starting from "". That keeps the "pages partition
the table" property the current comment argues for — it is in fact stronger, since it does not
depend on the table being unmodified during the scan — and it removes the whole regression.

ITabularStorage does not express WHERE chunk_id > ? today; this class already reaches for
getAll on the inherited SQLite storage, so a prepare against the same handle is the smallest
change, or query({ chunk_id: { value: last, operator: ">" } }) if the tabular layer grows it.

Two smaller things in the same file

• toVector (:113-117) hardcodes Float32Array, where the base class uses its configurable
  vectorCtor. Likewise entity.vector / entity.metadata are hardcoded where the base uses
  vectorPropertyName / metadataPropertyName. Both are correct for ChunkVectorStorageSchema
  today and silently wrong for a differently-constructed instance. The base's fields are private,
  which is why the duplication exists — worth a comment saying so, or a protected accessor
  upstream.
• secKnowledgeBaseSearch.test.ts pins that every kb_chunk read carries a LIMIT, which is the
  right assertion for the memory bound. Nothing pins the cost, so a keyset rewrite has no
  regression test to break — a page-count or statement-count assertion would keep it.

Verify

# reproduce the table above
node -e '<the probe above>'   # or add a bench under src/kb/

Found during the 2026-09-14 review. Snapshot: workglow-dev/prd → analysis/grades/2026-09-14/sec-detailed.md §4.2.

[sroussey]

Fixed on claude/admiring-lovelace-59uare — ba0b58c2 (keyset paging) and e58b2b1f (the toVector note below). No PR opened.

What changed

src/kb/PagedChunkVectorStorage.ts:96-119 — the for (let offset = 0; ; offset += SCAN_PAGE) loop over getAll({ limit, offset }) is now a cursor loop over the inherited getPage:

let cursor: PageCursor | undefined;
for (;;) {
  const page = await this.getPage({
    orderBy: [{ column: "chunk_id", direction: "ASC" }],
    limit: SCAN_PAGE,
    cursor,
  });
  for (const entity of page.items) { /* filter, cosine, keepBest */ }
  if (page.nextCursor === undefined || page.items.length === 0) break;
  cursor = page.nextCursor;
}

ITabularStorage has grown the operator the issue was missing, and more: getPage on a SQL backend pushes the whole keyset predicate down (BaseSqlTabularStorage.runSqlPage / buildKeysetWhere), so no prepare against the raw handle was needed. It also handles the two traps for free — ChunkVectorPrimaryKey is ["chunk_id"], a single column, so the predicate is one inequality rather than a row-value comparison, and the cursor is refused outright if the ordering it was built under stops matching. The memory bound is unchanged: one page of rows plus the topK list. --dry-run is untouched — nothing on this path writes.

The loop ends on an empty page as well as on an absent cursor. A table whose size is an exact multiple of 512 hands back a cursor for its last full page (nothing about that page says it was the last), and following the cursor alone never terminates.

Measured

Rerun from scratch rather than quoting the table above — same shape, smaller absolute numbers. Real SQLite file (WAL), kb_chunk via SqliteVectorStorage, 64-dimension vectors, 512-row pages, topK 5, one whole-table scan per measurement, page cache warmed identically before each. Node 24.20.0 / Bun 1.4.2. The keyset column is the shipped PagedChunkVectorStorage.similaritySearch, not a hand-rolled loop; all three arms are asserted to return the identical ranking at every size.

rows	OFFSET paging (was)	keyset paging (now)	unbounded read (what OFFSET replaced)
50,000	381 ms	305 ms	318 ms
100,000	839 ms	539 ms	589 ms
200,000	2,170 ms	1,025 ms	1,079 ms
400,000	6,768 ms	2,096 ms	2,170 ms

Two earlier runs of the same benchmark agreed within ~5% (400k: offset 7,042 / 7,019 ms, keyset 2,062 / 2,109 ms).

The superlinearity reproduces: 200k→400k costs OFFSET 3.1× where keyset costs 2.0×. The absolute regression I measured is 3.3× the unbounded read at 400k (+4.6 s per question) rather than the 7.3× / +18.6 s in the report — my vectors are 64-dimension where the original probe's width is not stated, and a narrower row makes the per-page re-walk cheaper relative to the scoring. Direction, exponent and fix are the same; the magnitude is machine- and row-width-dependent.

Plans, verbatim:

-- OFFSET page (was)
SCAN kb_chunk USING INDEX sqlite_autoindex_kb_chunk_1

-- keyset page (now)
SEARCH kb_chunk USING INDEX sqlite_autoindex_kb_chunk_1 (chunk_id>?)

SCAN vs SEARCH is the whole difference: the OFFSET form re-enters the index at the start each page and discards its way forward.

Tests

src/kb/secKnowledgeBaseSearch.test.ts, four cases, with SCAN_PAGE now exported so a corpus can be seeded at an exact page boundary rather than at a number that happens to cross one:

• seeks to the last key seen rather than counting rows past — pins the statement count at exactly 3 for a 2·SCAN_PAGE + 5 corpus, asserts no read carries OFFSET, and asserts every read after the first carries `chunk_id` >. This is the regression test the issue asked for, and I confirmed it fails on the OFFSET implementation: AssertionError: expected 'SELECT * FROM \kb_chunk` ORDER BY `chunk_id` ASC LIMIT ? OFFSET ?' not to match /OFFSET/i`.
• terminates on a corpus that is an exact multiple of the page — 2·SCAN_PAGE rows, ranking correct, exactly 3 reads (two full pages plus the empty one that ends it).
• ranks an empty index without reading a page twice — one read, no second.
• The original bounded-pages/whole-index-ranked case, re-seeded off SCAN_PAGE.

The two smaller things

• toVector — addressed as you suggested, with a comment rather than a behaviour change (e58b2b1f). It now says that Float32Array and the vector / metadata property names are restated because the base holds all three in private fields with no accessor, that they are right for the one ChunkVectorStorageSchema store getSecKnowledgeBase builds and silently wrong for any other, and that widening the class means reading them off the base rather than restating them. The protected accessor upstream is a @workglow/storage change and is not in this branch.
• Cost pinned — the statement-count assertions above are what a future regression would break.

Verification

command	exit	time
bun run format-check	0	—
bun run lint	0	—
bun run typecheck	0	1.7 s
bun run build	0	—
bunx vitest run src/kb	0	21 tests
bunx vitest run (full suite)	0	132 s — 1496 passed, 21 skipped, 151 files

No timeouts and no load-sensitive failures in the verify tier on this run.

---

Generated by Claude Code