Why Pulling Rankings From the Database Falls Over

Summary

ORDER BY score DESC LIMIT 10 collapses the moment you ask for one person's rank. A ZSet always keeps its sorted state and returns rank in O(log N).

Why this was needed

Leaderboard requirements usually come like this. Show the top 10, show my rank, show the 5 people before and after me, and the scores change in real time.

With an RDB, the first requirement is easy. You put an index on ORDER BY score DESC LIMIT 10. From the second one on, it collapses. "My rank" means counting the people with a higher score than mine, and SELECT COUNT(*) WHERE score > my_score has to count that whole range even with an index. If you ask for the rank of a low-ranked user out of 1 million users, it counts 990,000 rows. And this query arrives on every request from every user.

The third requirement, the 5 people before and after, is worse. You need to know the offset, so the rank calculation has to come first. The fourth, real-time updates, means writes keep coming into that index, which adds lock contention.

How it works

Redis's sorted set fits this problem as if it had been built for it. Internally it maintains a skip list and a hash table together, so both finding a score by member and scanning a range in score order are fast.

There are five core commands. ZADD inserts with a score, ZINCRBY adds atomically, ZREVRANGE pulls the top N, ZREVRANK gets a specific member's rank, and ZSCORE looks at a score. Rank and range lookups are all O(log N) or O(log N + M). Whether it is 1 million or 10 million users, a rank lookup does not exceed milliseconds.

Handling ties is the first hurdle in practice. When scores are equal, Redis sorts by the lexicographic order of the member string. But the product requirement is usually "with the same score, whoever achieved it first ranks higher". The solution is a composite score. You fold the score and the time into a single real number.

composite = points + (1 - ts / 1e10)
# points 5000, ts 1795000000 -> 5000.8205
# 같은 points 라면 ts 가 작을수록(먼저 달성) composite 가 크다

Because the fractional part is always between 0 and 1, it never crosses the integer boundary, and the score order is preserved while only ties are separated by time.

The second hurdle is rankings by period. Keep an overall ranking and a weekly ranking separately, and put a TTL on the weekly key. If you put the period in the key name, like lb:weekly:2026-W34, expiration cleans up automatically. If you need to add up several periods, ZUNIONSTORE handles it in one go, even accepting weights.

What you meet in the field

A ranking is one of the rare cases where Redis becomes the primary store rather than a cache. So you should either turn persistence on, or keep the original events (which user earned how many points, and when) somewhere else so that the ranking can be rebuilt. The author's recommendation is the latter — a ranking is derived data, so it must be possible to recreate it at any time.

And using an offset for pagination is not a problem with a ZSet. ZREVRANGE key 99 108 goes straight there through the skip list. It is completely different in nature from an RDB's OFFSET 99.

When handling large rankings

A sorted set is fast, but all of it is loaded in memory. As users grow, this fact starts to drive the design.

The size of one member is proportional to the length of the member string, so keeping the identifier you put in the key short alone makes a considerable difference. For example, use a numeric ID instead of a user name, and keep that ID in a form that can be represented as an integer rather than a string. Also, in many cases there is no need to put everyone in the ranking. If nobody looks at the exact rank of low-ranked users, it is much cheaper to keep only the top tens of thousands and treat the rest as "outside the top N". You can trim it periodically with ZREMRANGEBYRANK.

You also have to watch out for a single command that takes a long time. Redis processes one command at a time, so one wide-range query stops every other request. If code that fetches everything with ZRANGE key 0 -1 hits a key with 1 million members, the entire service freezes at that moment. The rule is to enforce a cap on page size, and to split any job that must scan everything into iterations with ZSCAN.

ZUNIONSTORE, which merges several periods, needs care for the same reason. Merging several large sets delays other requests for that time. It is safer not to call it on the real-time request path, and instead compute in advance and have requests read only the result.

Finally, a ranking is also the first place cheating is attempted. If you let the client send the score-raising request as is, someone will craft that request and send it directly. The server must compute the score, and the idempotency key we saw earlier is used here as well so that the same event cannot raise the score twice. The value of a ranking comes from its accuracy, so once trust is lost, the feature itself becomes meaningless.

What you will do in the next lab

You build a leaderboard of 200 players from 2,000 play records. You implement everything — the top 10, a specific user's rank, a page in the 100s of ranks, the tie rule, the weekly ranking, and the combined ranking — and at the end you bring up a rank lookup API and measure the time of 1,000 lookups.