Sharding & Partitioning

Optimize, scale up, replicate, cache, CQRS, then shard: because sharding is a one-way door that breaks joins and transactions.

Make IDs globally unique without a central bottleneck using per-shard non-overlapping sequence ranges, a Snowflake-style timestamp-plus-worker-plus-counter 64-bit id, or time-ordered UUIDv7.

Hash spreads evenly but kills range scans; range enables scans but risks hotspots; directory is flexible but adds a lookup: pick the shard key by cardinality, evenness, and query pattern.

Sharding buys scale but charges you in cross-shard queries, distributed transactions, resharding, lost foreign keys, coordinated migrations, and operational complexity.

Partitioning splits one logical table into smaller physical pieces: horizontally by rows (range/list/hash, and sharding when across servers) or vertically by columns.

Partitioning helps only when the query filters on the partition key so the engine can prune; choose range, list, or hash to match how you actually query.

With time-based partitions, archiving or deleting old data is dropping a whole partition: an instant metadata operation, not a costly mass DELETE.