Designing a High-Throughput Order Data Layer: Grab’s Architecture for Millions of Daily Transactions

For hyper-growth platforms like Grab, processing millions of food and grocery orders every single day introduces immense pressure on the data tier. The database layer is simultaneously the most mission-critical and the most brittle component of the entire architecture. A failure here directly translates to lost revenue, failed fulfillments, and broken user experiences.

To manage this scale, Grab engineered an order platform data layer designed for high availability, sub-second query latency, cost efficiency, and resiliency against traffic surges. This deep dive unpacks the architectural patterns, database selections, sparse indexing optimizations, and ingestion failover mechanisms that power their data infrastructure.

---

1. Workload Categorization and System Requirements

Before choosing database engines, the system’s access patterns must be dissected into distinct categories.

                                  ┌───────────────────────────────┐
                                  │         Client Traffic        │
                                  └───────────────┬───────────────┘
                                                  │
                         ┌────────────────────────┴────────────────────────┐
                         ▼                                                 ▼
        ┌─────────────────────────────────┐               ┌─────────────────────────────────┐
        │    Transactional Path (OLTP)    │               │     Analytical Path (OLAP)      │
        ├─────────────────────────────────┤               ├─────────────────────────────────┤
        │ • Point Lookups (Get Order)     │               │ • Order History (Past 3 Years)  │
        │ • State Mutations (Update/Done) │               │ • Historical Aggregations       │
        │ • Active/Ongoing Order Queries  │               │ • User Metrics & Reporting      │
        │ • Strict Strong Consistency     │               │ • Eventual Consistency (Lag OK) │
        └─────────────────────────────────┘               └─────────────────────────────────┘

1.1 Query Profiles

• Transactional Queries (OLTP): High-frequency, point-in-time reads and writes. Operations include placing an order, updating delivery statuses, modifying items, and fetching active orders. These require single-digit millisecond latency and strong consistency.
• Analytical Queries (OLAP / Nearline Reporting): Accessing order history spanning months or years, aggregating user spending, computing merchant payouts, and running ad-hoc filtering. These queries can tolerate small replication lags (eventual consistency) but require flexible querying capabilities.

1.2 The Spiky Traffic Challenge

On-demand food and mart delivery platforms do not experience uniform traffic. Instead, traffic is characterized by steep peaks around lunch, dinner, seasonal promotions, and unexpected bad weather. The database layer must absorb sudden order rate surges without throttling or causing latency degradation.

1.3 Core Design Goals

1. Stability and Availability: High Query Per Second (QPS) capacity. The system must support graceful degradation—isolated component failures should reduce non-critical functionality rather than causing a systemic outage.
2. Cost Effectiveness: At scale, poorly indexed queries or unmanaged provisioned capacity can inflate cloud spend by millions of dollars. The architecture must minimize idle compute and storage overhead.
3. Tailored Consistency: Strong consistency on transactional mutations (preventing stale order states), paired with bounded eventual consistency for analytical lookups.

---

2. Decoupling OLTP and OLAP: The Dual-Store Pattern

A common anti-pattern in scaling architectures is serving analytical and historical queries from the same primary transactional database. Long-running scans lock resources, consume buffer pools, and degrade the critical write path.

Grab solves this by decoupling the transactional store from the analytical store:

• The Transactional Store (OLTP): Acts as the single source of truth for active orders. It holds only active and recent data (e.g., retained for ~3 months via Time-to-Live mechanisms) to keep indexes lean and fast.
• The Historical Store (OLAP / Nearline): Holds an append-only or replicated audit trail of all historical orders across years, optimized for filtering, reporting, and broader range queries.

---

3. The Transactional Layer: AWS DynamoDB Deep Dive

For the transactional core, Grab selected AWS DynamoDB, a managed NoSQL key-value and document database. DynamoDB provides several architectural advantages aligned with Grab’s requirements:

1. Fully Managed Scalability: DynamoDB offloads operational burdens like OS patching, hardware provisioning, replication, and node repair.
2. Strong Consistency for Primary Key Reads: DynamoDB supports strongly consistent reads on primary key queries (GetItem), ensuring clients never observe stale order states after an update.
3. Dynamic Hot-Partition Management: Spiky workloads routinely cause “hot keys” in distributed databases. DynamoDB automatically reallocates throughput across partitions, splitting hot partitions or redistributing infrequently accessed keys into shared partitions to prevent hot-spot throttling.

3.1 The Sparse GSI Optimization: Lean Indexing

A critical requirement is retrieving ongoing orders for a given user quickly (e.g., rendering active tracking screens on the mobile app).

The Naive Approach

