The N+1 Query Pattern

A production incident, replayed by hand: the orders dashboard crawls, yet no single query is slow and the slow-query log is empty. Bring up the same database, replay the exact statement stream the application emits, expose the N+1 pattern by sorting pg_stat_statements by calls, and retire a thousand tiny queries with one join.

A dashboard that lists recent orders with their items melts down under load — yet every statement the database sees is tiny, so no slow-query log ever fires. The application fetches a page of orders, then quietly issues one more query per order for its items: the classic N+1 pattern, usually courtesy of ORM lazy loading. This book replays the incident on a database you bring up yourself: schema, data and the exact statement stream are plain SQL files, nothing hidden. You measure single calls and see why they look innocent, time a full page load and feel the sum, then catch the offender in pg_stat_statements — not by total time, but by an enormous calls count next to a penny mean. The fix is one join that delivers the whole page in a single round trip, and you prove it with wall-clock timings and server-side statistics side by side. The diagnosis path is the real one you'd use on call.

What you'll build

• Reproduce an N+1 access pattern on a local PostgreSQL stand
• Replay an application's statement stream verbatim from psql
• Read pg_stat_statements through the calls lens, not just total time
• Recognize the N+1 signature: huge calls, tiny mean, dominant total
• Fold a query-per-row loop into a single join and prove the win

Contents

1. The stand
2. Bring it up
3. The schema
4. Apply the schema
5. The data
6. Load it
7. A clean slate for statistics
8. What the application actually does
9. The cost of one call
10. One page load, timed
11. Let the traffic flow
12. Sort by calls, not by time
13. The fix: one query
14. The page in one round trip
15. The scoreboard