Real-time Leaderboards

Why sorting a million rows in SQL every time someone scores a point will melt your database.

The idea

In a multiplayer game with 1 million active players, scores update constantly. If you store scores in a SQL database, finding the "Top 10 Players" requires running ORDER BY score DESC LIMIT 10. This is incredibly slow to run 1,000 times a second. Even worse is finding a specific player's rank (e.g., "You are rank #42,105"). SQL has to count all 42,104 people ahead of you. To build a real-time leaderboard, you must use a data structure that is always sorted in memory: a Redis Sorted Set.

Step 1: The SQL way. We need the Top 3 out of 1 million players. SQL scans the index and sorts.

How it works (Redis ZSET)

A Redis Sorted Set (ZSET) stores data in RAM using a specialized data structure called a Skip List. When Alice scores 50 points, Redis doesn't just save the number; it physically moves Alice's record in memory to ensure the entire list remains perfectly sorted at all times. Therefore, asking for the "Top 10" takes O(log N) time—nearly instantaneous, regardless of whether you have 1 thousand or 10 million players.

// 1. Updating a score
// Adds 50 points to "alice" in the "global_leaderboard" set.
// Redis automatically shifts her position in the sorted list!
await redis.zincrby("global_leaderboard", 50, "alice");

// 2. Fetching the Top 3 Players
// ZREVRANGE returns the highest scores (Reverse Range)
const topPlayers = await redis.zrevrange("global_leaderboard", 0, 2, "WITHSCORES");
// Returns: ["alice", "150", "bob", "120", "charlie", "90"]

// 3. Getting a specific player's Rank
// ZREVRANK instantly tells you how many people are ahead of Alice
const rank = await redis.zrevrank("global_leaderboard", "alice"); // returns 0 (1st place)

Cost

Redis stores this entirely in RAM. 1 million players in a ZSET takes roughly 100MB of RAM. This is very cheap for one global leaderboard. However, if you want to create a personalized leaderboard for every single user (e.g. "Rank among your friends"), you cannot create 1 million ZSETs without blowing through your RAM budget. You must calculate friend leaderboards on-the-fly using standard SQL queries.

Watch out for