Database sharding vs partitioning: when one database is not enough

The orders table crossed 2 TB last quarter. The nightly vacuum no longer finishes by morning, adding an index is a weekend job, and the dashboard query that used to take 300 ms takes 4 seconds. In the planning meeting someone says "we need to shard," someone else says "we should partition," and for the next hour the two words get used as if they were synonyms.

They are not. Partitioning splits a table into pieces inside one database. Sharding splits your data across databases. One keeps every guarantee you currently rely on and makes some operations cheaper. The other buys you more machines and pays for them with joins, transactions, and a sequence you can no longer call from one place. Confusing the two is how a team signs up for the second when it only needed the first.

Two ways to split 2 TB

Inside one database, or across four

2,000 writes/s total

Partitioning

one database, one table name

RANGE (created_at)

WHERE created_at >= '2026-09-01'

• orders_2026_06 410 GB pruned
• orders_2026_07 455 GB pruned
• orders_2026_08 520 GB pruned
• orders_2026_09 615 GB scanned

one machine, one transaction, one planner

Sharding

four databases, four connection strings

hash(tenant_id) mod 4

router: tenant_id → connection string

• shard-0 300 /s
• shard-1 hot 1,100 /s
• shard-2 300 /s
• shard-3 300 /s

tenant 7 alone sends 800 writes/s to shard-1

Partitioning: one database, many tables behind one name

A partitioned table is a parent with no rows of its own and a set of child tables that each own a slice, chosen by range, by list, or by hash of a partition key. The planner knows the slices, so a query that constrains the key visits only the partitions that could hold matching rows. PostgreSQL calls this partition pruning; the table partitioning documentation covers the three strategies and which queries prune.

Monthly ranges on a timestamp are the common shape for append-heavy tables:

