skip to content

How would you choose the identifier-batch size for a service that fills deferred links in batches, and what would you measure to justify it?

level: principalimportance: should knowfreq 30%

answer

  1. two failure modes, not one
  2. anchor it to the page size
  3. per link, not one house number
  4. few standard sizes, recurring shapes
  5. rows fetched against rows touched

basics

~20 s

Set it per access path, anchored to the page size that path returns, keeping the service to a few recurring sizes. Justify it with statements per request, rows fetched against rows touched, and tail latency before and after.

solid answer

~50 s

Treat the size as a tuning decision with two failure modes. Too small and you are back to many statements, because a page of 50 owners at a size of 5 is still 10 round trips. Too large and you over-fetch links nobody touches, hold a bigger result in memory at once, push against limits on how many bind parameters a statement may carry, and emit a differently shaped statement for every list length. The practical anchor is the page size of the path: a page of 25 filled by one statement is the shape you want. Keep the service to a handful of sizes so statement shapes recur, and set them per link rather than as one house number. Justify with statements per request, rows fetched versus rows actually touched, and the tail latency of the endpoint — before and after, on real data volumes.

go deeper

for a junior

Recall that the size is a cap on keys per statement and that both extremes hurt: too small leaves many round trips, too large fetches links nobody uses.

for a middle

Explain the anchor — cover the page or chunk the path processes — and name the costs of an oversized list: wasted rows, memory, parameter limits, varying statement shapes.

for a senior

Show the measurements you would take at production data volumes and on the real link, and insist on tail latency and the fetched-to-touched ratio rather than mean latency alone.

for a principal

Own it as a per-path policy with a small set of standard values, a recorded rationale, and a trigger for revisiting when page sizes or fan-out change.

## Why this is a judgment call and not a default The batch size caps how many pending keys one statement may carry. Both directions of getting it wrong are real, and they fail differently, which is why no single number is right for a whole service. **Too small** and the mechanism barely helps. A page of 50 owners with a size of 5 still costs 10 round trips to fill one collection — better than 50, and still ten waits on a path that could have had one. **Too large** and the costs move somewhere less visible: - links are filled for owners nobody will touch, and those rows were transferred, parsed and turned into objects for nothing; - more of the result is resident at one moment, which shows up as memory pressure rather than as latency; - the key list can push against how many bind parameters one statement may carry, and layers vary in whether they split the list or fail; - every distinct list length is a distinct statement shape, so a system with an arbitrary size and lively traffic emits a long tail of one-off shapes. ## Anchor the number to the page The most useful heuristic is not a constant, it is a relationship: **make the size cover the unit of work the path actually processes**. - A screen that returns a page of 25 owners and touches one deferred link on each wants a size that fills the page in one statement. Two statements to serve one page is an avoidable wait. - A background job that walks the whole table in chunks of 500 wants a size tied to the chunk, not to the screen's number. - A path that touches the link on only a few of the owners it loads wants a *small* size, because the over-fetch is the dominant cost there rather than the round trips. That is why the setting belongs **per link, or at least per access path**, with a house default rather than a house rule. ## Keep the set of sizes small Since the list length is part of the statement text, a service that uses 25 here, 30 there and 47 somewhere else emits three families of shapes plus the short tails of each. Standardising on a few values — a small size, a page-sized one, a bulk one — costs almost nothing in fit and gives the engine repeated, recognisable statements. How much that reuse is worth on the engine side is a database question; from this side it is simply free tidiness. ## What to measure Numbers first, opinions second. Four measurements settle almost every argument: 1. **Statements per request** on the path, at a realistic data volume. This is the number the change is supposed to move, and it should be read before and after. 2. **Rows fetched against rows actually touched.** The ratio names the over-fetch directly. A path fetching five times what it reads is telling you the size is too large for it, or that batching is the wrong instrument for it. 3. **Tail latency of the endpoint**, not the mean. Batching moves work between waits and transfers, and the mean can improve while the tail gets worse if the larger statement occasionally lands on cold pages. 4. **Peak resident rows per request**, so a size that halves the statement count but doubles memory is caught before it is deployed. Two conditions make all four untrustworthy if ignored: measure at production-like data volumes, because a fan-out of two in a test database can be a fan-out of two hundred in production, and measure on the real link, because round-trip latency is what makes an extra statement expensive in the first place. ## Revisiting the choice The number is tied to things that change. Page sizes get adjusted by a product decision; fan-out grows as data accumulates; the path that touched every link is refactored into one that touches a few. A size chosen well two years ago and never re-read is now simply a number nobody can justify — so the durable output of this decision is not the value but the **reason recorded next to it**, in terms of page size and touch rate, so the next person can tell whether it still holds. ## What a strong answer sounds like Both failure modes named, the size anchored to the page or chunk the path processes rather than to a folk constant, a small set of standard values across the service, a per-link rather than global setting, and four measurements taken at real data volume on the real link — with the explicit admission that there is no correct number, only a number that is currently justified.

  • Why not simply set a very large size everywhere and stop thinking about it?
    Because the costs move rather than disappear: links are filled for owners nobody touches, more rows sit in memory at once, the key list can exceed what one statement may carry, and each list length is another statement shape. A large size is right for a bulk walk and wrong for a path that touches a few links out of many.
  • Which single number would you look at first to tell whether the size is wrong?
    Rows fetched against rows actually touched on that path. A ratio near one with several statements per request says the size is too small; a ratio of many-to-one says it is too large or that batching is the wrong instrument there. Statement count alone can look healthy while the service fetches five times what it reads.
  • How do you keep the choice from going stale?
    Record the reason beside the value — the page size and touch rate it was chosen for — and re-read it when either changes. Keeping statements per request and the fetched-to-touched ratio on the dashboard for the hottest paths turns a stale number into a visible drift rather than a surprise.

saying these in an interview costs you the question

  • Quotes one magic number as correct for every path
  • Sees only the too-small failure mode and none of the too-large one
  • Ignores that longer key lists mean more distinct statement shapes
  • Judges the setting on mean latency alone
  • Tunes it against test-sized data and ships the result
  • Treats the value as permanent once chosen