Skip to content

Taming the N+1 Query Problem at 4 Million Requests Per Minute

Published on
Updated
Reading time
5 mins read

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:

  1. The gateway requested candidate items.
  2. For each candidate item, the service executed secondary key lookups to fetch attached conditions, quotas, and contextual metadata.
  3. 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:

MetricBefore RedesignAfter RedesignImprovement
Lookup Cost per Requestparent + up to 200 extra reads (11 endpoints)1 document fetchN+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.

Share this post