The N+1 Query Problem: Symptoms, Detection, and Fixes

Infrastructure PostgreSQL Database
4 min read

A large share of “the database is slow” reports are not about the database but about query counts — above all, N+1. One query for the listing (1) plus one query per row in the listing (N): N+1 queries total. Each query is milliseconds, so everything looks fine in development — then row counts and traffic grow, and connections and round trips multiply. This article walks the whole arc: how it forms, how to detect it, how to fix it, how to prevent it.

How it forms: lazy loading #

app.py 
# The ORM code looks harmless
orders = session.query(Order).limit(100).all()   # 1 query
for order in orders:
    print(order.user.name)                        # 1 query each time — 100 total

ORM relationship fields commonly default to lazy loading: touching order.user fires a SELECT for that user at that moment, so no query is visible in the code, but a number of queries equal to the loop count are issued at runtime. The essence of the problem is not the ORM but the code shape of “iterate a collection while touching a relationship” — hand-written SQL code of the same shape has the same problem.

Detection: logs and pg_stat_statements #

In development, the application side is fastest: turn on the ORM’s query log (SQLAlchemy echo=True, Django connection.queries, and so on) and count queries per request. Enforcing a per-request query ceiling in tests (query-count asserts) adds regression protection on top.

In production, the database’s cumulative statistics are the accurate source: find queries with abnormally high calls in pg_stat_statements.

Top queries by calls 
SELECT calls, round(mean_exec_time::numeric, 2) AS mean_ms,
       left(query, 60) AS query
FROM pg_stat_statements
ORDER BY calls DESC LIMIT 10;

If single-row lookups shaped like WHERE id = $1 monopolize the top of the call counts, that’s the classic N+1 signature. The mean is 1ms, so it’s invisible in mean-time rankings — a culprit visible only in calls and total time is the key point of N+1 detection.

The fixes: three shapes #

① One shot with JOIN. Fetch the listing and the relationship in one query — join fundamentals territory, a good fit for 1:1 and N:1 relationships (order → user). For 1:N collections (user → orders), though, JOIN multiplies rows, and the next shape is better.

② Batched loading with IN. Two queries total: fetch the listing, then fetch the relationships in one WHERE user_id IN (...) sweep and stitch them together in the application. N+1 collapses to 2, with no row explosion even for 1:N.

Batched IN loading 
SELECT * FROM orders LIMIT 100;                          -- query 1
SELECT * FROM users WHERE id IN (…the listing's user_ids…);  -- query 2

③ The ORM’s eager-loading options. Let the ORM write the above two shapes for you. In SQLAlchemy that’s joinedload (the JOIN shape) and selectinload (the IN shape); in Django, select_related and prefetch_related respectively. The loading strategies covered in the SQLAlchemy course are exactly this problem’s toolbox. The working pairing: N:1 gets the JOIN shape, 1:N collections get the IN shape.

Prevention: design for data shape, not query counts #

More important than fixing is not rebuilding it. One principle: decide first on “the shape of data this screen (endpoint) needs,” then write the code that fetches that shape in the minimum number of queries. Code touching a relationship field inside a loop is the alarm site. Additionally, a per-request query ceiling enforced in CI mechanically catches the N+1s that quietly resurrect during refactoring.

Note that after all N+1s are gone, the remaining “call counts normal but slow” problems are a different family — that’s when you move to the performance diagnostics routine’s mean ordering and EXPLAIN.

Summary #

  • N+1 is the pattern of one listing query plus one per row. The cause is usually ORM lazy loading and the “relationship access inside a loop” code shape.
  • Detect in development with query logs and query-count tests; in production, with the calls ranking in pg_stat_statements.
  • The fixes are JOIN (N:1), batched IN loading (1:N), and the ORM eager-loading options that write these for you.
  • It is invisible in mean-time rankings and visible only by calls and total time — that’s the detection takeaway.
  • Prevention is the design habit of deciding a screen’s data shape first, plus a per-request query ceiling in tests.
X