Find the PostgreSQL N+1 query in an application trace

PostgreSQL 18 scope. The SQL in this article was run on PostgreSQL 18.6 against synthetic fixtures.

An N+1 problem can hide behind individually fast queries. The application loads a page of parents, then issues one child query for each parent. Every database call looks harmless in isolation, but the endpoint pays repeated network, parsing, scheduling, and execution costs.

The diagnosis begins in the application trace, not with a single slow-query threshold. Look for the same query shape repeated with different identifiers inside one request. Then ask whether those lookups represent one set operation that the database could perform together.

Make the workload visible

Use PostgreSQL 18 and a disposable database for the SQL below. The application pattern is illustrative.

CREATE TABLE nplus_customer (
  id bigint PRIMARY KEY,
  name text NOT NULL
);
CREATE TABLE nplus_order (
  id bigint PRIMARY KEY,
  customer_id bigint NOT NULL REFERENCES nplus_customer(id),
  total numeric(10,2) NOT NULL
);
CREATE INDEX nplus_order_customer ON nplus_order(customer_id);
INSERT INTO nplus_customer VALUES (1, 'Ada'), (2, 'Lin'), (3, 'No orders');
INSERT INTO nplus_order VALUES (10, 1, 20.00), (11, 1, 30.00), (12, 2, 5.00);

The problematic endpoint selects fifty customers and then repeats SELECT ... FROM nplus_order WHERE customer_id = $1 fifty times. A trace should record request identity, query fingerprint, invocation count, and total database time. Avoid logging sensitive bound values unnecessarily; identifiers can often be correlated through safe trace metadata.

Separate sequential calls from concurrent calls. Parallelizing fifty lookups can reduce one request's wall time while increasing database concurrency and connection pressure. It is not the same optimization as eliminating redundant round trips.

Use two bounded set queries

One robust pattern is to fetch the parent page, then fetch all children for exactly that page:

SELECT id, name
FROM nplus_customer
WHERE id > 0
ORDER BY id
LIMIT 50;

-- Bind the actual IDs from the parent page in an application.
SELECT id, customer_id, total
FROM nplus_order
WHERE customer_id = ANY (ARRAY[1,2,3]::bigint[])
ORDER BY customer_id, id;

The application groups child rows by customer_id and preserves the original parent order. Parents with no children remain present because the parent result is authoritative. An empty ID array yields no child matches; handle it explicitly rather than generating malformed SQL.

This reduces the number of database round trips without forcing one giant joined rowset. It also keeps the parent page size independent of child cardinality.

Use a joined aggregate when the endpoint needs a summary

If the page needs only an order count and total, do not fetch every order object merely to aggregate it in application code.

WITH page AS (
  SELECT id, name
  FROM nplus_customer
  WHERE id > 0
  ORDER BY id
  LIMIT 50
)
SELECT p.id, p.name,
       count(o.id) AS order_count,
       COALESCE(sum(o.total), 0) AS order_total
FROM page p
LEFT JOIN nplus_order o ON o.customer_id = p.id
GROUP BY p.id, p.name
ORDER BY p.id;

count(o.id) counts actual child rows; count(*) would count the null-extended left-join row for a parent with no orders. The COALESCE defines the empty total as zero. That is appropriate for this endpoint's sum but should not be applied blindly to every nullable aggregate.

Reference request flow. Page parents first, then fetch the required child set or aggregate. The optimization preserves empty parents and page boundaries.

Avoid trading N+1 for result explosion

A direct join followed by LIMIT 50 limits joined rows, not necessarily fifty distinct customers. A customer with many orders can consume most of the page. Joining two child collections can multiply rows: ten orders and eight addresses can become eighty joined rows for one customer before aggregation.

Page the parent relation first, aggregate child relations separately, or use bounded lateral queries when the endpoint needs the top few children per parent. A lateral subquery may still execute once per parent inside the database. That can be appropriate, especially with a matching index, but inspect loops and buffers rather than calling it automatically set-optimal.

Returning all children can also be too much. A fifty-customer page may include one customer with a million orders. Define an endpoint contract: summary only, capped recent children, or separately paginated child collections. Reducing round trips should not remove resource bounds.

Preserve consistency and authorization

Two queries under read committed isolation can observe different snapshots. If the endpoint requires one consistent parent-and-child view, use a single statement or an appropriately scoped transaction with the required isolation level. Do not lengthen transactions casually; make the consistency requirement explicit.

Apply tenant and authorization predicates to both parent and child access. An ID list derived from authorized parents is useful, but the application's trust boundaries may still require explicit tenant scoping. Do not let a batching optimization turn a private lookup into an unrestricted ANY query with user-controlled identifiers.

Prepared statement behavior can differ for tiny and very large arrays. Test realistic page sizes and skew. A batch of thousands of IDs may call for a different approach than a page of fifty, such as a temporary relation or a join to a structured input set.

Measure at the request level

Compare endpoint latency, query count, total rows transferred, database CPU, and connection occupancy. Keep serialization and application grouping costs in view. An optimization that reduces SQL time but sends an enormous response may not improve the user experience.

The final evidence should connect the trace to the new access pattern: repeated fingerprint count fell, the parent page still contains the same identities, empty parents remain present, and the response has bounded size. The goal is fewer unnecessary interactions with the database, not merely fewer SQL statements at any cost.

Sources and review scope

• Table expressions and joins
• Aggregate functions
• Array comparisons
• Transaction isolation