Database Sharding — The Choices We Wish We'd Made Earlier
KU
Kiril Urbonas
last month • 8 min read
Sharding isn't just "split the table" — the shard key choice cascades through queries, joins, rebalancing, and operations. The decisions that pay off and the ones we redid.
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.
The forcing functions:
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.
This is the decision you can't easily undo. The shard key determines:
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.We picked tenant_id. The reasoning:
Things that biased us toward tenant_id:
Things that we considered as risks:
Both materialized. We handled them; see below.
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.
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:
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.
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:
Per move: ~10 seconds of partial unavailability for ~5% of tenants. Acceptable.
Migrations now run per-shard. We had to:
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.
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.
Be honest about whether you need it:
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.
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.
Get the latest tutorials, guides, and insights on AI, DevOps, Cloud, and Infrastructure delivered directly to your inbox.
Most post-mortems produce a document and no follow-through. The format, the discipline, and the cultural moves that actually convert incidents into engineering improvements.
Default-deny, namespace isolation, egress control — the patterns we use, the gotchas around DNS, and where Cilium changed our calculus.
Explore more articles in this category
The Backstage demo always wows leadership. Then six months later the catalog has 400 stale entries and nobody trusts it. Here's what got ours to actually stick.
We inherited 200-odd AWS resources built by hand over four years, with no state file anywhere. Here's how import blocks and a generation workflow got them under Terraform without a rebuild.
A single ALTER TABLE took a lock and stalled every write for 40 seconds during peak traffic. Expand-contract is how we stopped shipping outages.
Evergreen posts worth revisiting.
