Designing a Real-Time Gaming Leaderboard: Horizontally Scalable & Highly Available

Gaming platforms represent some of the most data-intensive, latency-sensitive environments in distributed systems. Millions to billions of player events (kills, quiz submissions, points earned, match completions) are generated every hour, and players expect instant feedback. Leaderboards must display rankings split across multiple temporal windows (hourly, daily, weekly, lifetime) and segmented by dynamic criteria (geographic regions, private friend groups, specific match lobbies, or tournaments).

Architecting a leaderboard that supports high-throughput writes alongside ultra-low-latency analytical aggregations requires abandoning monolithic database designs in favor of Read-Write Path Separation.

---

1. System Requirements

Functional Requirements

• Real-Time Ranking: Compute player ranks based on metrics such as total score, win counts, or completion times.
• Flexible Slicing and Grouping: Support queries partitioned across:
  • Time Windows: Hourly, daily, weekly, monthly, and lifetime.
  • Contexts: Global, regional (country/city), match-specific, or private tournament groups.
• Live Updates: When an action occurs, the leaderboard must reflect the change almost instantaneously.

Non-Functional Requirements

• Minimal Data Latency (Ingestion Lag): The duration between when an event is recorded and when it is visible to read queries must be sub-second (near-zero lag).
• Ultra-Low Query Latency: Aggregations and ranking queries must return within tens of milliseconds, even during peak gaming traffic.
• High Ingestion Throughput: The system must horizontally scale to ingest hundreds of thousands of concurrent player actions.
• Workload Isolation: Ingestion spikes should not degrade query latencies, and heavy analytical queries must not bottleneck gameplay transactions.

---

2. Naive Approaches and Their Architectural Pitfalls

Pitfall 1: The Unified OLTP Database

A common initial design places both write ingestion and analytical queries inside a single relational database (such as PostgreSQL or MySQL).

[Player Submissions] ──> [Single RDBMS Node] <── [Leaderboard Queries]
                             (Compute + Storage)

Why it fails:

• Coupled Compute and Storage: Ingestion and complex aggregation queries contend for the same CPU, memory, and disk I/O.
• Contention Under Load: Massive write spikes (e.g., thousands of simultaneous answers in a live quiz) monopolize buffer pools and locks, slowing aggregation queries to a crawl. Conversely, heavy ranking queries (ORDER BY score DESC LIMIT 100 over millions of rows) starve the write path, causing submission timeouts.

Pitfall 2: Master-Replica Architecture with Identical Engines

To relieve read pressure, engineers often set up asynchronous read replicas of the primary transactional database.

[Player Submissions] ──> [Primary Node] ──(Async Replication)──> [Replica Node] ──> [Leaderboard Reads]

Why it fails:

• Mismatched Workload Primitives: Transactional databases are optimized for OLTP (single-row updates, point lookups, ACID guarantees), not OLAP (columnar scans, high-cardinality group-by operations, complex aggregations).
• Example with NoSQL (e.g., DynamoDB): DynamoDB provides single-digit millisecond latency for key-value lookups at any scale. However, replicating DynamoDB data to another DynamoDB instance does not solve the analytical challenge: DynamoDB lacks native, low-latency aggregation operators and requires costly table scans or pre-calculated indexes that quickly become inflexible.

---

3. The Read-Write Path Separation Pattern (CQRS)

To build a scalable leaderboard, we decouple the system into two distinct paths tailored to their specific operational characteristics:

1. The Write Path (OLTP): Optimized for low-latency, high-throughput point writes and single-key updates to persist the ground truth.
2. The Read Path (OLAP / Real-Time Analytics): Optimized for multi-dimensional aggregations, joins, and fast range scans over mutable data.

flowchart LR
    subgraph Write Path
        A[Client / Admin] -->|Submit Answer / Action| B[Ingestion API]
        B -->|PutItem / UpdateItem| C[(OLTP: DynamoDB)]
    end

    subgraph Synchronization
        C -->|Change Data Capture / CDC| D[Real-Time Streaming Engine]
    end

    subgraph Read Path
        D -->|Low-Lag Ingestion| E[(OLAP: Rockset)]
        F[Game Client] -->|Fetch Leaderboard| G[Leaderboard Service]
        G -->|Low-Latency Aggregation| E
    end

---

4. Production Architecture: DynamoDB + Rockset

Consider an online competitive quiz platform featuring tournaments, individual quizzes, questions, and player submissions.

Data Modeling on the Write Path (DynamoDB)

DynamoDB serves as the primary system of record. Every event is an atomic, low-overhead write operation:

