Day 114: From N+1 Queries to Batch Aggregation — The Right Order for List Page Performance

After adding "per-article excerpt + tag cloud" to the category pages last week, everything looked fine locally. Once it went live, the category list page started taking two seconds to load page 2 during peak hours. Today I didn't write any new features; I spent the day walking through this list-page performance problem from start to finish, and along the way I understood a bit better what to do first — and what to do later — when optimizing performance.

Locating the Symptom: Measure Before You Change Anything

The first step was not changing code but splitting the request time into segments. I added a timing point at the API entry and another at the database connection layer, stitched together by request_id, and the conclusion was clear: across a couple thousand log lines, the list endpoint took about 1,800 ms, of which roughly 1,600 ms was on the database side — a textbook database bottleneck, not network or template rendering.

Next I sorted pg_stat_statements by total elapsed time, and within twenty minutes found the culprit: the tag query ran an average of 320 times per request. The list page returns twenty articles, so it fired one tag query per article — nearly four hundred SQL statements in total, pushing right up against the queue limit. This is a textbook N+1 query. Each round trip looks like only a few milliseconds, but multiply by the count and then by concurrency, and it becomes the two seconds users actually feel.

Why N+1 Always Shows Up on List Pages

ORMs and lazy loading make this too easy: you write article.tags in your code, and the framework helpfully runs one query per article. A single detail page is fine; a list of twenty means twenty round trips. Each round trip against a same-city database costs roughly three to five milliseconds — not much on its own, but they're serial, so times twenty is seventy or eighty milliseconds. Stack pagination and concurrent requests on top, the connection pool fills up with short queries, and queuing latency amplifies everything further. On top of that, parsing and plan generation for each query are fixed overheads; batching amortizes them, which often pays off more than adding an index to a single SQL statement.

The Fix: Two Steps, In Order

Step 1: Batch IN Query

Without changing the data structure, I made the most basic rewrite: after the list query gets twenty article ids, fetch all their tags in one shot with WHERE article_id = ANY($1), then group them into a map by article_id in the application layer. This step was almost non-invasive: the business code still assumes "each article carries its own tags" — only the fetch strategy changed. After shipping it, the list endpoint dropped from about 1,800 ms to about 500 ms. The N+1 was gone.

Step 2: Push Aggregation Down Into the Database

But most of the remaining time was still in SQL. So I pushed the aggregation down as well: one SQL statement with LEFT JOIN plus array_agg returns the tags of all twenty articles as just two columns, article_id, tags[]:

SELECT a.id, array_remove(array_agg(t.name ORDER BY t.name), NULL) AS tags
FROM articles_v4 a
LEFT JOIN article_tags at ON at.article_id = a.id
LEFT JOIN tags t ON t.id = at.tag_id
WHERE a.category_id = $1 AND a.locale = $2 AND a.status = 'published'
GROUP BY a.id
ORDER BY a.published_at DESC
LIMIT 20;

One statement replaced the previous two rounds of work — "fetch the list, then fetch tags per article." The real enemy of a database is the number of round trips; concurrent connections are just the amplifier. This lesson has been verified the hard way, repeatedly. First consider eager loading: fetch the tags of all twenty articles together, and the list page's cost stops growing linearly with page size. Then consider SQL-level aggregation: the deeper the pagination, the more it pays to push filtering, joins, and aggregation into the same statement.

Spillover Benefits

The earlier in the pipeline you optimize, the cheaper the win. After converting the list endpoints across the board to the batch style, endpoint latency came down to ninety milliseconds. This experience reinforced the point: don't let intuition jump you straight into indexes and caches. Locate, fix the biggest win first — the order matters far more than the tooling.

Checklist

• Stitch segmented timings with request_id first; confirm whether the bottleneck is in the app, the database, or the network
• Sort pg_stat_statements by total elapsed time; measure, don't guess
• Default list endpoints to batch loaders or JOINs to avoid N+1
• Any "per-article × child table" read deserves a separate execution-count review in list scenarios
• Prefer aggregating within the same query round, then consider indexes, and only then consider caching
• Archive before/after benchmark results with each change

After the optimization, the list endpoint's median latency during peak hours stayed under ninety milliseconds, and the slow-query alerts stopped firing. This only fixed the most expensive segment — but with the order right, the rest of the work finally has a foundation.