Sharding Strategies for Horizontal Scale

A database rarely needs sharding on its first difficult day. More often, a team reaches for it after a primary can no longer absorb writes, an index no longer fits comfortably in memory, maintenance windows become dangerous, or one tenant can disturb everyone else. Sharding addresses those limits by dividing rows across independent database nodes. It also turns one database into a distributed system whose routing, metadata, failures, and migrations become application concerns.

That exchange is worth making only when the bottleneck is genuinely the size or write throughput of one database. Read replicas can scale read-heavy traffic. Native table partitioning can make pruning, retention, and index maintenance cheaper while all partitions still live under one database authority. Better indexes, archived history, caching, batching, and a larger machine are simpler interventions. If one well-tuned primary still owns the working set and write rate with healthy headroom, replicas or partitioning usually suffice.

Sharding becomes justified when a single failure domain, storage device, buffer pool, or write-ahead log is the limiting resource and data can be divided by a stable ownership boundary. The design then starts with that boundary, not with a node count.

Choose a strategy from access patterns #

Four common strategies answer the question “Which shard owns this row?” differently.

Range sharding fits time-series history, ordered catalogs, and data whose common queries follow the same range boundary. Static ranges are easy to understand, but monotonic IDs or timestamps direct all new writes to the last range. Pre-splitting, bucketing time with another dimension, or assigning noncontiguous ranges can reduce that hotspot.

Hash sharding is a strong default for point reads keyed by account, device, or tenant. A uniform hash spreads even sequential input, but it destroys useful ordering. Plain modulo is especially painful during growth: changing from $N$ to $N+1$ changes the result for most keys. Consistent hashing places both keys and virtual nodes on a ring. Adding one physical shard then transfers only nearby intervals, approximately

for balanced membership, rather than almost every key. Virtual nodes improve balance and let differently sized machines own different numbers of intervals.

Directory sharding stores placement as data. It is ideal when large tenants need dedicated shards, small tenants can share capacity, and operators must move one tenant without changing its identity. The directory must be highly available, versioned, cached with bounded staleness, and protected from split ownership during updates.

Geographic sharding places data by legal residency or dominant access region. It should usually select a regional shard group first, then use range, hash, or directory sharding inside that group. Decide what happens when an account moves, a user travels, or a workflow joins users from different regions; “route to the closest region” is not an ownership rule.

Design the shard key and routing contract #

A useful shard key has high cardinality, stable ownership, reasonably uniform traffic and storage, and alignment with the most important query boundary. tenant_id often works for business software because authorization, reads, writes, and retention are tenant-scoped. It fails when one tenant is larger than a shard or when most queries compare all tenants. user_id spreads a social workload, but a celebrity account can still become hot because uniform key counts do not imply uniform request rates.

For shard $s$, define its observed load as a weighted combination rather than row count alone:

The imbalance worth watching is

Choose weights from actual constraints. A shard with little data can still be saturated by a hot key. Mitigations include caching immutable reads, splitting a giant tenant by a second key, write bucketing for mergeable counters, isolating noisy tenants, and applying per-tenant admission limits. Random suffixes spread writes but make reads fan out, so use them only when aggregation is explicit.

Routing belongs in a small, shared component or database proxy, not in ad hoc conditionals across services. This TypeScript ring uses SHA-256, virtual nodes, unsigned 64-bit points, binary search, and wraparound. Membership should come from versioned configuration rather than process-local discovery.

import { createHash } from 'node:crypto';

interface Shard {
  readonly id: string;
  readonly connectionString: string;
}

interface RingPoint {
  readonly hash: bigint;
  readonly shard: Shard;
}

function hash64(value: string): bigint {
  const digest = createHash('sha256').update(value).digest();
  return digest.readBigUInt64BE(0);
}

export class ConsistentHashRouter {
  private readonly ring: RingPoint[];

  constructor(shards: readonly Shard[], virtualNodes = 256) {
    if (shards.length === 0 || virtualNodes < 1) {
      throw new Error('At least one shard and one virtual node are required');
    }

    this.ring = shards
      .flatMap((shard) =>
        Array.from({ length: virtualNodes }, (_, index) => ({
          hash: hash64(`${shard.id}:${index}`),
          shard,
        })),
      )
      .sort((left, right) => (left.hash < right.hash ? -1 : 1));
  }

  route(shardKey: string): Shard {
    const target = hash64(shardKey);
    let low = 0;
    let high = this.ring.length;

    while (low < high) {
      const middle = low + Math.floor((high - low) / 2);
      const point = this.ring[middle];
      if (!point || point.hash >= target) high = middle;
      else low = middle + 1;
    }

    const selected = this.ring[low] ?? this.ring[0];
    if (!selected) throw new Error('Hash ring is empty');
    return selected.shard;
  }
}

The hash algorithm, byte encoding, virtual-node count, shard IDs, and membership epoch are persisted protocol choices. Changing any of them silently changes ownership. Test routing with fixed vectors in every language that implements it.

Keep queries and transactions shard-local #

The cheapest cross-shard query is the one the data model avoids. Co-locate entities that change together, and carry the shard key through primary keys, foreign keys, queue messages, cache keys, and API commands. On each shard, a tenant-scoped schema might enforce local uniqueness:

CREATE TABLE orders (
  tenant_id UUID NOT NULL,
  order_id UUID NOT NULL,
  status TEXT NOT NULL,
  total_cents BIGINT NOT NULL CHECK (total_cents >= 0),
  created_at TIMESTAMPTZ NOT NULL,
  PRIMARY KEY (tenant_id, order_id)
);

