Sharding & Partitioning

Splitting a dataset horizontally across multiple database servers (shards), so each server holds a subset of the rows. It scales storage and write throughput beyond a single machine.

Horizontal splits rows across nodes (each shard has all columns for some rows). Vertical splits columns (e.g. rarely-used large columns in a separate table/store). Sharding refers to horizontal partitioning across machines.

The attribute (e.g. user_id) used to decide which shard a row belongs to. Its value is fed into the partitioning function (range/hash) to route reads and writes.

Vertical scaling has a ceiling (and gets very expensive), and one node is still a single point of failure and single write bottleneck. Sharding scales horizontally on commodity hardware and spreads write load, which replication alone can't do.

A shard (or key) that receives a disproportionate share of traffic or data, becoming a bottleneck while other shards sit idle — undermining the point of sharding.

No — they're complementary. Sharding splits data for scale; replication copies each shard for availability and read scaling. Production systems shard and then replicate each shard.

user_id for a consumer app (most queries are per-user), tenant_id for a multi-tenant SaaS, or account_id for a banking system. The key aligns storage with the dominant access pattern so most queries hit one shard.

Range: contiguous key ranges per shard. Enables efficient range scans and ordered queries, but sequential keys (timestamps, auto-increment IDs) all hit the newest shard, creating write hotspots. Hash: apply a hash to the key for uniform distribution and no hotspots, but you lose range-query locality (a range scan must hit all shards). Some systems hash a prefix and range within (compound keys) to get both.

A query that can't be routed to a single shard (because it doesn't filter on the shard key) must be sent to all shards and the results merged. It's costly because latency is bounded by the slowest shard, it consumes resources on every node, and it doesn't scale — adding shards makes such queries touch more nodes. Good shard-key design minimizes scatter-gather.

A lookup service (the directory) maps each key or key-range to its shard, decoupling routing from a fixed function. This makes rebalancing and adding shards flexible (just update the mapping) and supports uneven splits. The trade-off: the directory is an extra dependency and potential bottleneck/SPOF, so it must be highly available and cached.

Options: route in parallel to the relevant shards and merge/aggregate results (scatter-gather); denormalize/duplicate data so the query can be answered from one shard; maintain a separate read-optimized store (search index or data warehouse) for cross-cutting queries; or restructure the shard key to align with the query. For cross-shard writes, use sagas or two-phase commit, accepting added complexity.

Related rows may live on different shards, so a JOIN can't run locally. Solutions: co-locate related data by sharding on the same key (e.g. shard orders by user_id like the users), denormalize so joined data is embedded, perform the join in the application after fetching from each shard, or keep small reference tables replicated on every shard. Cross-shard JOINs are slow and are usually designed away.

You can't rely on a single auto-increment counter. Options: UUIDs (random, no coordination, but large and unordered), Snowflake-style IDs (timestamp + machine/shard ID + sequence, giving roughly time-ordered unique 64-bit IDs), per-shard ranges handed out by a central allocator, or database sequences with different offsets/increments per shard. Snowflake IDs are popular because they're compact, unique, and sortable by time.

One key (a celebrity account, a viral item) gets so much traffic that its shard is overwhelmed. Mitigations: add a random suffix/salt to spread the key's writes across multiple sub-partitions (fanning out reads), cache the hot key aggressively (with replication), split the entity's data, or give hot tenants dedicated shards. Detection (per-key metrics) is needed to react dynamically.

Avoid hash % N (which remaps ~everything when N changes). Use consistent hashing with virtual nodes so adding a shard only moves the keys in the arcs it takes over (~1/N of data). Or pre-split data into a large fixed number of logical partitions (e.g. 1024) and map partitions→physical nodes; adding a node just reassigns some partitions, moving whole partitions rather than rehashing keys. Move data in the background, dual-read/dual-write during migration, verify, then cut over routing.

Consistent hashing places nodes and keys on a ring; a key belongs to the next node clockwise, so topology changes only affect neighboring arcs. But with few nodes, the ring is uneven and load is skewed. Virtual nodes give each physical node many positions on the ring, smoothing distribution and letting powerful nodes hold more vnodes (weighting). When a node fails, its vnodes' keys spread across many remaining nodes rather than dumping onto one neighbor. This yields even load and minimal, distributed data movement on changes.

Two main approaches. Two-phase commit (2PC): a coordinator asks all shards to prepare (durably promise they can commit), then commits everywhere; it gives atomicity but blocks if the coordinator fails and hurts availability/latency. Sagas: break the transaction into local transactions per shard with compensating actions to undo prior steps on failure; this is non-blocking and available but only eventually consistent and requires idempotency and careful compensation logic. Best practice is to design shard keys so most transactions stay within one shard and reserve cross-shard transactions for rare cases.

