Nov 20, 20265 min read

The N+1 Query Problem in Backend APIs

What the N+1 query problem is, why ORMs and GraphQL resolvers create it, how to spot it in traces and pg_stat_statements, and the three fixes: joins, batched ANY queries and DataLoader-style batching.

Illustration for The N+1 Query Problem in Backend APIs

An endpoint that lists 50 orders with their line items can make 51 database queries without anyone writing a loop around SQL, because the ORM lazily loads items per order or a GraphQL resolver fetches them per parent.

Each query takes a millisecond, so it feels fine locally and slows down in production, where every round trip crosses the network.

Where it comes from

The pattern is always the same: fetch a list, then fetch related data per item.

orders := listOrders(ctx, customerID) // 1 query for _, o := range orders { o.Items = listItems(ctx, o.ID) // N queries }

ORMs hide it behind property access, such as order.items triggering a lazy load, and GraphQL makes it structural, since each field resolver runs independently for every parent object. The cost is mostly latency rather than database load: with 1 ms of network round trip per query, 100 orders cost 100 ms in round trips alone.

Detecting it

Two signals catch most cases:

  • Traces. A request trace that shows dozens of nearly identical queries under one endpoint is an N+1, and it is visible on the first request, even with small data.
  • Call counts. In pg_stat_statements, a query like SELECT ... FROM order_items WHERE order_id = $1 with a calls count many times higher than the list endpoint's request rate points to the same loop.

Tests can also assert on query counts per request, which stops the problem from returning when someone adds a new field.

Fix 1: join

When the related rows are needed in full and the cardinality is small, a single join returns everything:

SELECT o.id, o.created_at, i.sku, i.quantity FROM orders o LEFT JOIN order_items i ON i.order_id = o.id WHERE o.customer_id = $1 ORDER BY o.created_at DESC, o.id;

Joins duplicate parent columns for every child row and break LIMIT on the parent, since the limit applies to joined rows. For paginated lists, the next fix is usually better.

Fix 2: batch with ANY

Fetch the parents, collect their IDs, and fetch every child in one query with = ANY, which takes an array parameter:

SELECT order_id, sku, quantity FROM order_items WHERE order_id = ANY($1) ORDER BY order_id;

Two queries regardless of list size, pagination stays on the parent query, and an index on order_items (order_id) serves both. This is what ORM eager loading does under the hood, for example GORM's Preload or include in Prisma relation queries.

Fix 3: batching loaders for GraphQL

In GraphQL, resolvers cannot see each other, so the fix is a loader that collects every key requested during one tick and issues one batched query. DataLoader popularized the pattern, and ports exist for most languages. The loader must be created per request so cached results never leak between users.

Best fit: joins for small, unpaginated relations, ANY batching for lists and APIs, per-request loaders for GraphQL resolvers.

Spotting and fixing N+1 in Encore

Encore

Encore is a backend framework with built-in infrastructure, and every database query a service runs is recorded in the request's trace, so an N+1 shows up in the local development dashboard as a column of repeated query spans the first time you call the endpoint. The batched version reads like this:

Get started
brew install encoredev/tap/encore &&
encore app create
Copy

Run in your terminal to get started locally.

package orders import ( "context" "time" "encore.dev/storage/sqldb" ) var db = sqldb.NewDatabase("orders", sqldb.DatabaseConfig{Migrations: "./migrations"}) type Item struct { SKU string `json:"sku"` Quantity int `json:"quantity"` } type Order struct { ID int64 `json:"id"` CreatedAt time.Time `json:"created_at"` Items []Item `json:"items"` } type ListResponse struct { Orders []*Order `json:"orders"` } //encore:api auth method=GET path=/customers/:customerID/orders func List(ctx context.Context, customerID int64) (*ListResponse, error) { rows, err := db.Query(ctx, ` SELECT id, created_at FROM orders WHERE customer_id = $1 ORDER BY created_at DESC LIMIT 50`, customerID) if err != nil { return nil, err } defer rows.Close() resp := &ListResponse{} byID := map[int64]*Order{} var ids []int64 for rows.Next() { o := &Order{Items: []Item{}} if err := rows.Scan(&o.ID, &o.CreatedAt); err != nil { return nil, err } resp.Orders = append(resp.Orders, o) byID[o.ID] = o ids = append(ids, o.ID) } if err := rows.Err(); err != nil || len(ids) == 0 { return resp, err } // One query for every order's items instead of one per order. items, err := db.Query(ctx, ` SELECT order_id, sku, quantity FROM order_items WHERE order_id = ANY($1)`, ids) if err != nil { return nil, err } defer items.Close() for items.Next() { var orderID int64 var it Item if err := items.Scan(&orderID, &it.SKU, &it.Quantity); err != nil { return nil, err } byID[orderID].Items = append(byID[orderID].Items, it) } return resp, items.Err() }

encore run starts Postgres locally, so the trace before and after the change is two requests apart, and in production the same trace view covers requests against RDS or Cloud SQL in your own account. The databases docs cover queries and migrations, and the service-to-service call docs matter for the distributed version of the problem, where a loop calls another service once per item.

An AI coding agent writes the per-item loop easily because it reads naturally, and with traces available locally it can be asked to check the query count for a new endpoint before opening the pull request.

Best fit: services where list endpoints and service calls need to stay at a constant number of queries per request.

How to approach it

Open a trace for every list endpoint and count the queries. Replace per-item loads with an ANY batch or a join, add per-request loaders in GraphQL, and add a test that fails when the query count grows with the list size.

Frequently asked questions

What is the N+1 query problem?

It is when code runs one query to fetch a list of N rows and then one more query per row to fetch related data, for N+1 queries in total. Each query is fast, but the round trips add up, so an endpoint listing 100 orders with their items makes 101 database calls.

How do I detect N+1 queries?

Look at a trace of the request and count the database spans: a list endpoint with dozens of nearly identical queries is an N+1. In pg_stat_statements, a simple lookup by foreign key with a call count far higher than the endpoint's request count points to the same thing.

How do I fix N+1 queries without an ORM?

Collect the parent IDs and fetch all children in one query with WHERE parent_id = ANY($1), then group the results in memory by parent ID. That turns N+1 queries into two regardless of the list size.

How do I see N+1 queries in an Encore app?

Open the request's trace in the local development dashboard after calling the endpoint under encore run. Encore records every database query as part of the trace, so an N+1 appears as a long run of repeated query spans under one request.

Deploy to your cloud on AWS and Google Cloud
without managing infrastructure

Encore lets developers and agents define application infrastructure directly in code, then automatically provisions it from local development to production in your AWS or GCP account.

~/orders — claude
Claude Code
Claude Code v2.1.180Opus 5.5~/orders