DynamoDB Schema Pattern: Gaming Leaderboard
Leaderboards are the canonical “DynamoDB or Redis?” question. Both can do it. The right answer depends on the size of your boards, how often you query rank vs top-N, and whether you need durability of every score or just the current snapshot.
This pattern handles the DynamoDB-friendly cases: daily, weekly, and seasonal leaderboards with thousands to low-millions of players, and game session history that you actually want to keep. The trick is the padded score sort key - storing scores as zero-padded fixed-width strings so DynamoDB’s lexicographic sort matches numeric sort. With that, top-N becomes a simple Query with ScanIndexForward: false.
For global all-time leaderboards with tens of millions of players and high-frequency rank lookups, Redis sorted sets are the right tool. I’ll be honest about the trade-off below.
Access patterns
| # | Access Pattern | Operation |
|---|---|---|
| AP1 | Get player by ID | GetItem |
| AP2 | Get player by email (login) | Query (GSI) |
| AP3 | Get top N scores on a leaderboard | Query (sorted desc) |
| AP4 | Get player’s best score on a leaderboard | Query (GSI, scoped) |
| AP5 | Get player’s scores across all leaderboards | Query (GSI) |
| AP6 | List player’s recent game sessions | Query (primary) |
| AP7 | List sessions in a game (across players) | Query (GSI) |
| AP8 | Get session detail | GetItem |
| AP9 | Get leaderboard metadata | GetItem |
| AP10 | Player’s rank on a leaderboard (≤ 10K players) | Query (count items above) |
Ten access patterns, four entities, two GSIs.
The padded score trick
DynamoDB sort keys are strings. Sorting is lexicographic, not numeric. That means "95" sorts after "100" because "9" > "1" byte-wise.
Pad the score to a fixed width and the problem disappears:
score 100 → "0000000100"
score 95 → "0000000095"
score 1500 → "0000001500"
Lex sort ascending: "0000000095", "0000000100", "0000001500"
Numeric sort: 95, 100, 1500
✅ same orderFor most games, 10 digits (max ~10 billion) is plenty. Bigger games can use 12 or 16. The choice is permanent for the leaderboard - changing pad width invalidates the entire sort - so pick a comfortable max upfront.
The Score entity computes paddedScore from score automatically. Application code only ever sees the numeric value.
Entities
- Player: profile, lifetime aggregate stats (denormalized for fast display).
- Leaderboard: metadata for a board (daily, weekly, seasonal, global). Each board has its own ID.
- Score: one player’s score on one leaderboard. The leaderboard’s partition collects all scores.
- Session: the play that produced the score. Stored in the player’s partition for “my recent games.”
Table design
Primary key structure
| Entity | PK | SK |
|---|---|---|
| Player | PLAYER#<playerId> | #METADATA |
| Leaderboard | LEADERBOARD#<leaderboardId> | #METADATA |
| Score | LEADERBOARD#<leaderboardId> | SCORE#<paddedScore>#<playerId> |
| Session | PLAYER#<playerId> | SESSION#<ulid> |
The Score’s primary key collects all scores for a leaderboard in one partition, sorted by paddedScore then playerId. Querying LEADERBOARD#season_3 with ScanIndexForward: false, Limit: 100 returns the top 100 scores, no GSI required.
Sessions are stored in the player’s partition because “my game history” is the most common query.
GSI design
| GSI | Entity | gsi1pk / gsi2pk | gsi1sk / gsi2sk | Purpose |
|---|---|---|---|---|
| GSI1 | Player | PLAYER_EMAIL#<email> | PLAYER#<playerId> | Login |
| GSI1 | Score | PLAYER#<playerId> | SCORE#<leaderboardId> | Player’s scores across boards |
| GSI2 | Session | GAME#<gameId> | SESSION#<sessionId> | All sessions in a game |
GSI1 is overloaded: PLAYER_EMAIL# and PLAYER# prefixes don’t collide. GSI2 is dedicated to Sessions.
Sample data
| pk | sk | gsi1pk | gsi1sk | Entity Data |
|---|---|---|---|---|
PLAYER#p_alice | #METADATA | PLAYER_EMAIL#alice@x.com | PLAYER#p_alice | { name: "alice42", lifetimeScore: 145200, gamesPlayed: 87 } |
LEADERBOARD#season_3 | #METADATA | - | - | { name: "Season 3", type: "season", startsAt: "...", endsAt: "..." } |
LEADERBOARD#season_3 | SCORE#0000009850#p_bob | PLAYER#p_bob | SCORE#season_3 | { score: 9850, attainedAt: "..." } |
LEADERBOARD#season_3 | SCORE#0000008420#p_alice | PLAYER#p_alice | SCORE#season_3 | { score: 8420, attainedAt: "..." } |
LEADERBOARD#season_3 | SCORE#0000005100#p_carol | PLAYER#p_carol | SCORE#season_3 | { score: 5100, attainedAt: "..." } |
PLAYER#p_alice | SESSION#01HW... | - | - | { gameId: "tetris", score: 8420, durationSec: 285 } |
The LEADERBOARD#season_3 partition contains the leaderboard metadata and all scores in one partition. A descending query with Limit: 10 returns the top 10 - in one read.
Resolving each access pattern
AP1 - Get player by ID: GetItem(pk=PLAYER#p_alice, sk=#METADATA)
AP2 - Get player by email: Query(GSI1, gsi1pk=PLAYER_EMAIL#alice@x.com)
AP3 - Top N scores on a leaderboard:
Query(pk=LEADERBOARD#season_3, sk begins_with SCORE#,
ScanIndexForward=false, Limit=N)The killer query. One read, sorted, capped at N. The padded score makes lex sort equivalent to numeric sort.
AP4 - Player’s best score on a leaderboard:
Query(GSI1, gsi1pk=PLAYER#p_alice, gsi1sk=SCORE#season_3)Returns the player’s score on a specific leaderboard. If you allow multiple scores per player per board (e.g. attempts), keep only the best on insert via a conditional write.
AP5 - Player’s scores across all leaderboards:
Query(GSI1, gsi1pk=PLAYER#p_alice, gsi1sk begins_with SCORE#)AP6 - Player’s recent game sessions:
Query(pk=PLAYER#p_alice, sk begins_with SESSION#, ScanIndexForward=false)AP7 - All sessions in a game:
Query(GSI2, gsi2pk=GAME#tetris)AP8 - Session detail: GetItem(pk=PLAYER#<playerId>, sk=SESSION#<sessionId>) - requires both keys.
AP9 - Get leaderboard metadata: GetItem(pk=LEADERBOARD#season_3, sk=#METADATA)
AP10 - Player’s rank on a leaderboard:
Query(pk=LEADERBOARD#season_3,
sk > SCORE#<player's paddedScore>#zzz,
Select=COUNT)Counts the number of scores higher than the player’s. Add 1 to get the rank.
This works fine for boards with < 10,000 players - one query, one round trip. For boards with millions of players, this becomes expensive because the count is O(N) above the player. See “When DynamoDB isn’t the right fit” below.
ElectroDB entity definitions
export const PlayerEntity = new Entity({
model: { entity: "player", version: "1", service: "leaderboard" },
attributes: {
playerId: { type: "string", required: true },
name: { type: "string", required: true },
email: { type: "string", required: true },
lifetimeScore: { type: "number", required: true, default: 0 },
gamesPlayed: { type: "number", required: true, default: 0 },
createdAt: {
type: "string", required: true,
default: () => new Date().toISOString(), readOnly: true,
},
updatedAt: {
type: "string", required: true,
default: () => new Date().toISOString(),
set: () => new Date().toISOString(), watch: "*",
},
},
indexes: {
primary: {
pk: { field: "pk", composite: ["playerId"], template: "PLAYER#${playerId}" },
sk: { field: "sk", composite: [], template: "#METADATA" },
},
byEmail: {
index: "GSI1",
pk: { field: "gsi1pk", composite: ["email"], template: "PLAYER_EMAIL#${email}" },
sk: { field: "gsi1sk", composite: ["playerId"], template: "PLAYER#${playerId}" },
},
},
}, { client, table });Why this design
Padding the score in the SK is the entire reason DynamoDB can serve top-N at all. Without it, you’d need to either store scores as numbers in a GSI sort key (DynamoDB doesn’t support numeric SKs in this way), or post-sort everything in application code. Padding is permanent per board but trivial to implement.
Separate boards per period. Don’t try to maintain one global leaderboard partition forever. Spawn a new LEADERBOARD#daily_<date> each day and a new LEADERBOARD#season_N per season. Each board’s partition stays bounded in size and read traffic, and you can TTL old boards out of existence.
Score updates use conditional writes. A player improving their score on a leaderboard means deleting the old score record and inserting a new one - DynamoDB doesn’t let you mutate a key. Wrap the delete + insert in a transaction to avoid the “old score gone, new score not yet written” race window.
lifetimeScore and gamesPlayed on the Player record are read-optimized. Update them via a Stream-driven projection when sessions are written, rather than computing on every read.
Session history and leaderboard scores are different entities. A session is the immutable record of one play. A score is the mutable best-on-this-board record. Treating them as one entity would mean either re-keying on improvement (expensive) or duplicating the most recent attempt onto the score record (works, but conflates two concerns).
When DynamoDB isn’t the right fit
For some leaderboard shapes, Redis sorted sets (ZADD + ZRANGE) are simply better:
For massive global leaderboards (> 1M players with frequent rank lookups), Redis stores the leaderboard as a sorted set in memory. ZRANK is O(log N) - rank lookups are microseconds regardless of size. DynamoDB’s count-based rank (AP10) is fine for tens of thousands of players, gets uncomfortable at hundreds of thousands, and becomes a real cost at millions.
For real-time updates with very high write volume, Redis can sustain hundreds of thousands of ZADDs per second on a single instance. DynamoDB partition limits cap a single LEADERBOARD#X partition at 1,000 WCU/sec. You can shard the leaderboard partition, but Redis avoids the problem entirely.
For live dashboards with dozens of rank queries per page load, Redis is in-memory - the entire leaderboard is in RAM. DynamoDB charges per query.
The hybrid approach is what many production games use: Redis for live ranking and top-N display; DynamoDB for durable storage of player profiles, session history, and per-period leaderboard archives. When a season ends, the Redis sorted set gets snapshotted into DynamoDB Score records via a one-time job, and the Redis set can be discarded.
For most games though - especially if you’re early-stage, your boards are scoped (daily, weekly, season), and you don’t have millions of concurrent players - DynamoDB-only is simpler, cheaper, and avoids running another stateful service.
What this schema doesn’t support
| Unsupported Query | Why | If You Need It |
|---|---|---|
| Player’s percentile rank globally | O(N) count above; expensive at scale | Redis, or precomputed and cached |
| ”Players within 100 of my score” (window query) | Possible but awkward (sk BETWEEN bounds) | Two-sided range query on the leaderboard partition |
| Cross-game leaderboards | No index spans games | Add a GAME#<gameId> partition with cross-board scores, or stream to a separate index |
| Real-time top-100 with sub-100ms freshness for millions of players | Partition write limits | Redis or sharded leaderboard partitions |
This is the unsupported queries exercise. Most games don’t need any of these; the ones that do should plan for Redis from the start.
Design this visually → coming soon
Drag a Player, Leaderboard, Score, and Session onto a canvas. See the padded-score SK pattern. Spot the partition that would be hot if you tried to make it global. That’s what I’m building at singletable.dev.
Pattern #8 of 10 in the SingleTable pattern library. The padded-key trick also appears in the analytics events pattern for time-bucketed sorting. The Redis-vs-DynamoDB tradeoff returns in the chat messaging pattern (read receipts) and the event sourcing pattern (rapid replay).
