Fixing the N+1 Queries Behind Our Docker Containers Page
Our new containers page made five requests and two hundred database queries every time it loaded, and dropped every container past the hundredth without saying so. One endpoint shaped for the page, two missing joins, an hourly cache and one index brought it to two requests and seven queries, with every row present.
We'd spent the previous week rebuilding the containers page in our new web app card by card. There's a state summary at the top, a list of containers that are misbehaving, and a prune card. Below that sits a table of every container on every server, grouped by host, with a 24 hour CPU sparkline on each row. On the Monday after the last card merged, I collapsed a few server groups to check the new toggle, watched the skeleton flash for what felt like a beat too long, and decided to audit the API under the page before building anything else on top of it. Everything had been built against a dev database with a handful of containers, which is exactly the setup that hides how an API behaves once a real customer's fleet is behind it.
Counting before guessing
I didn't want to reason about the serializers one at a time, so I wrote a throwaway test that built a workspace, called each endpoint the page uses, and recorded the number of queries and the response size with Django's CaptureQueriesContext. Then it doubled the data and ran again. That second run is the useful one, because the absolute count tells you very little while the slope tells you everything. The containers list made 10 queries with six containers and 40 with thirty six, so one extra query for every row. The hosts list went from 7 to 15 with three times the servers, about two extra per server. A list whose cost grows with its length is the textbook N+1, and here there were two of them on a page that polls every sixty seconds.
For the real numbers I used a bigger fixture: ten servers across three projects, fifteen containers each, five container names on the ignore list, and two days of five minute samples, which comes to 86,400 history rows. On that, a single load of the page cost five requests and 200 queries, and added up to about 300 ms of server time. The containers list alone ran 104 queries. It also returned exactly 100 rows, although 150 containers existed.
The cap I wrote myself
That last number was the one I didn't like, because I wrote the code. Our paginator allows at most 100 items per page, the page asked for page_size=100 and never looked at the next page, and everything after that point (the state counts, the tabs, the per-host groups) is computed in the browser from whatever came back. A workspace with 120 containers would have seen 100 of them and counts that agreed with each other perfectly and were all wrong. Nothing errored and nothing looked broken, which is the worst way for a monitoring product to be wrong. The page needs every row because it filters client-side, so pagination was simply the wrong contract for it.
Where the queries went
The containers serializer shows each container's host name, and the queryset never joined the host table, so Django fetched the host lazily once per row. That fix is one line:
1(2 ContainerSnapshot.objects3 .filter(host__project__workspace=workspace)4 .select_related("host")5)
The hosts list was worse and more interesting. Per server it fetched the latest metrics row, the status row, the project, the workspace, two subscription lookups and the plan, which is seven queries per server and 73 for ten. The status and project could be joined and the metrics prefetched, but the plan lookups came from a serializer method that decides whether a host counts as online. It reads the plan's check interval to get the timeout, and it did that for every row even though every row belongs to the same workspace. A list serializer shares one child instance across all its rows, so I cached the computed timeout on that instance and the lookup happens once per request. The ignore list had the same problem in miniature, fetching the user who created each entry, and got a join as well. With those changes the hosts list runs 7 queries whether there are two servers or two hundred.
One request shaped like the page
Fixing the joins makes each request cost what it should, but the page was still making five of them, and four of those existed only to decorate the fifth. It needed the host name and online state for the group headers, the ignore list to mark containers as expected to exit, and the projects list only to turn a project id into a name. Each of those endpoints serves other pages and returns far more than this one reads. The containers rows carried volumes, linked monitors, block I/O and human formatted duplicates of fields we already had, and none of it was rendered.
So I added an endpoint that returns exactly what the table draws, with no pagination: slim container rows from a .values() query, the hosts those containers run on with their project name and agent state, and the ignored names as a plain list. It's three flat queries plus the membership lookup every authenticated request makes, whatever the size of the fleet. On the 150 container fixture it answers in about 5 ms with 4 queries. Each row now costs about 600 bytes against roughly 1,100 before, so the full list of 150 is smaller than the old capped list of 100.
Sparklines don't need to be fresh every minute
The sparklines were the slowest single request left, between 50 and 100 ms depending on the run, and the page fetched them on every poll. They show hourly CPU averages for the last day, so the only bucket that can change during a given hour is the current one, and nobody needs the current hour's partial average to the minute. The result is now cached per workspace in Redis for five minutes, under a key that includes the hour, so a new bucket starts a fresh entry on its own. A cached response takes about 2 ms. The browser asks for sparklines every ten minutes instead of every minute, and the table no longer waits for them. It renders when the container rows arrive and the sparklines draw in afterwards.
The underlying query also needed an index. History is matched by host and container name, because the name survives a container being recreated while the container id doesn't. The only composite index on that table started with host and container id, so Postgres used the host's foreign key index and filtered the names out afterwards. For one host, three names and a 24 hour window, EXPLAIN ANALYZE showed the index scan returning 8,640 rows to keep 867. With an index on (host, container_name, recorded_at DESC) it returned the 867 directly and touched 33 buffers instead of 124, and the query went from 4.1 ms to 1.1 ms. That table takes a write per container per minute, so the migration uses AddIndexConcurrently with atomic = False, the same way we built the last index on a busy table, and nobody's inserts wait for the build.
Not loading the same page twice
The last change was in the browser. Leaving the containers page and coming back used to show the skeleton again while the same five requests ran. The hook now keeps the last board in a module level variable, so a return visit paints the previous data immediately and refreshes behind it. The one thing I was careful about is that module state outlives a sign out in a single page app, so the memo is keyed to the session's refresh token. A different account in the same tab gets a cold load and never a glimpse of someone else's containers.
The drawer made the same mistake
A day later I was building the drawer that opens when you click a container, and I ran the same two-size test against the requests it makes before writing any more of it. The container itself and its CPU chart came back flat at three queries each, once I stopped the chart from prefetching monitors it never reads. The picker for linking an uptime monitor to the container did not. It borrowed the uptime page's monitor list, which is the containers page's mistake again in a smaller form, and that list made 21 queries with two monitors and 61 with twelve. Each row fetched its server, its container and that container's server one at a time, then made one more query to check DNS delegation for the monitor's domain. Joining the three relations and loading the delegation rows for the whole page in a single query brought the list to 14 queries at any size, which also speeds up the uptime page that shares it. The picker only needs a name, a URL, a status and whether another container already uses the monitor, so it now has its own endpoint that returns exactly that in two queries. Both are in the two-size test now, so a new N+1 in either one fails the build.
Results
All of this was measured on the same fixture of 150 containers and 86,400 history rows, against Postgres in Docker on a laptop, using the median of six warm runs. Production will differ in absolute terms, but the query counts don't depend on the machine.
| Before | After | |
|---|---|---|
| Requests per page load | 5 | 2 |
| Database queries per page load | 200 | 7 |
| Containers returned | 100 of 150 | 150 of 150 |
| Containers list response | 106 ms, 104 queries | 5 ms, 4 queries |
| Hosts list response | 73 queries, ~95 ms | 7 queries, 9 ms |
| Sparklines response | 50 to 100 ms, every minute | 2 ms cached, every 10 minutes |
| Queries per minute of an open page | ~188 | ~4 |
| History rows read per sparkline lookup | 8,640 | 867 |
The figure I care about most is the per minute one, because that's the cost of somebody leaving the tab open on a second monitor all afternoon, and on a containers page that's the normal case. Every open tab used to cost the database about 188 queries a minute, and now it's about 4. To keep it there, there's a test that loads each of these endpoints at two rows and at eighty and fails if the query count differs, which is cheap to write and would have caught both N+1s the day they were introduced. The broader lesson I'm taking is that an endpoint written for a model and an endpoint written for a screen are different things, and a page that polls deserves the second kind. The other is to count queries at two sizes instead of one, because a single count only tells you a number, while two counts tell you how that number grows.