The whole design, in writing
Learn system design by building a real-time leaderboard like a game ranking or Reddit hot list step by step. An interactive guide covering why SQL ORDER BY + COUNT melts at scale, the sorted-set (Redis ZSET) that gives O(log N) rank and top-K, relative leaderboards around a user, sharding a global ranking, time-windowed boards with resets, a durable source of truth behind the in-memory index, and the unhappy paths (ties, approximate rank at huge N, anti-cheat, hot shards).
Every step of the build above, written out: the problem each piece solves, the option that was taken and the ones that were not, the numbers, and how it fails in production.
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.
What the new pieces do
- Playersclient
- Millions of players submitting scores and constantly asking "what's my rank?" and "show the top 100" — in real time.
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?
SQL ORDER BY for top-K and COUNT for rank. What's the problem at 10M players?
SQL sorts fine; the issue is cost. Computing a global rank means counting how many scores beat yours — an O(N) operation — on every rank query, thousands of times a second, over constantly-changing data.
The results are correct — they're just far too slow. Each rank query scans/counts a huge portion of the table, and scores change constantly, so you can't just cache the answer.
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).
Why this piece earns its place
The first instinct after seeing this is to keep the database and make rank cheap by storing it — a rank column, refreshed by a job. It’s worth pricing out loud, because it fails in a way that sounds reasonable in a meeting. A single score change doesn’t invalidate one rank; it shifts the rank of everyone between that player’s old position and their new one, so maintaining an exact rank column on write is the same linear cost you were trying to escape, moved to the write path — where it is now paid on every submission instead of every query. Refresh it on a schedule instead and rank stops being live: a player who just scored sees the old number until the job runs, which is precisely the moment they are staring at the screen. An index on score doesn’t rescue it either — it makes the top-K read cheap and leaves the count of everyone above you exactly as expensive. Rank has to be maintained incrementally by the structure, not recomputed or cached beside it.
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?
What single structure makes update, rank AND top-K all fast?
A hash gives O(1) score lookup but no ordering — computing rank or top-K still requires scanning/sorting everyone. You need the scores kept in sorted order incrementally.
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).
Re-sorting an array of millions on every update or query is O(N log N) — worse than the SQL you're replacing. The structure must maintain sorted order as scores change.
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 this piece earns its place
Notice the assumption this shape bakes in: that a player’s standing is one comparable number. A sorted set totally orders members by a single score, so anything the product ranks by has to be folded into that one value before it goes in. That’s fine while the rule is “highest score wins”, and it gets awkward the moment the rule is “highest score, then whoever got there first, then higher level” — each extra criterion has to live in the low-order part of the same number, and that number is a float, so the bits you spend on a tiebreak are bits the score itself no longer has. Encode too coarsely and the ties come straight back; encode too finely and a large score quietly loses its tiebreak to rounding. So I’d ask early what the ordering rule actually is, because a composite ordering is a decision about the key, and the key is the one thing that is expensive to change once millions of members are already stored under it.
What the new pieces do
- Leaderboard Svcgateway
- Applies score updates and answers rank / top-K / around-me queries by talking to the ranking index and store.
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.
Why this piece earns its place
Two things here stay linear, and they are where this goes wrong under real traffic. The first is K. Top-K is O(log N + K), which is only cheap while K is small; an endpoint that lets a client ask for ten thousand rows in one go hands any caller the power to make one request cost about what the scan you just eliminated cost. Cap K at the service and never expose an unbounded range read. Where the slice starts is not the problem — a rank-indexed read seeks straight to any position in O(log N), which is exactly what the next step leans on to serve a player sitting deep in the tail — so it is the width of the window you hand back that needs the limit, not its depth. The second is that the whole board is one key. A key lives on one node, and that node runs commands for it one at a time, so every update and every rank query in the system queues behind every other one on a single core — O(log N) per command is a budget against that queue, not a free pass. Pipeline updates so a burst costs one round trip instead of thousands, and keep slow commands off that key entirely; one careless full-range read in a console stalls every player’s rank query while it runs.
- O(log N)update + rank
- O(log N + K)top-K
- liveapply on every score
What the new pieces do
- Sorted Setcache
- The core: a Redis sorted set keyed by player, scored by score. O(log N) update and rank, O(log N + K) top-K.
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?
A player ranked 4,281,900 wants the players right around them. How?
ZREVRANK gives the player's position in O(log N); ZREVRANGE over [rank-5, rank+5] reads the surrounding slice in O(log N + K). A relative leaderboard is just a rank lookup plus a small range read — both cheap.
Pulling millions of entries to locate one player is exactly the linear cost you eliminated. Use rank + a bounded range read instead.
Neighbors change on every nearby score update — precomputing and maintaining them is far more work than a live O(log N) rank + range read from the sorted set.
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.
Why this piece earns its place
The catch in composing two reads is that they are two reads. Between the ZREVRANK and the ZREVRANGE, other players score, so the window comes back drawn around a position that may no longer be this player’s — and the most embarrassing version of that is a screen headed “you are here” that doesn’t contain the player at all. Do both in one atomic step (a script executed server-side), or take the player’s own row from the rank call and render that rather than trusting the range to hold them. The edges need clamping too: near the top, rank minus five is negative, and a negative index means something else entirely to a range read. And expect apparent movement with no cause. Among equal scores the order comes from whatever breaks the tie, so a player whose score hasn’t changed can still slide a place when someone ties them, and two refreshes a second apart can show different neighbours. Decide whether that’s acceptable before a support ticket decides it for you.
- rankO(log N)
- ± windowsmall range read
- any playerinstant context
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?
The board exceeds one node. Why is sharding a ranking uniquely hard?
Hashing players across shards spreads storage, but then a global rank requires combining counts from every shard — you can't read one shard's local rank and call it global. Ranking has a cross-shard dependency a plain KV store doesn't.
Replication scales reads but not size or write throughput of a single huge board, and every write still hits every replica. For very large boards you must partition, then reconcile global rank across partitions.
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.
Why this piece earns its place
Score-range shards quietly assume you know the shape of the score distribution and that it holds still. Neither is usually true. Scores pile up near the bottom, so bands cut evenly across the score space own wildly uneven populations — a band like 0–1k can hold most of the player base while the top band holds a handful — and the distribution drifts upward over a season as players get better, so a split that was balanced at launch is lopsided by the end. Worse, a player’s shard is a function of the very thing that keeps changing. Crossing a band boundary isn’t an in-place update, it’s a move between two nodes, and a move made of two operations can leave a player in both shards or in neither. Add to the new band before removing from the old, so the failure you get is a duplicate you can reconcile rather than a player who vanished from the board. Rebalance from measured population rather than from the score space, and treat the boundary bands as your hot ones — that is where the moves concentrate.
- score-rangeor region shards
- global rank= local + higher shards
- approxpercentile at huge N
What the new pieces do
- Shard Acache
- One partition of a global ranking — a sorted set for a score range or region, so ranking scales past one node.
- Shard Bcache
- Another ranking partition. A global rank is assembled by combining per-shard ranks (or approximated).
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.
Why this piece earns its place
The knob here is how many windows are live at once, and it multiplies the write path. Every active window is another write per score, so daily plus weekly plus all-time already triples the cost of one submission, and the moment someone asks for monthly, per-region and per-game-mode boards, a single score fans out into a dozen writes and a dozen resident sets in memory. TTL is the other half of the same knob: expire aggressively and you save memory but lose the “how did I do yesterday?” view players actually ask for; keep everything and old windows accumulate forever with nothing applying pressure to remove them. Set retention from what the product will display, not from what feels safe. And notice that the key names a date, which quietly makes a policy decision: midnight in whose timezone? Pick one canonical zone and say so in the UI — with per-player local resets, two friends comparing their daily boards aren’t even looking at the same board.
- key per windowdaily/weekly/all-time
- reset= new key + TTL
- fan-outwrite to active windows
What the new pieces do
- Windowed Boardsservice
- Maintains time-boxed leaderboards (daily/weekly/all-time) as separate sorted sets that reset on schedule.
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.
Why this piece earns its place
A queue in front of two stores means delivery is at-least-once, so the consumer has to be safe to replay — and that is a property of the update you choose, not of the queue. If an update is absolute (“this player’s score is now 5000”), applying it twice changes nothing, and an old message arriving late can be rejected on the spot by only accepting a higher value. If it’s an increment, a redelivered message silently inflates a player’s score and nothing anywhere reports an error; you then need an event id and a dedupe record, which is real state you now have to keep. Prefer absolute updates unless the product genuinely accumulates. The other thing to be honest about is that the rebuild is an operation, not a sentence in a design doc. Regenerating a board of ten million players out of the database takes time, and ranking is degraded throughout, so it should build into a fresh key and swap in when complete rather than serving a half-filled board — and it should be something you have actually run, because a recovery path nobody has exercised is a hope, not a backstop.
- ZSETderived index
- DBsource of truth
- rebuildableregen index from DB
What the new pieces do
- Score Eventsbus
- A high-volume stream of score updates, decoupling a firehose of writes from the ranking index and durable store.
- Scores DBdata
- The durable record of every player's score. The ZSET is a fast derived index rebuilt from this if lost.
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.
Why this piece earns its place
Detection is asynchronous, so the interesting half of anti-cheat isn’t blocking the bad score — it’s withdrawing one you already accepted and already showed everyone. That is a design requirement, and it lands on decisions you’ve already made. The cheated entry sits in the all-time set and in every windowed set it touched, so the removal has to fan out exactly the way the write did, and anything served from it — a cached top page, a screenshot someone shared — is downstream of the mistake. Pulling the player out also shifts everyone below them by one, which is the correct outcome and still reads as a bug to the person it happens to, so say it in the UI rather than letting them discover it. Removal shouldn’t be a plain delete either: keep the ban in the durable record, or the next submission from that account walks straight back onto the board. And be careful how hard the rate limit bites — an exceptional legitimate run and a script don’t look that different, so the limit should route a suspect submission into review rather than reject it outright.
You did it
You just designed a leaderboard.
Everything you assembled, in order
- SQL rank is O(N) (count everyone above) — it melts at millions of players and high query rates.
- A sorted set gives O(log N) update + rank and O(log N + K) top-K — all three fast at once.
- Redis ZSET: ZADD to update, ZREVRANK for rank, ZREVRANGE for top-K.
- Relative "around me" boards = rank lookup + a small range read, instant for any player.
- Sharding is hard because rank is global — partition by score range/region and approximate the tail.
- A sorted set per time window makes daily/weekly/all-time boards and clean resets trivial.
- Keep a durable DB as source of truth; the ZSET is a fast, rebuildable index. Guard ties, scale, cheats.