Database Sharding: The Choices We Wish We'd Made Earlier

Pick the shard key your hottest queries already filter on, which for multi-tenant SaaS is almost always tenant_id, and make four other decisions before you migrate: globally unique IDs, a plan for whale tenants, routing outside the application, and cross-shard query tooling. Retrofitting any of them after sharding is where our pain came from.

We sharded our primary Postgres database after two years of telling ourselves we never would. Sharding worked: the system is faster and scales further, and it is operationally more complex. Every sharding choice is a constraint on every future query, so this is the list we wish we'd had.

Shard last, not first

The forcing functions for us were storage growth on one large table, write throughput with shrinking headroom on a single primary, and maintenance (VACUUM, migrations, restore tests) that grew with data size until a migration no longer fit a window.

Before sharding, we exhausted the cheaper levers: declarative partitioning, archiving cold data, read replicas, materialized views for expensive aggregates. Those bought us about a year. If your pain is reads, routing reads to replicas solves it without sharding. If your pain is one append-heavy table, declarative partitioning for time-series data keeps you on one node much longer. Sharding adds write and storage scale. It does not make reads cheaper.

The shard key is the decision you can't undo

The key decides which rows live together, which queries are single-shard (fast) or fan-out (slow), and how rebalancing works. Our candidates were user_id, tenant_id, created_at, and a hash of a compound key.

We picked tenant_id. The dominant query is "everything for one tenant," so most queries became single-shard. Per-tenant operations (export, GDPR deletion, tenant maintenance) align with shard boundaries. This matches the Citus guidance, which says to distribute multi-tenant tables by a common tenant column so rows with the same value land on the same node and joins stay local:

-- citus: co-locate tenant tables, replicate small shared tables
SELECT create_distributed_table('orders', 'tenant_id');
SELECT create_distributed_table('order_items', 'tenant_id', colocate_with => 'orders');
SELECT create_reference_table('currencies');

Two risks we accepted came true: whale tenants skewed shard sizes, and cross-tenant analytics needed fan-out. A time-based key would have been worse: every write lands on the newest shard, which becomes the permanent hotspot.

The four things we'd do on day one

Plan for whales. Our biggest tenant ended up alone on a shard that ran hot while others idled. We retrofitted a tenant_id plus sub-bucket key for the largest tenants, splitting their rows by a deterministic hash. Retrofitting was painful. Reserving the bucket column from day one is nearly free.

Use globally unique IDs. Per-shard auto-increment IDs collide the moment rows move between shards or get merged in analytics. Use UUIDv7 or a Snowflake-style scheme for anything that can cross a shard boundary. PostgreSQL 18 ships a built-in uuidv7() function, so there's no extension to install.

Keep routing out of the application. Our first shard map lived in app code, so every routing change needed a deploy. We moved it to a proxy layer. The map itself is simple; this is roughly what ours does:

# shard_router.py
import hashlib

SHARD_COUNT = 16
OVERRIDES = {"tenant-acme": [12, 13, 14, 15]}  # whale tenants span buckets

def shard_for(tenant_id: str, row_key: str = "") -> int:
    buckets = OVERRIDES.get(tenant_id)
    if buckets:
        h = int(hashlib.sha256(row_key.encode()).hexdigest(), 16)
        return buckets[h % len(buckets)]
    h = int(hashlib.sha256(tenant_id.encode()).hexdigest(), 16)
    return h % SHARD_COUNT

Note the trap in that sketch: h % SHARD_COUNT means changing the shard count remaps almost every tenant. That's fine only if moves are explicit (see below) and the map is stored, not recomputed.

Build cross-shard tooling before cutover. We built cross-shard query routing three months after sharding. We needed it in week two.

Cross-shard queries are the tax

Single-shard queries are easy. The fan-out remainder is where complexity lives. A LIMIT 10 across shards fetches the top 10 from each, merges, and takes the global top 10. A count(*) sums per-shard counts. A join between a sharded table and a small shared table works if the small table is replicated everywhere, which is what Citus reference tables do.

