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:

  1. Has high cardinality and spreads load evenly.
  2. Matches the dominant access pattern, so most queries hit one shard.
  3. Keeps data that is transactionally updated together on the same shard (e.g. tenant_id for SaaS, customer_id for orders).
  4. 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.