The query that looks fine at 5,000 users and melts your database at 500,000 — and the primitive that makes it trivial.
Every engineer who has ever built a leaderboard has written the same query:
SELECT player_id, score
FROM player_scores
WHERE project_id = $1
ORDER BY score DESC
LIMIT 100;With an index on (project_id, score DESC), this runs in a few milliseconds. On staging, with 500 seed users, it is faster than your API overhead. Ship it, take credit, move on.
Six months later, the leaderboard page is the slowest route in your application. Not because of a complex join. Not because of an N+1. Just because the table is now tens of millions of rows, scores update thousands of times per second, and every "show me this player's rank" request has to count rows above them. The index that used to save you now needs to be walked, the heap has to be read for each row that makes it into the top 100, and you are paying that cost on every page load.
This is the moment most teams discover that leaderboards are not actually a database problem. They are a sorted-set problem wearing a database costume.
In this post, I will walk through why naive leaderboard queries break between 50K and 500K active users, why Redis sorted sets are the correct primitive, the four commands that do most of the work, and the parts of a production leaderboard — time windows, rank-change notifications, atomic resets, durability, segmentation — that every tutorial conveniently skips. Then I will show how we packaged all of it in the EngageFabric SDK so your team does not have to build it twice.
Why ORDER BY score DESC Breaks at Scale
Let's get specific about why a perfectly reasonable SQL query becomes a production problem. There are four things going wrong, and they compound.
1. Write amplification on a hot index
The leaderboard table has an index on (project_id, score DESC) so reads are fast. That index has to be updated every single time a score changes. In a gamification workload, scores change constantly — every XP event, every quest completion, every purchase. You are not doing one update per user per day; you are doing dozens.
At 100K DAU with an average of 20 score-affecting events per user per day, you process two million index updates per day. Postgres handles this, but it is not free. Each update invalidates buffer cache, fragments the index, and creates pressure on autovacuum. As the dataset grows, index bloat grows with it, and write latency starts creeping up in ways that do not show up in any single-query EXPLAIN.
2. Rank lookups are O(N) hidden behind a pretty syntax
The moment your product needs "what is my rank?" — and it will, because that is the single most engaging piece of information on a leaderboard — you get to write this:
SELECT COUNT(*) + 1 AS rank
FROM player_scores
WHERE project_id = $1 AND score > (
SELECT score FROM player_scores
WHERE project_id = $1 AND player_id = $2
);That COUNT(*) is a sequential scan over every row with a higher score. For the top player, it is cheap. For the 487,000th player out of 500,000, Postgres has to count 486,999 rows. Every time. On every page load.
You can optimize this with window functions, materialized views, or approximate rank, but every optimization trades freshness for performance. And users notice when their rank lags behind their last action.
3. Ties and ordering stability become your problem
Two players both at 1,250 XP. Which one is rank 42 and which is rank 43? Your query does not care, but the players absolutely do. Without a secondary sort key (typically the timestamp of when they reached that score, with earlier wins), your ranks shuffle every time Postgres feels like it. This means you need a composite index, which means more write amplification, which brings us back to problem #1.
4. Time windows multiply the problem
"Daily leaderboard, weekly leaderboard, all-time leaderboard" sounds like three features. In naive SQL, it is three tables — or one table with a period column and three times the rows. Either way, every score event now writes to three places. The all-time leaderboard is monotonically growing. The daily needs to be cleared at midnight. The weekly needs a different cadence. Now the atomic-reset problem is a distributed-systems problem, and you are holding long-running transactions to wipe rows without confusing in-flight writes.
Teams who hit this wall usually reach for a cache in front of Postgres. It works for a while. But the cache is just a bandaid on a data-structure mismatch: you are using a tool optimized for arbitrary queries to do one specific thing — maintain a sorted collection with fast rank and range reads — and there is a better tool for that job.
The Right Primitive: Redis Sorted Sets
A Redis sorted set is exactly the data structure a leaderboard wants: a collection of unique members (player IDs), each associated with a score (a double), maintained in sorted order. Under the hood it is implemented as a skip list paired with a hash table, which gives you the two operations that matter most:
- Inserts and score updates in O(log N) — roughly 20 comparisons for a million-member set.
- Range reads in O(log N + K) where K is the number of elements returned — fetching the top 100 from a million-member set is dozens of operations, not hundreds of thousands.
That is the whole trick. Postgres is a B-tree-shaped answer to a skip-list-shaped question. When you line the structure up with the workload, the problem deflates.
The skip list, briefly
You do not have to deeply understand skip lists to use sorted sets, but it helps to have a rough mental model. Imagine a linked list of members sorted by score. Now add a sparse second level that skips every few nodes, and a sparser third level above that, and so on. To find a score, you start at the top level and skip forward until you would overshoot, then drop down a level and continue. With logarithmic expected height, you land on any member in O(log N) steps.
Insertions are the same cost. Deletions too. Range reads walk the bottom level for K steps once you have landed on the start. That is it. That is the entire algorithmic reason Redis runs leaderboards faster than Postgres.
The hash table sidekick
The skip list alone would make "get by score" fast but "look up this player's score" slow — you would have to scan. So Redis also keeps a hash from member to score alongside the skip list. Every modification updates both. This is why ZSCORE and ZRANK for a specific member are both O(log N) and feel instant in practice.
The Four Commands That Matter
The Redis documentation lists roughly thirty sorted-set commands. In practice, a production leaderboard is built on four of them. Learn these well and you can skim the rest.
ZADD — insert or update a score
ZADD leaderboard:project_abc 1250 player_123Adds player_123 to the sorted set with score 1250, or updates their score if they are already there. This is the workhorse. Every XP event, every action that moves a score, calls ZADD.
Two flags matter:
XX— only update, do not create. Useful when you want to ignore score events for players who are not eligible for a leaderboard.GT/LT— only update if the new score is greater/less than the current. Critical for "best score" leaderboards (golf scoring, high-score games) where you do not want a worse attempt to overwrite a better one.
ZADD leaderboard:project_abc GT 1250 player_123ZINCRBY — add to an existing score
ZINCRBY leaderboard:project_abc 50 player_123Adds 50 to player_123's score, creating the member at 50 if they did not exist. This is the right command for cumulative leaderboards where each event contributes an incremental amount (XP earned, revenue, steps walked). It is also what EngageFabric uses internally for increment operations — the scoring service calls ZINCRBY when the incoming event is an increment and ZADD when it is a set.
ZREVRANGE — read by rank (highest first)
ZREVRANGE leaderboard:project_abc 0 99 WITHSCORESReturns members ranked 0 through 99 (the top 100), highest score first, with each member's score attached. This is every "show me the top 100" API call, cheap enough that you do not need to cache it.
For an ascending leaderboard (lowest wins — think golf strokes or completion time), use ZRANGE instead. Sort order is a configuration decision made per leaderboard, not a per-query decision.
ZREVRANK / ZRANK — look up a player's rank
ZREVRANK leaderboard:project_abc player_123Returns player_123's position in the sorted set (zero-indexed, highest-first with ZREVRANK). O(log N). This is the one operation that was killing you in Postgres, done in microseconds.
If you only remember one command, remember ZADD. Everything else is
downstream of getting data into the set correctly.
The Minimal Working Example
Here is a complete functional leaderboard against a Redis instance:
import Redis from 'ioredis';
const redis = new Redis();
const KEY = 'leaderboard:all_time';
// Record a score
await redis.zadd(KEY, 1250, 'player_123');
// Get the top 10
const top = await redis.zrevrange(KEY, 0, 9, 'WITHSCORES');
// Get player_123's rank (0-indexed)
const rank = await redis.zrevrank(KEY, 'player_123');
// Get their score
const score = await redis.zscore(KEY, 'player_123');That is it. That is a functional real-time leaderboard. No joins, no indexes to tune, no query planner surprises. Every operation is O(log N) or better.
Of course, a production leaderboard is not ten lines. It is what happens in the next six sections.
Time Windows: The Part Tutorials Skip
Every real product needs multiple leaderboards running simultaneously. A "Today's Top Earners" leaderboard resets at midnight. A weekly leaderboard resets on Monday. An all-time leaderboard never resets. Sometimes there is a seasonal leaderboard that resets when a campaign ends.
Most Redis tutorials stop at "here is how to do a leaderboard." Time windows are where the interesting decisions live. There are two viable patterns, and they trade off differently.
Pattern A: One set, explicit reset
Use a single sorted set per leaderboard. When the window ends, clear the set. This is the pattern EngageFabric uses internally, and it maps naturally to the configuration model: each leaderboard has a resetSchedule (daily, weekly, monthly, seasonal) and a scheduled job flushes the scores at the boundary.
// At reset time
await redis.del(`leaderboard:${projectId}:${leaderboardId}`);
await redis.del(`leaderboard:${projectId}:${leaderboardId}:metadata:*`);The strengths of this pattern: trivially simple key naming, easy to reason about for consumers, no proliferation of Redis keys. The weakness: the reset is an event you have to execute atomically. In-flight writes during the reset boundary can land on either side, and if your reset runs in a canonical timezone, users in other timezones experience the reset during their active hours.
Pattern B: Key per window
Use a separate sorted set for each window, with the window identifier in the key name:
leaderboard:project_abc:daily:2026-04-23
leaderboard:project_abc:weekly:2026-W17
leaderboard:project_abc:all_timeOn every score event, write to all relevant windows. Reading from "today" means computing the current window key. The strengths: the reset is implicit — when the calendar day changes, writes naturally flow to a new key. There is no atomic-reset problem because there is no reset at all. You can also set TTLs on each window key so old leaderboards self-clean.
The weakness: more keys, more memory pressure (especially if you keep history around), and the need to pick a canonical timezone for defining window boundaries.
Which should you use?
For products with a handful of leaderboards and straightforward reset schedules, Pattern A is simpler to operate and gives you historical data in the database for analytics (EngageFabric persists entries to Postgres on every score update, so resetting Redis does not lose the history). For products with many overlapping time-window leaderboards or complex seasonal campaigns, Pattern B's implicit resets avoid a whole class of edge cases.
Both patterns handle the scaling problem. The choice is about operational ergonomics, not correctness.
Rank-Change Notifications
"You just jumped from #47 to #39" is one of the highest-engagement messages you can send. It is also the thing developers most often over-engineer.
The naive approach is to poll: every minute, for every active user, fetch their rank, compare to last known rank, notify on change. This scales poorly and creates enormous noise because ranks fluctuate constantly in active leaderboards.
A better approach: compute rank-change at write time. When a score update comes in, read the player's rank before and after the write, and publish an event with both values. This is what EngageFabric does on every score update — the score service reads the rank, applies the ZADD or ZINCRBY, reads the new rank, and publishes an event to a Redis pub/sub channel with playerId, score, previousScore, rank, and previousRank. Downstream consumers (a WebSocket gateway, an email worker, an analytics pipeline) subscribe to that channel and decide whether the change is notification-worthy.
async function recordScoreWithRankChange(
key: string,
playerId: string,
delta: number,
): Promise<{ oldRank: number | null; newRank: number | null }> {
const oldRank = await redis.zrevrank(key, playerId);
await redis.zincrby(key, delta, playerId);
const newRank = await redis.zrevrank(key, playerId);
return { oldRank, newRank };
}There is a race here — another write can land between the two rank reads — but for notification purposes it does not matter. The goal is not cryptographic accuracy; it is a plausible "you moved from X to Y" message.
The harder problem is deciding when to actually notify. A player who climbs from #8743 to #8701 does not want a notification. A player who enters the top 100 for the first time definitely does. A good heuristic:
- Crossed a round-number threshold (10, 50, 100, 500, 1000)
- Entered or left the top 10 or top 100
- Moved by more than 20% of their previous rank
Storing "last notified rank" per player in a separate hash lets you compute the delta since their last notification, not since their last score event. That is the delta that should trigger a push.
Durability: The One Serious Objection to Redis
The most legitimate objection to running leaderboards on Redis is durability. A Postgres write is fsync'd to disk by default. A Redis write, in its default configuration, is not.
In practice, this is a smaller problem than it sounds, because you should not be treating Redis as the source of truth.
AOF with fsync every second
Redis's append-only file in appendfsync everysec mode writes every operation to disk, fsync'd once per second. In the worst case, a crash loses up to one second of writes. For a leaderboard, one second of lost score events is annoying but not catastrophic — players will re-earn that score on their next action.
Replication for high availability
A primary-replica setup with automatic failover (via Redis Sentinel or a managed service) means you can lose a node entirely without losing the leaderboard. Writes acknowledged by the primary are asynchronously replicated to replicas, and replica promotion is automatic.
The source of truth question
The cleanest pattern is to treat Redis as a materialized view, not the source of truth. The source of truth is your event log — every score-affecting action written durably to Postgres. Redis is a fast projection of that log.
This is exactly how EngageFabric is structured. Every score update writes to Redis for fast reads and also persists to a LeaderboardEntry row in Postgres (asynchronously, so it does not block the hot path). When a client requests rankings, the service reads from Redis; if Redis is empty (cold start, after a flush, during recovery), it automatically falls back to the database. Redis losing state becomes a rebuild, not a data-loss event.
If you are building this yourself, implement the same fallback. The additional complexity is small, and it changes Redis from a risk to a convenience.
Segmentation: When One Leaderboard Is Not Enough
Real products have more than one leaderboard. "Top players globally" is one view. "Top players in my country" is another. "Top players on the enterprise plan" is a third. How do you support all of these without running Redis out of memory?
Per-segment sorted sets
If your segments are known ahead of time and relatively small in number (country, subscription tier, tenant), give each one its own sorted set. Write to both the global set and the relevant segment set on every score event. Reads are trivially fast because each segment's set only contains its own members.
EngageFabric supports this via a segmentField on the leaderboard config and a segment parameter on rankings queries; internally, the Redis key pattern is leaderboard:<projectId>:<leaderboardId>:<segment>:scores, which lets the global and per-segment sets live side by side.
Dynamic segments (friends, teams)
For dynamic or per-user segments — "rank me among my friends" — the per-segment pattern breaks down because every user has their own unique segment. Two options:
Option A: maintain a per-user set of friends, then compute the leaderboard on-the-fly using ZINTERSTORE against the global leaderboard. For reasonable friend counts, this is fast enough on demand.
Option B: fetch all friend scores via ZMSCORE (Redis 6.2+) and sort client-side. For small friend groups (fewer than 50), this is often cheaper than building a temp set.
Honest Trade-Offs
It would be dishonest to write this post without a section on what Redis sorted sets are bad at. Because there are real limitations.
Memory cost
Redis keeps the entire sorted set in RAM. A sorted set with a million members and reasonable member IDs (UUIDs, string player IDs around 32 bytes) costs roughly 80-100 MB of RAM. Ten million members is around a gigabyte. If you have hundreds of leaderboards across dozens of projects, memory adds up.
Mitigations: aggressive cleanup of unused segment keys, using short numeric IDs where possible, and scaling horizontally via Redis Cluster once a single primary is no longer enough. For the vast majority of real-world workloads, a single well-provisioned Redis instance is plenty.
Operational overhead
Running Redis in production means understanding replication, persistence tuning, cluster mode (if you outgrow a single primary), memory pressure, and backup strategies. A managed service removes most of this, at the cost of some money and some control.
If your team does not already run Redis, the operational lift of adding it can feel larger than the engineering lift of a half-decent Postgres leaderboard. That calculus changes as soon as you have a second use case for Redis (caching, rate limiting, session storage, pub/sub), because now Redis is part of the platform and the leaderboard is free.
No ad-hoc analytics
Postgres gives you GROUP BY and window functions and aggregate queries. Redis sorted sets give you exactly the operations defined on sorted sets. If you want to know "what is the average score of players who joined this month?" you are not getting it out of a sorted set — you are going to the durable event log or to a copy of the data in an analytical store.
The right mental model: Redis is the hot path for interactive leaderboard queries, and Postgres (or a data warehouse) is where you ask questions about the leaderboard. Keeping both in sync is a feature, not a bug.
How EngageFabric Handles All of This
Everything above is what we built at EngageFabric — the Redis sorted sets as the primary store, the Postgres persistence for durability, the pub/sub channel for real-time rank changes, the segment key pattern, the reset schedule, the fallback to database when Redis is cold. It is a few thousand lines of tested code and the operational discipline of running Redis in production.
For your application, leaderboard integration looks like this:
import { PlayPulse } from '@engagefabricsdk/sdk';
const client = new PlayPulse({
apiKey: 'your-project-api-key',
projectId: 'your-project-id',
});
// Identify the player once
await client.identify('user_123', { displayName: 'Alex' });
// Track score-affecting events — the rules engine updates any
// leaderboards configured to listen for this event
await client.events.track({
externalUserId: 'user_123',
eventName: 'quest_completed',
properties: { xpEarned: 50 },
});
// Read the top 100 from a configured leaderboard
const top = await client.leaderboards.getTopN('weekly-xp', 100);
// Get a player's rank with neighbors above and below
const me = await client.leaderboards.getPlayerRanking(
'weekly-xp',
'user_123',
{ neighbors: 5 },
);The scoring rules, time windows, and segmentation are configured in the admin console. The SDK wraps the complexity. The infrastructure runs on Redis with Postgres persistence, so the durability and recovery concerns above are our problem, not yours.
That is not a sales pitch so much as the point of the whole post: leaderboards are a narrow, well-understood problem with a correct data structure. Once you have built it right, you should never have to build it again.
Key Takeaways
Leaderboards are a sorted-set problem, not a database problem. Postgres will carry you to tens of thousands of users. Beyond that, the mismatch between B-tree indexes and sorted-collection workloads starts leaking through every optimization.
Redis sorted sets give you O(log N) inserts, O(log N + K) range reads, and O(log N) rank lookups. These are the operations leaderboards need.
Time windows are the hidden complexity. One-set-with-reset and key-per-window are both valid patterns; they trade simplicity for atomicity differently.
Durability is a real concern but tractable. Treat Redis as a materialized view over a durable event log or relational table, not the source of truth. A Redis failure becomes a rebuild, not a data-loss event.
Segmentation scales to dozens of fixed segments cheaply. Dynamic segments (friends, teams) are expressible via ZINTERSTORE or ZMSCORE + client-side sorting for smaller groups.
Memory and operational overhead are the real trade-offs. They are solvable with managed services and discipline. They do not change the conclusion — just the rollout plan.
Building a leaderboard and do not want to reinvent this from scratch? EngageFabric ships real-time leaderboards with time windows, segmentation, and rank-change events as part of the SDK. Read the documentation or try the live demo.
