Taming the N+1 Query Problem at 4 Million Requests Per Minute
- Published on
- Updated
- Reading time
- 5 mins read
On this page

When an architecture serves roughly 4M requests per minute at steady state and close to 7M at peak, small architectural inefficiencies compound exponentially. A sub-optimal data model that works fine at 500 RPS becomes a cluster-threatening bottleneck at multi-million RPM scale.
In our domain covering 38 applications on the checkout critical path, we noticed that our database tier was absorbing an unreasonable read amplification. Looking closely at APM traces revealed a classic culprit disguised in distributed clothing: an N+1 distributed document lookup pattern.
Here is how we authored an Architecture Decision Record (ADR) and restructured the data model to remove an N+1 lookup costing up to 200 extra database reads per request across 11 endpoints, a redesign that at full rollout retires around 300M documents, 245 GB of RAM and 780 GB of disk.
The Problem: Distributed N+1 Lookups
Our original data model stored parent domain entities and their child rule/status entities in normalized, separate documents across Couchbase buckets.
When a customer opened their checkout basket or checked eligibility for an order:
- The gateway requested candidate items.
- For each candidate item, the service executed secondary key lookups to fetch attached conditions, quotas, and contextual metadata.
- In heavy baskets containing multiple promotional lines, a single client request triggered up to 200 extra roundtrips to the document store.
Even with sub-millisecond document lookups, 200 sequential or batch-parallel network hops added up to high latency variance, high CPU usage on the database cluster, and severe thread pool starvation under sudden campaign traffic spikes.
The Solution: Document Embedding & Atomic Unit-of-Work
In relational modeling, normalization avoids redundancy. But in high-scale read-heavy document systems, normalization without joins forces the application layer to act as a naive query engine.
We authored an Architecture Decision Record (ADR) proposing an embedded document architecture:
- Embed related child entities directly into the aggregate root document.
- Ensure that an entity lookup is always exactly 1 network hop: an O(1) key-value fetch.
- Maintain entity mutation consistency via atomic units of work (CAS / optimistic locking).
// Before: Fragmented Documents
Key: "coupon:12345"
Key: "coupon_rule:12345:part_1"
Key: "coupon_rule:12345:part_2"
Key: "coupon_quota:12345"
// After: Single Embedded Aggregate Root
Key: "coupon:12345"
{
"id": "12345",
"status": "ACTIVE",
"discount": { "type": "PERCENTAGE", "value": 15 },
"rules": [
{ "type": "CATEGORY", "value": "electronics" },
{ "type": "MIN_BASKET_AMOUNT", "value": 500 }
],
"quota": { "total": 100000, "remaining": 42100 }
}
Migration Execution with Zero Risk
You cannot simply change the schema of hundreds of millions of production documents on a live checkout system. The rollout required defensive engineering:
1. Dual-Format Deserialization
We updated the domain entity deserializers to support both formats simultaneously. If the embedded payload was present, it parsed it directly; if not, it fell back to secondary lookups.
2. Atomic CAS Updates Behind Feature Flags
When modifying records, we transitioned writes to the new embedded structure using optimistic locking (Compare-And-Swap) to prevent lost updates from concurrent writers. CAS is local to each Couchbase cluster, so conflicts between our active-active datacenters are still settled by XDCR conflict resolution:
public void updateCouponAggregate(String id, Consumer<CouponAggregate> mutator) {
int attempts = 0;
while (attempts++ < MAX_RETRIES) {
GetResult result = collection.get(id);
CouponAggregate aggregate = result.contentAs(CouponAggregate.class);
mutator.accept(aggregate);
try {
collection.replace(id, aggregate, ReplaceOptions.replaceOptions().cas(result.cas()));
return;
} catch (CasMismatchException e) {
log.warn("CAS mismatch for coupon {}, retrying...", id);
}
}
throw new ConcurrencyException("Failed to update aggregate after max retries: " + id);
}
3. Progressive Rollout & Garbage Collection
- We enabled write-embedding for a small slice of traffic behind the feature flag, monitored error rates and latency, and ramped up incrementally, keeping the flag as our rollback switch.
- A background worker crawled the historical dataset, migrated existing documents, and tombstoned obsolete child documents.
The Results
Here is what the redesign changes at full rollout:
| Metric | Before Redesign | After Redesign | Improvement |
|---|---|---|---|
| Lookup Cost per Request | parent + up to 200 extra reads (11 endpoints) | 1 document fetch | N+1 lookup removed |
| Documents | - | - | ~300M retired |
| Cluster Memory (RAM) | - | - | 245 GB freed |
| Cluster Storage (Disk) | - | - | 780 GB freed |
Conclusion
At massive scale, architecture is fundamentally about sympathy for the hardware and the network. Reducing distributed network roundtrips through intentional, aggregate-based domain modeling is almost always more effective than attempting to patch bad data models with more caching layers.