Mastering Database Sharding, Replication, and High‑Availability Strategies

Basic Concepts

The article introduces fundamental techniques for handling massive data volumes by distributing a single logical table across multiple physical tables.

Single Database

A single‑instance database stores all data in one table, which can become a bottleneck as data grows.

Sharding

Sharding solves the single‑table size problem by evenly distributing rows across multiple tables (shards).

Replication

Replication creates one or more slave databases that synchronize write operations (insert/update/delete) from the master, providing data redundancy while allowing a temporary inconsistency window during sync.

Sharding + Grouping

Combining sharding with grouping enables more complex data distribution patterns.

Routing Strategies

Requests are routed to the correct shard based on the user ID or primary key. Common routing methods include:

Range (号段) : Define numeric intervals (e.g., [1,10k] for shard 1, [10k,20k] for shard 2). Simple and evenly distributes data, but request load may be uneven if certain ranges are hotter.

Hash : Compute Hash(userId) % shardCount. Provides uniform data and request distribution; however, adding a new shard requires data migration, which can be mitigated with consistent hashing.

Availability

Read High Availability

To boost read performance and reliability, a master‑slave setup with multiple redundant slaves and read‑write separation is used. The master handles writes, while slaves serve reads.

Problems: the master is a single point of failure for writes, and the replication lag can cause stale reads.

Write High Availability

Solution 1: Dual Master

Writes are replicated between two masters. This approach can cause conflicts and is rarely used.

Solution 2: Master‑Master Sync + Virtual IP

A virtual IP (VIP) points to the active master. The active master replicates to a standby. If the active master fails, the VIP moves to the standby, making the failover transparent to applications. Drawbacks include 50 % resource utilization and no read‑scale benefit.

Read Performance

Read High Performance

Solution 1: Redundant Slaves & Read‑Write Separation

Adding more slaves and separating read traffic from write traffic improves both performance and reliability.

Solution 2: Cache Layer

Cache introduces the following flow:

Read flow : 1) Query cache; 2) If hit, return data; if miss, read from DB and populate cache.

Write flow : 1) Evict cache entry; 2) Update DB.

In extreme cases, a race condition can cause stale reads: a write evicts the cache but the DB update is not yet committed, so a subsequent read misses the cache, fetches stale data from the DB, and re‑caches it, propagating the stale value.

Two mitigation strategies:

Set an expiration time for each cache entry, forcing a DB read after expiry.

Perform asynchronous cache eviction after the DB update (e.g., schedule eviction 60 seconds later), ensuring eventual consistency.

Read‑Write Consistency

In a master‑slave architecture, replication lag creates a window where reads may not see the latest writes.

Solution: force reads to the master during the lag window by routing requests through a data‑access middleware that detects the synchronization delay and directs reads to the primary.