Database Sharding: When One Postgres Stops Working

Most teams reach for database sharding months before their single Postgres instance is actually the bottleneck, then spend the next year discovering that cross-shard joins and distributed transactions are harder than the write load they were trying to escape. Use one Postgres instance with read replicas and connection pooling while your working set fits in RAM; shard only when you have measured a single writable primary that cannot be scaled vertically and indexing or partitioning cannot absorb the write rate. Avoid sharding early because it moves your hardest problem from hardware to application code.

What actually forces you to shard a Postgres database

Sharding becomes necessary when a single writable primary cannot keep up with write throughput, when tables grow past the point where vacuum, index maintenance, and migrations finish inside your maintenance window, or when a single node's storage and memory limits are reached and vertical scaling has stopped. Those are the only three forcing functions worth acting on.

Read volume is not one of them. Add replicas first. Write volume is the real wall because every replica replays the same WAL, so replicas multiply read capacity but not write capacity. If your workload is 90 percent reads, sharding is almost always the wrong answer.

Postgres 12 and later handles partitioned tables well enough that partition pruning happens at plan time; verify with EXPLAIN that your queries include the partition key. If partitioning solves the table-size problem, you do not need sharding yet.

Database best practices for choosing a shard key

A shard key must appear in the vast majority of your queries, distribute writes evenly, and never change after insert. Tenant ID, account ID, or user ID are the safe choices because they are already in your authorization context. Never shard on a timestamp or a surrogate UUID: timestamps create a hot shard for current writes, and random UUIDs destroy locality for range scans.

Record the key on every table that participates in a shard. A child row without the key forces a scatter-gather query, and scatter-gather queries are where sharded systems quietly become slower than the monolith you replaced.

-- Every table carries the shard key. This is non-negotiable.
CREATE TABLE orders (
    id         bigint GENERATED ALWAYS AS IDENTITY,
    tenant_id  bigint NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    PRIMARY KEY (tenant_id, id)
);

CREATE INDEX orders_tenant_created_idx ON orders (tenant_id, created_at DESC);

Note the composite primary key: tenant_id first, then id. This keeps the key colocated with the shard and preserves uniqueness inside a tenant. Postgres will require this order for a partitioned table in versions before 11; from 11 onward it still gives the planner the partition key it needs.

Sql joins and acid transactions across shards

Sharding preserves SQL joins and ACID transactions only when every row in the query lives on the same shard. Cross-shard joins must be performed in application code or against a reference table, and cross-shard transactions lose atomicity. Plan for this before you shard, not after.

Two patterns cover most of it:

• Colocate related data. Put orders, order_items, and payments on the same shard by tenant_id. Then familiar joins and BEGIN ... COMMIT keep working.
• Replicate small reference tables everywhere. Countries, currencies, plans, and feature flags can live on every shard as read-only copies.

What breaks is the join you did not plan for: a report that joins users on shard A with events on shard B. That query cannot be a single SQL statement. Either push it to an analytics store, or make it a scatter-gather loop in the app and accept the latency.

If you cannot name the single key that keeps your three most critical queries on one shard, you are not ready to shard.

A reproducible cross-shard transaction failure

This is the failure mode that surprises teams. It is not theoretical, and you can reproduce it in a few minutes with two Postgres databases.

1. Run two Postgres containers on ports 5432 and 5433; create a wallet table on each with a balance column, and insert 100 into row 1 on both.
2. In psql against the first database, run BEGIN; UPDATE wallet SET balance = balance - 100 WHERE id = 1; and leave the transaction open.
3. In psql against the second database, run BEGIN; UPDATE wallet SET balance = balance + 100 WHERE id = 1; and leave it open.
4. Commit the first, then kill the second client with \q without committing.

The observable symptom: 100 units vanish. There is no error, no alert, and no application log line, because each Postgres instance enforced ACID correctly within itself. Postgres cannot coordinate a two-phase commit across two databases you just split by hand. You either accept eventual consistency with a compensating action, keep the invariant on one shard, or introduce a distributed transaction coordinator, which brings its own failure modes.

Distributed database migrations

Database migrations across shards must run per shard, be idempotent, and tolerate a partial rollout where some shards are on the new schema and some are still on the old one. Deploy schema changes in two phases: add columns and backfill first, then switch reads and writes and drop old columns in a later release.

# Laravel 11+: run migrations on every shard
# config/database.php must define one connection per shard
php artisan migrate --database=shard_1 --force
php artisan migrate --database=shard_2 --force

# Verify all shards are at the same migration batch
for db in shard_1 shard_2; do
  php artisan migrate:status --database=$db | tail -n 1
done

Never run migrations as part of the application boot. A rolling deploy means new code will hit shards that are still on the old schema. Use the expand-contract pattern: add nullable columns, backfill, switch code, then drop in a later release. This keeps every shard readable during the transition.

How to debug slow queries in a sharded system

Debug a slow query in a sharded system by first isolating whether the cost is per shard or from fan-out. Run the query against one shard directly, time it, then multiply by shard count. If the per-shard time is fast but the request is slow, the problem is scatter-gather, not SQL.

1. Capture the request in your app; note the shard keys it touches.
2. Run EXPLAIN (ANALYZE, BUFFERS) against a single shard for the same predicate. See PostgreSQL EXPLAIN ANALYZE for reading the plan.
3. Compare row counts returned per shard to detect an uneven distribution. A hot shard will show a much higher row count and duplicate work.
4. Check whether the predicate includes the shard key; a missing shard key turns the query into a full scan on every shard.

In most incidents the fix is to add the missing shard key or move the aggregation into an analytics replica, not to add more shards.

Frequently asked questions

Do I need to shard Postgres for 10 million rows?

No. A single Postgres 16 instance handles tables of tens of millions of rows comfortably if the working set fits in memory and indexes are tuned. Shard when write throughput saturates a tuned primary, not when a row count crosses an arbitrary threshold.

Should I shard with Postgres or use a distributed SQL database?

Choose application-level sharding when you need a small number of predictable shards and can control query patterns. Choose a distributed SQL system (CockroachDB, Citus) when you need transparent cross-shard queries and strong global consistency and can accept its tuning complexity and operational cost.

How many shards should I start with?

Start with a number you can double: 8, 16, or 32. Shard count should be a power of two so resharding is a split, not a full rewrite. Pick based on the largest opportunity you can foresee, then plan to reshard by moving whole tenants between shards using a routing table.

Sharding is a real tool, but it trades a hardware problem for a code problem. Before you commit, follow the caching strategies that cut DB load in Laravel, measure write throughput, and confirm that no single primary can serve your workload. When it truly cannot, shard on one immutable key and accept that cross-shard joins and ACID transactions must be redesigned in the application. For an official reference on declarative partitioning, read the PostgreSQL table partitioning documentation.