The list that shipped the whole record
A decisions list took almost eight seconds to show 117 rows, because it was sending the entire record for a view that only renders a line.
By Andrew Pyle
A list endpoint that worked, just slowly, had been dressed up as a detail page the whole time.
01A heavy list
Why would 117 rows weigh a megabyte
I should have asked first why a list of 117 short rows weighed a megabyte. The page that lists my decision sessions, and the forms that hang off it, had been loading very slowly. When I timed the pieces on production, the list endpoint took about 7.7 seconds and returned about 1.06 megabytes. The detail view and the forms were fine, at about 0.77 seconds. Whatever was wrong lived on the list path alone.
That split was useful. A slow detail page would have pointed at the data or the database in general. A slow list next to a fast detail page pointed at something the list did differently. When I read the code, the list did nothing different at all. It used the same base query and the same serializer as the detail view, and that sameness turned out to be two separate bugs.
The old query was one line.
queryset = DecisionSession.objects.all().prefetch_related('decisions')It took every session and prefetched its decisions. A prefetch is a good tool for avoiding a query per child row, and it did that job. It also loaded every child row into memory, all 641 of them, for a page that never shows a single one.
02Two bugs, one symptom
Round trips and dead weight
Where the seconds went
The first bug was the time. The query prefetched each session's decisions but never joined in the parent project. So as each row was serialized, the code went back to the database through the connection pooler for that row's project, one at a time. That came to about 77 sequential round trips, and it was nearly all of the seven seconds. Each query was small and quick. There were far too many of them, each one waiting for the last.
Where the megabyte went
The second bug was the size. Because the list reused the detail serializer, every row carried a large raw field that made up about 40 percent of the payload, plus the full nested list of decisions. The list screen renders one line per session. It never shows those decisions or that raw field.
How each bug hid the other
The two bugs also covered for each other. The nested prefetch made the query look carefully tuned, because there was a prefetch right there in the code. The missing join on the parent sat next to it and was easy to read past. Reusing the detail serializer hid the missing join behind a prefetch that was doing a different job. The fix carries a code comment that calls the missing join a scar.
03Serve what you show
The list gets its own query and serializer
The base query now joins the parent project, which helps the list and the detail view alike. On top of that, the list action gets its own shape, built around what the list page shows. Instead of loading every decision to count them, it asks the database for three counts per session: how many decisions, how many were accepted, and how many carry a recommendation, using one join and a group by. It also orders explicitly by newest first, so pagination stays stable. A slim list serializer drops the two raw fields and the nested decisions. The detail view is unchanged and still returns everything.
The front end had to change too. It had been reading the nested decisions to compute those counts itself, so three places that read the list moved to the new counts: the list page, a card that shows pending decisions, and a dashboard that uses the same data. They now share a separate type for a list item, so the code says plainly which fields a list row has.
I ran the new list read-only against production with the same 117 rows.
| Measure | Before | After |
|---|---|---|
| Queries | about 78 | 1 |
| Time | about 7.7 seconds | 0.195 seconds |
| Payload | about 1.06 megabytes | about 90 percent smaller |
The regression test creates two sessions, so that a per-row lookup on the project would add queries, then asserts at most six queries for the list, asserts the heavy fields are gone, and asserts the three counts are present. The query count stays bounded however many rows there are. That suite and the filter suite passed, 19 tests, on SQLite.
One thing is still missing. I planned to re-time the live endpoint after deploy to confirm the result end to end, and I have no recorded number from that run, and by my own rule that built means exercised, that number is still owed. The change took about 78 minutes from opening to merge, one evening in early October. A list and a detail view answer different questions, and when they share a serializer, the list pays for the detail's answer on every row. This list has its own serializer now, and I read a list's payload size as a symptom before I go looking at the database.
Related
writing
A limit that was never enforced
Eighty of my scheduled tasks ran on a time limit different from the one they declared, because the loader quietly threw it away, and the first plan I built on those numbers was wrong.
writing
The pooler that turned my writes read-only
Half my writes failed with a read-only error, on a database that was never read-only.
writing
A deploy should drain, not kill
A routine deploy quietly ate a day of work, and the monitor that was supposed to notice reported nothing wrong.
writing
A cold cache is not free
My feature-flags page felt instant once I cached it, and the first request to each fresh worker after a deploy still paid about fifteen seconds.
writing
A 200 is not the same as access
My leak probe failed with seventeen leaks on its first real run. Every one of them was the same page that was never a leak.