Create a Global Secondary Index (GSI) on user_id:

• Problem: Every order ever placed by a user remains in this GSI.
• Over years, a user might accumulate hundreds of completed orders. When querying for active orders, the database must scan through hundreds of historical records to filter for status = 'ONGOING'.
• This bloats the GSI storage, wastes provisioned Read/Write Capacity Units (RCUs/WCUs), and increases query latency.

Grab’s Solution: Partial/Sparse Indexing via a Synthetic Attribute

DynamoDB does not index items where the GSI partition key is null or omitted. Grab leveraged this internal behavior by introducing a dedicated attribute: user_id_gsi.

                      State: ONGOING                                       State: COMPLETED
         ┌──────────────────────────────────────┐             ┌──────────────────────────────────────┐
         │ order_id    : "ord_9876"             │             │ order_id    : "ord_9876"             │
         │ user_id     : "usr_1234"             │  Transition │ user_id     : "usr_1234"             │
         │ status      : "ONGOING"              │ ──────────► │ status      : "COMPLETED"            │
         │ user_id_gsi : "usr_1234"             │             │ user_id_gsi : NULL (attribute removed│
         └──────────────────┬───────────────────┘             └──────────────────┬───────────────────┘
                            │                                                    │
                            ▼                                                    ▼
                 Indexed in GSI Table                                   Removed from GSI Table
         ┌──────────────────────────────────────┐             ┌──────────────────────────────────────┐
         │ GSI Partition Key : "usr_1234"       │             │                                      │
         │ Item Ref          : "ord_9876"       │             │         (Entry Deleted)              │
         └──────────────────────────────────────┘             └──────────────────────────────────────┘

Execution Mechanism

1. Order Creation: When a new order is initialized with status ONGOING, the application writes the item with user_id_gsi set equal to user_id.
2. GSI Ingestion: DynamoDB automatically propagates the item to the GSI partitioned by user_id_gsi.
3. Order Completion/Cancellation: When the order transitions from ONGOING to COMPLETED or CANCELLED, the application explicitly updates the item to set user_id_gsi = null (or deletes the attribute).
4. Sparse Eviction: DynamoDB instantly purges the item from the GSI.

Engineering Impact

• Near-Zero Noise: The GSI contains only ongoing orders across the entire platform. If there are 50,000 active deliveries platform-wide, the index holds exactly 50,000 items, regardless of how many hundreds of millions of historical orders exist in the base table.
• Ultra-Fast Queries: Retrieving active orders for a user returns only relevant items without client-side or server-side post-filtering.
• Massive Cost Reduction: Avoids write amplification and storage bloat on historical data within the secondary index.

3.2 Time-To-Live (TTL) Eviction

Because analytical stores capture historical records, the DynamoDB primary table does not need to store completed orders indefinitely. Grab configures a Time-To-Live (TTL) attribute (e.g., 3 months). DynamoDB’s background sweep identifies expired items and purges them from the primary table without consuming allocated write throughput.

---

4. The Analytical Layer & Ingestion Pipeline

For historical queries, complex filtering, and analytical reporting, a dedicated database engine is required. While massive enterprise aggregations frequently utilize data warehouses (e.g., BigQuery, Snowflake, Redshift), nearline transactional reporting for users and merchants requires fast, structured operational queries without the high spin-up latency of columnar warehouses.

Grab chose sharded MySQL as the analytical/historical database, structuring partition keys carefully to avoid cross-shard queries.

                                  ┌────────────────────────┐
                                  │     Order Service      │
                                  └───────────┬────────────┘
                                              │
                     ┌────────────────────────┴────────────────────────┐
           (Sync)    │                                                 │ (Async Fire-and-Forget)
                     ▼                                                 ▼
         ┌───────────────────────┐                         ┌───────────────────────┐
         │     AWS DynamoDB      │                         │  Primary: Kafka Bus   │
         │  (OLTP - Active Data) │                         │     (99.95% SLA)      │
         └───────────────────────┘                         └───────────┬───────────┘
                                                                       │
                                                        Failover Path  ├─── Failure ───► ┌───────────────┐
                                                        (Fallback)     │                │   AWS SQS     │
                                                                       ▼                └───────┬───────┘
                                                           ┌───────────────────────┐            │
                                                           │   Ingestion Workers   │◄───────────┘
                                                           └───────────┬───────────┘
                                                                       │ (Idempotent Upsert)
                                                                       ▼
                                                           ┌───────────────────────┐
                                                           │     Sharded MySQL     │
                                                           │  (OLAP / Historical)  │
                                                           └───────────────────────┘

4.1 Resilient, Multi-Tiered Asynchronous Ingestion

Every order state change emitted by the Order Service must be copied to the MySQL historical database. Direct synchronous writes would couple the availability of the transactional write path to MySQL’s health. Therefore, data replication is strictly asynchronous.

The Ingestion Hierarchy

1. Primary Transport (Apache Kafka): Events are pushed to Kafka topics. A cluster of ingestion consumers reads from these topics and writes the batched events into MySQL.
2. Fallback Transport (Amazon SQS): Kafka provides high availability, but even a 99.95% SLA allows for occasional downtime. If the Order Service encounters failures publishing to Kafka, it switches to a fallback path using Amazon SQS.
3. Dead Letter Queues (DLQ): If the SQS consumer path encounters poisonous payloads or downstream persistence failures, messages move to a Dead Letter Queue (DLQ) for alerting, backoff, and manual or automated replay.

This multi-tiered messaging layer guarantees that the analytics pipeline remains operational even during large-scale network splits or broker failures.

---

5. Handling Concurrency and Out-of-Order Delivery

In high-throughput, distributed event-driven architectures, in-order delivery cannot be guaranteed globally. Network anomalies, consumer thread retries, and multi-broker partitioning mean events may arrive at the MySQL ingestion worker out of sequence.

Two critical scenarios must be addressed at the database level:

Scenario 1: UPDATE Arrives Before CREATE

Due to transient network re-routing, a driver assignment update (ORDER_ACCEPTED) might hit the ingestion worker before the initial ORDER_CREATED event.

• Naive Fix: Dropping the update because the primary key does not exist causes data loss.
• Grab’s Solution: Ingestion workers always execute an Idempotent Upsert (e.g., using INSERT ... ON DUPLICATE KEY UPDATE in MySQL). If the row does not yet exist, it is initialized with available fields.

Scenario 2: Stale Updates Overwriting Fresh Data

Assume two updates occur in rapid succession:

1. Update 1 (Order Prepared at $t_1$)
2. Update 2 (Order Picked Up at $t_2$, where $t_2 > t_1$)

If Update 2 arrives and commits before Update 1 due to consumer concurrency, applying Update 1 unconditionally would roll back the record to a stale state.

                       Arrival Timeline at Ingestion Worker:

       t = 0ms                                  t = 10ms (Late Arrival)
┌──────────────────────┐               ┌──────────────────────────────────────┐
│ Apply Update 2 (t_2) │ ────────────► │ Attempt Update 1 (t_1)               │
│ Status = PICKED_UP   │               │ Status = PREPARED                    │
└──────────┬───────────┘               └──────────────────┬───────────────────┘
           │                                              │
           ▼                                              ▼
   Database Record:                              Evaluates Monotonic Guard:
   [status: PICKED_UP,                           WHERE event_timestamp > existing.t_2
    last_updated: t_2]                           (Condition Evaluates to FALSE)
                                                          │
                                                          ▼
                                                Write Discarded / No-Op

The Monotonic Timestamp Guard

To solve out-of-order execution, the ingestion pipeline pairs the upsert with an explicit state/timestamp comparison:

INSERT INTO orders (order_id, user_id, order_status, updated_at)
VALUES ('ord_123', 'usr_456', 'PREPARED', '2023-01-01 12:00:00')
ON DUPLICATE KEY UPDATE
    order_status = IF(VALUES(updated_at) >= orders.updated_at, VALUES(order_status), orders.order_status),
    updated_at   = IF(VALUES(updated_at) >= orders.updated_at, VALUES(updated_at), orders.updated_at);

By leveraging monotonic timestamp guards within the query, outdated versions are ignored, ensuring eventual consistency converges to the true final state.

---

6. Summary: Architectural Patterns and Takeaways

Grab’s order data platform achieves massive scale, resilience, and cost-efficiency through foundational distributed systems design:

Challenge	Traditional Pitfall	Grab’s Architectural Solution
Mixed Query Interference	Single database serving transactional mutations and historical analytics	Separation of concerns: DynamoDB (OLTP) for active state, MySQL (OLAP) for analytical history
Secondary Index Bloat	Creating standard GSI on user_id, indexing millions of dead orders	Sparse Indexing via Synthetic Attribute: Index only active orders using user_id_gsi, setting to null on completion
Message Broker Failure	Ingestion pipeline stalls when Kafka is unavailable	Multi-Tiered Failover: Kafka primary $\rightarrow$ AWS SQS fallback $\rightarrow$ Dead Letter Queues
Out-of-Order Events	Data inconsistency or deadlocks from out-of-order processing	Idempotent Upserts with Version Checks: Conditional SQL updates using monotonic timestamp checks