When a database outgrows one server, because there are too many writes or too much data for one machine’s disk and memory, sharding splits the data across several independent database servers (shards), each holding a subset of the rows. It’s the most powerful way to scale writes, and also the most complex. Most applications never need it. Here’s what it involves, and what to try first.

Partitioning vs sharding vs replication

  • Replication copies the same data to several servers. It scales reads and adds availability, but every write still goes to one primary. See database replication.
  • Partitioning splits a large table into pieces within one server (PostgreSQL declarative partitioning, MySQL partitions), for example by month. It helps maintenance and some queries, but not the limits of one machine.
  • Sharding splits data across multiple servers. Each shard is a separate database (often with its own replicas).

Before sharding: exhaust the simpler options

Sharding adds permanent complexity to every query, migration and backup. Usually try these first:

  1. Optimise queries and indexes. It’s often the biggest win by far. See MySQL EXPLAIN and database indexing.
  2. Cache hot reads (Redis), and use connection pooling. See connection pooling.
  3. Scale up: more RAM, faster NVMe and more cores. One modern server handles a lot. See vertical vs horizontal scaling.
  4. Read replicas for read-heavy load.
  5. Archive old data, and partition big tables by time.
  6. Split by service: move independent domains (analytics, logs, search) into their own databases. See time-series databases.

Shard when writes or total data size genuinely exceed what one primary can handle, with headroom to grow.

The shard key

Everything depends on choosing the shard key, the column that decides which shard a row lives on:

  • Tenant or customer ID in SaaS apps: each customer’s data lives on one shard, so most queries stay on one shard.
  • User ID in consumer apps.
  • Geography (region), for data residency.

A good shard key:

  • appears in most queries, so they can be routed to a single shard;
  • spreads data and load evenly (no “celebrity” hot shard);
  • rarely changes for a given row.

Sharding strategies

Strategy How Pros Cons
Hash shard = hash(key) % N (or consistent hashing) Even distribution Range queries span all shards. Changing N moves data (consistent hashing reduces that)
Range Key ranges per shard (IDs 1–1M → shard 1…) Efficient range scans Hot spots on the newest range
Directory / lookup A lookup table maps keys to shards Flexible, easy to move individual tenants Lookup service must be fast and highly available
Geographic By region Data locality, compliance Uneven sizes

Many systems use many logical shards (e.g. 1,024) mapped onto fewer physical servers, so rebalancing means moving logical shards, not re-hashing every row.

The hard parts

  • Cross-shard queries: reports and searches across all tenants must query every shard and merge the results. Often you feed a separate analytics store instead (ClickHouse, a data warehouse).
  • Cross-shard transactions: atomic updates across shards need distributed transactions (two-phase commit) or application-level sagas. Design so most transactions stay within one shard.
  • Unique IDs: auto-increment per shard collides. Use UUIDs (v7 for ordering), snowflake-style IDs, or shard-prefixed sequences.
  • Resharding: moving data when shards grow uneven, or adding capacity, without downtime. Plan for it from day one.
  • Operations: N times the backups, monitoring, migrations and failovers.

Tools that help

Tool For Notes
Vitess MySQL Sharding middleware used at very large scale. Handles routing and resharding
Citus PostgreSQL Extension that distributes tables across nodes, with SQL mostly unchanged
ProxySQL MySQL Query routing rules (simpler sharding setups)
MongoDB sharded clusters MongoDB Built-in sharding with config servers and mongos routers
Distributed SQL (CockroachDB, YugabyteDB, TiDB) Postgres- or MySQL-compatible Automatic sharding and rebalancing. Applications see one database. See CockroachDB

Distributed SQL databases remove much of the manual sharding work, at the cost of higher per-query latency, and different operational characteristics.

Application-level sharding: a simple pattern

For multi-tenant SaaS on a few servers:

  1. A small directory table maps tenant_id → database host.
  2. The app looks up the tenant’s database on each request (cached), and connects there.
  3. New tenants are placed on the least-loaded shard. Big tenants can be moved individually.

That’s often all a growing SaaS needs, for years.

Quick reference

Question Guidance
Need to scale reads? Replicas and caching, not sharding
Need to scale writes or size? Optimise, scale up, then shard
Shard key In most queries, evenly distributed, stable
Avoid Cross-shard transactions in hot paths
Plan for Resharding, global IDs, per-shard operations

Related: high availability on VPS and SQL vs NoSQL.

  • Databases
  • Architecture
  • Scaling