CREATE TABLE orders (
    id          uuid        NOT NULL,
    tenant_id   int         NOT NULL,
    created_at  timestamptz NOT NULL,
    total_cents bigint      NOT NULL,
    PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

CREATE TABLE orders_2026_08 PARTITION OF orders
    FOR VALUES FROM ('2026-08-01') TO ('2026-09-01');

CREATE TABLE orders_2026_09 PARTITION OF orders
    FOR VALUES FROM ('2026-09-01') TO ('2026-10-01');

CREATE INDEX ON orders (tenant_id, created_at);

SELECT count(*)
FROM orders
WHERE tenant_id = 7
  AND created_at >= '2026-09-01'
  AND created_at <  '2026-09-08';

The primary key has to include created_at, because a unique constraint on a partitioned table must contain the partition key; that is the first thing partitioning asks you to give up. The query touches one child, and the plan says so (example run, trimmed):

Aggregate
  ->  Index Only Scan using orders_2026_09_tenant_id_created_at_idx
        on orders_2026_09 orders
        Index Cond: ((tenant_id = 7) AND (created_at >= ...) AND (created_at < ...))

Four months of history in four partitions, and this query reads one of them. The other three are not scanned, their indexes are not walked, their pages stay cold. Dropping a month of data becomes DROP TABLE orders_2026_03, which is instant, instead of a DELETE that rewrites 400 GB and leaves the space for vacuum to reclaim. Each partition's index is a quarter of the size, so it stays in memory longer, and the reasons an index gets ignored at 2 TB apply less at 500 GB.

What did not change: it is still one machine, one buffer pool, one write-ahead log, one planner, one connection limit, one set of transactions. A query that does not constrain created_at visits every partition and is often slower than before, because the planner now coordinates four index scans instead of one. Partitioning makes a big table cheaper to manage. It does not give you more CPU.

Sharding: many databases, and everything that disappears

Sharding puts the slices on different servers, each running its own database, and your application decides where a row belongs. The deciding value is the shard key: a tenant id, a user id, or a hash of the row's id. Choose the key that appears in nearly every query, because a query without it has to ask every shard and merge the results itself.

What you lose is not subtle:

• Cross-shard joins. An order on shard 1 cannot join to a customer on shard 3. Either co-locate everything a tenant owns on one shard, or copy reference data to every shard and accept that it drifts.
• Single transactions. Moving credit from a tenant on one shard to a tenant on another is two commits with a gap between them, the same gap the transactional outbox exists to close.
• One global sequence. nextval() is per database. Use time-ordered UUIDs or give each shard its own id range, and give up "order 10,000,001 came right after 10,000,000."
• One schema migration. Every migration now runs N times, and old and new schema coexist across shards for the whole rollout.
• Pagination across the whole dataset. Keyset pagination works inside a shard; a global "next page" merges across all of them.

Backups, monitoring, connection pools, and failover also multiply by N. A four-shard cluster is not one database that happens to be bigger. It is four databases you must keep healthy at once.

Partitioning splits a table. Sharding splits your guarantees. The first is a maintenance decision; the second is an architecture you will live inside for years.

Hot shards: the split is only as even as your keys

The brochure arithmetic is clean: hash 2 TB four ways and each shard holds 500 GB; spread 2,000 writes per second four ways and each shard takes 500. Real keys are not uniform. Suppose one tenant produces 40% of all writes, ordinary in a B2B product with one large customer. That tenant lands entirely on one shard, because the point of a tenant key is that a tenant's rows stay together.

The shard holding tenant 7 takes that tenant's 800 writes per second plus a quarter of everyone else's 1,200, another 300, for 1,100 writes per second. The other three take 300 each. You bought four servers so the busiest one could run at 55% of the original load rather than 25%, and it is the one that will page you. Storage skews the same way, and the shard that is both largest and busiest fails over slowest.

Hashing the row id instead spreads tenant 7 across all four shards evenly. It also puts every one of that tenant's queries on all four shards at once, the cross-shard tax you chose the tenant key to avoid. No key gives you both. There is only knowing your distribution before you commit, and a directory that lets you move the outliers by hand.

Routing and the resharding trap

The router is a function from a key to a connection string, and it must be deterministic: the same key resolves to the same shard on every machine, on every deploy, forever, or two application instances write the same tenant to two databases. A directory, a table mapping tenants to shards, gives you control; a hash gives a default for tenants nobody has pinned. Microsoft's sharding pattern guidance calls the same strategies lookup, range, and hash.

using System.Text;

public sealed class ShardRouter(
    IReadOnlyDictionary<string, string> directory,
    IReadOnlyList<string> shardConnectionStrings)
{
    public string Resolve(string tenantId)
    {
        // Pinned tenants (migrated, isolated, or hot) override the hash.
        if (directory.TryGetValue(tenantId, out var pinned))
        {
            return pinned;
        }

        var bucket = StableHash(tenantId) % (uint)shardConnectionStrings.Count;
        return shardConnectionStrings[(int)bucket];
    }

    // FNV-1a over UTF-8 bytes. string.GetHashCode() is randomized per process
    // in .NET and would send the same tenant to a different shard after a restart.
    private static uint StableHash(string value)
    {
        const uint offsetBasis = 2166136261;
        const uint prime = 16777619;

        var hash = offsetBasis;
        foreach (var b in Encoding.UTF8.GetBytes(value))
        {
            hash ^= b;
            hash *= prime;
        }

        return hash;
    }
}

The modulo in that fallback is the resharding trap. Add a fifth shard and the rule becomes mod 5; a key stays put only when its hash gives the same remainder under both, roughly one key in five. The other 80% of your rows must move while writes keep arriving, and every row in flight might be read from the wrong place. Consistent hashing, or a fixed set of virtual buckets, say 1,024 assigned across shards, means a fifth shard takes about a fifth of the buckets, roughly 205, and nothing else moves. A directory means moving one tenant at a time: copy, dual-write briefly, flip the pointer, delete. Boring and safe, which is the correct mood for moving a customer's data.

The list to exhaust before you shard

Sharding is the most expensive fix on the menu and it is rarely the first one that applies. In roughly increasing order of cost:

1. Indexes and query shape. Most "the database is too slow" is one missing index or one query that walks a table; reading the query plan is cheaper than any server.
2. Read replicas. If reads are the load, replicas take them off the primary; replication and failover have trade-offs of their own but need no shard key.
3. Caching. The hot 1% of rows can leave the database entirely.
4. Partitioning. Cheaper maintenance, smaller indexes, instant archival, no lost guarantees.
5. Archiving cold data. If 80% of the 2 TB is orders nobody has read in three years, move them to cheap storage and the table is 400 GB.

Shard when the write rate or working set genuinely exceeds one machine after all of that, and shard by the key your queries already carry. Do it before you must; resharding under load is the version of this project that goes badly.

Where the trade-off gets practiced

The "Shard the Database" fundamentals challenge in Katabench's System Design Studio puts this decision on a canvas. You build the design from provider-neutral components, route the application to more than one Database, and deterministic rules check required paths, redundancy, and whether anything bypasses the routing. The capacity simulation reports throughput, p99 latency, and the component that saturates first under a versioned traffic scenario, where a hot shard shows up as one database at its ceiling while the others idle. It is a reproducible model built for learning, not a provisioned load test, and there is no single correct diagram.

The cheaper steps on the list above are what the Database track grades against real PostgreSQL query plans; the track overview describes how each one is checked. Most teams that think they need shards need step one.