Removing Bottlenecks: The N+1 Problem

This is not a Postgres problem. The example below uses a database because that's where most people meet N+1 first, but the same shape — one cheap remote call accidentally turning into N expensive round-trips — shows up everywhere a program talks to something across a wire. By the end of the article you'll see the same pattern in REST, Elasticsearch, Redis, S3, and Kafka. The fix is the same in all of them.

You write an endpoint. It loads ten blog posts and, for each one, the author's name. In dev with three rows in your laptop's Postgres, it returns in 12 ms. You ship it.

Production has 50,000 posts. The endpoint now takes four seconds for one paginated page of ten.

Nothing in your code looks slow. The query log tells the story:

SELECT * FROM posts LIMIT 10;        -- 1 query
SELECT * FROM users WHERE id = 1;    -- 1 query
SELECT * FROM users WHERE id = 2;    -- 1 query
... (8 more)
SELECT * FROM users WHERE id = 10;   -- 1 query

Eleven round-trips for ten posts. This is N+1: one query to get the parent list, plus N queries — one per row — to get each child. It's the most common bottleneck in backend code, and the costliest per line of code written — posts.map(p => p.author.name) looks like memory access. It's not. It's the network.

Watch the wire

Flip between the patterns below. Each pulse is one round-trip between your app and the database. The time-tape shows wall-clock cost. The naive pattern fires eleven; the fixes fire two or one.

The animation maps each round-trip to ~140 ms — generous compared to a real LAN, conservative compared to a managed Postgres in another availability zone. The exact number depends on your network. The shape doesn't. The two endpoints in the diagram could just as well be labelled APP ↔ REDIS, APP ↔ ELASTIC, APP ↔ S3, or APP ↔ payment-service. Same wire, same toll.

Why the cost is round-trips, not work

Each query has a fixed latency floor that has nothing to do with how much data it touches: the network round-trip, parsing, planning, the connection-pool checkout, and the result-stream protocol. On a fast LAN that's 1–5 ms. From an app server to a Postgres in a different cloud AZ, it can be 10–30 ms. Multiply by N and you have linear-time latency — not because the database is doing more work, but because you're paying that toll N+1 times.

It gets worse:

• Connection-pool starvation. Concurrent N+1 endpoints can drain a pool of 100 connections before you'd think possible. One slow endpoint becomes everyone's slow endpoint.
• It scales with engagement. Bigger lists for your power users mean bigger N. The customer who matters most pays the most.
• It hides inside ORMs. posts.each { |p| p.author.name } reads like memory traversal. The ORM's lazy-loading turns each .author into a fresh query. Your code looks blameless.

Same shape, different surface

The cleanest mental model for this isn't software. It's shipping.

Before the 1950s, sea cargo moved one parcel at a time: dockers loaded each crate by hand, the ship sailed, dockers unloaded each crate by hand, and the ship returned for the next batch. The ship and the dock were fast. The ceremony around them was where the time went. Then someone standardised the shipping container, ports invested in cranes, and ocean freight collapsed in cost — not because ships got faster, but because one voyage now carried hundreds of parcels.

N+1 is the pre-container era of your codebase. Each iteration is a tiny ship making the full ceremonial trip — connection setup, parsing, planning, send, wait, receive, teardown — to deliver one little parcel. Toggle the eras below to feel the gap.

Once you see this shape, you start seeing it everywhere. Every system in your stack that you talk to over a wire — a database, a cache, a search index, a queue, a blob store, another service — has the same round-trip floor. The bug pattern repeats almost verbatim, just with a different SDK in the loop. A short tour:

