SubsetDemandController: consolidate join key segments for remote sync sources

[daveycodez]

Validated against @tanstack/db 0.9.2; the same code is on main in packages/db/src/query/live/subset-demand-controller.ts.

This is a performance enhancement, not a correctness bug: the current behaviour loads the right rows. The cost shows up in sync adapters that turn each loadSubset into a remote query or live subscription.

What happens today

SubsetDemandController.setDemand(subscription, plan, keys) tracks a lazy join's demanded keys as a list of segments. Each segment is one subscription.requestSnapshot({ where: inArray(ref, keys) }). When the demanded key set changes:

1. Existing segments that still intersect the new key set are kept as they are, including their keys that are no longer demanded.
2. Segments with no remaining demanded keys are aborted and releaseSnapshot'd.
3. Keys not covered by a kept segment get one new segment, inArray(ref, addedKeys).

So segments only accumulate and fragment. They are never merged, and a segment is released only once all of its keys have left demand.

Measured behaviour

Setup: an on-demand parent collection behind a window (orderBy updatedAt desc, limit 20) and a lazy join to a child collection on child.id = parent.childId. A fake remote adapter records each loadSubset for the child collection.

Scenario	Child loadSubset calls
Cold load of the window	1: id in [20 ids]
Each row that later enters the window (e.g. a parent's updatedAt bumps it into the top 20)	+1 per row: id in [1 id]
Over time, as rows churn through the window	the 1-key segments pile up; each stays live while any of its keys is still in the window, so there can be up to about one segment per window row
Window answered partly from local rows first, then completed by the server	2: id in [10] + id in [10]

For the record, join demand is correctly bounded by the limit: 100 local parents under a limit 20 window demanded exactly 20 child keys.

Why it matters

For in-memory or local sources, extra segments cost almost nothing. For an adapter where every loadSubset becomes a remote live query or subscription (realtime backends, server-side query subscriptions), the same join produces N small subscriptions instead of one. The backend has to keep and re-evaluate each of them on every relevant write, and each one is a separate round trip when it opens. A busy feed-style window drifts from 1 subscription toward roughly limit subscriptions per join.

Adapters can try to merge inArray predicates themselves, but they only see individual loadSubset/unloadSubset calls. They don't know which requests belong to one join plan, so merging safely means guessing at internals.

Possible options

(a) Consolidate in the controller. When keys are added and there are already live segments, request one new segment for the union of all still-demanded keys and release the segments it replaces after the new one settles, so rows never flicker. This could be debounced to one consolidation per microtask or tick, so a burst of window changes produces one request. A size threshold would help too (only consolidate once there are more than K segments, or when the added set is small next to the existing coverage) to avoid re-requesting a large set for every single-key change. Under this option the window above would keep about 1 segment per join, not up to 20.

(b) Let the adapter choose. Add a collection- or sync-level option (e.g. joinSegmentStrategy: 'incremental' | 'consolidated') or a hook that tells the sync layer which requests belong to the same join plan, so the adapter can merge them into one remote subscription. In-memory sources would keep today's incremental behaviour by default.

Related: #1880 (a sort-key change on an ordered window reloads the full source). Different code path, same theme: request shapes that are cheap locally but expensive for remote on-demand adapters.