My Diagnosis Was Wrong: What Profiling a 60-Million-Row Leaderboard Actually Taught Me

Short summary

A developer assumed a GROUP BY ranking query over 60M rows was catastrophically slow, but profiling revealed 567ms. The real problem was paying that cost on every request for a result that barely changes. After ruling out indexing, cursor pagination, and archiving, caching the full sorted ranking emerged as the right architectural fix rather than making the query faster.

• •Profiling disproved the assumption that a 60M-row aggregate query was slow — it ran in 567ms
• •The real cost was repeated execution of an irreducible full-scan-and-sort for a rarely-changing ranking
• •Caching the complete sorted result, not per-page, is the correct architectural response

Generated with AI, which can make mistakes.