Proposal: duplication-free eager loading — a UNION ALL "sectioned query" strategy for Preload (production case: 180s → 24s)

[sandonemaki]

Hi! We run bob in production on a large PostgreSQL workload and have contributed a few loader-performance PRs before (= ANY loader queries, O(N+M) map stitching, composite-key IN). This discussion is about a bigger topic: the execution strategy of Preload itself. We have a working PoC with measurements and would love your thoughts on the direction before we invest in a real implementation.

One note on how this relates to our open perf PRs (#718, #726, #727, #729, #736): those are small, self-contained improvements for pain we're feeling in production today, so we'd be grateful if they could be considered independently of this discussion. What's proposed here is still at the idea stage and will naturally take longer to mature — and even if it lands, the current JOIN strategy remains the default and the automatic fallback, so those improvements stay valuable either way.

The problem

Preload loads to-one relations via LEFT JOIN. When the parent set is large and many parents share the same related row, the related row's bytes are transferred once per parent row. Real numbers from one of our production queries:

• 61,773 tasks rows sharing just 49 servers rows (duplication factor ≈ 1,261×)
• the same server row travels the wire ~1,261 times
• end-to-end: 180s

Rewriting that call site from Preload to ThenLoad brought it down to 24s. Profiling splits the JOIN penalty in two:

1. Duplicate transfer — related-row bytes are copied per parent row (~2× payload in our case).
2. Wide-row scan cost — the JOIN result carries parent + related columns in every row, so scan work grows with rows × total width. Even with duplication factor 1.0, JOIN preload measurably loses to ThenLoad in our benchmarks.

Why we don't want to just use ThenLoad everywhere

ThenLoad fixes both issues but costs one extra query (RTT) per relation, and the parent filter's result set is only shared via a second round trip. On remote databases with non-trivial latency, N preloaded relations mean N extra RTTs. What we actually want is:

• 1 query / 1 RTT (Preload's strength)
• each related row transferred exactly once (ThenLoad's strength)
• pgx binary wire format preserved end-to-end (no JSON round-trip)
• a single snapshot (no consistency drift between parent and child reads)

Hypothesis: sectioned UNION ALL queries

Stack the parent section and the relation section vertically instead of joining horizontally:

WITH src AS MATERIALIZED (
    SELECT id, name, server_id FROM tasks
    WHERE ...            -- original WHERE / ORDER BY / LIMIT stay confined here
)
SELECT 0 AS _set, src.*, NULL::uuid AS "server.id", NULL::text AS "server.os_family"
FROM src
UNION ALL
SELECT 1, NULL, NULL, NULL, s.id, s.os_family
FROM servers s
WHERE s.id IN (SELECT server_id FROM src)

The scanner dispatches on _set into per-section typed scanners, then stitches children to parents by key with an O(N+M) map — same shared-instance semantics as ThenLoad. NULL-padded columns are close to free on the Postgres wire format (a 4-byte length header per NULL column).

PoC results (real production data, PostgreSQL 17):

scenario	vs JOIN Preload
high duplication (61,773 rows, ~1,261×)	e2e −65%, payload −50%, allocs −73%
duplication 1.0	e2e −43% (wide-row scan cost removed)
vs ThenLoad	parity on loopback; expect +1 RTT advantage on remote DBs

The part we care most about: not just the first relation

A single-edge version already helps, but most of our heavy call sites preload several sibling relations and/or nested chains (Preload.Cve.MasterCve(Preload.MasterCve.PublicCve())). We audited our codebase: of our ~150 heaviest preload call sites, roughly 60% are nested chains and another ~15% are "2nd or later sibling" positions. A first-edge-only implementation would leave most of the win on the table.

Our hypothesis is that siblings and nesting are not separate features — the Preload tree flattens into a sequence of sections:

• each tree node becomes one MATERIALIZED CTE (so the next level can semi-join it, and it executes exactly once),
• each node emits one UNION ALL section,
• siblings = more sections referencing src; nesting = sections referencing the previous level's CTE.

WITH src AS MATERIALIZED (
    SELECT id, master_cve_id, main_user_id FROM cves WHERE ...
),
rel_master AS MATERIALIZED (
    SELECT id, public_cve_id, ... FROM master_cves
    WHERE id IN (SELECT master_cve_id FROM src)
)
SELECT 0 AS _set, src.*, NULL, ...           FROM src
UNION ALL
SELECT 1, NULL..., u.id, u.name, NULL...     FROM users u
    WHERE u.id IN (SELECT main_user_id FROM src)        -- sibling
UNION ALL
SELECT 2, NULL..., rel_master.*              FROM rel_master  -- nested level 1
UNION ALL
SELECT 3, NULL..., p.id, p.title, ...        FROM public_cves p
    WHERE p.id IN (SELECT public_cve_id FROM rel_master)      -- nested level 2

Every row travels the wire once, at any depth/breadth, in one query.

On the codegen side we believe this avoids combinatorial explosion: generate only per-edge section parts (SQL fragment + typed scanner), and compose any tree at runtime by assigning _set numbers. Two refinements that fall out naturally:

• sibling edges to the same table (e.g. MainUser/SubUser → users) can merge into one section with id IN (main ∪ sub), removing even cross-edge duplication;
• if total column width exceeds a threshold, split into multiple queries (hybrid) — a guardrail for very large trees.

Shapes that don't fit (e.g. ordering the parent by a related column, which genuinely requires the JOIN) would fall back to the current JOIN strategy automatically, so this could ship as an opt-in strategy with zero behavior change by default.

Alternatives we considered and rejected

• JSON aggregation / jOOQ-style MULTISET (json_agg of related rows): 1 query and duplication-free, but abandons the binary wire format — the JSON encode/decode cost erases the alloc savings, and type fidelity suffers (numeric/timestamp/bytea).
• Pipelined ThenLoad (pgx batch): 1 RTT, but the child query must either re-execute the parent's WHERE (double evaluation of a potentially expensive filter) or go through a temp table; snapshot consistency also needs an explicit transaction.
• Row-valued subquery columns ((SELECT s FROM servers s WHERE ...) AS server): still duplicated per parent row — doesn't address the problem.

Questions

1. Does the overall direction — an alternative, opt-in Preload execution strategy producing sectioned UNION ALL queries — sound acceptable for bob, or would you prefer this living outside the core as an extension?
2. For siblings + nesting, do you see design concerns with the "flatten the Preload tree into MATERIALIZED CTEs + sections" approach (planner behavior around CTE materialization, NULL-padding wire cost on wide trees)?
3. Codegen structure: generating per-edge section parts only, with runtime tree composition, is our current plan. Does that fit how you'd want the templates organized?
4. Anything we've missed — prior discussions, or an alternative strategy you've already ruled in/out?

We're happy to contribute the implementation, benchmarks (we have real-workload data at multiple duplication factors), and docs. Dialect-wise the PoC is PostgreSQL; the SQL shape itself is portable, but we'd propose starting psql-only behind the opt-in.

[stephenafamo]

I've very conflicted about this....
While the benefits are clear, I'm worried about the complexity and possible downstream effects (counts, joins, e.t.c.)

While of course Bob needs to include some "magic" to improve the developer experience, I wonder if this is a bit too much magic which then becomes hard to reason about and maintain.

[sandonemaki]

Thanks for the honest read — that's a fair concern, and it matches our own hesitation about pitching this as core behavior. Two clarifications that hopefully narrow the risk surface, and then a suggestion.

On downstream effects: the proposal doesn't touch counts or joins at all. It's an alternative execution strategy for Preload only — counts don't go through Preload's code path, and join mods are unaffected. Query shapes that genuinely need the JOIN (ordering/filtering the parent by a related column) would simply be unsupported by the strategy, not rewritten. Not a single line of the existing code paths changes.

On "too much magic": agreed. Looking back, the magic lives in the "automatic fallback" part of the original proposal — and that's a kind of behavior bob has never had. In bob, the loading strategy is always something the user picks explicitly at the call site (Preload means a JOIN, ThenLoad means a separate query); nothing ever switches strategies silently. So we withdraw the automatic fallback and align this feature with bob's existing conventions:

• Sectioned loading becomes another explicitly chosen strategy alongside Preload/ThenLoad. Call sites that don't use it are completely unaffected.
• An unsupported shape is an explicit error by default (like the rest of bob, it never silently does something else). For operations that need it, fallback to the JOIN strategy is available as an explicit, per-call-site option — observable (logged) when it fires, never silent.
• The SQL is deterministic — the same query always produces the same text, inspectable at any time with .Build().

In other words, this keeps bob's property that reading your own code tells you exactly what SQL will run. The implementation follows the same principle: the query is assembled from bob's existing builder and the generated models' metadata; the genuinely new code should be limited to composing the sections and the layer that dispatches result rows by _set.

That said, given your hesitation, maybe the right first step is the option from Question 1: we build this outside the core, as a separate package on top of bob's public APIs, validate it against our own test suite and production workload, and report back. The numbers we'd report: before/after per-endpoint and per-query latency from our production APM, CPU/heap data from the continuous profiler, observed fallback rates, and controlled per-shape benchmarks (single edge / siblings / nested). No maintenance burden lands on bob, and the idea gets validated — or falsified — with evidence instead of speculation. The experiment would also answer the complexity question with something concrete — how much code this actually takes, and whether it truly stays within bob's public API — instead of us debating it in the abstract.

If you'd rather keep it discoverable under bob's roof instead, an explicitly experimental in-tree package (clearly marked, no stability promises) would also work for us — but we're equally happy starting fully outside and only coming back with results.

Two questions if we go that route:

1. If we hit a wall that needs a small, non-magical extension point in bob (nothing specific yet — possibly exporting an existing internal), would you be open to reviewing that as its own tiny PR?
2. Is there anything you'd want such an experiment to demonstrate before you'd consider revisiting it for core?

[stephenafamo]

we build this outside the core, as a separate package on top of bob's public APIs, validate it against our own test suite and production workload, and report back. The numbers we'd report: before/after per-endpoint and per-query latency from our production APM, CPU/heap data from the continuous profiler, observed fallback rates, and controlled per-shape benchmarks (single edge / siblings / nested). No maintenance burden lands on bob, and the idea gets validated — or falsified — with evidence instead of speculation.

Yes, this would be perfect. See if the plugin API is sufficient for this, if it isn't I'd want to see what is missing from the plugin interface.

1. If we hit a wall that needs a small, non-magical extension point in bob (nothing specific yet — possibly exporting an existing internal), would you be open to reviewing that as its own tiny PR?

Yes, for sure

2. Is there anything you'd want such an experiment to demonstrate before you'd consider revisiting it for core?

I'd want to know the edge cases. How it interacts with the other features of bob.

• What does it break?
• Where does one have to be careful when using it?
• Does it play nicely if used as a query within another query? e.g. as a CTE, as a target for another query?

---

Above all, thank you so much for all your contributions, all the PRs and the improvements you've been making to Bob. I really appreciate it.

[sandonemaki]

@stephenafamo
Thanks — we'll proceed exactly as you described: external package on bob's public APIs, validated on our workload, report back with numbers. Before writing any code we audited bob's extension surface against the full design, so here is the answer to "is the plugin API sufficient?", plus what we already know on your edge-case questions and what the report will verify.

Is the plugin API sufficient?

Yes. We found no blocking gap — the experiment needs zero changes to bob. The package splits into a codegen half and a runtime half:

Codegen half (the plugin API proper): sufficient as-is. A small generator binary wired like bobgen-psql/main.go, plus one StatePlugin that registers a new gen.Output — the documented "Adding a New Output" flow. Everything the templates need is already in the template data:

• relationship edges with their join columns (Relationships → orm.Relationship.Sides → FromColumns/ToColumns),
• Column.DBType for the NULL::type padding casts (the same pattern the in-tree 009_rel_query.go.tpl uses to build psql.Cast(..., DBType)),
• aliases for stable Go identifiers.

From that we generate only per-edge section parts — a column-list/padding SQL fragment plus a typed section scanner — and compose trees at runtime by assigning section numbers. Codegen output stays linear in the number of edges, as hoped in the original post.

Runtime half: the public API covers it, with three workarounds worth reporting (none blocking; this is the "what's missing" list):

1. The generated NULL-tolerant scan mappers (<table>ScanMapperNullable) are unexported with no accessor, so we generate our own typed scanners into our own output package. Fine for us — but a runtime-only library (no codegen) would be stuck here.
2. Relationship metadata is effectively gen-time-only: the orm.PreloadRel/PreloadSide values built by the generated loaders are captured inside closures and can't be enumerated at runtime. Same story — fine for a codegen-based package (we bake the edges at gen time), blocking for a runtime-only one.
3. Loaders/mapper mods attached to an inner query don't propagate when that query is embedded in another (details under your CTE question below), so our runtime explicitly hoists nested ThenLoad loaders onto its own execution — the same manual move bob itself makes in cached.go (SetLoaders(l.GetLoaders()...)) and in the mysql insert path.

Everything else composes from exported API: the SQL shape is built entirely with the public builder (sm.With(...).As(parentQuery).Materialized() + sm.UnionAll(...), and argument re-indexing is already threaded correctly through CTEs and combines); execution goes through bob.Allx with a custom scan.Mapper that dispatches on the discriminator column after the row is scanned (the same shape as the generated preload mapper); stitching reuses the exported Preload(name, rel) methods so R.<Rel>, R.Loaded and back-references behave identically; after-select hooks fire through the exported AfterQueryHook.

One DX papercut, in the spirit of "what's missing from the plugin interface": plugin dispatch is a type assertion against the hook's generic parameters (gen.Run does plugin.(StatePlugin[C]) etc.), so a plugin instantiated with the wrong type parameters is silently skipped — no error, no warning. Easy to lose an hour to. If you'd take it, we'd send a tiny PR making gen.Run report plugins that matched no hook.

Edge cases — what we already know, what the report will verify

What does it break? Outside the strategy: nothing — no shared code path is touched, and call sites opt in through a separate entry point. Inside it, shapes that genuinely need the JOIN (ordering or filtering the parent by a related column) are explicit errors, per the no-magic contract from earlier in this thread.

Where does one have to be careful? The honest list so far, mostly from PoC measurements on production data:

• Low duplication regresses payload. At duplication factor 1.0 the wire bytes were +11% vs JOIN (each child row pays one extra NULL-padded row); crossover is around factor ~2. This will be the first line of the docs — it's a per-call-site decision, exactly like Preload vs ThenLoad today.
• DB-side CPU is higher, not lower. EXPLAIN ANALYZE showed roughly 2× the JOIN's server-side time at high duplication (CTE materialization + the semi-joins). The end-to-end win comes from transfer and client scan/allocs (−50% payload, −73% allocs in our case). Anyone optimizing for DB CPU rather than latency/allocs should not pick this strategy.
• work_mem: the MATERIALIZED CTE can spill to disk for large parent sets.
• Result ordering: the parent's ORDER BY lives inside the CTE, and the final slice order is restored client-side via an ordinality column rather than an outer ORDER BY (the outer sort measured 5–10% of e2e in the PoC and is logically unnecessary). Ordering guarantees are part of the correctness test matrix.
• Wide trees: NULL padding is cheap on the wire (a 4-byte header per NULL column), but PostgreSQL caps a result row at 1664 columns — the hybrid-split guardrail from the proposal handles this, and it's part of the benchmark matrix.
• Hook semantics need one explicit decision. Today, JOIN-Preload runs neither the child model's query hooks nor its after-select hooks, while ThenLoad runs both. We'll match Preload — it's the strategy being replaced — and state that in the compatibility table.

Does it play nicely as a query within another query? We audited how bob handles this today, because the honest baseline matters: embedding any bob query (as a CTE body, a subquery, or a union branch) is purely textual. WriteQuery composes the SQL and re-indexes the args correctly, but loaders, mapper mods and query hooks are consulted only on the top-level query being executed — so today, a Preloading query embedded as a CTE keeps its JOIN and prefixed columns but silently loses the stitching. With that baseline, the answer splits in two:

• Composing into it: yes. The parent query handed to the strategy is an ordinary bob select and may itself contain CTEs, subqueries, window functions, etc.; it lands unmodified inside the src CTE (confining the parent's WHERE/ORDER/LIMIT there is the whole point of the design). This also means the strategy applies at any level where a select executes — including as the child query that a ThenLoad issues — so sibling Preloads, nested chains, and Preload-under-ThenLoad call sites are all covered by the same mechanism, which is where most of our production pain lives.
• Composing it into another query: no, by design. A sectioned result set is heterogeneous rows — meaningless as input to another query. The entry point therefore returns scanned models rather than an embeddable query value, so misuse is a compile error instead of silent breakage — strictly better than the current failure mode, and consistent with the explicit-over-magic line we committed to earlier. The generated SQL stays inspectable the same way Build/bob.Debug work today.

The report-back stays as promised: a compatibility matrix (feature × works / explicit-error / needs-care), observed fallback rates, controlled per-shape benchmarks (single edge / siblings / nested / wide trees), and production APM + continuous-profiler numbers. Next milestone is the codegen plugin emitting runnable sections for our schema, then a production canary.

[stephenafamo]

we'd send a tiny PR making gen.Run report plugins that matched no hook.

Sure.. sounds like a reasonable addition