Sharding vs Partitioning: Split the Table or Split the Database

Sharding vs partitioning in one line: partitioning splits one large table into smaller pieces inside a single database server, where one query planner still sees every piece, routes queries and enforces what it can; sharding splits the data across separate database servers, each holding a slice and knowing nothing about the others, so routing, cross-shard queries and moving data between servers become your application's job. Sharding is partitioning taken across machines. The PostgreSQL and MySQL behaviour below is quoted from their manuals, as of October 2026; the routing and resharding are shown with a small Python model.

	Partitioning	Sharding
Where the pieces live	one server, one database	many servers, many databases
Who routes queries	the planner, by partition pruning	the application or a proxy, by shard key
Joins and transactions	work as usual	cross-shard ones are your problem
What it scales	manageability and scans of one table	write throughput and total storage

The problem it solves

A table of orders grows to billions of rows. Queries for last month still read index pages full of last decade's data, deleting old rows takes hours, and maintenance never finishes. That is a single-table problem, and partitioning answers it. A different problem is that the one server can no longer absorb the writes or hold the data at all. No amount of reorganising inside it helps; the data has to live on more machines, and that is sharding.

The intuition

Partitioning reorganises the filing cabinet; sharding buys more buildings.

• Partitioning keeps one brain. PostgreSQL's declarative partitioning lets you declare a table PARTITION BY RANGE, LIST or HASH, then attach partitions to it. Range partitions hold non-overlapping ranges, lower bound inclusive and upper exclusive; list partitions hold named values; hash partitions hold the rows whose key hash leaves a given remainder. Queries still go to the parent table, and the planner skips partitions that cannot match the WHERE clause. The manual calls this partition pruning, and it is driven by the partition bounds, not by indexes.
• Old data leaves cheaply. Dropping a partition, or detaching it, is far faster than a bulk DELETE and avoids the VACUUM work a big delete causes. The manual's rule of thumb is that partitioning pays off once the table is larger than the server's memory.
• Sharding has no shared brain. Each shard is a full database. Something above them, application code or a proxy, maps the shard key to a server. A query without the key is sent to every shard and the results merged. Foreign keys, unique constraints and transactions only hold within one shard.

Some limits already appear inside one server, and they hint at what sharding loses entirely. PostgreSQL only allows a unique or primary key on a partitioned table if it includes every partition key column, because each partition's index can only check its own rows. MySQL states the same rule, and its partitioned InnoDB tables do not support foreign keys at all.

One more meaning to keep apart: horizontal partitioning splits rows, which is everything above; vertical partitioning splits columns, moving rarely read or large columns into a second table that shares the primary key. Sharding is always horizontal.

When to use which

• Partition when one table is huge but the server copes: time-based data you drop by month, queries that filter on the partition key, maintenance that has to work on smaller pieces.
• Shard when one server cannot take the write rate or hold the data even after scaling up, adding replicas for reads and caching.
• Both is common: each shard is itself a partitioned database.
• Neither while indexes, replicas and a bigger machine still have room. Sharding is the most expensive option to operate.

Watch it run

The animation is the lesson's sharding story. One database splits into three shards owned by hash buckets of user_id, behind a router. A query carrying a user_id hashes to one bucket and touches one shard of three. A search by surname carries no key, so every shard is asked and the router merges and re-sorts the results. Then shard B turns hot and does 62% of the work, cross-shard joins and constraints break, and resharding is shown as a migration. Partitioning has the same routing idea, with the router replaced by the planner on the same machine, and the cross-server costs are the part partitioning never pays.

The code

What partitioning looks like in PostgreSQL. Queries go to orders; the planner prunes:

CREATE TABLE orders (id bigint, placed date, total numeric)
  PARTITION BY RANGE (placed);
CREATE TABLE orders_2026q1 PARTITION OF orders
  FOR VALUES FROM ('2026-01-01') TO ('2026-04-01');
CREATE TABLE orders_2026q2 PARTITION OF orders
  FOR VALUES FROM ('2026-04-01') TO ('2026-07-01');

EXPLAIN SELECT * FROM orders WHERE placed = '2026-05-14';  -- scans orders_2026q2 only
DROP TABLE orders_2026q1;                                   -- a quarter gone, no DELETE

A toy model of pruning: 12,000 orders laid out by range on the month, or by hash on the id. Each layout prunes only on its own key:

import random
import zlib

def h(key):
    return zlib.crc32(str(key).encode())          # stable across runs, unlike hash()

random.seed(3)
orders = [{"id": i, "month": random.randint(1, 12)} for i in range(12000)]

ranges = [(1, 4), (4, 7), (7, 10), (10, 13)]      # FROM .. TO, upper bound exclusive
by_range = [[o for o in orders if lo <= o["month"] < hi] for lo, hi in ranges]
by_hash = [[o for o in orders if h(o["id"]) % 4 == r] for r in range(4)]
print([len(p) for p in by_range], [len(p) for p in by_hash])
# [2928, 3161, 3044, 2867] [2999, 3001, 2999, 3001]

def prune_range(month):                           # which partitions can hold this month?
    return [i for i, (lo, hi) in enumerate(ranges) if lo <= month < hi]

def prune_hash(order_id):                         # which partition can hold this id?
    return [h(order_id) % 4]