• Tournaments & Quizzes: Managed by admin services with simple key-value CRUD operations.
• Player Submissions: A flat key-value record capturing the state:

{
  "PK": "TOURNAMENT#2024_06_CHAMPIONSHIP",
  "SK": "USER#98412#QUIZ#4412#Q#12",
  "user_id": "98412",
  "tournament_id": "2024_06_CHAMPIONSHIP",
  "quiz_id": "4412",
  "question_id": "12",
  "selected_option": "C",
  "is_correct": true,
  "score_delta": 100,
  "response_time_ms": 1240,
  "region": "us-east",
  "timestamp": 1718968438
}

Because DynamoDB provides predictable, single-digit millisecond latency regardless of scale, it effortlessly absorbs spikes in concurrent user responses.

Continuous Ingestion via Change Data Capture (CDC)

Instead of writing custom polling logic or complex ETL microservices, continuous ingestion leverages native database tailing (such as DynamoDB Streams integrated with Rockset):

• Rockset tails the transactional mutations from DynamoDB in real time.
• Ingestion lag remains sub-second, ensuring records written to DynamoDB are almost immediately queryable.

Data Analytics & Aggregation on the Read Path (Rockset)

Rockset acts as the real-time analytical layer. It indexes all fields dynamically (via converged indexing—combining row, columnar, and search indexes) and provides a distributed SQL execution engine optimized for low-latency analytical queries.

Example: Aggregating Leaderboard by Tournament and Region

SELECT
    user_id,
    SUM(score_delta) AS total_score,
    COUNT(question_id) AS questions_answered,
    AVG(response_time_ms) AS avg_speed
FROM
    quiz_submissions
WHERE
    tournament_id = '2024_06_CHAMPIONSHIP'
    AND region = 'us-east'
    AND timestamp >= CURRENT_TIMESTAMP() - INTERVAL 1 HOUR
GROUP BY
    user_id
ORDER BY
    total_score DESC,
    avg_speed ASC
LIMIT 100;

---

5. Scaling and Compute Isolation

In a live production environment, different query patterns can destabilize each other if they share the same compute resources. Analytical engines that separate compute from storage allow you to instantiate isolated compute clusters (Virtual Instances) pointing to the same shared dataset:

1. Ingestion Virtual Instance: Dedicated exclusively to tailing DynamoDB and building indexes. Ingestion spikes do not steal CPU cycles from reads.
2. Public Global Leaderboard Instance: Dedicated to high-QPS, shallow-depth queries (e.g., Top 100 players globally).
3. Analytics / Custom Group Instance: Dedicated to complex, ad-hoc queries (e.g., filtering ranks across private friend leagues, historical time slices, or specific geographic radii).

flowchart TD
    DDB[(DynamoDB Primary)] -->|Streams| VI_Ingest[Virtual Instance: Ingestion]
    VI_Ingest --> SharedStorage[(Shared Analytical Storage)]
    
    SharedStorage -.-> VI_Global[Virtual Instance: Global Top 100]
    SharedStorage -.-> VI_Custom[Virtual Instance: Custom / Regional Queries]
    
    VI_Global --> Client1[Public Leaderboard API]
    VI_Custom --> Client2[User Profile / Friend Analytics]

---

6. Architecture Comparison Matrix

Criteria	Single Node RDBMS	Read Replicas (RDBMS / NoSQL)	Decoupled CQRS (DynamoDB + Rockset)
Write Scalability	Low (bounded by single master)	Low to Medium	Extremely High (horizontal key-value scale)
Aggregation Performance	Degrades under write load	Medium (limited by engine type)	Sub-second (vectorized, distributed query engine)
Ingestion Lag	Zero (same node)	Low to Medium (replication lag)	Sub-second (near real-time stream tailing)
Compute Isolation	None	Partial (reads isolated from writes)	Complete (ingestion and read tiers isolated)
Operational Overhead	High at scale	High (replica management, failovers)	Low (fully managed cloud-native services)

---

Key Takeaways

• Right Tool for the Right Workload: Do not force an OLTP transactional store (like DynamoDB or MySQL) to execute complex multidimensional aggregations. Use OLTP for fast ingestion and ground-truth storage, and an analytical search engine (like Rockset) for queries.
• Separate Read and Write Paths: Decoupling ingestion from querying prevents read traffic spikes from affecting mission-critical player interactions.
• Zero-ETL Streaming: Use built-in CDC integrations between your transactional store and analytics engine to minimize operational complexity and maintain near-zero data latency.
• Compute Isolation is Essential: Separating ingestion compute from query compute guarantees predictable, low-latency performance for end users regardless of traffic surges.