Sequential IDs with range sharding send all new inserts to the last range, so the newest shard absorbs all write traffic while older shards go cold — a classic write hotspot. Fixes: switch to hash-based sharding on the ID (uniform distribution), shard on a higher-cardinality/more-uniform key aligned with access patterns (e.g. user_id), or use hashed/random ID schemes (Snowflake with the machine bits, or reversed/bit-shuffled IDs) so consecutive inserts scatter across shards. Migrate by resharding with the new scheme (dual-write + backfill).

Plan for whole-partition moves: if you pre-split into many logical partitions mapped to physical shards, resharding just reassigns partitions to new nodes. Steps: stand up new shards; start replicating/copying the partitions destined to move while the old shards still serve; enable dual-write so new writes land in both old and new locations; backfill historical data idempotently; verify consistency (checksums/row counts); flip the routing layer to the new mapping partition-by-partition; then stop dual-write and clean up. Keep the routing map versioned and reversible so you can roll back per partition if verification fails.

Three placements. Client-side (routing in the app/driver): lowest latency, no extra hop, but every client must embed logic and know the topology (hard to change). Proxy/router tier (e.g. Vitess vtgate, ProxySQL): centralizes routing and hides topology from apps, at the cost of an extra hop and a component to scale/operate. Coordinator per request (any node forwards): simple clients but adds internal hops. Most large systems use a proxy tier for operability, backed by a metadata/directory service that's cached to avoid a per-query lookup bottleneck.

The data is partitioned by the shard key, but an index on another attribute spans shards. Local indexes: each shard indexes only its own rows — cheap writes, but a query on the indexed attribute must scatter-gather across all shards. Global indexes: the index is itself partitioned (by the indexed term) so a lookup goes to one index shard, but writes must update a remote index partition, usually asynchronously (eventually consistent index). You pick based on whether reads or writes on that attribute dominate, and accept the fan-out or the consistency lag accordingly.

Sharding adds permanent complexity (routing, cross-shard queries/transactions, rebalancing, operational burden) and is hard to undo. Try first: vertical scaling, read replicas for read load, caching to offload hot reads, query/index optimization, connection pooling, archiving cold data, and table partitioning within one instance. Only shard when a single primary genuinely can't hold the data or absorb the write throughput, and design the shard key extremely carefully because changing it later is painful. Premature sharding is a common, costly mistake.

One partition of a sharded dataset, living on its own database server and holding a subset of the rows. All shards together contain the full dataset.

No, but queries that do filter on the shard key can be routed to a single shard (fast). Queries that don't must be broadcast to all shards and merged (scatter-gather), which is slower and less scalable — so you design the shard key to cover the most common queries.

A logical shard is a unit of data partitioning (e.g. one of 1024 partitions); a physical shard is an actual server/instance. Many logical shards map onto fewer physical shards. This indirection makes rebalancing easy: to scale out, you move whole logical shards to new physical nodes without re-hashing individual keys.

Each shard is backed up independently, so a full backup is the set of per-shard backups. Challenges: getting a globally consistent point-in-time snapshot across shards (they back up at slightly different times), coordinating restores, and handling schema/topology changes between backup and restore. You need tooling that tracks the shard map with the backups and can restore a single shard or the whole cluster consistently.

A small, relatively static table (e.g. countries, currencies, product categories) that is replicated in full to every shard. Because it's present locally on each shard, queries can join against it without a cross-shard operation. It trades a little storage duplication and write fan-out for avoiding expensive cross-shard joins on common lookup data.

Track per-shard metrics: data size, QPS (reads/writes), CPU, storage, and per-key access frequency to find hot keys. Compare distributions across shards to spot skew (one shard much larger or busier). Alert on shards approaching capacity or on a growing spread between the busiest and least-busy shard. Log slow scatter-gather queries. With this telemetry you can rebalance (move logical shards), split hot shards, or re-key before a shard becomes a bottleneck. Automated systems continuously rebalance based on these signals.

Split it. With a range scheme, pick a split point that divides the range roughly in half and migrate half the keys to a new shard (updating the shard map). With many pre-created logical shards, just move some logical shards off the overloaded physical node. Do the migration online: copy data in the background, dual-write during the transition, verify, then flip routing and clean up. For hot keys causing the growth (not just size), you may instead salt/split the key or give the tenant a dedicated shard. Automatic splitting is a feature of systems like HBase/Bigtable and Vitess.

Application-level: your code computes the shard and connects to the right DB. Full control and no extra infrastructure, but the logic is duplicated across services, topology changes are painful, and cross-shard queries/transactions are hand-rolled. Native/proxy sharding (Vitess, Citus, MongoDB sharded clusters, CockroachDB): the database or a proxy tier handles routing, rebalancing, and often cross-shard queries/transactions transparently, so apps see a single logical database. It reduces app complexity and eases operations at the cost of running/understanding that layer and some feature limitations. Most large teams prefer a managed/native solution over reinventing sharding in the app.