Batching Lazy Loads with Dataloaders #

A GraphQL API resolves orders { customer { name } }. Each order’s resolver fetches its customer independently, so a 200-order page issues 200 primary-key lookups. Eager loading is not available: the resolver does not know, when it runs, which other orders are in the page. A dataloader collects those lookups within one tick of the event loop and issues a single query for all 200 ids.

The plan-level view of per-object lookups is in lazy loading plan artifacts. This page covers the batching layer that fixes them when eager loading cannot.

The Condition #

A dataloader sits between application code and the database:

  1. Resolvers call loader.load(id) and receive a promise.
  2. The loader accumulates ids until the current tick ends.
  3. It issues one query for the batch, typically WHERE id = ANY($1).
  4. It distributes results back to the callers, and caches within the request.

From the database’s perspective, hundreds of identical single-row statements become one array lookup. The plan changes from many executions of an index scan to one index scan with an array condition — the shapes compared in IN lists vs VALUES vs ANY(array) plans.

Two properties matter for planning:

Dataloaders also deduplicate: 200 orders belonging to 40 customers produce 40 ids, not 200.

From 200 lookups to one Four stages. Resolvers request customers by id individually. The dataloader collects the ids for the current tick and deduplicates them. One query is issued with the ids as an array. Results are distributed back to the waiting resolvers and cached for the request. resolvers load(id) per order collect + dedupe 200 ids → 40 keys one query WHERE id = ANY($1) distribute cached per request batching happens per tick, so unrelated resolvers share a batch

Annotated EXPLAIN Evidence #

Before, one statement per order:

-- pg_stat_statements
 calls   | mean_exec_time | query
---------+----------------+------------------------------------------------
 8240110 |          0.039 | SELECT … FROM customers WHERE id = $1

-- plan of one call
Index Scan using customers_pkey on customers  (actual time=0.02..0.02 rows=1 loops=1)
  Index Cond: (id = $1)
  Buffers: shared hit=4

After, one statement per batch:

 calls  | mean_exec_time | query
--------+----------------+------------------------------------------------
 206002 |          0.412 | SELECT … FROM customers WHERE id = ANY($1)

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, name FROM customers WHERE id = ANY ($1);   -- 40 ids

Index Scan using customers_pkey on customers  (actual time=0.03..0.21 rows=40 loops=1)
  Index Cond: (id = ANY ('{81,92,…}'::bigint[]))
  Index Searches: 40
  Buffers: shared hit=124
Execution Time: 0.24 ms
-- 40 index descents in one statement instead of 40 round trips

The saving is mostly outside the database:

before: 200 round trips × ~0.25 ms network + 0.04 ms server ≈ 58 ms per request
after:    1 round trip  × ~0.25 ms network + 0.24 ms server ≈  0.5 ms per request
What batching changes Three items are annotated. Call counts falling by a factor of forty while mean time rises tenfold. An Index Cond with an array showing one statement per batch. Index Searches equal to the number of distinct ids, one descent per key. calls 8,240,110 → 206,002 one statement per batch Index Cond: (id = ANY ('{…}')) array lookup Index Searches: 40 one descent per distinct id total server time falls modestly; round trips fall dramatically

Step-by-Step Resolution #

  1. Identify per-object lookups by key in pg_stat_statements: very high calls, tiny mean_exec_time, a WHERE key = $1 shape.

  2. Introduce a loader per entity and key, not one global loader. customerById, addressesByCustomerId, and so on; each maps cleanly to one query shape.

  3. Return results in request order, filling gaps with null; this is the contract every dataloader implementation expects.

  4. Cap the batch size. Very large arrays make the per-statement latency spike and can exceed protocol limits. A few hundred ids per batch is a common ceiling, with the loader splitting larger batches:

    new DataLoader(keys => fetchCustomers(keys), { maxBatchSize: 500 });

  5. Index the batch key — the loader’s query is only as good as the index behind it. For one-to-many loaders (addressesByCustomerId), that means an index on the referencing column, as in covering indexes for foreign key joins.

  6. Check the plan with a realistic array length, not with one element, so the planner’s choice reflects production batches.

  7. Keep caching request-scoped. A longer-lived cache introduces stale reads and cross-request leakage.

