Design a game leaderboard (top-K ranking)

First questions. Is the score a running total or a best single result? Do scores only go up? What happens on ties? Is there one global board or also friends, regional, daily and weekly boards? How fresh must the top 100 be, and does every player need an exact rank or only the top few thousand? Is there a season reset? Who produces scores, the client or the server?

Assumed scope: 50 million players, one global board per season plus daily and weekly boards, a score that only increases, scores written by the server from verified match results, exact rank for everyone, freshness of a few seconds. Out of scope: anti-cheat detection, matchmaking, and rewards.

Planning figures for the exercise, not facts about any company.

• Writes: assume 10 million daily active players × 10 matches = 100 million score updates a day ÷ 86,400 s = about 1,200 a second; at a 5× peak, about 6,000 a second. That is above the 5,000-a-second planning figure for one durable primary, so the durable table must be partitioned. Four shards put each at 1,500 a second.
• Index size: 50 million players × about 100 B per sorted-set entry (id, score, structure overhead) = 5 GB. One in-memory node holds the whole ranking.
• Depth: log2 of 50 million is about 26 (2^25 is 33.5 million, 2^26 is 67 million). Insert and rank lookup are about 26 steps each, so the ranking structure is not the bottleneck.
• Reads: 10 million players × 20 board views a day = 200 million ÷ 86,400 = about 2,300 a second; at 5×, 11,600 a second. That is under 20,000, but the top 100 is the same for everybody, so cache that one blob and leave only per-player rank lookups (roughly half the reads) for the index.
• Rebuild: if the index is lost, reloading 50 million entries at an assumed 100,000 inserts a second takes 50,000,000 ÷ 100,000 = 500 s, about 8 minutes.

The numbers say: one sorted set is enough for the ranking, the durable store is what needs partitioning, and the top 100 should be served from a cached blob.

Treat the table and the log as truth, and the ranking index as a derived structure that can be rebuilt.

1. Ingest. A finished match emits one event per player: {match_id, player_id, points} to a log partitioned by player_id. Per-player order is kept, and the idempotency key is (match_id, player_id).
2. Updater. A consumer applies the event to the durable table player_scores(player_id PK, season, total, updated_at), sharded by player id into four shards. A unique row applied_matches(match_id, player_id) written in the same transaction rejects a duplicate delivery. The consumer then sets the player's value in the ranking index.
3. Ranking index. An in-memory sorted set per board, lb:season:42, member is the player id and the sort value is a number. The update sets the absolute total, never an increment, so a replay is harmless. Redis sorted sets have a GT option on ZADD (check the current docs) that only applies a higher value, which protects against an out-of-order write.
4. Ties. Make the tie-break part of the sort value. Use total × 1,000,000 + (1,000,000 − minutes_since_season_start) so that the player who reached a total first ranks higher. With totals up to 10 million that is under 10^13, inside the range a double represents exactly (about 9 × 10^15). Check the store's exact-integer range.
5. Read: top 100. A refresher runs the range query for the top 100 once a second and writes a small blob to a cache or CDN key with a 1 s TTL. Read traffic never reaches the index for this.
6. Read: my rank. A reverse-rank lookup on the sorted set gives the exact position in about 26 steps. The neighbours (five above and below) are a range query from that rank. A friends board is a batch of score lookups for the friend list (a few hundred ids), sorted in the application.
7. Periodic boards. Daily and weekly boards are separate keys, lb:daily:2026-10-19, written by the same updater and given an expiry. A reset is just a new key, with no migration.

Hard problem 1: growing past one node. Say there are 500 million players or 100,000 writes a second. Partition the index by hash(player_id) across 16 sorted sets. The global top 100 is the merge of each shard's top 100 (1,600 candidates), which is exact, because a player in the global top 100 must be in the top 100 of their own shard. An exact rank becomes the sum of 16 "how many scores are higher than mine" counts, 16 calls per lookup. Past the top few thousand, switch to a score histogram and show "top 3%" rather than a number.

Hard problem 2: recovery. A failed-over replica may be missing the last writes, because replication is asynchronous. The updater keeps its log offset, so after a failover it replays from a few minutes back; the absolute-set write makes that safe. A full loss is the 8-minute rebuild from the table.

• Exact rank versus approximate. Pick exact while one sorted set (or a small number of them) holds everyone, since it is cheap. Flip to a histogram with "top N%" once a rank needs a wide fan-out or the board has hundreds of millions of rows.
• Sharding by player id versus by score range. Player-id hash spreads load evenly and makes top-K a simple merge, but rank costs a fan-out. Score ranges make rank cheap but pile players into popular score bands. Pick the hash; flip to ranges only when rank lookups dominate and the band sizes are well known.
• Refreshing the top 100 on a timer versus on every write. Pick the 1 s timer: it costs one range query a second no matter what, while per-write refresh costs 6,000 a second. Flip to push only if the product demands sub-second changes at the very top.
• Table as truth versus in-memory index as truth. Pick the table plus log, because replication and snapshots can lose recent writes. Flip to index-only for a casual game where a few lost scores after a crash are acceptable.

Common mistakes here:

• Using SELECT ... ORDER BY score DESC LIMIT 100 and a COUNT(*) WHERE score > mine on every request against a large table.
• Forgetting ties, so two players share a score and the order flips between refreshes.
• Writing increments to the index, so a replayed event inflates a score.
• Treating the cache or the index as the only copy with no way to rebuild.
• Sharding by player id and then claiming the top 100 is just the top 100 of one shard.
• Recomputing the top 100 on every request instead of serving one shared blob.
• Trusting scores sent by the client.