Add Batch Support for Order Queries
MediumYou've joined a team whose order service has an N+1 problem. get_orders takes a list of order IDs and calls the database once per ID, so a page that shows 20 orders makes 20 round trips. Refactor it so any list of IDs costs one round trip, without changing what callers get back.
The batch call itself is easy to write. Returning exactly what callers expect from it is the harder part.
What's in the codebase
order_service.py.OrderServicewithget_orderandget_orders. The starterget_orderscallsfetch_oneonce per ID. This is the only file you change.db_client.py. A finished mock database client holding 50 orders.fetch_one(order_id)returns one order in one round trip of about 10 ms.fetch_many(order_ids)returns many orders in one round trip of about 15 ms. It dedupes the input IDs, drops IDs that don't exist, and returns rows in arbitrary order. The client logs every call, which is how the tests count round trips.test_runner.py. Seven tests: backward compatibility, one round trip, input order, duplicates, missing IDs, empty input, and a speed check of 20 IDs in under 0.12 seconds.scenarios.py. A timing demo over batches of 5, 10, and 20 IDs.instructions.md. Restates the contract and the run commands.
Out of the box, five tests pass and the two efficiency tests fail. The timing demo shows the cost growing with the batch size, from about 0.05 seconds at 5 IDs to about 0.20 seconds at 20.
Your task
Refactor get_orders so a call costs one database round trip, however many IDs it receives. get_order must keep its current behavior: the order for an existing ID, None for a missing one.
Callers rely on this contract:
get_ordersreturns a list with the same length and order as the input. Position i holds the order for the ID at position i.- A duplicate ID appears at each of its positions in the output.
- An ID with no matching order produces
Noneat its position. Don't raise and don't skip it. get_orders([])returns[]and makes no database calls.- Each call makes exactly one
fetch_manycall and nofetch_onecalls.
get_orders(["o1", "o2", "o3"]) -> [order o1, order o2, order o3] (1 fetch_many) get_orders(["o2", "o1", "o2"]) -> [order o2, order o1, order o2] get_orders(["o1", "missing", "o2"]) -> [order o1, None, order o2] get_orders([]) -> [] (no calls)
You're done when all seven tests pass and the timing demo shows roughly the same time at every batch size.
Follow-up
Once the tests pass, the interviewer asks for a DataLoader-style layer on top of OrderService:
- Requests for the same order made in the same pass of the event loop share one fetch.
- Results are cached for the length of one request. A later request fetches fresh data.
- A call with 10,000 IDs is split into bounded batches, and the caller still receives one aligned list.
There are no tests for this part. Write the code and be ready to explain where coalescing happens, what can be shared between requests, and when the cache is cleared.
Using AI on this problem
This round usually allows AI for reading and writing code, but not for the decisions that carry the signal. Use it to summarize db_client.py, or to draft the batch call once you've chosen a design. Decide for yourself how the returned rows map back to the input, then check any generated code by hand against duplicates, missing IDs, and empty input.
Approach
- Return
[]straight away for empty input, before any database call. - Call
fetch_manyonce with the full input list. - Build a map from order ID to order from the returned rows.
- Build the result by walking the input list and looking up each ID in the map. A missing ID gives
None, and a duplicate ID gives the same order at each of its positions.
Pythondef get_orders(self, order_ids):
if not order_ids:
return []
rows = self.db.fetch_many(order_ids)
order_by_id = {order["id"]: order for order in rows}
return [order_by_id.get(order_id) for order_id in order_ids]
The realignment costs O(n). Building the result from the input list rather than from the returned rows is what restores the order, the duplicates, and the missing slots in one step.
Leave get_order as it is. It already makes one fetch_one call and returns the right values, so routing it through get_orders would change its database calls for no benefit.
All seven tests pass, and the timing demo shows about 0.015 seconds at every batch size, with one fetch_many call per batch.
What to focus on
- Measure first. Run
scenarios.pybefore and after the refactor. The call counts and the flat timing are your evidence that the N+1 is gone. - Never trust database result order.
fetch_manyowes you the rows, not the sequence. Realigning the rows with the request is the main work in this problem. The fetch itself is one line. - Treat backward compatibility as a constraint.
get_orderreturn values stay identical. A renamed method, a reshaped result, or an exception whereNoneused to be is a breaking change. - Guard the edges early. An empty list that still costs a database trip, and a missing ID that raises instead of returning
None, are where unguarded batch code fails. Handle both before the fetch logic.
The DataLoader follow-up
Coalescing lives in a loader object. Each load(order_id) call adds the ID to a pending set and returns a future. The first call in a pass of the event loop schedules a single dispatch, for example with loop.call_soon, which runs after the current callers have queued their IDs. The dispatch makes one batched fetch and resolves every future from the result.
The batching mechanism can be shared, but the cache can't. Cached orders belong to one request, so create a new loader per request and let the cache die with it. A cache shared across requests serves stale data, and in a multi-user service it can serve one user's data to another.
For 10,000 IDs, dedupe them, split them into fixed-size chunks, call fetch_many once per chunk, merge the rows into one map, and realign against the original input once at the end. The caller still gets one list in input order.
Leveling signals
Related courses

