The big idea

What makes a leaderboard hard?

"Show the top 100" and "what's my rank?" sound trivial — until it's ten million players, scores change every second, and every player refreshes their rank constantly. Suddenly you're computing a global ordering over millions of rows, live, thousands of times a second.

A real-time leaderboard needs three operations to all be fast: update a score, get a player's rank, and fetch the top-K (or a window around a player). The whole design is about the right data structure — a sorted set — plus how to scale, window, and persist it.

How to read this: Each step opens with a real design decision — make the call before I show you what ships. Watch the design grow, and at the end drop the ranking index and stall the store to see index-vs-truth and read-vs-durability. Hit Begin.

Step 1 · The naive query

ORDER BY melts

The obvious approach: a scores table, and answer with SQL. Top-K is ORDER BY score DESC LIMIT 100; a player's rank is COUNT(*) WHERE score > mine. Why does this collapse at scale?

Design decision: SQL ORDER BY for top-K and COUNT for rank. What's the problem at 10M players?

The call: Rank is O(N) per query (count everyone above you) and re-sorting live data thrashes. — A COUNT of all higher scores is linear in players, run on every "what's my rank?" and invalidated by every score change. At 10M players and high query rates it melts the database. You need a structure that keeps things sorted and answers rank in O(log N).

Rank via COUNT(*) WHERE score > mine is O(N) — you count everyone above you — run on every rank query and invalidated by every score change. At millions of players and thousands of queries per second it crushes the database. You need a structure that stays sorted and answers rank in O(log N).

The operation that's expensive: Top-K alone is cheap with an index (ORDER BY … LIMIT). The killer is rank: a global position requires knowing how many are above you, which is linear unless the structure maintains order incrementally. That single requirement drives the whole design.

Step 2 · The right shape

A service around a sorted structure

Put a leaderboard service in front, but the real question is what it stores behind it. You need one structure that makes update, rank, and top-K all fast at once. What data structure does that?

Design decision: What single structure makes update, rank AND top-K all fast?

The call: A sorted set (balanced tree / skip list) keyed by player, ordered by score. — A sorted set keeps players ordered by score with O(log N) insert/update and rank, and O(log N + K) range reads for top-K — exactly the three operations you need, all fast. Redis implements this as a ZSET (skip list + hash).

Back the service with a sorted set — a structure (skip list or balanced tree + a hash) that keeps members ordered by score and supports O(log N) insert/update, O(log N) rank, and O(log N + K) range reads. Redis ZSET is the canonical implementation: ZADD to set a score, ZREVRANK for rank, ZREVRANGE for top-K. All three operations, all fast.

Why a sorted set wins: It maintains order incrementally, so it never re-sorts. The hash side gives O(1) "does this player exist / what's their score", the skip list gives ordered rank/range. It's the single structure that makes the three leaderboard operations cheap simultaneously.

Step 3 · The three operations

Update, rank, top-K in O(log N)

Concretely: a player scores 5000, then asks "where am I?", and the UI wants the top 100. Trace how the sorted set answers each without ever scanning everyone.

Update: ZADD board 5000 player inserts or moves the player in O(log N). Rank: ZREVRANK board player returns their position in O(log N) — the structure knows how many are above without counting them. Top-K: ZREVRANGE board 0 99 WITHSCORES reads the highest 100 in O(log N + K). Every leaderboard query is now logarithmic, not linear, and updates are cheap enough to apply live.

