Getting Elo Leaderboard Queries Under 100ms

The 42 Elo Leaderboard had one performance target that mattered more than any other: the public leaderboard and player profile pages had to stay under 100ms, even during a tournament weekend when half the campus was refreshing the standings between matches. It ended up averaging 30ms. Getting there wasn't one clever trick — it was refusing to guess, and fixing the two or three query shapes that were actually slow.

Start with what the queries actually look like

The leaderboard view sorts every active player by rating, descending, and paginates. The player profile view pulls a single player's match history, filtered by player ID and ordered by date. Both look trivial written as SQL, and both were the first thing to fall over under load, because a default Postgres table only has an index on its primary key — every ORDER BY rating DESC or WHERE player_id = ? was doing a sequential scan and sorting the result in memory:

SELECT id, username, rating
FROM players
WHERE active = true
ORDER BY rating DESC
LIMIT 50;

SELECT * FROM matches
WHERE player_id = $1
ORDER BY played_at DESC;

Fine on a seed table of forty rows. Not fine once match history accumulates across seasons and the leaderboard is being polled by dozens of open tabs at once — the sequential scan cost grows with the table, right when tournament traffic is at its highest.

EXPLAIN ANALYZE before touching anything

The instinct when a query is slow is to add an index and see if it helps. That's guessing. EXPLAIN ANALYZE tells you exactly what the planner is doing and where the time actually goes, and it's the difference between fixing the real bottleneck and adding an index that never gets used:

EXPLAIN ANALYZE
SELECT id, username, rating FROM players
WHERE active = true ORDER BY rating DESC LIMIT 50;

-- Seq Scan on players  (cost=0.00..842.10 rows=12000 width=24)
--   Filter: active
--   -> Sort  (cost=... actual time=41.220..41.310 rows=50 loops=1)

A sequential scan followed by an in-memory sort on every request, on a table that's mostly read and rarely written, is exactly the case a composite index is for.

Composite indexes matched to the query, not the table

A single-column index on rating alone still leaves the planner filtering active separately. The fix is a composite index built in the same order the query filters and sorts, so Postgres can walk it directly instead of touching the heap and sorting after the fact:

CREATE INDEX idx_players_active_rating
    ON players (active, rating DESC);

CREATE INDEX idx_matches_player_played
    ON matches (player_id, played_at DESC);

Same principle both times: an index is only as good as its match to the actual WHERE/ORDER BY shape a view issues. Re-running EXPLAIN ANALYZE after adding these turned the leaderboard query from a sequential scan and sort into an index scan that returns the top 50 rows without touching the rest of the table — the query plan, not a benchmark number, is what confirmed the fix actually worked.

Not every read belongs in Postgres

Win/loss streaks and rating deltas are derived from match history — computable with an aggregate query, but there's no reason to recompute them on every profile view when they only change when that specific player's match history changes. Those went into Redis, written once on the match-submission write path and invalidated for the affected players' keys instead of on a TTL:

func (s *Store) InvalidateStats(playerIDs ...int64) error {
    keys := make([]string, len(playerIDs))
    for i, id := range playerIDs {
        keys[i] = fmt.Sprintf("stats:player:%d", id)
    }
    return s.redis.Del(ctx, keys...).Err()
}

Invalidate-on-write instead of expire-on-timer matters here because a stale streak is wrong in a way players notice immediately after a match — a TTL either serves stale data for up to its full window or gets set so short it stops saving any real work. Tying invalidation to the write that actually changes the data means the cache is never wrong, just occasionally cold.

Keeping the write path from slowing the read path

Match submissions are read-modify-write against a shared rating and needed row-level locking to stay correct under concurrent reports — that's a correctness problem, covered in more detail on the project page. What matters for query performance specifically is that those locked, transactional writes are isolated to the match-submission endpoint. The leaderboard and profile reads never wait on that transaction, because they're a separate code path hitting indexes that satisfy them without a lock in sight. Keeping the hot read path free of anything the write path is doing was as much a factor in the 30ms average as the indexes themselves.

The actual lesson

None of this required exotic tooling — a composite index matched to the real query shape, a cache invalidated by the write that changes its data instead of a timer, and EXPLAIN ANALYZE run before and after every change instead of trusting intuition about what's slow. The same discipline shows up in the raytracer's SIMD work: profile first, change one thing, measure again. Guessing at optimizations is how you end up with an index nobody uses and a bottleneck nobody found.

← Back to the 42 Elo Leaderboard project

Getting Elo leaderboard queries under 100ms