• REST / RPC fan-out. A loop calling users.get(id) for each item in a list. The fix is a users.batch_get([ids]) endpoint — and if the upstream service doesn't expose one, request-coalescing wraps the per-call API into a batch.
• Elasticsearch / OpenSearch indexing. for doc in docs: es.index(doc) is the textbook reindex bottleneck. Replace with es.bulk(docs) and you go from N HTTP requests to one. Real-world result: reindexing a 10M-document corpus drops from hours to minutes.
• Redis / Memcached lookups. Calling GET key in a loop instead of MGET k1 k2 …. The pipeline saves the connection-write cost per key; the multi-get saves the round-trip. Both apply.
• S3 / object storage. for key in keys: s3.get_object(key) is the same anti-pattern with HTTPS instead of TCP. The fix is concurrency (parallel range gets) or, when you control the data layout, a manifest file that lets you pull a packed archive in one request.
• Kafka / SQS / event publishing. for event in batch: producer.send(event) opens N TCP frames; producer.send_batch(batch) opens one. At high throughput this changes a producer from CPU-bound to network-bound — in a good way.
• Filesystem walks. Calling stat(path) on every entry in a directory listing instead of using a single readdir that returns the metadata inline. Same shape, just running over a kernel-syscall boundary instead of TCP.

The surface and the SDK change. The diagnosis doesn't: a loop, calling a remote thing, paying its round-trip floor every iteration. The fix is the same word — batch — even if the API is named IN(…), bulk, mget, _msearch, pipeline, or send_batch.

Three ways to remove it

1. Batch with IN(…)

Collect the foreign keys you'll need, then fetch them all in one query.

SELECT * FROM users WHERE id IN (1, 2, 3, ..., 10);

Round-trips: 2 (the parent list + one batched child query). Network cost stays roughly constant in N — until you hit your database's parameter limit, at which point you chunk into batches of 1,000.

2. JOIN

If you only need joined columns, fold both tables into one query.

SELECT p.*, u.name AS author_name
FROM posts p
JOIN users u ON u.id = p.author_id
LIMIT 10;

Round-trips: 1. The cheapest option. Prefer this when the child columns are few and the join cardinality is sane (you're not duplicating wide rows).

When to prefer batched IN over JOIN: when the child rows are reused (a per-request cache), are wide (don't drag every column over the wire), or are shaped completely differently from the parent list (a JOIN would be awkward to map).

3. Dataloader / request-coalescing

In an async system — GraphQL resolvers, microservice fan-out — the call sites don't know about each other. getUser(1) and getUser(2) happen in separate stack frames; you can't refactor them into one query without losing the modularity that made you split them in the first place.

The fix is a per-request batching cache that intercepts the calls, lets a microtick pass, then flushes them as a single query. Facebook's DataLoader is the canonical implementation, but the pattern is small enough to write yourself in 50 lines.

loader.load(1)  ─┐
loader.load(2)  ─┤   single batched query
loader.load(3)  ─┘   to the data source

Round-trips: 2 (or 1, if you compose with a JOIN-able batch). Same latency profile as IN(…), reached without rewriting any of your business logic.

Three signals you have one

If you wrote a for loop over a database result set and then accessed anything the database knows about on each row, you almost certainly have an N+1 — even when your ORM hides it. Look for:

1. Repeating SQL in your query log. The same statement firing ten times in a row is the smoking gun.
2. Endpoint latency that scales linearly with page size. Doubling limit doubles latency, and your CPU graph is flat. That's network round-trips.
3. P99 alerts that line up with your largest customers, not your largest payloads. The customer with 500 items in their list is paying 500 round-trips while the rest of your traffic flies through.

Rule of Thumb

Any time you have a for loop and one line of the loop body crosses a network — a database, a cache, an HTTP API, a search cluster, a queue, a blob store, anything on the other side of a socket — you almost certainly have an N+1. Two round-trips beat N+1 every time, and one beats two. The fix is mechanical once you spot the loop: pick whichever name your SDK gives to "batch" — IN(…), bulk, mget, _msearch, pipeline, send_batch, getMany, a request-coalescer — and use it.

The hard part is spotting the loop, because the bug looks like normal code. Log the outgoing calls in dev. The same SDK call firing ten times in a row for a single request is the signal you've found one.

Next in Removing Bottlenecks: The hot partition problem — when your Kafka producer's "FIFO ordering" key concentrates 90% of the traffic onto one partition, and most of your consumer fleet sits idle next to it.