DB sharding strategy writer
A deterministic recipe for partitioning relational data across shards using composite keys and range-based routing. No magic — just math, migration paths, and operational runbooks.
Shard key selection
Choose a high-cardinality column that appears in 80%+ of WHERE clauses. Tenant ID, user ID, or a hash of both. Avoid monotonically-increasing keys — they create hot shards.
Routing function
Map the shard key to a logical shard via consistent hashing. Maintain a lookup table in a lightweight coordinator (Redis, etcd). Rebalance with virtual nodes to keep distribution within 5% variance.
Migration playbook
Dual-write to old and new shard for one release cycle. Backfill historical data with a throttled batch job. Flip reads after verification. Keep the old shard read-only for 30 days as a rollback safety net.
Cross-shard queries
Scatter-gather with a timeout per shard. Aggregate in the application layer. For joins, denormalize aggressively or use a separate search index (Elasticsearch) fed by change-data-capture.
This recipe assumes PostgreSQL 15+ with logical replication enabled. Adjust ring size and virtual node count based on your shard count — start with 256 vnodes per physical shard.