Data Partitioning and Sharding
Horizontal and vertical partitioning, choosing shard keys, hash vs range vs directory sharding, consistent hashing, hot spots and resharding strategies.
What it is
Partitioning splits data into parts. Sharding usually means horizontal partitioning across separate database nodes so that each holds a subset of rows and handles a subset of traffic. It is the main way to scale writes and storage beyond one machine.
| Type | Description | Example |
|---|---|---|
| Vertical partitioning | Split columns or tables into different stores | Move large product descriptions to a document store |
| Functional partitioning | Different domains in different databases | Orders DB, catalog DB, users DB |
| Horizontal (sharding) | Rows split across nodes by a key | Customers A–M on shard 1, N–Z on shard 2 |
Sharding strategies
| Strategy | How | Pros | Cons |
|---|---|---|---|
| Hash | shard = hash(key) mod N or consistent hashing | Even distribution | Range queries scatter; resharding moves data |
| Range | Key ranges per shard | Efficient range scans | Hot spots on sequential keys (timestamps) |
| Directory / lookup | Mapping table key → shard | Flexible moves, tenant placement | Lookup service is critical and must be cached |
| Geo | By region/country | Data residency, locality | Uneven sizes; cross-region queries |
Consistent hashing places nodes and keys on a ring; adding a node moves only about 1/N of keys. Virtual nodes smooth the distribution. It is used by Dynamo-style stores and many caches.
flowchart LR R[Router / shard map] -->|hash customer_id| S1[(Shard 1)] R --> S2[(Shard 2)] R --> S3[(Shard 3)] S1 --> S1r[(Replica)] S2 --> S2r[(Replica)] S3 --> S3r[(Replica)]
Choosing a shard key
A good shard key:
- Has high cardinality and spreads load evenly.
- Matches the dominant access pattern, so most queries hit one shard.
- Keeps data that is transactionally updated together on the same shard (e.g. tenant_id for SaaS, customer_id for orders).
- Avoids monotonic values (auto-increment IDs, timestamps) for range sharding.
Real-world example
A multi-tenant SaaS shards by tenant_id using a directory: small tenants are packed onto shared shards; a very large tenant gets a dedicated shard. Because all of a tenant's data lives together, transactions stay local and cross-tenant queries (rare, analytics) go to the warehouse instead.
Advantages and disadvantages
| Advantages | Disadvantages |
|---|---|
| Scales writes and storage nearly linearly | Cross-shard joins and transactions are hard |
| Smaller indexes, faster queries per shard | Resharding is an operational project |
| Failure isolation per shard | Hot keys still overload a single shard |
| Enables data residency | More operational surface (backups, upgrades per shard) |
Technology options
Native sharding: DynamoDB, Cassandra/ScyllaDB, MongoDB, Cosmos DB, Bigtable, Spanner, CockroachDB, YugabyteDB. Sharding layers for MySQL/PostgreSQL: Vitess, Citus, PlanetScale, Neon/Aurora Limitless Database (version-sensitive). Application-level sharding with a shard map.
Architect-level considerations
[!ARCHITECT] Delay sharding until needed, but choose the future shard key now and include it in primary keys and APIs. Retro-fitting a tenant or customer key into every table is far more painful than sharding itself.
[!WARNING] Hot partitions Managed stores often limit throughput per partition (for example, DynamoDB documents per-partition throughput limits). A celebrity user, a flash-sale SKU or "today's date" as a key can exceed it regardless of total capacity.
Interview questions and answers
How would you reshard a live database with no downtime?
Use a directory or consistent hashing so you can move a subset of keys; double-write or CDC-replicate the moving keys to the new shard, backfill, verify with checksums, flip the routing for those keys, then stop writes to the old location and clean up. Do it in small batches with rollback.
How do you run a query that needs data from all shards?
Scatter-gather with pagination and timeouts for occasional queries, or better, maintain a separate read model (search index or warehouse fed by CDC) designed for that query.
What shard key would you choose for a chat system?
Conversation ID, because messages are read and written per conversation and ordering is per conversation. User inboxes can be a separate index keyed by user ID.