Designing a Real-Time Leaderboard: Why Sorted Sets Beat a SQL ORDER BY at Scale

TL;DR: A leaderboard looks like a simple feature — sort some scores, show the top N — but a plain SQL table falls over once you have millions of players updating scores in real time. The fix isn’t a bigger database, it’s a different data structure: a sorted set (like Redis’s ZSET), which keeps items ranked as you insert them so reads and writes both stay fast no matter how large the leaderboard gets.

The problem

Picture a small team building a mobile trivia game called QuizRush. Early on, they add a “weekly tournament” feature: players answer questions, earn points, and see a live top-100 leaderboard plus their own rank. The first version is exactly what you’d expect from a small team moving fast. There’s a Postgres table called scores with columns for user_id, tournament_id, and points. To show the top 100, the API runs:

SELECT user_id, points FROM scores
WHERE tournament_id = ?
ORDER BY points DESC
LIMIT 100;

To show a single player their own rank, it runs something like:

SELECT COUNT(*) FROM scores
WHERE tournament_id = ? AND points > ?;

This works fine in testing. It works fine with a few thousand players. Then QuizRush gets featured in an app store spotlight, and the weekly tournament goes from 5,000 players to 2 million almost overnight. Suddenly the leaderboard page takes eight seconds to load. Submitting an answer — which updates your score and should feel instant — starts timing out during peak hours. The on-call engineer opens the database dashboard and sees one thing dominating every slow query log: the leaderboard queries.

This is an extremely common story, and it’s worth understanding exactly why it happens before jumping to a fix.

The naive approach: every rank lookup and every “top N” request forces the database to re-sort or re-count under load.

Why it happens

A relational database is built to answer flexible questions about structured data — joins, filters, aggregates, transactions. It is not built to constantly re-rank a fast-changing list and hand back “where do I stand” answers in milliseconds. Two things make the leaderboard workload especially painful for a table like this.

First, the write pattern is brutal. In a real tournament, thousands of players are submitting new scores every second. Each of those is an UPDATE that can invalidate whatever index the database was using to keep things sorted, and it can also trigger lock contention if many players’ rows happen to live near each other on disk.

Second, and more importantly, rank is not something a plain table stores — it has to be computed on every single read. There’s no column called “current rank” that updates itself. Every time someone opens the leaderboard or checks their position, the database has to look at the whole relevant slice of data and figure out the ordering from scratch. A COUNT(*) WHERE points > ? query to find one player’s rank has to scan through every player who beat them. When there are 2 million players, that scan gets expensive, and it gets expensive again for the next player who asks, and the next.

None of this is a Postgres problem specifically — MySQL, SQL Server, and every other relational database have the exact same limitation for this exact workload. The issue is the shape of the problem, not the tool.

SQL’s ORDER BY approach is simple to build but doesn’t scale for read-heavy ranking. A sorted set costs a little more complexity up front in exchange for reads that stay fast.

The better approach

The fix is to reach for a data structure whose entire job is keeping items ordered by a score as they’re inserted, instead of asking a general-purpose database to re-sort everything on every read. This is exactly what a sorted set is — a structure where every element has a score, and the structure always knows the rank order of every element without having to recompute it.

Redis, an in-memory data store commonly used for this kind of workload, has a built-in sorted set type (called a ZSET) that’s a near-perfect fit for leaderboards. It supports three operations that map directly onto what a leaderboard needs:

• Update a score — ZADD leaderboard 4820 user123 adds or updates a player’s score. Internally, Redis keeps the whole set ordered using a structure called a skip list, so this insert also re-sorts the player into the correct position. This takes O(log N) time — meaning the time grows only slightly as the leaderboard gets bigger, unlike a full table scan which grows roughly in proportion to the table size.
• Get the top N — ZREVRANGE leaderboard 0 99 returns the top 100 players, already sorted, instantly. No scanning, no re-sorting on read.
• Get one player’s rank — ZREVRANK leaderboard user123 returns exactly where that player stands, also in O(log N) time, without counting through every player above them.

For QuizRush, the fix looks like this: every score update still gets written somewhere durable (more on that below), but the leaderboard itself — the thing being read constantly — lives in a Redis sorted set. Submitting an answer becomes a single ZADD. Loading the leaderboard becomes a single ZREVRANGE. Checking your own rank becomes a single ZREVRANK. All three stay fast whether there are 5,000 players or 5 million, because the data structure was built for exactly this shape of problem.

Redis handles the fast, constantly-changing rank state, while Postgres still gets the data asynchronously for durability and reporting.

Pitfalls to avoid

Moving to a sorted set solves the core scaling problem, but it introduces a few new things to think carefully about.

Don’t make Redis your only copy of the data. Redis is in-memory, and even with its optional persistence features, most teams treat it as a fast cache rather than a system of record. Keep writing scores to Postgres (or whatever durable store you already trust) as well, and treat Redis as the derived, queryable view built for speed. If Redis restarts and loses recent state, you want to be able to rebuild it from the source of truth rather than lose player data.

Watch memory growth. A sorted set with 10 million players in it is a lot of data sitting in RAM. If you run leaderboards per tournament, make sure old tournaments’ sorted sets get expired or archived once they’re no longer active, instead of accumulating forever.

Decide how you want to handle ties. By default, a sorted set with tied scores will order those members alphabetically by their ID, which is rarely the behavior you actually want (“first to reach this score should rank higher” is more common). A common trick is to encode the tiebreaker into the score itself — for example, storing a combined value like points - (timestamp / large_constant) so that among equal point totals, the earlier submission naturally sorts first.

Don’t reach for this pattern if you don’t need it. If your leaderboard has a few hundred users and gets checked occasionally — an internal team dashboard, a small community app — a plain SQL ORDER BY is simpler to operate, easier for your whole team to reason about, and completely fine. The sorted-set approach earns its extra moving part (another system to run and keep in sync) specifically when read volume or player count gets large enough that the database’s re-sort-on-every-read behavior becomes the bottleneck. Reach for it when you actually see that pain, not preemptively.

The underlying lesson generalizes past leaderboards: whenever you notice you’re asking a general-purpose database to constantly recompute an ordering that a specialized structure could maintain incrementally, that’s usually a sign it’s time to bring in a tool built for exactly that job.