What we fixed: making the class list fast for a thousand students

A teacher with a large batch told us the Messages page was slow. They were right, and the reason turned out to be the most classic bug in database-backed software.

The symptom

Opening the instructor inbox took about three seconds with 120 students. It got worse as the batch grew, which is the tell: the page was doing work proportional to the number of students, not to the number of things on screen.

The diagnosis

The inbox needed two facts about each student — the last message in that conversation, and how many of their messages were unread. The code asked for them one student at a time: for each enrolled student, fetch their most recent message, then count their unread messages.

Two queries per student. At 120 students that is 240 queries, plus the page's own — we measured 261 queries and 3.0 seconds. At 1,000 students it would have been over two thousand.

This is called an N+1, and it hides well: written with Promise.all, it looks parallel. It is not — it is still one network round-trip to the database per row.

The gradebook had the same shape, worse: about five queries per student, 501 queries for the same class.

The fix

Ask the database the whole question once, instead of asking it the same small question repeatedly.

• Unread counts became a single grouped count over the whole course.
• Newest message per student became one query using a window function, which ranks each conversation's messages by date and keeps the top one.
• Marksheets were restructured so course-level data (which exercises exist) is fetched once, and per-student data is fetched for every student at once rather than one at a time.

We also added the indexes those queries need, capped message threads at the most recent 200, and paginated both pages — because rendering a thousand rows is slow even when the data arrives instantly.

The numbers

Measured on a seeded course with 1,000 enrolled students and 3,880 messages:

• Instructor inbox — 2,001 queries became 24
• Gradebook — about 5,001 queries became 25
• Platform admin — three queries per instructor became one for the whole page

Both pages now render in under 200 ms, and — the part that actually matters — the query count no longer moves when the class gets bigger.

The rule we kept

A list page must issue a fixed number of queries regardless of how many rows it lists. It is written down now, and every new page is checked against it.