Eager Loading and Batching
The two general fixes for per-row queries — load the relation up front, or collect the ids and fetch once — and what each one over-fetches.
The requirement, the obvious build, and why it breaks
Every lesson starts where the work starts: someone asked for something, and the first implementation that comes to mind has a problem.
How do I load related data in a bounded number of queries without fetching things nobody asked for?
The list endpoint must include each order's customer, its line items and the shipping address, and it must not issue a query per order.
Mark every relation eager. Then everything is always loaded and no lazy access can ever surprise us.
Loading one order now loads its customer, its items, the items' products, and whatever those declare eager. A single findById becomes a graph traversal.
- Loading one order now loads its customer, its items, the items' products, and whatever those declare eager. A single
findByIdbecomes a graph traversal. - Endpoints that need only the order id pay for the entire graph, on every call.
- Eager joins across two one-to-many relations multiply: 100 orders x 5 items x 3 shipments is 1,500 rows for 100 objects (Joins: INNER, LEFT, RIGHT, FULL, CROSS, SELF).
- The fix is invisible in the same way the bug was: the fetch strategy lives on the class, so no call site shows what it costs.
What is actually happening
- Join-based eager loading puts the relation in the same statement. One round trip; parent columns repeat once per child row; the mapping layer de-duplicates parents by primary key.
- Query-based eager loading (preload) issues a second statement,
SELECT * FROM children WHERE parent_id IN (...), and stitches the results together in memory. Constant statement count, no multiplication, one extra round trip per relation. - Request-level batching — the DataLoader pattern — defers individual
load(id)calls within a tick, collects the ids, and issues oneWHERE id IN (...). It converts an N+1 that is spread across unrelated code into a batch without any caller knowing. - All three trade the same thing: statements versus bytes. Fewer statements generally means more rows or more columns crossing the wire.
- Batching also gives you a per-request cache for free, which is a correctness consideration: the same id loaded twice in one request returns the same value even if the row changed in between.
One statement or two
The join and the preload solve the same problem with opposite currencies. The join spends rows to save a round trip; the preload spends a round trip to save rows. Neither is a default you can apply blindly, and the deciding factor is the shape of the relation.
For many-to-one — an order's customer — the join adds columns to rows you were already fetching, so it is nearly free. For one-to-many — an order's line items — the join multiplies the parent rows by the child count, and the preload is usually the better trade.
Explicit at the call site
.select_related('customer') and .prefetch_related('line_items'); the semantic split between join and second query is the same.The durable fix is not a keyword, it is a place. Fetch strategy belongs to the use case, because only the use case knows which relations it will touch. Putting it on the entity means every caller inherits one strategy chosen for a different endpoint.
The Python below is SQLAlchemy; the important part is that two functions with the same return type declare different loading, and a reader of either can tell what it will cost.
1def orders_for_list(session, tenant_id, limit):2 # many-to-one: join adds columns, not rows3 return session.scalars(4 select(Order)5 .where(Order.tenant_id == tenant_id)6 .options(joinedload(Order.customer))7 .limit(limit)8 ).all()9 10def order_for_invoice(session, tenant_id, order_id):11 # one-to-many: second batched SELECT, no multiplication12 return session.scalars(13 select(Order)14 .where(Order.tenant_id == tenant_id, Order.id == order_id)15 .options(selectinload(Order.line_items))16 ).one()Two statements of intent. The list path never loads line items; the invoice path never pays for a join fan-out. Neither relies on the default declared on Order.
When the fan-out is structural, batch it
Sometimes the per-row query is not in a loop you can see. A GraphQL resolver runs per field per node; a permission check runs per object; a formatter looks up a currency per line. The call sites are unrelated to each other and none of them can eagerly load anything.
A request-scoped batching loader turns that into two statements without changing any caller: individual load(id) calls are queued within a tick, the ids are collected, and one query answers all of them. The price is a component with real semantics you must understand — especially that it memoizes.
1const userLoader = new DataLoader<string, User>(async (ids) => {2 const rows = await db.query<User>(3 'SELECT * FROM users WHERE tenant_id = $1 AND id = ANY($2)',4 [tenantId, ids], // tenant is part of the key, not optional5 )6 const byId = new Map(rows.map((r) => [r.id, r]))7 return ids.map((id) => byId.get(id) ?? new Error('not found'))8})9 10// unrelated call sites, one statement11const [a, b] = await Promise.all([userLoader.load(x), userLoader.load(y)])Two details carry the lesson: the batch function must return results in the order of the requested ids, and the tenant must be baked into the loader — a loader keyed on id alone will serve one tenant's row to another.
How to build it
Most important first.
- Make loading explicit per use case:
findOrdersForList()loads the customer;findOrderForInvoice()loads items and address. The entity declares the relation; the query declares the strategy. - Prefer preload (second batched query) as the default for one-to-many, and join for many-to-one. A many-to-one join adds columns, not rows; a one-to-many join adds rows.
- Never join two sibling one-to-many collections in one statement. Load them as two batched queries instead.
- Chunk the
INlist. Parent sets are unbounded in principle; drivers and databases have parameter limits, and a 50,000-element list produces a statement nobody planned for. - Use a request-scoped batching loader when the fan-out is structural rather than local — GraphQL resolvers and permission checks are the canonical cases (What GraphQL Costs).
What can go wrong
- Over-fetching: eager everything means every endpoint pays the widest query. The N+1 is gone and p99 is worse.
- Cartesian multiplication from stacked one-to-many joins, which can turn a 100-row page into hundreds of thousands of rows and OOM the process before any timeout fires.
- A batched loader whose per-request cache outlives the request — now it is a stale global cache with no invalidation (Cache Invalidation).
LIMITapplied to a joined statement limiting *rows*, not parents, so a page of 20 returns 7 orders because their items filled the limit.- Batching that changes ordering:
WHERE id IN (...)returns rows in whatever order the plan produces, and code that assumed input order silently mismatches ids to results.
- Two statements are two snapshots. Under
READ COMMITTEDthe parents and the preloaded children can come from different points in time, so a child may reference a parent state that no longer holds (Isolation Levels). - A request-scoped loader returns the value it fetched earlier in the request even after another transaction changed the row — deliberate, and worth knowing when a handler both reads and writes the same entity.
- Batched loaders are an authorization hazard: a loader keyed only by id will happily return a row belonging to another tenant to whichever caller asked first. Key the loader by tenant plus id, or filter inside the batch function (Object-Level Authorization).
- The per-request cache means an authorization decision made once is reused. Ensure the cache key includes everything the decision depended on.
- Eager loading widens what a response can accidentally contain. A DTO boundary is what stops a newly-eager relation from appearing in an API payload (Three Models, Not One).
- "Eager loading is the fix for N+1." It is one fix, and applied globally it is a different performance bug.
- "A join is always fewer queries, so it is always better." Fewer queries, more rows. Which dominates depends on the fan-out.
- "DataLoader is a cache." It is a batcher that happens to memoize within a request. Treating it as a cache is how stale reads and cross-tenant leaks appear.
- "
IN (...)is slow." It is a perfectly ordinary predicate; the engine will index-scan or hash it. The thing to bound is the *list length*, not the operator (Why Is This Query Slow? Indexes).
Operating it
- Statement count per request should be constant with respect to page size once fixed. Assert it in tests; graph it in production.
- Rows returned per statement — the signal that catches an over-eager join is a row count far larger than the objects rendered.
- Response payload size by route, which is where over-fetching shows up after the query count looks healthy.
- For batching loaders, log batch sizes. A loader whose batches are all size one is not batching, and that is a common misconfiguration.
- Preload keeps statement count constant as page size grows; join keeps it at one but grows transferred rows multiplicatively. At larger pages, preload usually wins.
- At high concurrency the constant-statement property matters more than the byte count, because statements hold pool connections and bytes do not (Connection Pools).
- Very large parent sets need chunking, and chunking reintroduces a bounded number of statements — 3 or 4, not N. That is a fine outcome; say so explicitly rather than treating it as a regression.
- Explicit per-use-case loading is more code and more names. It buys you a call site that tells the truth about cost.
- Preload costs one extra round trip per relation. Almost always worth it; not free.
- Request-scoped batching adds a component with its own lifecycle, cache semantics and authorization implications. It is the right answer for structural fan-out and overkill for one list endpoint.
Where this applies
Backend advice is context-sensitive. These labels say what each claim is specific to, and where a different stack or scale would differ.
- GENERALJoin versus batched second query versus per-request batching are strategy shapes, available under different names in every mature data layer.
- FRAMEWORK-SPECIFICSQLAlchemy names them
joinedloadandselectinload; Django names themselect_related(join, many-to-one) andprefetch_related(second query, one-to-many); Hibernate usesJOIN FETCHplus batch-size hints. The defaults differ, so the same relation declaration behaves differently across them. - DATABASE-SPECIFICWhether the join or the two-statement form is cheaper depends on the planner: Postgres may hash-join a large
INlist efficiently, while an engine that drives nested loops from an index can make the join form far more sensitive to fan-out (Join Algorithms: Nested Loop, Hash, Merge).
Where the depth lives
This domain teaches the application-side mechanism and hands the rest off.