← back to work

Database Sharding Layer

Role: Systems Engineer
Timeline: 10 weeks
Stack: Rust, MySQL, gRPC, etcd, Prometheus, Orchestrator

Problem

A SaaS analytics platform stored 8TB of time-series event data in a single MySQL 8.0 instance. Write throughput degraded as the dataset grew: INSERT latency climbed from 2ms to 450ms at peak. Queries across the full dataset required table scans taking 30+ seconds. The growth trajectory projected 20TB within 12 months.

The requirement was clear: horizontal sharding with zero application code changes. The database layer had to be transparent.

Architecture

                   ┌──────────────────────────┐
                   │  Sharding Proxy (Rust)   │
                   │  - gRPC endpoint          │
                   │  - hash ring routing      │
                   │  - connection pooling     │
                   └─────┬──────┬──────┬───────┘
                         │      │      │
              ┌──────────┘      │      └──────────┐
              │                 │                 │
     ┌────────▼───────┐ ┌──────▼────────┐ ┌──────▼────────┐
     │  Shard 0       │ │  Shard 1      │ │  Shard 2-15   │
     │  (MySQL 8.0)   │ │  (MySQL 8.0)  │ │  (...16 nodes)│
     │  500GB / 4TB   │ │  500GB / 4TB  │ │               │
     └────────────────┘ └───────────────┘ └───────────────┘

Shard key: Composite hash of (tenant_id, org_id, date_hour). This provides:

  • Even data distribution across shards (tested: < 5% variance)
  • Query locality — all data for a tenant lives in one shard
  • Time-range scans within a single shard

Shard Key Decision Tree

Is the query pattern known? ─→ YES ─→ Use composite key
        │                                    │
        NO                                   │
        │                                    │
        ▼                                    ▼
Use hash of primary key ─────────→ Test distribution
                                        │
                                        ▼
                              Variance < 5%?
                              YES ─→ Ship it
                               NO ─→ Adjust hash bits / add shard

This decision tree was applied to every proposed shard key candidate. We rejected 3 candidate keys before settling on the composite hash.

Rebalancing Strategy

Rebalancing is the hardest problem in sharding. We implemented virtual shards (1024 virtual shards mapped to 16 physical nodes) to minimize data movement during topology changes.

Rebalance procedure:

  1. Add new node → register in etcd → proxy detects via watch
  2. Proxy marks 64 virtual shards as "moving" for the new node
  3. Background copy process streams rows via SELECT ... WHERE shard_id IN (...) + INSERT ... ON DUPLICATE KEY UPDATE
  4. Once copy completes, proxy atomically flips routing via etcd transaction
  5. Old shards enter read-only mode → eventually dropped

Worst-case rebalance: 12 minutes for 500GB shard | Observed downtime: 400ms (one TCP timeout during etcd flip)

Consistency Guarantees

  • Read-your-writes: Proxy tracks last-write shard and routes subsequent reads from the same connection to that shard for 5 seconds (configurable)
  • Cross-shard transactions: Not supported. The proxy rejects multi-shard transactions with a clear error message
  • Global secondary indexes: Built on top of a separate index table sharded by index key

Results

  • Write throughput: 450ms → 4ms P99 (100x improvement)
  • Full-scan queries: 30s → 800ms (shard-local scan)
  • Zero application changes: Applications connected to the proxy via standard MySQL protocol
  • Storage: 8TB → evenly distributed across 16 nodes with 58% headroom