Gaming Leaderboard: requirements, scale and why the relational approach fails

"Design a real-time gaming leaderboard." A small, satisfying problem where one data-structure choice decides the entire design — and where one innocuous-sounding requirement turns an easy problem into a hard one.

This lesson finds that requirement, sizes the problem, and measures exactly how the obvious design fails. The second lesson builds the data structure that fixes it, scales it past one node, and works through the follow-up questions.

The requirement that changes everything

"Show the top ten players" is trivial. Keep a sorted list, take the first ten. Any database does it with an index.

"Show a player their own rank out of 25 million" is a different problem. Rank is not a property stored anywhere — it is a count of how many players score higher, which means answering the question requires touching every higher-scoring row. The second half of this lesson measures exactly how badly that goes.

So the first question to ask is whether arbitrary player rank is in scope. If the answer is no, the design is a cache and half a page. If yes, you have a real problem. Interviewers almost always say yes, because the no version is not worth 45 minutes.

The questions that shape everything after

1. How many players, and how many are active? Total registered and daily active are different numbers, and the leaderboard only needs the active ones.
2. Top-N only, or arbitrary player rank? As above. This is the pivotal question.
3. How fresh must ranks be? Updated instantly on every score, or recomputed every minute? A one-minute tolerance permits a batch design and removes most of the difficulty.
4. What is the time scope? All-time, daily, weekly, seasonal? Each is a separate leaderboard with its own lifecycle, and daily leaderboards need expiry.
5. How are ties broken? Same score, who ranks higher? Earlier achievement is the usual answer, and it has a neat implementation covered in Follow-ups.
6. Are there segmented leaderboards? Per country, per friend group, per guild. Each segment multiplies the number of leaderboards to maintain.
7. Is the score cumulative or a single best? "Points this season, added up" and "highest single game" behave differently under updates.

The assumptions this section uses

Requirements and scale

With those assumptions, the requirements are short.

Functional requirements

• Record a score change for a player, on a named leaderboard.
• Return the top N players on a leaderboard, with scores.
• Return a specific player's rank and score.
• Return the players immediately around a given player — the "you are 4,102nd, here are the ten either side" view.
• Support daily, weekly, and all-time scopes, plus country and friend segments.

Non-functional requirements

• Rank read latency well under 100 ms, because it is rendered on a results screen.
• Score updates visible within about a second.
• Correct ranks — a player who scores higher must never appear lower.
• Durable enough that a node loss does not erase a season's standings.

The arithmetic

Score updates. 5 million daily active players × 10 games each = 50 million updates per day. Divided by 100,000 seconds, that is 500 updates per second average. Games cluster in the evening, so take a 5× peak: 2,500 updates per second.

Rank reads. Every game ends with a results screen showing rank, so at minimum one rank read per game: another 50 million per day. Add players opening the leaderboard voluntarily, call it another 25 million. Total 75 million reads per day, 750 per second average, 3,750 per second at peak.

Data size. 25 million monthly players in the all-time leaderboard. A member entry is a player identifier — say a 16-byte string — plus a score. In an in-memory sorted set, real per-member cost including index and pointer overhead lands around 100 bytes, so 25 million members is roughly 2.5 GB.

That is the number that reframes the problem. The entire all-time leaderboard fits in the memory of one ordinary machine, with room to spare. Add daily and weekly scopes and a hundred country segments and you are still in the tens of gigabytes.

What that implies

• Do not shard first. One node holds the data and handles the request rate. Scaling beyond one node covers what happens at 100× this scale, but leading with sharding here is solving the wrong problem.
• The choice is the data structure, not the topology. Get that right and the design is finished; get it wrong and no amount of hardware rescues it.
• Durability needs a separate answer. An in-memory structure holding a season's standings is a data-loss risk that Follow-ups addresses.

Why the relational approach fails

Start with the obvious design and measure it, because the failure is specific and the number is what justifies everything after.

The schema and the two queries

1CREATE TABLE leaderboard (2  player_id   BIGINT PRIMARY KEY,3  score       INT NOT NULL,4  updated_at  TIMESTAMP NOT NULL5);6CREATE INDEX idx_score ON leaderboard (score DESC, updated_at ASC);

Top ten is fine:

SELECT player_id, score FROM leaderboard ORDER BY score DESC LIMIT 10;

The index is already in score order, so the database reads ten entries from one end and stops. Sub-millisecond, regardless of table size. There is no problem here.

A player's rank is the disaster:

SELECT COUNT(*) + 1 FROM leaderboard WHERE score > (  SELECT score FROM leaderboard WHERE player_id = 8823941);

The index makes this a range scan rather than a full table scan, which sounds like a fix and is not. A player in the middle of the distribution has roughly half the table above them. With 25 million rows, that is 12.5 million index entries counted, per request.

The measurement

An index-only count scans entries at somewhere around 5 to 10 million per second on a typical server — the exact figure depends on hardware, cache residency, and index width, so treat it as an order of magnitude rather than a benchmark.

12.5 million entries ÷ 8 million per second ≈ 1.5 seconds per rank query.

Against a target of well under 100 ms, that is 15× too slow for one user. Now apply the load: 3,750 rank queries per second, each occupying a CPU core for 1.5 seconds, requires 5,600 cores doing nothing but counting. The design does not need tuning; it needs replacing.

The counting approach grows linearly with player count. The sorted-set approach, covered in the next lesson, grows logarithmically — which over this range is indistinguishable from flat.

The repairs that do not work

Worth naming, because interviewers offer them and accepting one is a trap.

Cache the ranks. Ranks change on every score update. At 2,500 updates per second, every update potentially shifts the rank of everyone below the changed player, so the cache is invalidated continuously. Caching works for stable data; ranks are the opposite.

Store a rank column. One player scoring higher shifts every rank below them by one. A single update becomes up to 25 million row updates.

Recompute periodically. Rank the whole table every minute and store the result. This does work, and it is the right answer if the freshness requirement allows a minute of staleness — so ask. Under a real-time requirement it fails, and the recomputation itself is a full sort of 25 million rows every 60 seconds.