"The order table has reached 2 billion rows and queries are slowing down. Design a sharding strategy. How do you choose the sharding key, handle cross-shard queries, and migrate data without downtime?"
Alibaba processes billions of transactions, and database sharding is a fundamental skill for backend engineers. This question tests your ability to make practical trade-offs in data partitioning, handle the operational complexity of migrations, and design for both current needs and future growth.
Analyze the query patterns first: what are the most common access paths? This determines the optimal sharding key.
Explain your sharding key selection: user_id for user-centric queries, with a secondary index strategy for merchant-centric queries.
Address cross-shard queries: scatter-gather for analytics, denormalization or materialized views for frequently needed cross-shard data.
Describe the zero-downtime migration plan: dual-write, backfill, verify, switch reads, stop old writes.
First, I'd analyze query patterns. For an order table, 80%+ queries are by user_id (user checking their orders) and 15% by merchant_id (merchant managing their orders). I'd choose user_id as the primary sharding key using consistent hashing with 1024 virtual shards mapped to 16 physical databases initially — this allows resharding by remapping virtual shards without moving most data. For merchant queries, I'd maintain an asynchronous denormalized merchant_orders table sharded by merchant_id, updated via binlog subscription. The migration plan has four phases: (1) Dual-write: new orders go to both old and new databases, using TDDL's shadow routing. (2) Backfill: a batch job migrates historical data with checksum verification. (3) Verify: run shadow reads against both systems, comparing results for consistency. (4) Switch: gradually shift read traffic to the new sharded database (10% → 50% → 100%), then stop writes to the old database. Rollback plan: if verification fails, stop dual-write to the new database and revert reads. Total migration timeline: 2-3 weeks for a 2-billion-row table.
Mention TDDL (Alibaba's distributed database middleware) to show you know their stack.
Consistent hashing with virtual shards is the industry best practice — it enables resharding without full data migration.
Always include a rollback plan in your migration strategy.
OceanBase is excellent for many use cases, but for extreme-scale tables with well-understood access patterns, application-level sharding via TDDL gives more control over data placement and query routing. In practice, Alibaba uses both approaches for different scenarios.
These cross-shard analytical queries should not hit the transactional sharded database. Instead, stream order events via binlog to an analytics store (like AnalyticDB or Elasticsearch) optimized for full-scan queries.
HireMe AI listens to your interview in real-time, detects questions, and generates instant answer suggestions — so you never freeze up again.
Try HireMe AI Free