Where the request time goes Before batching, 200 round trips contribute 50 milliseconds of network time and 8 milliseconds of server time. After batching, one round trip contributes 0.25 milliseconds of network time and 0.24 milliseconds of server time. The remaining application time of 12 milliseconds is unchanged. before: network 50 ms (200 round trips) before: server 8 ms after: network 0.25 ms (1 round trip) after: server 0.24 ms batching removes round trips, which usually dominate

Before and After #

-- BEFORE: per-object primary key lookups
SELECT … FROM customers WHERE id = $1        calls 8.2M/day   200 round trips per request

-- AFTER: dataloader batching
SELECT … FROM customers WHERE id = ANY($1)   calls 206k/day   1 round trip per request

Where batching is not the answer #

A dataloader turns N queries into one, but it does not turn N lookups into a join. When the parent set is known up front — a REST endpoint listing orders, a report, a background job — eager loading produces one query with a join or one batched query without the loader machinery, and it lets the planner see the whole shape. Dataloaders exist for the case where the parent set is discovered incrementally, which in practice means graph-shaped APIs and deeply nested rendering.

They also do not fix the second-order problem: a batched loader for addressesByCustomerId still runs once per level of the graph, so a deeply nested query issues one query per entity type per level. That is usually acceptable — a dozen queries rather than thousands — but it means query counts grow with query depth, and a public API needs depth limits for the database’s sake as much as for the resolver’s.

Finally, batching changes the shape the planner sees. A single-row lookup is planned as a point query; a 500-element array lookup is planned as 500 index searches, and above some size the planner may prefer a bitmap scan or even a sequential scan. Testing with realistic batch sizes, and capping the maximum, keeps that choice predictable.

Measuring the effect honestly #

Dataloader wins are mostly latency, not database load, and the two should be measured separately. On the database side, compare total_exec_time for the affected statements before and after: it usually falls modestly, because the same index descents still happen, just grouped. On the application side, compare request latency and the number of statements per request, where the improvement is often an order of magnitude because each avoided round trip saves a full network exchange plus the driver’s own per-statement overhead.

That distinction matters when deciding whether a change is worth the complexity. If the database is the bottleneck — high CPU, saturated I/O — batching helps less than it appears, and the real fix is fewer rows or better indexes. If the application is waiting on the network for thousands of tiny queries, batching is exactly the right tool and needs no database change at all.

Common Pitfalls #

One loader for everything. Mixed key types and shapes prevent batching. Diagnostic signal: batches of size one. Fix: a loader per entity and key.

Unbounded batch size. Huge arrays raise latency and can hit protocol limits. Diagnostic signal: occasional very slow batched statements. Fix: maxBatchSize.

Caching across requests. Stale or cross-tenant data. Diagnostic signal: data appearing that the current user should not see. Fix: request-scoped loaders.

Missing index on the batch key. The batched query scans. Diagnostic signal: a sequential scan under an ANY(array) condition. Fix: index the key.

Frequently Asked Questions #

What does a dataloader do to my SQL? #

It replaces many single-key lookups with one query per batch using an array condition, typically WHERE id = ANY($1). The database sees one statement text and far fewer round trips.

Is a dataloader a replacement for eager loading? #

No. When the full parent set is known in advance, eager loading is simpler and lets the planner see the whole query. Dataloaders are for cases where keys are discovered incrementally, such as GraphQL resolvers.

How large should a dataloader batch be? #

Large enough to remove most round trips, small enough that one statement stays fast and predictable — a few hundred keys is a common cap. Test plans with realistic batch sizes rather than single keys.

Up: Lazy Loading Plan Artifacts