Partitioning and sharding

The orders table is 4 TB. Every night a job deletes orders older than three years, and it now takes six hours, fights with daytime traffic, and leaves the table bloated. Queries for "this month's orders" read indexes that cover a decade. Nobody wants to shard. Nobody has to. Splitting this one table into monthly pieces, on the same server, fixes all three problems.

Partitioning and sharding both split data into pieces. Partitioning splits a table into pieces inside one database server. Sharding splits the data across several database servers. The first is a quiet afternoon's work. The second changes how your whole application works. This chapter is about knowing which you need.

Partitioning: one table, many pieces, one server#

Postgres and MySQL both let you declare a table as partitioned. To your queries it still looks like one table. Underneath it is several smaller tables, each holding one range of rows.

CREATE TABLE orders (
  id          bigint,
  customer_id bigint,
  created_at  timestamptz NOT NULL,
  total       numeric
) PARTITION BY RANGE (created_at);
 
CREATE TABLE orders_2026_09 PARTITION OF orders
  FOR VALUES FROM ('2026-09-01') TO ('2026-10-01');
CREATE TABLE orders_2026_10 PARTITION OF orders
  FOR VALUES FROM ('2026-10-01') TO ('2026-11-01');

Three ways to split. Range, by date or id, the most common, for data that ages. List, by explicit values such as region or country. Hash, to spread rows evenly when there is no natural range.

Here is what a query does with it.

The nightly delete becomes DROP TABLE orders_2023_09, which takes milliseconds instead of six hours and leaves no bloat behind. Old partitions can be moved to cheaper storage or archived. And because each partition has its own smaller indexes, maintenance like vacuum and reindexing happens in manageable pieces.

The catch: queries that do not filter on the partition key must check every partition, and in Postgres a unique constraint has to include the partition key. Partition by the column almost every query filters on.

Sharding: many servers#

Partitioning does not add capacity. Every partition still lives on the same machine, using the same CPU, memory and disk. When writes or data size outgrow the largest machine you can run, you shard: split the rows across several independent database servers, each a primary with its own replicas.

Choosing the shard key and the way keys map to shards is its own chapter in the system design path: Sharding and Consistent hashing. The short version: pick a key with many values that nearly every query includes, such as customer_id or tenant_id, and never one that only increases, like a timestamp.

Who does the routing#

What gets hard#

Sharding solves capacity and hands you a new set of problems, permanently.

Queries without the shard key must ask every shard and merge the results, a scatter gather, which gets slower as shards are added.

Joins across shards are not possible in plain SQL. Keep data that is joined together on the same shard by giving it the same shard key: shard orders and order_items both by customer_id, and a customer's whole history lives together.

Transactions across shards need two phase commit or a saga, both slower and more complex than a local transaction. Design so that the common writes touch one shard.

Unique ids and constraints can no longer come from one auto increment sequence. You need a global id scheme, the subject of Design a unique ID generator.

Resharding, moving data when a shard fills up, has to happen while the app keeps running.

Checkpoint#