Transactions are harder. Atomic writes across shards need two-phase commit, sagas with idempotent steps, or an honest decision to accept eventual consistency. We keep related data on one shard so this is rare, and use sagas for the rest. Track the cross-shard query rate; we alert when it creeps past roughly 10-15% of queries, because growth there means the shard key no longer fits the workload.

Moving tenants between shards

We use explicit, range-style moves rather than consistent hashing, because with 16 shards an auditable map beats an elegant one. Consistent hashing pays off at hundreds of shards. Each move uses logical replication with a row filter (PostgreSQL 15+), which our logical replication upgrade guide covers in depth:

-- on the source shard
ALTER TABLE orders REPLICA IDENTITY USING INDEX orders_tenant_id_id_key;
CREATE PUBLICATION move_t42 FOR TABLE orders WHERE (tenant_id = 42),
  order_items WHERE (tenant_id = 42);

-- on the target shard
CREATE SUBSCRIPTION move_t42
  CONNECTION 'host=shard-03 dbname=app user=replicator'
  PUBLICATION move_t42;

Then wait for catch-up, pause writes for that tenant for a few seconds, flip the routing entry, resume, and delete the old rows. The replica identity line is not optional: the PostgreSQL docs say that if a publication publishes UPDATE or DELETE, the row filter may only use columns covered by the replica identity.

Errors and failure modes we hit

• ERROR: cannot update table "orders" with DETAIL: Column used in the publication WHERE expression is not part of the replica identity. The filter column isn't in the replica identity. Use a unique index that includes tenant_id.
• Duplicate key violations after a move: the target shard's sequence was behind the copied IDs. Globally unique IDs make this disappear.
• A new shard missing columns: we added a shard and forgot to apply the latest migration, so writes failed on it. CI now checks that every shard's schema matches before any rebalance.
• A routing bug ten minutes into our first production move. We rolled back, then built a shadow-shard environment where every routing change runs first.

Where this breaks

None of this helps a read-heavy workload, data under a few hundred GB that fits comfortably on one well-tuned node, or a single hot table you could partition or archive instead. Tenant sharding fails when no natural tenant exists (a social graph, a global marketplace), when most queries span tenants, or when one tenant is larger than any node you can buy. Application changes are bigger than they look: our ORM assumed one connection, and rewriting query patterns took about twice our estimate.

Questions people ask

How do I choose a shard key?

Pick the column your most frequent and latency-sensitive queries already filter by, so they hit one shard. For multi-tenant SaaS that's usually tenant_id. Check three things: values spread load reasonably evenly, related tables share the key so joins stay local, and no single value outgrows a shard. Avoid time-based keys for write-heavy tables, because the newest shard takes every insert.

When should you shard a Postgres database?

After partitioning, archiving, read replicas, query tuning and vertical scaling have run out. Real triggers are write throughput a single primary can't sustain, data growth past what one instance handles comfortably, and maintenance operations that no longer fit their windows. If the bottleneck is reads, add replicas; sharding adds write and storage capacity, not read capacity.

What is the difference between partitioning and sharding?

Partitioning splits one table into pieces inside a single database server, and PostgreSQL's planner prunes partitions it doesn't need. Sharding spreads data across separate servers, each holding a subset. Partitioning is transparent to the application and keeps transactions and joins normal. Sharding adds network hops, cross-shard queries and distributed transactions, which is why it should come second.

What to read next

The shard key is permanent in practice, so pick it carefully; everything else is tooling you can build over time.

• Postgres logical replication for zero-downtime upgrades, the mechanism we use for shard moves
• Postgres connection pooling with PgBouncer in front of RDS, the connection layer that sharding stresses
• pg_stat_statements for Postgres query analysis, which tells you which queries are cross-shard
• Postgres autovacuum tuning from production stalls, discipline that matters more per shard

Sources

• PostgreSQL documentation: logical replication row filters
• PostgreSQL documentation: table partitioning
• Citus documentation: choosing a distribution column