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

Database Sharding — The Choices We Wish We'd Made Earlier#

We sharded our primary Postgres database 18 months ago. Two years before, we'd been telling ourselves we'd never need to — vertical scaling had headroom, partitioning was fine, "single-node is the right default." Then it wasn't.

Sharding worked. The system is faster, scalable, and operationally more complex than before. This post is the decisions that earned their place, the ones we redid, and the meta-lesson: every sharding choice is a constraint on every future query.

Why we sharded (eventually)#

The forcing functions:

• Storage: ~3TB on the largest table, growth trajectory said 10TB within a year. RDS instances above that get expensive and operationally painful.
• Write throughput: ~50K writes/sec at peak. Single-primary Postgres handled it but with margin shrinking quarter over quarter.
• Maintenance windows: VACUUM, schema migrations, backup-restore tests all grew with data size. A 4-hour migration is fine; a 14-hour migration isn't.

Sharding was option-of-last-resort. We tried first: aggressive partitioning, archiving cold data, read-replicas for read scaling, materialized views for expensive aggregates. Those got us another year. Then we sharded.

The shard key choice (the most consequential decision)#

This is the decision you can't easily undo. The shard key determines:

• Which rows live on which shard.
• Which queries are single-shard (fast) vs cross-shard (slow).
• How rebalancing works.

For us, the candidates were:

• user_id — most queries are user-scoped.
• tenant_id — we're multi-tenant; tenant-scoping makes operational sense.
• created_at (time-based) — natural for append-heavy workloads.
• Hash of compound key — most uniform distribution.

We picked tenant_id. The reasoning:

• The dominant query pattern is "all data for a tenant." Tenant-scoping makes 90% of queries single-shard.
• Per-tenant operations (export, delete-all-data for GDPR, tenant-specific maintenance) align with shard boundaries.
• Tenant load is uneven but bounded — no tenant is so large it can't fit on a single shard.

Things that biased us toward tenant_id:

• A few queries that aggregate across tenants exist but are not hot path.
• Tenant onboarding/offboarding gives us natural rebalancing opportunities.

Things that we considered as risks:

• "Whale" tenants (large customers with disproportionate data) skew shard sizes.
• Cross-tenant analytics requires fan-out.

Both materialized. We handled them; see below.

What we'd do differently#

If we sharded again tomorrow, we'd:

Salted shard keys for whale isolation. Even with tenant-scoped sharding, our biggest tenant ended up alone on one shard. That shard ran hot; the others ran cold. We retrofit "tenant_id + sub-bucket" for whale tenants — splitting their data across multiple shards via a deterministic hash. The retrofit was painful; doing it from day one would have been free.

Better cross-shard tooling earlier. We built cross-shard query routing in month 3 of post-sharding life. We needed it in week 2. Build it before you shard, not after.

A consistent ID scheme. We used auto-increment IDs per shard. Cross-shard collisions are theoretically impossible but the IDs aren't globally meaningful. We'd use a globally-unique scheme (UUID or Snowflake-style) from day one for everything that crosses shard boundaries.

Separate shard mapping from connection routing. Initially, the shard-mapping logic lived in our application. Schema changes to the routing required app deploys. We moved it to a proxy (PgCat in our case) later. Externalize earlier.

Cross-shard queries: the operational tax#

90% single-shard sounds great. The other 10% is where complexity lives.

Cross-shard transactions. Postgres doesn't do distributed transactions across separate shards. Anything that needs atomicity across shards is now an application concern — usually via two-phase commit, saga patterns, or "accept eventual consistency."

We minimize these by keeping related data on the same shard (the whole point of tenant-scoping). For the few that remain, we use sagas with idempotent steps.

Cross-shard reads. A query that needs data from multiple shards becomes:

1. Run the query on every shard.
2. Combine results.
3. Re-sort if needed.

For a LIMIT 10 query across all shards, you fetch the top 10 from each shard, combine, then take the global top 10. Each shard does ~normal work; the combiner does proportional work to the number of shards.

