Partitioning vs. Sharding¶

Two ways to split data¶

Both techniques split one large dataset into smaller pieces, for different reasons:

The measurements below were made on PostgreSQL 16.2 with a 6-million-row events table, once as a plain table and once partitioned by month.

Partitioning within one database¶

Partitioning schemes¶

CREATE TABLE events (id bigint, created_at timestamptz NOT NULL, payload text)
  PARTITION BY RANGE (created_at);

CREATE TABLE events_2024_03 PARTITION OF events
  FOR VALUES FROM ('2024-03-01') TO ('2024-04-01');
-- ... one per month; a DEFAULT partition can catch everything else

The parent table holds no rows; each partition is a real table with its own heap, indexes, statistics, and VACUUM. An index created on the parent is created on every partition.

Partition pruning¶

The planner excludes partitions that cannot contain matching rows. Measured, querying 10 days in March:

SELECT count(*) FROM events_p
WHERE created_at >= '2024-03-10' AND created_at < '2024-03-20';

Finalize Aggregate
  -> Gather
       -> Partial Aggregate
            -> Parallel Index Only Scan using events_p_2024_03_created_at_idx
               on events_p_2024_03                      ← only March is touched
Execution Time: 9.141 ms

Eleven of twelve partitions never appear in the plan. Pruning happens at planning time for constants and at execution time for parameters and join values. It requires the query to constrain the partition key: a query on id alone produced a plan with 12 scan nodes, one per partition — more work than on an unpartitioned table with an index on id, since each partition's index is searched separately.

The biggest practical win: dropping old data¶

Measured, removing January (≈ 510,000 rows):

~100x faster here, and the gap grows with table size: deleting a month from a multi-terabyte table can run for hours and bloat everything, while dropping a partition stays a metadata operation. For data with a retention period (logs, events, metrics), this alone justifies partitioning by time. DETACH PARTITION ... CONCURRENTLY (PostgreSQL 14+) avoids blocking queries on the parent.

Other benefits and costs¶

Rule of thumb: partition when a table is large (hundreds of GB or billions of rows) and most queries or maintenance operations align with one key — typically time. Keep partition counts in the tens or low hundreds.

Sharding across servers¶

Why shard¶

A single primary has hard ceilings: write throughput (one WAL, one machine's fsync and CPU), storage, and memory for the working set. Replication scales reads but every write still goes to one node. Sharding gives each server responsibility for a subset of the rows, so writes, storage, and cache scale with the number of servers.

Choosing the shard key¶

The shard key decides which server owns a row. It determines almost everything:

For a multi-tenant SaaS, tenant_id is the classic key: almost every query is scoped to a tenant, and each tenant's rows (users, orders, invoices) live together. The risk is one huge tenant outgrowing a shard.

Mapping keys to shards¶

Worked example — why hash % N hurts resharding. With 4 shards, key k lives on hash(k) % 4. Going to 5 shards, a key stays put only if hash(k) % 4 == hash(k) % 5, which holds for about 1 in 5 keys — ~80% of all data moves. With consistent hashing, adding the fifth shard moves only ~20% (the fraction the new shard takes over).

What sharding costs¶

Who does the routing¶

Order of operations¶

Before sharding, exhaust the cheaper options: indexes and query fixes, vertical scaling (a single modern server handles tens of thousands of transactions per second and many terabytes), read replicas for read load, caching, partitioning for large tables, and moving analytics to a separate system. Sharding is a one-way door: it complicates every query, transaction, and operation for the rest of the system's life. See Vertical vs. Horizontal Scaling.

Common pitfall¶

Choosing a shard key without checking the access patterns first. Sharding by user_id when the dominant query is "all orders in a date range across all users" turns that query into a scatter-gather over every shard — the same effect measured above at partition level, where a query without the partition key ran 12 scans instead of one, but now over the network and with the slowest shard deciding the latency. The key is effectively permanent once traffic depends on it: changing it means migrating live data between servers. Write down the top queries and transactions, check that each includes the candidate key, and only then commit to it.