A class performance report took a minute and a half
SberClass · Sberobrazovanie · 2023
Problem
A teacher opened the class summary and waited a minute and a half. During school hours these requests came in bursts and ate CPU on the main database, and assignment delivery — the main thing students actually use — slowed down along with it. The complaints were vague: the platform is laggy, with nobody pointing at the report.
What I did
- Found the real source of load instead of guessing: I took
pg_stat_statementsand sorted by total time. The top rows were not the report itself but 5 queries around it. - Ran them through
EXPLAIN (ANALYZE, BUFFERS). A classic N+1: every student triggered a separate query for results, and on top of that Hibernate loaded a full entity graph when only three fields were needed. - Where mapping into entities added nothing, I moved the queries to
JdbcTemplatewith a flat projection. This was not “dropping the ORM” — I replaced exactly the places where the ORM was in the way. - Added composite indexes matching the actual predicates, and verified that the planner really used them instead of ignoring them.
- Moved nightly aggregates into a materialized view refreshed on a schedule. Data that is up to a day old is an acceptable price here: a report about a past period does not have to be real-time.
Result
The report now builds in 12 seconds instead of 90. The bigger win was not the report itself: the heavy analytical queries stopped competing with live traffic, and the complaints about lag during school hours went away.