For an aggregation (SELECT count(*) FROM events), you fetch the count from each shard and sum. Easy.

For a JOIN where one side is sharded and the other isn't, you co-locate the small side (replicate it across all shards) or pull both to a coordinator.

We built a small "query coordinator" layer that handles these patterns. It's ~3K lines of code; it handles most of our cross-shard needs.

Rebalancing#

When a shard gets too big or hot, you need to move data. There are two main approaches:

Range-based moves. "Move tenants with ID 1000-2000 from shard A to shard B." Conceptually simple; operationally painful (downtime during the move, or complex dual-write logic).

Consistent hashing. New shards added smoothly; only a fraction of data moves. Operationally cleaner; harder to predict which data is where.

We use range-based moves. Our shard count is small (8 shards initially, 16 now); explicit ranges are auditable. For very large fleets (hundreds of shards), consistent hashing scales better.

Each move:

1. Set up logical replication from source shard to target shard for the moving tenant IDs.
2. Wait for catch-up.
3. Briefly pause writes for the moving tenants (single-digit seconds).
4. Switch shard routing.
5. Resume writes.

Per move: ~10 seconds of partial unavailability for ~5% of tenants. Acceptable.

Schema migrations#

Migrations now run per-shard. We had to:

• Make the migration tool shard-aware (run against each shard).
• Build idempotency carefully (running a migration twice should be safe).
• Handle the rolling-deploy case (some shards on new schema, some on old, briefly).

Migrations take N times longer where N is the shard count. For small migrations this is fine. For big ones we parallelize the actual data changes within each shard.

What we monitor#

• Per-shard size and growth rate. Catches drift before rebalancing is urgent.
• Per-shard QPS / latency. Hotspot detection.
• Cross-shard query rate. Should stay below 10-15% of total queries; growth means the shard key is fitting poorly.
• Replication lag (for the shard-internal HA replicas).
• Routing layer latency. The proxy/router adds latency; should be sub-millisecond.

Things we got wrong#

Underestimating the application changes. ORMs assumed a single connection; we ripped out and replaced query patterns across the codebase. ~6 weeks of dev work. Should have planned for double.

Treating sharding as "more of the same database." Sharding is a different programming model. Joins, transactions, aggregations all change. Teams need training on the new patterns.

No dry-run path. Our first attempted shard migration we did on production with insufficient testing. We caught a bug in shard routing 10 minutes into the migration and rolled back. Now we have a "shadow shard" environment where every routing change is exercised before prod.

Schema-on-write for new shards. We added a new shard and forgot to migrate the latest schema to it. New writes fell into a shard with missing columns. Now there's a check in CI that all schemas match before any rebalance.

When sharding is the wrong answer#

Be honest about whether you need it:

• Read-heavy workload. Read replicas usually solve this. Sharding adds write-scale, not read-scale.
• Sub-100GB data. Vertical scaling is fine. The operational cost of sharding isn't justified.
• Single hot table out of many. Sometimes you can shard just that table or even just denormalize. The whole-DB sharding pattern is heavy.
• You haven't tuned the existing DB. Add indexes, vacuum aggressively, archive cold data, scale up. Sharding is the last lever, not the first.

We waited too long to shard (the year before was painful). We didn't wait long enough on a few sub-systems where sharding wasn't actually the right answer. Calibration takes time.

What to read next#

• Postgres logical replication for zero-downtime upgrades — the mechanism we use for shard moves
• Postgres connection pooling — PgBouncer in front of RDS — the connection layer that sharding stresses
• pg_stat_statements — Postgres query analysis without guessing — what tells you which queries are cross-shard
• Postgres autovacuum — tuning from production stalls — operational discipline that gets more important under sharding

Sharding is one of those decisions that's harder than it looks but, once done, unblocks a lot of growth. The shard key choice is permanent in practice; pick carefully. Everything else is tooling and operational discipline that you can build over time. The post above is what we wish someone had told us before we started.