Write Offloading

SYNCHRONOUS (direct DB write):
  Client → App Server → Database → Acknowledge
  Latency: 20-100ms (wait for DB)
  Throughput: limited by DB write speed
  During spike: DB overwhelmed → timeouts → failures

OFFLOADED (buffered, async):
  Client → App Server → Buffer/Queue → Acknowledge (fast!)
                              ↓
                    Worker → Database (at its own pace)
  Latency: 1-5ms (write to queue only)
  Throughput: limited by queue capacity (much higher)
  During spike: queue absorbs burst → DB processes steadily

Write Request: UPDATE users SET name = 'Alicia' WHERE id = 42

Step 1 — Append to WAL (sequential disk write):
  WAL entry: {txn: 1001, table: users, id: 42, name: "Alicia"}
  → Sequential write to disk: ~0.1ms (fast!)
  → Write is now DURABLE (survives crash)

Step 2 — Apply to data files (random disk write):
  Update the actual row in the users table on disk
  Update indexes
  → Random I/O: ~5-20ms (slow, but can be deferred)

Step 3 — Acknowledge to client:
  "Write confirmed" (after WAL write, not after data file update)

Crash Recovery:
  System crashes after Step 1 but before Step 2
  On restart: replay WAL entries → apply missing changes
  → No data loss, database is consistent

TRADITIONAL (store current state):
  users table: { id: 42, name: "Alicia", balance: 500 }
  → You know the current balance
  → You DON'T know how it got there

EVENT SOURCING (store events):
  events table:
    { id: 1, user: 42, type: "AccountCreated", data: {name: "Alice"} }
    { id: 2, user: 42, type: "Deposited",      data: {amount: 1000} }
    { id: 3, user: 42, type: "Withdrawn",       data: {amount: 300} }
    { id: 4, user: 42, type: "NameChanged",     data: {name: "Alicia"} }
    { id: 5, user: 42, type: "Deposited",       data: {amount: 100} }
    { id: 6, user: 42, type: "Withdrawn",       data: {amount: 300} }

  Current state = replay all events:
    Created → balance: 0
    +1000   → balance: 1000
    -300    → balance: 700
    +100    → balance: 800
    -300    → balance: 500
    Name: "Alicia"

  → You know the current balance AND the full history

Problem: user has 10,000 events. Rebuilding state = replay 10,000 events.

Solution: periodic snapshots
  Every 1,000 events, save a snapshot of current state:
    Snapshot at event 9000: { balance: 4,200, name: "Alicia" }

  To get current state:
    Load snapshot (event 9000) → replay events 9001-10000 only
    → 1,000 replays instead of 10,000

SYNCHRONOUS (without queue):
  Client → App → INSERT INTO orders (...) → wait 50ms → "Order confirmed"
  Problem: DB handles 10K writes/sec. Flash sale: 100K writes/sec → DB dies.

ASYNC (with queue):
  Client → App → Publish to Kafka/SQS → "Order received" (2ms)
                        ↓
              Worker → INSERT INTO orders (...) → at DB's pace

  Client latency: 2ms (queue write only)
  DB load: steady 10K/sec (worker processes at sustainable rate)
  Flash sale: queue absorbs 100K/sec burst, worker drains over time

  Queue depth during spike:
    T=0:   0 messages
    T=1m:  (100K - 10K) × 60 = 5.4M messages queued
    T=5m:  spike ends, queue starts draining
    T=14m: queue fully drained, all orders processed

INDIVIDUAL WRITES (1,000 separate INSERTs):
  INSERT INTO events (user_id, type, data) VALUES (1, 'click', '...');
  INSERT INTO events (user_id, type, data) VALUES (2, 'view', '...');
  ... (998 more)
  → 1,000 network round trips
  → 1,000 transaction commits
  → ~5 seconds total

BATCH WRITE (1 bulk INSERT):
  INSERT INTO events (user_id, type, data) VALUES
    (1, 'click', '...'),
    (2, 'view', '...'),
    ... (998 more);
  → 1 network round trip
  → 1 transaction commit
  → ~100ms total (50x faster)

Buffering flow:
  Incoming writes → In-memory buffer (array)
  When buffer reaches 1,000 items OR 100ms has passed:
    → Flush: bulk INSERT all buffered items
    → Clear buffer, start collecting again

10,000 Servers
      │
      │ 500K events/sec
      ▼
┌──────────────┐
│    Kafka     │  ← absorbs full ingestion rate
│  (3 brokers) │  ← partitioned by service name
│              │  ← 7-day retention (event sourcing)
└──────┬───────┘
       │
       │ Consumer group (10 workers)
       │ Each buffers 10K events → bulk INSERT
       ▼
┌──────────────┐
│  ClickHouse  │  ← receives batched bulk inserts
│  (3 nodes)   │  ← column-store, compressed
│              │  ← handles 100K+ rows/sec per node
└──────────────┘

Spike handling:
  Normal: 500K/sec → Kafka → consumers keep up
  Spike (2M/sec): Kafka queues 1.5M/sec excess
  After spike: consumers drain the backlog
  No data loss, no DB overload