kept = prune_range(5)                             # WHERE month = 5
print(kept, sum(len(by_range[i]) for i in kept), "rows read instead of", len(orders))
# [1] 3161 rows read instead of 12000
print(prune_hash(8123), "for WHERE id = 8123; month = 5 on the hash layout reads all 4")
# [2] for WHERE id = 8123; month = 5 on the hash layout reads all 4

hits = [o for i in kept for o in by_range[i] if o["month"] == 5]
print(len(hits) == sum(o["month"] == 5 for o in orders))   # True  <- pruning lost nothing

Hash partitions come out even; range partitions follow the data. A month filter reads one range partition of four, about a quarter of the rows, and finds every match. An id filter prunes the hash layout to one partition, but the month filter cannot prune it at all. Sharding has exactly this property, with each "partition" on another server.

What sharding adds is moving data when servers are added. With hash mod N, going from four to five servers changes the answer for most keys. Consistent hashing places servers at many points on a ring and gives each key to the next point clockwise, so only the keys the new server takes over move:

import bisect

def ring(nodes, vnodes=100):                      # each server owns many points on a circle
    points = sorted((h(f"{n}#{v}"), n) for n in nodes for v in range(vnodes))
    return [p for p, _ in points], [n for _, n in points]

def owner(key, r):
    points, names = r
    i = bisect.bisect(points, h(key)) % len(points)   # first point clockwise from the key
    return names[i]

keys = range(100000)
old_nodes, new_nodes = ["db0", "db1", "db2", "db3"], ["db0", "db1", "db2", "db3", "db4"]
moved_mod = sum(h(k) % 4 != h(k) % 5 for k in keys)
r_old, r_new = ring(old_nodes), ring(new_nodes)
moves = [(owner(k, r_old), owner(k, r_new)) for k in keys]
moved_ring = sum(a != b for a, b in moves)
print(f"hash mod N, 4 -> 5 servers: {100 * moved_mod / len(keys):.0f}% of keys move")
# hash mod N, 4 -> 5 servers: 80% of keys move
print(f"consistent hashing, 4 -> 5: {100 * moved_ring / len(keys):.0f}% of keys move")
# consistent hashing, 4 -> 5: 26% of keys move
print(all(b == "db4" for a, b in moves if a != b))  # True  <- every moved key went to db4

Modulo moved 80% of 100,000 keys between servers; the ring moved 26%, close to the ideal fifth, and every moved key went to the new server rather than shuffling between old ones. Inside one database a partition change is a DDL statement; across shards, each moved key is a copy over the network while traffic continues.

The complexity

• Partition pruning: a query on the partition key reads 1/P of the data with P partitions, decided at planning time, or at execution time for parameters.
• Sharded query with the key: one shard, one network round trip. Without the key: all S shards, then a merge in the router.
• Adding one server to N: hash mod N moves about N/(N+1) of the keys, consistent hashing about 1/(N+1).

Where it goes wrong

• Partitioning on a column queries do not filter on. Nothing is pruned, and every query touches every partition.
• Expecting a global unique key. Partitioned tables in PostgreSQL and MySQL require the partition key in every unique key; across shards there is no global uniqueness check at all.
• Sharding too early. Cross-shard joins, distributed transactions and resharding are permanent costs; partitioning, replicas and a bigger server are cheaper first.
• Choosing hash mod N for shards. The first resharding moves almost everything.
• Calling partitioning "sharding". Interviewers listen for whether you know the difference is one server or many.

When it shows up in interviews

Directly, as "what is the difference between sharding and partitioning?", and inside design rounds when the data outgrows one machine. Choosing a shard key and spotting hot shards are covered in database sharding explained, and when to scale up before scaling out is in vertical vs horizontal scaling. The short version, with the router animation, is the Database Sharding lesson.

How to say it in an interview

"Partitioning splits a big table into pieces inside one database. The planner still sees all of them, prunes partitions that cannot match, and I can drop old partitions instead of deleting rows. Sharding splits the data across separate database servers by a shard key, so writes and storage scale out, but routing, cross-shard queries, global constraints and resharding become my problem. I partition first when the pain is one huge table, and shard only when one server cannot keep up, using consistent hashing or many small virtual shards so adding a server moves only a fraction of the data."

Frequently asked questions

What is the difference between sharding and partitioning?

Partitioning divides a table into pieces within one database server, managed by its planner, so queries and joins work as usual. Sharding divides data across multiple independent servers by a shard key, so the application or a proxy routes queries and handles anything that spans shards.

Is sharding horizontal or vertical partitioning?

Horizontal. Sharding splits rows: each shard holds the same columns for a different subset of rows. Vertical partitioning splits columns into separate tables that share a key, usually to keep large or rarely read columns out of the main table.

Does PostgreSQL support sharding natively?

Its built-in feature is declarative partitioning inside one database. The manual notes that partitions can be foreign tables living elsewhere, but then keeping their contents within the partition bounds is your responsibility. Spreading data across servers is usually done by the application or by extensions.

Sources

• Table Partitioning — PostgreSQL Documentation
• Partitioning Keys, Primary Keys, and Unique Keys — MySQL 8.4 Reference Manual
• Restrictions and Limitations on Partitioning — MySQL 8.4 Reference Manual
• Partitioning Types — MySQL 8.4 Reference Manual