Partitioning splits one large table into smaller pieces; sharding is partitioning where the pieces live on different machines, so each node owns only a slice of the data and takes only a slice of the traffic. Interviewers probe it because it is the point where a database stops being "just SQL": the choice of shard key decides whether load spreads evenly, whether your queries stay on one node, and whether you can add capacity without a painful migration. For a Backend or AI engineer, the same ideas show up in Kafka partitions, Redis Cluster, Cassandra, DynamoDB, and any vector store that spreads embeddings across nodes.
ONE NODE, PARTITIONED SHARDED ACROSS NODES REPLICATION (contrast)
+------------------------+ +---------+ +---------+ +---------+ +---------+ +---------+
| orders | | Shard 0 | | Shard 1 | | Shard 2 | | Primary | | Replica |
| p_2026_01 | p_2026_02 | | ids A-H | | ids I-P | | ids Q-Z | | ALL data| | ALL data|
| p_2026_03 | ... | | node 1 | | node 2 | | node 3 | +---------+ +---------+
+------------------------+ +---------+ +---------+ +---------+
one server, one engine each node has a DIFFERENT subset each node has the SAME data
helps: pruning, cheap drops, helps: write scale, storage scale helps: read scale, availability
maintenance and per-node working setSingle-node partitioning (PostgreSQL declarative partitioning, MySQL partitioned tables) gives the planner a way to skip irrelevant pieces (partition pruning) and lets you drop an old month by dropping one partition instead of running a huge DELETE. It does not add a second machine, so it does not raise the ceiling on CPU, disk throughput or memory. Sharding does, because each shard is an independent database.
The three ideas are often confused, so state the split explicitly: replication copies the same data to more nodes (read scale, availability), partitioning divides data on one node, and sharding divides data across nodes (write scale, storage scale). Production systems combine them: every shard is itself a primary with replicas. Sharding also usually gives you nothing for availability on its own: lose a shard without replicas and you lose that slice of the data.
Strategy How key maps to shard Good at Bad at
-------------- ----------------------------- ------------------------- -------------------------
RANGE id 1-250 -> shard 0, ... range scans, locality, monotonic keys: all NEW
easy to split a range writes hit the last shard
HASH hash(key) mod N (or ring) even spread of keys range queries scatter to
every shard
DIRECTORY lookup table: key -> shard full flexibility, move lookup service is a
(lookup) (tenant 42 -> shard 7) one tenant at will bottleneck / SPOF
Good shard key: high cardinality, evenly distributed access, present in most queries
Bad shard key: country, status, boolean (few values, skewed)
created_at / auto-increment id (all writes go to the newest range)The shard key is the column (or columns) whose value decides which shard owns a row. Every read or write that includes it can go straight to one shard; anything else must ask all shards (scatter-gather) and merge. So a good key has three properties: high cardinality (many distinct values to spread), even access (no value gets vastly more traffic), and it appears in the queries you run most, such as tenant_id for a multi-tenant SaaS or user_id for per-user data. The shard key is very hard to change later, because changing it means moving most of the data.
The classic mistake is a monotonic key with range sharding. Real SQL below (SQLite, run locally) shows the effect: the 100 newest orders all land on one range shard while id % 4 spreads them evenly. Hash sharding fixes the write hotspot at the price of range queries: "orders between two ids" now touches every shard.
hash(key) mod 4 hash(key) mod 5 (add a shard)
key A: 7 mod 4 = 3 -> shard 3 7 mod 5 = 2 -> shard 2 MOVED
key B: 12 mod 4 = 0 -> shard 0 12 mod 5 = 2 -> shard 2 MOVED
key C: 20 mod 4 = 0 -> shard 0 20 mod 5 = 0 -> shard 0 stays
Changing N changes the divisor for EVERY key: roughly N/(N+1) of keys move.
CONSISTENT HASHING (hash ring)
0
D ● ● key1 ---> first node clockwise = B
● ● B
key2 ● ●
A ● ● C Each node owns the arc up to itself.
● ... ● Add node E: it takes over only the arc
ring between its predecessor and itself;
every other key stays where it was.
Virtual nodes: each physical node is placed at many points on the ring
(e.g. 100), so arcs are small and load evens out.With hash(key) mod N every key's home depends on N, so adding one node to a 4-node cluster remaps about 80 percent of the keys (the real run below measured 79.6 percent), which means a massive data migration and, in a cache, a cold-cache stampede on the database. Consistent hashing places both nodes and keys on a ring; a key belongs to the first node clockwise from its hash. Adding a node steals only one arc's worth of keys, about 1/(N+1) of them in expectation, and removing a node hands its arc to its neighbour.
The catch is balance: with one point per node, arcs are wildly uneven (measured below, one node held 4077 of 10000 keys, another only 124). Virtual nodes place each physical node at many ring positions so the load evens out and a joining node draws a little from everyone. Cassandra and Dynamo-style stores use this idea (a token ring with virtual nodes). Redis Cluster takes a related but different route: a fixed 16384 hash slots (CRC16(key) mod 16384), with whole slots assigned to nodes and moved individually. MongoDB hashed sharding also does not use a classic ring: it hashes the key and splits the hashed space into chunks that a balancer migrates.
Even key space != even load.
1000 writes, 700 belong to ONE celebrity account, 4 shards, hash sharding:
shard 0: 69 shard 1: 79 shard 2: 782 <-- hot shard 3: 70
Mitigations
- salt the hot key: user:celeb#0 ... user:celeb#7 (writes spread; reads must gather 8 keys)
- dedicated shard / capacity for known heavy tenants
- cache or buffer hot reads; batch/aggregate hot writes (counters)
- split the hot range further (range/chunk splitting)
New problems once data is spread:
cross-shard JOIN / aggregate -> scatter-gather, or denormalize / co-locate by shard key
cross-shard transaction -> 2PC (slow, blocking risk) or sagas (eventual, compensation)
global unique IDs -> no single auto-increment; Snowflake-style or UUID
secondary indexes -> local (query hits all shards) or global (extra write hop)
resharding / rebalancing -> live data movement, dual writes, verification
operations -> N databases to back up, upgrade, monitor, schema-migrateA hot partition happens when a shard receives disproportionately more traffic or data than its peers, even if keys are evenly hashed, because access is skewed: one celebrity account, one viral item, one big tenant, or a time-based key where "now" is always the hottest range. The shard that owns it saturates first and caps overall throughput, and adding more shards does not help because the hot key still maps to exactly one. Salting spreads a known hot key over several sub-keys, at the cost that every read of that key must fan out and merge; some managed stores (for example DynamoDB's adaptive capacity) also soften skew automatically, but only up to a point.
Sharding's costs are the real interview answer. Anything that used to be a local JOIN or transaction now crosses the network; cross-shard atomicity needs two-phase commit (coordination latency and blocking on coordinator failure) or sagas (eventual consistency plus compensating actions); auto-increment IDs stop being globally unique; and adding capacity means moving live data. Design the shard key so that the most common transaction touches one shard, for example keeping a customer's orders, addresses and payments together by customer_id.
hash(key) mod N remaps most keys when N changes (about 80 percent going from 4 to 5 nodes); consistent hashing moves roughly 1/(N+1) of keys and needs virtual nodes to stay balanced.tenant_id or user_id) and a strategy: range, hash, or directory lookup.-- SQLite (runs as-is): why a monotonic key + RANGE sharding creates a hot shard
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER);
WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM n WHERE i < 1000)
INSERT INTO orders SELECT i, i % 50 FROM n;
SELECT 'range' AS strategy, (id - 1) / 250 AS shard, COUNT(*) AS recent_writes
FROM orders WHERE id > 900 GROUP BY 2
UNION ALL
SELECT 'mod', id % 4, COUNT(*)
FROM orders WHERE id > 900 GROUP BY 2
ORDER BY 1, 2;
-- strategy shard recent_writes
-- -------- ----- -------------
-- mod 0 25
-- mod 1 25
-- mod 2 25
-- mod 3 25
-- range 3 100 <-- every one of the newest 100 writes hits shard 3# Python 3: cost of resharding, hash-mod vs consistent hashing (with and without virtual nodes)
import hashlib, bisect
from collections import Counter
def h(s):
return int(hashlib.md5(s.encode()).hexdigest(), 16)
keys = [f"user:{i}" for i in range(10000)]
# 1) hash(key) % N : add a 5th shard to a 4-shard cluster
def mod_assign(n):
return {k: h(k) % n for k in keys}
a, b = mod_assign(4), mod_assign(5)
moved = sum(1 for k in keys if a[k] != b[k])
print(f"hash mod N, 4 -> 5 shards: moved {moved}/{len(keys)} = {moved/len(keys):.1%}")
# 2) consistent hashing with virtual nodes
class Ring:
def __init__(self, nodes, vnodes=100):
self.vnodes = vnodes
self.ring = []
for n in nodes: self.add(n)
def add(self, node):
for i in range(self.vnodes):
bisect.insort(self.ring, (h(f"{node}#{i}"), node))
def get(self, key):
i = bisect.bisect(self.ring, (h(key), "")) % len(self.ring)
return self.ring[i][1]
def ring_assign(nodes, vnodes):
r = Ring(nodes, vnodes)
return {k: r.get(k) for k in keys}
for vn in (1, 100):
a = ring_assign(["A","B","C","D"], vn)
b = ring_assign(["A","B","C","D","E"], vn)
moved = sum(1 for k in keys if a[k] != b[k])
dist = Counter(b.values())
print(f"ring vnodes={vn:3}, 4 -> 5 nodes: moved {moved}/{len(keys)} = {moved/len(keys):.1%}; "
f"load after: " + ", ".join(f"{n}={dist[n]}" for n in sorted(dist)))
# Output (real, python3):
# hash mod N, 4 -> 5 shards: moved 7960/10000 = 79.6%
# ring vnodes= 1, 4 -> 5 nodes: moved 1695/10000 = 17.0%; load after: A=309, B=124, C=4077, D=3795, E=1695
# ring vnodes=100, 4 -> 5 nodes: moved 1889/10000 = 18.9%; load after: A=2089, B=2239, C=1969, D=1814, E=1889# Python 3: a hot key defeats even hashing; salting spreads it (at the cost of fan-out reads)
import hashlib
from collections import Counter
def h(s): return int(hashlib.md5(s.encode()).hexdigest(), 16)
N = 4
# 1000 writes: 700 from one celebrity, 300 spread across 300 ordinary users
events = ["user:celeb"] * 700 + [f"user:{i}" for i in range(300)]
c = Counter(h(k) % N for k in events)
print("plain shard key :", dict(sorted(c.items())))
# salting: append a suffix 0..7 to the hot key
salted = [f"{k}#{i % 8}" if k == "user:celeb" else k for i, k in enumerate(events)]
c = Counter(h(k) % N for k in salted)
print("salted hot key (x8) :", dict(sorted(c.items())))
# Output (real, python3):
# plain shard key : {0: 69, 1: 79, 2: 782, 3: 70}
# salted hot key (x8) : {0: 243, 1: 254, 2: 346, 3: 157}-- PostgreSQL (single-node declarative partitioning; not run here, no output shown)
CREATE TABLE events (
id bigint GENERATED ALWAYS AS IDENTITY,
created_at timestamptz NOT NULL,
payload jsonb
) PARTITION BY RANGE (created_at);
CREATE TABLE events_2026_01 PARTITION OF events
FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
CREATE TABLE events_2026_02 PARTITION OF events
FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');
-- EXPLAIN on a query with a created_at range should show only the matching
-- partition(s) being scanned (partition pruning). Retire old data cheaply:
DROP TABLE events_2026_01; -- or DETACH PARTITION first to archive itQ: What is a shard key?
A: It is the column (or columns) whose value determines which shard stores a row and which shard a query must visit. A good one has high cardinality, spreads access evenly and appears in the most frequent queries, so most operations hit exactly one shard; a poor one (a low-cardinality status, or a monotonic timestamp with range sharding) creates skew or hotspots. Changing it later means re-moving most of the data, so it is one of the most consequential design decisions.
Q: What is a hot partition?
A: A partition or shard that receives far more traffic or data than the others, so it saturates first and limits the whole system even though the other nodes are idle. Causes include a time-based key (all new writes go to the latest range), a celebrity or heavy-tenant key, or a poor key with few distinct values. Fixes include hashing or salting the key, giving heavy tenants dedicated capacity, caching or aggregating hot traffic, and splitting ranges, each with a cost such as fan-out reads or extra operational work.
Q: What problems does sharding introduce?
A: Cross-shard joins and aggregations become scatter-gather queries or force denormalization; cross-shard transactions need two-phase commit or sagas, trading latency, blocking risk or consistency; unique IDs and secondary indexes can no longer rely on a single node; and rebalancing or resharding requires moving live data safely. On top of that you now operate many databases, so backups, schema migrations and monitoring get harder. Because of this, sharding is generally a last step after indexing, caching, replicas and larger hardware.
Follow-up: Why not just use hash(key) mod N, and what does consistent hashing improve?
A: Mod N is simple and even, but changing N changes almost every key's destination (about 80 percent moved going from 4 to 5 in the demo above), forcing a huge migration or, for a cache, a wave of misses. Consistent hashing moves only about 1/(N+1) of keys when a node joins, and virtual nodes fix its uneven arcs. Alternatives such as Redis Cluster's fixed slots or a directory table also decouple key placement from node count, at the cost of maintaining the slot or lookup map.
Follow-up: Range or hash sharding for a time-series or event table?
A: Pure range sharding on time makes the newest range a permanent write hotspot, while pure hash sharding balances writes but turns "last hour of data" queries into full scatter-gather. A common compromise is a composite key: hash on an entity (for example device_id) to spread writes, then range or cluster by time within the shard, or use time partitioning per shard for pruning and cheap retention. The right answer depends on whether writes or time-range reads dominate.
Follow-up: How would you do a transaction that spans two shards?
A: First try to avoid it by choosing a shard key that keeps the transaction's rows together (for example everything for one customer). If unavoidable, two-phase commit gives atomicity but adds round trips, holds locks across the network and can block if the coordinator fails; a saga executes local transactions per shard and undoes them with compensating actions on failure, which is available and scalable but only eventually consistent and needs idempotent steps. Some distributed SQL databases provide cross-shard transactions internally, with the same coordination cost hidden.
In a design interview, bring up sharding only after showing that a single primary with replicas, good indexes and a cache cannot meet the write rate or dataset size, then justify the shard key from the access pattern: user_id for a messaging or feed service, tenant_id for multi-tenant SaaS, order_id hashed for a write-heavy order store. Say which queries become single-shard and which become scatter-gather, and how you will handle skew (a celebrity user, a giant tenant, a flash-sale item) with salting, dedicated shards or a cache in front. Mention rebalancing up front, consistent hashing with virtual nodes or a fixed slot count, so that adding a node moves a small fraction of data, plus per-shard replication for availability and a global ID scheme such as Snowflake-style IDs. The same reasoning applies to Kafka partition keys, Redis Cluster hash tags, and vector databases that split embeddings across nodes by tenant or by hash.
Sharding buys near-linear write and storage scaling by giving up the things a single relational node provides for free: cheap joins, multi-row transactions, global constraints and simple operations. Hash placement balances load but destroys range locality; range placement preserves locality but invites hotspots; a directory gives flexibility but adds a lookup service that must itself be available and fast. Consistent hashing and fixed slots make growth cheaper, but rebalancing still costs I/O and needs careful live migration, and no scheme removes hot keys caused by skewed access. It is the wrong choice when the dataset fits comfortably on one machine with replicas, when the real bottleneck is a missing index or a slow query, or when the workload needs frequent cross-entity transactions that no shard key can co-locate. In those cases scale vertically, add replicas and caching, or partition within a single node before distributing.