Query Batching

Batch reads to reduce roundtrips.

Overview

Query batching combines multiple data fetches into single round trips against the database or upstream service. The N+1 query pattern is the canonical performance bug, one query for the parent, then one query per child, then one query per grandchild. Batching collapses that into a constant number of round trips regardless of result-set size, and the latency improvement is usually an order of magnitude rather than a tweak.

• Batch reads to reduce round trips. One round trip with 100 IDs in an IN clause beats 100 round trips with one ID each by orders of magnitude.
• DataLoader pattern. Per-tick batching and deduplication; the request loop collects keys, dispatches one query, returns per-key results.
• IN-clause batching. Per-query the IN clause replaces per-row queries; the database does the work in one plan rather than 100.
• Per-tier batching plus N+1 prevention. Different tiers (UI, GraphQL, internal services) need different batch shapes; CI N+1 detection catches regressions before production.

The approach

The practical approach is DataLoader (or equivalent) at the resolver layer, IN-clause batching at the query layer, per-tier batch sizes tuned to the workload, and CI checks that fail PRs introducing N+1 patterns. The discipline is preventive: the cheapest time to catch an N+1 is in PR review, the most expensive is production after a 10x traffic spike.

• DataLoader pattern. Per-tick batching and dedupe at the resolver layer; the application code stays per-key but the database call is per-batch.
• IN-clause batching. Per-query IN clauses where the result set is bounded; tune batch size to avoid query-plan regressions on large lists.
• Per-tier batching. UI batching latency tolerates 10ms; internal-service batching can tolerate more; tune the wait window to the latency budget.
• N+1 detection plus documented pattern. CI hooks (rspec-N+1, pg-loadtest) catch regressions; per-service batching pattern documented for the next engineer.

Why this compounds

Query batching discipline compounds across services. Each batched query produces ongoing latency reduction at the user-visible boundary; the team’s database engineering vocabulary grows; new services inherit the batching pattern as a default rather than a retrofit. After a year, the team thinks in batches by default and N+1 patterns surface in PR review rather than in production.

• Latency. Right batching reduces tail latency where users feel it; the p99 stops being the queue of round trips.
• Cost efficiency. Fewer round trips means less database CPU per request; the same hardware serves more traffic.
• Engineering culture. Pattern-driven decisions replace ad-hoc query writing; engineers think about the database call shape, not just the field they need.
• Institutional knowledge. Each batch teaches a database pattern; the team grows a vocabulary for query shape that compounds across services.

Query batching is an engineering discipline that pays off across years. Nova AI Ops integrates with database telemetry, surfaces query patterns, and supports the team’s database engineering discipline.