CREATE INDEX orders_by_tenant_created
  ON orders (tenant_id, created_at DESC);

SELECT order_id, status, total_cents
FROM orders
WHERE tenant_id = $1 AND created_at >= $2
ORDER BY created_at DESC
LIMIT 50;

That query routes from tenant_id and executes on one shard. A global report without a shard key must scatter to all shards, run bounded subqueries in parallel, and merge results. Limit concurrency, push filters and partial aggregation down, attach deadlines, and define partial-result behavior. For frequent global analytics, stream change data into a warehouse or search index instead of using every transactional shard as a reporting engine.

Local transactions retain the database’s normal ACID guarantees. A transaction spanning shards requires distributed coordination such as two-phase commit, which adds latency, blocking modes, and a coordinator recovery problem. Many product workflows are better modeled as idempotent local transactions plus an outbox, events, and a saga with compensating actions. That is not atomicity: intermediate states are visible, so invariants and user messaging must permit them. Money transfer, uniqueness, and inventory allocation may require a dedicated strongly consistent service or deliberate coordination.

Global identifiers must not depend on one database sequence. UUIDv7 provides decentralized, roughly time-ordered IDs; Snowflake-style IDs combine a timestamp, worker ID, and sequence but require disciplined worker leasing and clock handling. IDs should be unique globally, while business uniqueness such as one username per platform needs a global registry, deterministic home shard, or reservation workflow.

Rebalance without changing ownership twice #

Growth requires moving ranges, virtual nodes, or directory entries while requests continue. Treat placement as a versioned state machine. A safe migration copies a bounded unit, catches up concurrent writes, verifies it, changes authoritative routing once, and keeps rollback data until confidence is high.

During copying, choose one source of truth. Source-authoritative writes plus change-data capture avoid ambiguous dual-write success. If dual writes are unavoidable, use idempotent operation IDs, record per-destination status, and define which side wins. At cutover, fence stale routers with an epoch: a shard receiving a request for an old epoch rejects or redirects it instead of accepting a write after ownership moved.

Throttle copy bandwidth by replication lag and foreground latency, not by a fixed maximum alone. Verify row counts by bucket, checksums, sampled semantic reads, and the change-stream checkpoint. Reshard in small units so pause, retry, and rollback are operationally credible. Schema versions must be compatible on both source and destination throughout the move.

Operate for visibility, failure, and recovery #

Dashboards need per-shard distributions for QPS, write rate, storage, CPU, connections, lock time, query latency, replication lag, error rate, and hottest keys. Fleet averages hide one overloaded shard. Track router epoch, directory lookup latency and cache age, fan-out width, partial-query rate, migration throughput, checksum failures, rejected stale-epoch writes, and the largest tenant’s share of each resource.

Every shard needs replicas, backups, and tested restore procedures. Replication handles machine failure and read scale; it does not replace point-in-time backups because deletion and corruption replicate too. Define recovery point objective and recovery time objective per data class. Regularly restore a shard into isolation, replay logs to a timestamp, validate application invariants, then rehearse publishing the replacement through metadata.

When a primary fails, promote only a replica known to satisfy the durability policy and fence the old primary before accepting writes. A network partition must not leave two writable owners. Routers should retry only idempotent operations and refresh placement after NOT_OWNER responses. Unknown commit outcomes need operation IDs or a status lookup, not blind retries.

Directory failure deserves its own plan. Routers can use last-known-good assignments for bounded time, but placement changes should stop until metadata authority returns. Back up the directory separately, audit every mutation, and ensure it can be rebuilt from durable ownership records. Run fault exercises for shard loss, metadata loss, stale routers, exhausted connection pools, corrupt snapshots, and interrupted migrations.

Know when not to shard #

Use read replicas when writes fit one primary and read throughput is the constraint. Use native partitioning when lifecycle management, partition pruning, vacuuming, or local index size is the issue but one database can still coordinate the workload. Archive cold rows when historical growth, not active working-set size, causes pressure. Cache repeated reads, batch writes, repair query plans, and scale vertically when those measures restore comfortable headroom at lower operational cost.

Shard only after measuring a limit that those tools cannot remove economically. Before committing, prove that ordinary requests are shard-local, estimate the largest tenant and hottest key, benchmark a skewed distribution, and perform a full shard move and restore in staging. The team must own routing metadata, global schema rollout, backup coverage, capacity balancing, and cross-shard debugging for the life of the system.

An incremental path is usually safest: introduce globally unique IDs and explicit tenant keys, remove hidden cross-tenant constraints, put routing behind one interface, and collect per-key load. Those changes improve architecture even if the system never shards. If it does, they prevent the migration from becoming a simultaneous rewrite of identity, data access, and operations.

Takeaways #

Sharding scales storage and writes by replacing one database with multiple ownership domains. Range, hash, directory, and geographic strategies optimize different access patterns; none rescues a shard key that ignores locality or traffic skew. Select the key from queries and invariants, measure hot keys as well as bytes, and keep routing a versioned protocol.

Design transactions and common queries to stay on one shard. Move global search and analytics to derived systems, and treat distributed transactions, global uniqueness, and regional ownership as explicit coordination problems. Rebalance through snapshot, change capture, verification, one fenced cutover, and a rollback window.

Finally, operate every shard as a recoverable database and the directory as critical control-plane data. If replicas, partitioning, archiving, or tuning solve the measured bottleneck, stop there. Sharding is successful not when rows are spread across machines, but when ownership remains predictable through growth, failure, and change.