The N+1 problem, the most common performance bug there is

Every other lesson in this module has been about making the database faster. This one is about the most common database performance bug in real applications, which the database is not involved in at all.

It is called N+1, and once you can see it you will find it in nearly every codebase you open.

The bug

You want to show a list of members and how many loans each has.

members = db.query("SELECT id, name FROM members LIMIT 20")   # 1 query

for member in members:
    count = db.query(                                              # 20 more queries
        "SELECT count(*) FROM loans WHERE member_id = %s", member.id
    )
    print(member.name, count)

1 + 20 = 21 queries. Hence N+1.

Every individual query is fast. Each uses an index. EXPLAIN on any one of them looks perfect. pg_stat_statements shows a mean execution time of 0.3ms, which nobody investigates.

And the page takes two seconds.

Why it is so much worse than it looks

Because the cost is not the query, it is the round trip.

Each round trip pays network latency twice, plus protocol parsing, plus planning, plus a connection-pool checkout. None of that appears in mean_exec_time.

Two consequences that make this bug particularly nasty:

It does not reproduce locally. On your laptop the database is a Unix socket away and the whole thing takes 2ms. In production, across an availability zone, it takes a second. This is why N+1 reaches production so reliably.

It scales with your data, not your traffic. Twenty members is fine. When a member has 2,000 loans, or the list grows to 500 rows, the same code is 25 times slower. The bug was always there.

The fix

One query.

SELECT m.id, m.name, count(l.id) AS loan_count
FROM members m
LEFT JOIN loans l ON l.member_id = m.id
GROUP BY m.id, m.name
LIMIT 20;

One round trip. The database does the work it is designed for. This is what modules 3 and 5 were teaching you to do, and this is why.

When a join is awkward, the second-best fix is two queries, not N:

members = db.query("SELECT id, name FROM members LIMIT 20")
ids = [m.id for m in members]

counts = db.query(
    "SELECT member_id, count(*) FROM loans WHERE member_id = ANY(%s) GROUP BY member_id",
    (ids,)
)
by_id = {row.member_id: row.count for row in counts}

Two queries regardless of N. = ANY(array) is the PostgreSQL idiom and it parameterises cleanly, unlike building an IN (...) list by string concatenation, which is both a SQL injection risk and defeats plan caching by producing a different query text every time.

This is exactly what ORMs call eager loading, and it is worth knowing that it is just this.

Where it comes from: ORMs

The bug is not usually written as plainly as above. It hides behind attribute access.

Django:

# N+1 — each .author touches the database
for book in Book.objects.all()[:20]:
    print(book.title, book.author.name)

# fixed: one query with a join
for book in Book.objects.select_related('author')[:20]:
    print(book.title, book.author.name)

select_related for a forward single-valued relationship (a JOIN). prefetch_related for reverse and many-to-many relationships (a second query with IN). For aggregates, do not loop at all:

Member.objects.annotate(loan_count=Count('loans'))[:20]

Rails:

Book.all.each { |b| puts b.author.name }          # N+1
Book.includes(:author).each { |b| puts b.author.name }   # fixed

Rails will tell you: the bullet gem flags N+1 in development, and since Rails 6.1 strict_loading raises an error rather than silently issuing the query.

SQLAlchemy:

session.query(Book).options(joinedload(Book.author))       # a JOIN
session.query(Book).options(selectinload(Book.author))     # a second IN query

selectinload is usually the better default for collections, because joinedload on a one-to-many multiplies your rows — the fan-out problem from module 5, appearing again.

Prisma / TypeORM:

prisma.book.findMany({ include: { author: true } })
bookRepository.find({ relations: ['author'] })

The pattern across all of them: the ORM makes the lazy version the default, because it is the one that works without you thinking about it. Convenience by default, correctness on request.

The one that catches everybody

books = Book.objects.select_related('author')       # good

for book in books:
    print(book.title)
    print(book.author.name)                    # fine, prefetched
    print(book.publisher.city)                      # N+1, not prefetched

One field you forgot reintroduces the whole problem. Adding a line to a template months later is the usual cause, and nothing in the diff looks like a database change.

Its cousin, in a template:

{% for book in books %}
  {{ book.author.name }}    <!-- a query, from inside a template -->
{% endfor %}

A template that can query is a template that can be slow, and the person writing the HTML has no way to know.

Finding it

Count the queries. The single most effective habit, and the one to take from this lesson.

from django.db import connection
print(len(connection.queries))     # with settings.DEBUG = True

Django Debug Toolbar shows the count and flags duplicates. Rails logs every query. In a test:

def test_member_list_does_not_n_plus_one(self):
    with self.assertNumQueries(2):
        self.client.get('/members/')

