Database Sharding & Partitioning
Scale data storage and query performance by splitting large datasets intelligently.
Definition
Partitioning organizes large tables into smaller logical pieces. Sharding distributes those pieces across separate database servers. Both aim to improve scale, performance, and operational control.
Why It Matters
- Single database node hits limits on storage, CPU, or throughput.
- Large tables cause slower maintenance, vacuum/index operations, and backup windows.
- Regional/user-segment traffic can be isolated for better performance and resilience.
When to Use
- Table sizes and write/read throughput exceed single-node comfort limits.
- Natural partition keys exist (time, tenant, geography, user).
- Operational team can support routing, observability, and migration tooling.
When NOT to Use
- Early-stage systems where vertical scaling still meets SLOs.
- Workloads dominated by global joins/transactions across all keys.
- Teams without automation for migration, backup, and shard health.
Code Variations
CREATE TABLE orders (
id BIGINT PRIMARY KEY,
user_id BIGINT NOT NULL,
created_at TIMESTAMP NOT NULL,
amount NUMERIC(12,2) NOT NULL
) PARTITION BY RANGE (created_at);shardId = hash(userId) % N
route query to db_shard_[shardId]
Example:
userId=12345 -> shard 21) Add new shard capacity
2) Move selected key ranges/virtual shards in batches
3) Dual-read/consistency verify
4) Switch router ownership
5) Decommission old placementPartitioning vs Sharding
| Aspect | Partitioning | Sharding |
|---|---|---|
| Scope | Inside one database cluster/node family | Across multiple independent database nodes/clusters |
| Primary goal | Manageability + query pruning | Horizontal scale beyond single node limits |
| App complexity | Lower to medium | Higher (routing, cross-shard queries, rebalancing) |
| Operational complexity | Moderate | High |
| Failure domain | Often shared infra | Can isolate shard-level failures |
Common Confusions Cleared Up
Partitioning can still hotspot one partition if key distribution is skewed. Sharding can hotspot one node if shard key is poor or traffic is non-uniform.
Data model should minimize cross-shard transactions/joins. Denormalization or async materialized views are often used to avoid fan-out queries.
Bad indexes and poor query design remain costly even after sharding; first fix query/index fundamentals.
Variations / Implementations
Partitioning strategies
Range partitioning (time/order ranges), hash partitioning (uniform spread), and list partitioning (category/tenant class). Choose based on query patterns.
Sharding strategies
Hash-based sharding for even distribution, range-based sharding for locality, and directory/lookup-based sharding for flexible control.
Shard key design
Best shard key has high cardinality, stable value, and aligns with dominant query filters. Poor key choice creates hotspots and expensive cross-shard fan-out.
Common Mistakes / Anti-Patterns
- Choosing low-cardinality shard key (few massive hotspots).
- Ignoring rebalancing strategy until shard is full.
- Random cross-shard queries from application without query discipline.
- No per-shard metrics/alerting.
- Assuming shard count is permanent (no future expansion design).