On-demand `orderBy` + `limit`: a sort-key change reloads the entire source and releases the window

Start in packages/db/src/query/live/ordered-source-loader.ts and trace onSourceChanges through invalidateSourceOrdering and loadFullSource. Run the framework-free reproduction from the issue to observe the request and unload sequence. Done means sort-key changes preserve a bounded window recovery, or expose the described recovery distinction without expanding the adapter subscription to the full matching source.

• I've validated the bug against the latest version of DB packages (@tanstack/db 0.9.2; the same path is on main in packages/db/src/query/live/ordered-source-loader.ts)

Describe the bug

A live query with orderBy + limit over a syncMode: "on-demand" collection loads its window correctly with loadSubset({ where, orderBy, limit }). But as soon as a visible row's sort key changes (or a visible row is deleted), OrderedSourceLoader.onSourceChanges calls invalidateSourceOrdering(), and the next load is loadFullSource(): a requestSnapshot carrying only the query's where, with no orderBy, limit or cursor. Once that settles, the loader retires the finite acquisitions, so the adapter gets unloadSubset for the window.

So a top-10 window turns into "every row matching where" after one sort-key change, and stays that way. For feeds ordered by updatedAt, that happens on basically every write.

Why it matters for server-backed adapters

To the adapter, the full-source request is indistinguishable from a genuine "give me everything matching where" request, so it must fetch (and, for a realtime backend, subscribe to) the whole matching set. In our app (InstantDB behind an on-demand collection) this turned a take: 12 "recent items" window into a live subscription to the entire table, 6,311 rows / 4 MB, re-run server-side on every write for every connected client. We're working around it in the adapter by recognising the request and answering it from the still-open window subscription. That depends on internals, though, and every on-demand adapter pays the same cost.

Reproduction

@tanstack/db 0.9.2, no framework. The fake adapter serves each request honestly: top-limit by updatedAt desc when a limit is given, every matching row otherwise.

import {
  BTreeIndex,
  createCollection,
  createLiveQueryCollection,
  eq,
  type LoadSubsetOptions,
} from "@tanstack/db"

type Row = { id: string; userId: string; updatedAt: number }

// Server: 1,000 rows for one user.
const server: Row[] = Array.from({ length: 1000 }, (_, i) => ({
  id: `r${i}`,
  userId: "u1",
  updatedAt: 1_000_000 - i,
}))

const show = (e: any): string =>
  e == null
    ? "none"
    : e.type === "ref"
      ? e.path.join(".")
      : e.type === "val"
        ? JSON.stringify(e.value)
        : `${e.name}(${e.args.map(show).join(", ")})`
const describe = (o: LoadSubsetOptions) => ({
  where: show(o.where),
  orderBy: o.orderBy?.length ?? 0,
  limit: o.limit,
  cursor: o.cursor ? "yes" : "none",
})

let syncApi: any
const items = createCollection<Row, string>({
  id: "items",
  getKey: (r) => r.id,
  syncMode: "on-demand",
  autoIndex: "eager",
  defaultIndexType: BTreeIndex,
  sync: {
    sync: (api) => {
      syncApi = api
      api.markReady()
      return {
        loadSubset: (opts: LoadSubsetOptions) => {
          console.log("loadSubset", JSON.stringify(describe(opts)))
          const rows = [...server].sort((a, b) => b.updatedAt - a.updatedAt)
          // Honour the boundary-tie load's extra `eq(updatedAt, v)` conjunct.
          const w: any = opts.where
          const tie =
            w?.name === "and"
              ? w.args.find((a: any) => a.args?.[0]?.path?.at(-1) === "updatedAt")
              : undefined
          const filtered = tie
            ? rows.filter((r) => r.updatedAt === tie.args[1].value)
            : rows
          const out =
            opts.limit !== undefined ? filtered.slice(0, opts.limit) : filtered
          api.begin()
          for (const r of out)
            api.write({
              type: api.collection.has(r.id) ? "update" : "insert",
              value: { ...r },
            })
          api.commit()
          return true
        },
        unloadSubset: (opts: LoadSubsetOptions) =>
          console.log("unloadSubset", JSON.stringify(describe(opts))),
      }
    },
  },
})

const top = createLiveQueryCollection((q) =>
  q
    .from({ i: items })
    .where(({ i }) => eq(i.userId, "u1"))
    .orderBy(({ i }) => i.updatedAt, "desc")
    .limit(10),
)
await top.preload()
console.log(`window ready: ${top.size} rows, local collection holds ${items.size}`)

console.log("\n--- server bumps r5 (a row inside the window) to the top")
const r5 = server.find((r) => r.id === "r5")!
r5.updatedAt = 2_000_000
syncApi.begin()
syncApi.write({ type: "update", value: { ...r5 } })
syncApi.commit()
await new Promise((r) => setTimeout(r, 50))
console.log(`after bump: window ${top.size} rows, local collection holds ${items.size}`)

Output:

loadSubset {"where":"eq(userId, \"u1\")","orderBy":1,"limit":10,"cursor":"none"}
loadSubset {"where":"and(eq(userId, \"u1\"), eq(updatedAt, 999991))","orderBy":0,"cursor":"none"}
window ready: 10 rows, local collection holds 10

--- server bumps r5 (a row inside the window) to the top
loadSubset {"where":"eq(userId, \"u1\")","orderBy":0,"cursor":"none"}
unloadSubset {"where":"eq(userId, \"u1\")","orderBy":1,"limit":10,"cursor":"none"}
unloadSubset {"where":"and(eq(userId, \"u1\"), eq(updatedAt, 999991))","orderBy":0,"cursor":"none"}
after bump: window 10 rows, local collection holds 1000

Expected behavior

After a sort-key change, re-establish the window with a bounded request rather than the full source: for example, re-issue the prefix request (where + orderBy + limit: offset + limit) when the ordering is expressible, as loadPrefix already does. The adapter already has to answer exactly that request correctly for the initial load, so it's no less safe as a recovery. The full-source request would stay the fallback when the ordering can't be expressed.

If the full-source recovery has to stay, it would help adapters to be able to tell it apart, e.g. a reason: "reorder-recovery" on LoadSubsetOptions, so an adapter that can prove it still holds the true top-N (a live, server-ordered, limited subscription) can answer from that instead of fetching everything.

Environment

• @tanstack/db 0.9.2 (@tanstack/react-db 0.4.1 in the app)
• Bun 1.x, macOS; the repro is framework-free