Write that assertion. It is the only way the fix stays fixed — a select_related is one careless edit away from being removed, and a query-count test fails loudly when it is. This is the single highest-value testing idea in this course.

From the database side, pg_stat_statements ordered by calls:

SELECT calls, round(total_exec_time) AS total_ms, round(mean_exec_time, 3) AS mean_ms, query
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 20;

A query with a tiny mean and an enormous calls is an N+1, every time. This is why the previous lesson said to order by total time rather than mean: sorting by mean_exec_time hides this bug completely.

Application performance monitoring tools show it visually — a waterfall with fifty identical narrow bars is unmistakable.

The opposite mistake

Do not now fetch everything eagerly.

Book.objects.select_related('author', 'publisher').prefetch_related(
    'categories', 'loans', 'reviews', 'reviews__member'
)

That is one query returning far more data than the page shows, and the fan-out can multiply rows badly. Prefetch what the page uses. Nothing more.

There is a genuine middle ground where N small queries are acceptable: N is small and bounded, the data is cached, or the loop runs in a background job where latency does not matter. The judgement is is N bounded, and is the round trip cheap — not "loops are bad".

Beyond the ORM

Two other places the same shape appears.

GraphQL has an N+1 problem by construction: each resolver fetches its own field, so a nested query fans out into a query per node. The standard answer is DataLoader, which batches the calls made within one tick of the event loop into a single IN query. If you write GraphQL, you will use it.

REST clients do it across HTTP: GET /books, then GET /authors/{id} twenty times. Same bug, 50ms round trips instead of 0.5ms, and much more visible.

Check your work

What N+1 is. One query for a list, then one more per row.

Why EXPLAIN will not find it. Every individual query is fast and correctly indexed.

Where the cost actually is. The round trip — network latency, parsing, planning, pool checkout.

Why it does not reproduce locally. A Unix socket is ~0.05ms; a cross-AZ hop is ~2ms, about 40× worse.

Why it gets worse over time. It scales with data volume, not traffic.

The best fix. One query with a join and an aggregate.

The second-best fix. Two queries, using = ANY(array) — not a concatenated IN list.

Two problems with a concatenated IN list. Injection risk, and a new query text each time defeats plan caching.

Django's two tools and when each applies. select_related for forward single-valued (a join); prefetch_related for reverse and many-to-many (a second query).

Why selectinload often beats joinedload for collections. joinedload multiplies rows — fan-out.

The mistake that reintroduces the bug. Accessing one relation you did not prefetch.

The habit worth building. Count the queries.

The test worth writing. assertNumQueries, so the fix cannot silently regress.

How to spot it in pg_stat_statements. Tiny mean, enormous calls — which sorting by mean hides.

The opposite mistake. Prefetching everything, which over-fetches and fans out.

When N small queries are acceptable. N is bounded and small, or the data is cached, or latency does not matter.

GraphQL's version and its fix. A resolver per field; DataLoader batches them.

Practice

1. Write the 21-query version against the library database and count the queries.
2. Rewrite it as one query with a LEFT JOIN and count. Compare wall-clock time.
3. Rewrite it as two queries with = ANY. Compare with both.
4. Time all three against a database on another machine, or add artificial latency with tc qdisc or a proxy. Note how the ranking changes.
5. Grow the list from 20 rows to 500 and re-time all three.
6. In an ORM project, find a loop that accesses a relation and count the queries it issues.
7. Fix it with the eager-loading tool and confirm the count drops.
8. Write an assertNumQueries test around it.
9. Deliberately add one un-prefetched field access and watch the test fail.
10. Install Django Debug Toolbar (or bullet for Rails) and load a page you wrote. Read the duplicate-query warning.
11. Query pg_stat_statements ordered by calls and identify the highest-count query in a real project.
12. Sort the same data by mean_exec_time and confirm the N+1 query is nowhere near the top.
13. Compare joinedload and selectinload on a one-to-many and count the rows each returns.
14. Over-prefetch deliberately: chain four prefetch_related calls and measure the bytes transferred.
15. Find an N+1 in a real open-source project and write down how you found it.

Official documentation

• Django — select_related and prefetch_related — Which to use when, in the authors' own words.
• Django — Aggregation — annotate(Count(...)), the right answer to counting in a loop.
• Django — assertNumQueries — The test that keeps the fix.
• SQLAlchemy — Relationship loading techniques — Every loader strategy and its trade-off.
• Rails — Eager loading associations — includes, preload and eager_load.
• Prisma — Relation queries — include and select.
• GraphQL DataLoader — The batching pattern, with the reasoning.

Next module: designing a schema that stays workable.