Rank without counting: A skip list augmented with subtree sizes (or Redis's span-annotated skip list) can compute rank by summing spans along the search path — O(log N), no scan. This is the algorithmic trick that turns the impossible O(N) rank query into a cheap one.

Step 4 · "Players near me"

Relative leaderboards

Most players aren't in the global top 100 — they want to see themselves in context: the handful of players just above and below them, and their own rank. How do you serve a window around an arbitrary player cheaply?

Design decision: A player ranked 4,281,900 wants the players right around them. How?

The call: Fetch the whole leaderboard and find them in it. — Pulling millions of entries to locate one player is exactly the linear cost you eliminated. Use rank + a bounded range read instead.

Compose two cheap ops: get the player's rank (ZREVRANK), then read the range around it (ZREVRANGE rank-5 rank+5). Both are O(log N)-ish, so a "players near me" view — the most-used leaderboard screen — is served instantly for anyone, anywhere in the ranking, without touching the millions of entries in between.

Rank + range = context: The relative leaderboard is why the sorted set matters so much: it turns "show this specific player in context" — meaningless in a hash, linear in an array — into a rank lookup plus a tiny window read. Almost every player interaction is this pattern.

Step 5 · Beyond one node

Sharding a global ranking

One Redis instance holds a lot, but a truly massive game (or a single hot board) can exceed one node's memory or throughput. Sharding a leaderboard is harder than sharding a KV store, because rank is global — it depends on everyone. How do you distribute it?

Design decision: The board exceeds one node. Why is sharding a ranking uniquely hard?

The call: Partition by score range (or region); global rank must be assembled across shards or approximated. — Split the score space into ranges each owned by a shard (0–1k, 1k–10k, …), or shard by region. A player's global rank = their local rank plus the counts of all higher-range shards — so rank requires cross-shard aggregation, which is why huge boards often serve approximate/percentile ranks instead of exact ones.

Partition the ranking — commonly by score range (each shard owns a band of scores) or by region. Top-K within a shard is local; a global rank = the player's local rank plus the total counts of all higher-scoring shards, so it requires cross-shard aggregation. Because exact global rank over shards is expensive, massive leaderboards often serve an approximate or percentile rank ("top 3%") and keep exact rank only for the top tier.

Rank is global, and that's the catch: A KV store shards cleanly because each key is independent. A rank isn't — it's a fact about the whole population. So sharding a leaderboard trades exactness for scale: exact for the small hot top, approximate/bucketed for the enormous tail (histograms of score → count give O(1) percentile).

Step 6 · Not one board but many

Time windows & resets

Players want a daily, weekly, and all-time board — and the daily one must reset cleanly at midnight without a giant blocking recompute. How do you run many time-boxed leaderboards?

Keep a separate sorted set per window: board:daily:2026-07-11, board:weekly:2026-W28, board:alltime. A score update writes to all active windows. "Reset" is trivial — a new day is just a new key, and the old one expires via TTL (no blocking wipe). Rolling windows (last 24h) use time-bucketed sub-sets you union, or a decay function. All-time is the durable one; windowed boards are cheap and disposable.

A key per window: Time-windowing by key turns "reset the leaderboard" from a dangerous mass operation into creating a fresh, empty sorted set and letting the old one TTL away. Writes fan out to the few active windows; reads pick the key they want. Simple, and no reset downtime.

Step 7 · Don't lose the scores

Durability behind the index

Redis is fast because it's in-memory — but the leaderboard's scores are real player progress you must not lose, and score writes can arrive as a firehose. How do you get both speed and durability?

Treat the sorted set as a fast, rebuildable index and keep a durable database as the source of truth for scores. High-volume updates flow through a stream/queue, applied to both the ZSET (for instant ranking) and the DB (for durability) — reads always hit the in-memory index. If Redis is lost, rebuild the ZSET from the DB. Use Redis persistence (AOF) too, but the DB is the real backstop.

Index vs source of truth: The ZSET is derived data optimized for the read pattern; the database is the authoritative store. Decoupling them means reads are always fast (memory), durability is guaranteed (DB), and the index is disposable — you can always regenerate the ranking from the truth.

Step 8 · The sharp edges

Ties, huge N, and cheaters

Real leaderboards get gamed and get messy: two players with the same score need a deterministic order, exact rank is meaningless for player #4,281,900, and someone will submit a score of 2 billion.

Break ties deterministically — e.g. earlier-achieved wins, by encoding a timestamp into the score's fractional part so order is stable. For the long tail, serve approximate rank / percentile from a score histogram (O(1)) instead of an exact global position no one needs. Stop cheating with server-side score validation, rate limits, anomaly detection, and signed submissions — never trust a client-reported score. And spread hot shards (a viral board) with replicas and read caching of the top-K.

Design for the unhappy path: Ties → deterministic tiebreak in the score. Rank at huge N → percentile from a histogram. Untrusted clients → validate server-side. Hot board → replicate + cache top-K. The elegant sorted set needs these guards to survive real players (and cheaters).

You did it

You just designed a leaderboard.

• S — Q
• A —
• R — e
• R — e
• S — h
• A —
• K — e