Single-round-trip remote joins for on-demand collections (let the sync layer fetch related rows) #1712
Send to my agentSummary#
Feature request: give the sync layer a way to fetch a join's related rows in the same remote request as the main subset, instead of the current two-phase load. Backends like Postgres (PostgREST embedded resources), InstantDB (nested queries), Convex, etc. can join/embed related tables in a single query — but today an on-demand join always costs at least two serial round trips, which compounds badly on high-latency connections or for users far from the DB.
How it works today (v0.6.17)#
The join loading is data-driven, not query-driven. In packages/db/src/query/compiler/joins.ts (processJoin), the "active" side of the join gets a tap on its pipeline. As rows flow through, the tap extracts the join keys it has seen and calls:
lazySourceSubscription.requestSnapshot({
where: inArray(lazyJoinRef, joinKeys),
optimizedOnly: true,
})
on the lazy side, which lands in that collection's loadSubset. The second request cannot start until the first has returned rows — the keys come out of the data stream.
But the waterfall isn't an information problem: at compile time processJoin already has the full QueryIR, both Collection instances, the join key paths on both sides, and the per-alias where pushdowns (sourceWhereClausesCache). The only missing piece is that LoadSubsetOptions can only describe one collection, so there's no way to ask a backend for "this subset plus its related rows."
Proposed design#
1. Let a sync layer declare it can fetch relations#
SyncConfigRes currently returns { cleanup, loadSubset, unloadSubset }. Add an optional:
loadSubsetWithRelations?: (
options: LoadSubsetOptions & { include: Array<RelationDescriptor> }
) => true | Promise<void> | false // false = "can't do this one, fall back"
type RelationDescriptor = {
targetCollection: Collection // the joined collection instance
on: { sourcePath: Array<string>; targetPath: Array<string> }
targetWhere?: BasicExpression<boolean> // the joined alias's pushed-down where
}
The framework doesn't need to know whether two collections share a backend — the sync layer decides. An adapter that owns both collections recognizes targetCollection and emits one nested/embedded query (?select=*,cards(*) for PostgREST, a nested object query for InstantDB); anything else returns false and the current path runs unchanged.
2. Hook in at compile time, not in the tap#
In processJoin, where lazy targets are computed, check whether the active side's collection sync exposes loadSubsetWithRelations and the lazy target is a plain collection ref (bail to current behavior for queryRef subqueries and unions — those are already special-cased via limitedSubquery / resultUnionLazySide). If so, the initial snapshot request for the main alias carries the include descriptors, so one round trip fetches both tables.
3. Ingest into both collections and pre-seed the dedupe#
The sync layer writes main rows through its own begin/write/commit as usual, and writes the embedded related rows into the target collection. Critically, it also registers the loaded keys with the target collection's DeduplicatedLoadSubset (packages/db/src/query/subset-dedupe.ts already tracks covered predicates and in-flight calls).
This is what makes the feature safe: the existing tap stays untouched as a fallback. When it later fires requestSnapshot({ where: inArray(key, seenKeys) }), the dedupe layer sees those keys are already covered and returns true synchronously — zero network. If anything slipped through (an optimistic local insert on the main side referencing an unloaded row, a key the remote join missed), the tap still fetches it. The remote join becomes a pure prefetch optimization with graceful degradation rather than a replacement of the join loader.
4. Unload symmetry#
unloadSubset already receives the same options object; with include present the sync layer releases the related rows too. The subscription field already threaded through LoadSubsetOptions provides the refcounting anchor.
Notes on correctness#
- The remote join only needs superset-of-fetch semantics: the local IVM pipeline still computes the actual join, so inner/left semantics, ordering, and re-filtering all stay local and correct. The remote query just has to return at least the related rows matching the fetched main rows.
- Backends that can't express the joined alias's
targetWhereremotely can drop it and over-fetch; the dedupe records only what was actually requested, so that's safe. Under-fetching is never allowed. - Initial scope: plain
from(collection)join targets only; aggregates,queryRefsubqueries with limit/offset, and unions keep the current behavior.
Question#
Is this (or something like it) on the roadmap? Happy to discuss the design — the "pre-seed the dedupe, keep the tap as fallback" shape seemed like the smallest change that gets single-round-trip joins without touching join semantics, but there may be a direction you'd prefer.
Sign in to join this conversation.