The N+1 query problem

One query fetches the parents and then a separate query runs for every parent to get its children, so 500 orders cost 501 round trips. Each query is fast, so it hides in development, but with 500 rows and 1 ms of network latency it adds half a second. Fix it by loading children for all parents in one query, with a JOIN or WHERE parent_id = ANY(%s), or with the ORM's eager loading.

Spot it in logs as the same query repeated with different ids, or with an APM trace showing hundreds of short spans.

GraphQL resolvers cause it naturally; the DataLoader pattern batches and caches per request.

A JOIN can multiply rows (parents × children); for two independent child lists, two batched queries are often better.

Add a test that counts queries for an endpoint so it can't regress.

from collections import defaultdict

import psycopg

with psycopg.connect(autocommit=True) as conn:
    conn.execute("CREATE TEMP TABLE orders (id int PRIMARY KEY)")
    conn.execute("CREATE TEMP TABLE items (order_id int, sku text)")
    conn.execute("INSERT INTO orders SELECT generate_series(1, 3)")
    conn.execute(
        "INSERT INTO items VALUES (1, 'a'), (1, 'b'), (3, 'c')"
    )

    ids = [
        r[0]
        for r in conn.execute(
            "SELECT id FROM orders ORDER BY id"
        ).fetchall()
    ]
    items: defaultdict[int, list[str]] = defaultdict(list)
    for order_id, sku in conn.execute(
        "SELECT order_id, sku FROM items"
        " WHERE order_id = ANY(%s) ORDER BY order_id, sku",
        (ids,),
    ):
        items[order_id].append(sku)
    print({i: items[i] for i in ids})

Common mistakes

• Fixing it with a cache instead of batching.
• Eager-loading everything on every endpoint, which over-fetches.
• Assuming the ORM batches automatically.

Explain the round-trip cost, show the batched query, and mention a query-count test to keep it fixed.

Follow-up questions

Think of your answer first, then open the hint.

1. How does GraphQL avoid N+1?

   Show hint

   Batch loaders that collect keys during one tick and issue one query.

2. JOIN or two queries?

   Show hint

   JOIN for one-to-one or small one-to-many; separate batched queries for several collections.