Database Sharding Strategies Explained: Range-Based, Hash-Based, and Directory-Based Partitioning with Real Production Examples

At some point vertical scaling stops being an answer. You’ve added CPUs and RAM, moved to larger instance classes, tuned your connection pool, added read replicas for query offload. But writes still go to one machine, and that machine has a ceiling. Sharding is what you reach for when the ceiling is real.

This article is about making the right decision for how to shard. The three core strategies, range-based, hash-based, and directory-based, each make different tradeoffs that only become visible under production load. Choosing wrong is expensive: resharding a live system is one of the most operationally painful things you can do, and poor shard key selection locks you into hotspots that no amount of hardware will fix.

What Sharding Actually Is

Sharding is horizontal partitioning: you split a single logical dataset across multiple physical databases (shards), each owning a distinct subset of the data. Unlike replication, where every node holds a full copy, each shard holds data that no other shard holds. The total dataset is the union of all shards.

The application (or a routing layer in front of it) must know which shard holds a given record before it can query or write. That routing decision is the core of every sharding strategy.

// Minimal shard router interface — all strategies implement this
interface ShardRouter {
  getShard(shardKey: string | number): ShardConnection;
  getAllShards(): ShardConnection[];
}

interface ShardConnection {
  id: string;
  host: string;
  port: number;
  database: string;
}

Range-Based Sharding

Range-based sharding assigns records to shards based on a contiguous range of the shard key. Shard 1 owns keys 0–999999, shard 2 owns 1000000–1999999, and so on.

The main benefit is locality for range queries. If you’re querying time-series data for a specific month, or pulling all orders for an account ID range, you can direct the query to exactly one shard and avoid scatter-gather entirely.

class RangeShardRouter implements ShardRouter {
  private readonly ranges: Array<{
    min: number;
    max: number;
    shard: ShardConnection;
  }>;

  constructor(
    ranges: Array<{ min: number; max: number; shard: ShardConnection }>
  ) {
    // Ranges must be sorted and non-overlapping
    this.ranges = ranges.sort((a, b) => a.min - b.min);
  }

  getShard(shardKey: string | number): ShardConnection {
    const key = Number(shardKey);
    for (const range of this.ranges) {
      if (key >= range.min && key <= range.max) {
        return range.shard;
      }
    }
    throw new Error(`No shard found for key: ${key}`);
  }

  getAllShards(): ShardConnection[] {
    return this.ranges.map((r) => r.shard);
  }

  getShardForRange(min: number, max: number): ShardConnection[] {
    // Return all shards whose ranges overlap with [min, max]
    return this.ranges
      .filter((r) => r.min <= max && r.max >= min)
      .map((r) => r.shard);
  }
}

// Example: time-series data partitioned by epoch day
const dailyRanges = [
  { min: 0, max: 86399, shard: { id: "shard-0", host: "db-0", port: 5432, database: "metrics" } },
  { min: 86400, max: 172799, shard: { id: "shard-1", host: "db-1", port: 5432, database: "metrics" } },
  // ...
];

The problem with range-based sharding is hotspots at the boundary. If your shard key is a timestamp or a monotonically increasing ID, all new writes land on the same shard: the current range. Every other shard is cold. In a write-heavy workload this single-shard write bottleneck is often worse than not sharding at all.

Range sharding works well when:

• The shard key has natural, query-aligned ranges (dates, geographic regions, customer tiers)
• Reads are range-heavy and writes are roughly uniform across the key space
• The key distribution does not monotonically increase over time, or you intentionally pre-split ranges to spread new writes

Hash-Based Sharding

Hash-based sharding applies a hash function to the shard key and maps the result to a shard. The goal is uniform distribution: no single shard should receive disproportionately more writes than any other.

import { createHash } from "crypto";

class HashShardRouter implements ShardRouter {
  private readonly shards: ShardConnection[];

  constructor(shards: ShardConnection[]) {
    this.shards = shards;
  }

  getShard(shardKey: string | number): ShardConnection {
    const hash = this.hash(String(shardKey));
    const index = hash % this.shards.length;
    return this.shards[index];
  }

  getAllShards(): ShardConnection[] {
    return [...this.shards];
  }

  private hash(key: string): number {
    // Use a stable, non-cryptographic hash for routing
    const digest = createHash("md5").update(key).digest("hex");
    // Take first 8 hex chars to avoid Number precision issues
    return parseInt(digest.substring(0, 8), 16);
  }
}

This simple modulo approach breaks down at resharding time. If you go from 8 shards to 10 shards, almost every key remaps to a different shard, requiring a full data migration. This is why production systems use consistent hashing instead of simple modulo: only K/N keys need to move when adding a shard (where K is total keys and N is the new shard count).

Consistent hashing places both shards and keys on a hash ring. Each key is owned by the nearest shard clockwise on the ring. Adding a new shard means it takes over a slice of one existing shard’s responsibility, and only the keys in that slice need to move.

class ConsistentHashRouter implements ShardRouter {
  private readonly ring: Map<number, ShardConnection> = new Map();
  private readonly sortedPositions: number[] = [];
  private readonly virtualNodes: number;

  constructor(shards: ShardConnection[], virtualNodes = 150) {
    this.virtualNodes = virtualNodes;
    for (const shard of shards) {
      this.addShard(shard);
    }
  }

  addShard(shard: ShardConnection): void {
    for (let i = 0; i < this.virtualNodes; i++) {
      const position = this.hashPosition(`${shard.id}:vnode:${i}`);
      this.ring.set(position, shard);
      this.sortedPositions.push(position);
    }
    this.sortedPositions.sort((a, b) => a - b);
  }

  removeShard(shardId: string): void {
    for (let i = 0; i < this.virtualNodes; i++) {
      const position = this.hashPosition(`${shardId}:vnode:${i}`);
      this.ring.delete(position);
      const idx = this.sortedPositions.indexOf(position);
      if (idx !== -1) this.sortedPositions.splice(idx, 1);
    }
  }

  getShard(shardKey: string | number): ShardConnection {
    if (this.ring.size === 0) throw new Error("No shards configured");
    const keyPosition = this.hashPosition(String(shardKey));
    // Find first position >= keyPosition (clockwise lookup)
    const idx = this.lowerBound(keyPosition);
    const position = this.sortedPositions[idx % this.sortedPositions.length];
    return this.ring.get(position)!;
  }

  getAllShards(): ShardConnection[] {
    return [...new Set(this.ring.values())];
  }

  private hashPosition(key: string): number {
    const digest = createHash("md5").update(key).digest("hex");
    return parseInt(digest.substring(0, 8), 16);
  }

  private lowerBound(target: number): number {
    let lo = 0;
    let hi = this.sortedPositions.length;
    while (lo < hi) {
      const mid = (lo + hi) >> 1;
      if (this.sortedPositions[mid] < target) lo = mid + 1;
      else hi = mid;
    }
    return lo;
  }
}

Virtual nodes (the virtualNodes parameter) solve the uneven distribution problem that arises from physical nodes landing in a concentrated arc on the ring. With 150 virtual nodes per shard, load variation stays within a few percent in practice.

Hash-based sharding trades range query efficiency for write uniformity. Queries that need multiple adjacent records must scatter to all shards, gather results, and merge. That is acceptable when most queries are point lookups keyed on the shard key and range scans are rare or bounded.

Directory-Based Sharding

Directory-based sharding uses a separate lookup service to resolve shard assignments. Instead of computing the shard from the key, the application asks a directory: “where does record 42 live?” The directory returns the shard location.

interface DirectoryEntry {
  shardKey: string;
  shardId: string;
  createdAt: Date;
}

class DirectoryShardRouter implements ShardRouter {
  private readonly directory: Map<string, ShardConnection>;
  private readonly shards: Map<string, ShardConnection>;
  // Local cache to avoid a directory lookup on every read
  private readonly cache: Map<string, ShardConnection> = new Map();

  constructor(
    shards: ShardConnection[],
    initialMappings: Array<{ shardKey: string; shardId: string }>
  ) {
    this.shards = new Map(shards.map((s) => [s.id, s]));
    this.directory = new Map();
    for (const mapping of initialMappings) {
      const shard = this.shards.get(mapping.shardId);
      if (shard) this.directory.set(mapping.shardKey, shard);
    }
  }

  getShard(shardKey: string | number): ShardConnection {
    const key = String(shardKey);
    // Check local cache first
    const cached = this.cache.get(key);
    if (cached) return cached;

    const shard = this.directory.get(key);
    if (!shard) throw new Error(`No shard mapping for key: ${key}`);

    this.cache.set(key, shard);
    return shard;
  }

  getAllShards(): ShardConnection[] {
    return [...this.shards.values()];
  }

  // Reassign a key to a different shard without recomputing a formula
  reassign(shardKey: string, newShardId: string): void {
    const shard = this.shards.get(newShardId);
    if (!shard) throw new Error(`Unknown shard: ${newShardId}`);
    this.directory.set(shardKey, shard);
    this.cache.delete(shardKey); // Invalidate cache
  }
}

The directory lookup is the key advantage: you can move any record to any shard by updating one row in the directory table. There is no formula to re-derive, no hash ring to rebalance. This makes resharding surgical rather than bulk.

The tradeoff is that the directory becomes a dependency. If it is unavailable, no queries can be routed. The directory must be highly available, cached aggressively at the application layer, and treated as critical infrastructure. In practice most production systems using this pattern store the directory in a fast key-value store (Redis, etcd, or a small replicated relational table) with aggressive local caching and a fallback to a secondary directory replica.

Directory-based sharding also enables non-uniform placement decisions. A single large tenant can be given a dedicated shard. Hot accounts can be migrated to faster hardware without touching anything else. That flexibility is hard to achieve with purely formulaic approaches.

Shard Key Selection

The shard key decision is permanent in any scheme that embeds the key into the data model. Get it wrong and every query is a cross-shard scatter, or every write hits the same shard.

The selection criteria, in priority order:

Cardinality. The shard key must have enough distinct values to distribute data across shards. A boolean field has two values. An enum with five options is nearly as bad. User ID, tenant ID, and document ID typically have high enough cardinality.

Write distribution. The key must not produce hotspots under your write pattern. Sequential IDs produce all-writes-to-the-hot-shard on range schemes. Timestamps produce the same problem. If the natural key is monotonic, either hash it or use a synthetic random-prefix key.

Query co-location. Queries that need to run in a single database transaction must operate on data sharing the same shard key value, or be prepared to span shards with distributed transactions (expensive) or be restructured to avoid the cross-shard join entirely. The shard key should align with the most common query boundary.

Anti-patterns to avoid:

// Bad: Sequential auto-increment as shard key with range sharding
// All inserts hit the highest-range shard
interface BadOrder {
  orderId: number; // Auto-increment — monotonically increasing
  userId: string;
  amount: number;
}

// Bad: Low-cardinality field as shard key
interface BadUser {
  userId: string;
  accountTier: "free" | "pro" | "enterprise"; // 3 values — maps to 3 shards maximum
  email: string;
}

// Better: Composite key that combines tenant context with a local identifier
interface BetterOrder {
  // Shard on userId — co-locates all orders for a user on one shard
  // Add random UUID suffix if write volume per user is very high
  orderId: string; // `${userId}:${uuidv4()}`
  userId: string;
  amount: number;
}

In multi-tenant SaaS, tenant ID is almost always the right shard key. It co-locates all data for a tenant, avoids cross-shard queries for tenant-scoped operations, and allows large tenants to be migrated to dedicated shards using directory-based reassignment.

Resharding and Rebalancing

Sharding decisions made at launch rarely survive unchanged. Usage patterns shift, shard sizes diverge, and new shards must be added to handle growth. Resharding is the process of moving data between shards while the system stays online.

Virtual node split. In consistent hashing, a large shard can be split by adding virtual nodes for a new physical shard that takes over a slice of the existing shard’s key space. The split moves only the data in that slice.

Split-merge operations. For range-based sharding, a large range shard can be split in two by choosing a midpoint, migrating the upper half to a new shard, updating routing tables, and then marking the upper half of the original range as read-only during migration. Merging two small shards works in reverse.

The critical constraint during resharding is dual-read. The application must be able to read from both the old and new shard for a key until migration is confirmed complete and routing tables are updated.

class ReshardingCoordinator {
  private readonly sourceRouter: ShardRouter;
  private readonly targetRouter: ShardRouter;
  private readonly migratingKeys: Set<string> = new Set();

  constructor(source: ShardRouter, target: ShardRouter) {
    this.sourceRouter = source;
    this.targetRouter = target;
  }

  async readWithFallback(shardKey: string): Promise<ShardConnection> {
    if (this.migratingKeys.has(shardKey)) {
      // During migration: try target first, fall back to source
      try {
        return this.targetRouter.getShard(shardKey);
      } catch {
        return this.sourceRouter.getShard(shardKey);
      }
    }
    return this.sourceRouter.getShard(shardKey);
  }

  markKeyMigrating(shardKey: string): void {
    this.migratingKeys.add(shardKey);
  }

  markKeyMigrated(shardKey: string): void {
    this.migratingKeys.delete(shardKey);
  }
}

Cross-Shard Queries and Distributed Joins

Cross-shard queries are the cost you pay for horizontal scale. When a query cannot be resolved from a single shard, the application layer must scatter the query to multiple shards, collect the results, and merge them.

async function scatterGatherQuery<T>(
  shards: ShardConnection[],
  query: (shard: ShardConnection) => Promise<T[]>,
  merge: (results: T[][]) => T[]
): Promise<T[]> {
  // Fan out in parallel — do not serialize shard queries
  const resultSets = await Promise.all(shards.map(query));
  return merge(resultSets);
}

// Example: paginated query across all user shards
async function getRecentOrders(
  router: ShardRouter,
  afterTimestamp: Date,
  limit: number
): Promise<Order[]> {
  const allShards = router.getAllShards();

  return scatterGatherQuery(
    allShards,
    async (shard) => {
      // Each shard returns its own top-N by timestamp
      return queryOrders(shard, { afterTimestamp, limit });
    },
    (results) => {
      // Merge and re-sort, then take global top-N
      return results
        .flat()
        .sort((a, b) => b.createdAt.getTime() - a.createdAt.getTime())
        .slice(0, limit);
    }
  );
}

Scatter-gather is acceptable for infrequent analytics queries. For hot paths, cross-shard queries signal a shard key choice that doesn’t match the primary query pattern. The fix is usually data denormalization: duplicate the records each shard needs rather than joining across shard boundaries.

Distributed transactions (writes that span multiple shards) are expensive and operationally fragile. Two-phase commit adds latency and requires a transaction coordinator that becomes a single point of failure. The standard recommendation is to design the data model so that mutations affecting multiple entities either live on the same shard (by choosing the right shard key) or can be expressed as eventually consistent operations.

Production Implementations

Instagram (user ID modulo). Instagram shards Postgres using user ID as the shard key with a modulo strategy. All of a user’s photos, follows, and feed data live on the same shard. Cross-user operations (like feed generation) are handled by pre-computed materialized views rather than runtime joins. The modulo approach is simple to reason about and operationally transparent, at the cost of needing a complete rehash when adding shards.

Discord (snowflake ID epoch-based). Discord’s message IDs are snowflake IDs: a 64-bit integer encoding a millisecond-precision timestamp, a worker ID, and a sequence number. The leading bits are the timestamp, which makes the IDs sortable and time-bucketed by construction. Discord partitions messages by channel ID using consistent hashing, but the snowflake structure means per-channel message ranges are naturally range-queryable within a shard without additional indexing overhead. The epoch-based ID also allows Discord to derive approximate creation time from an ID without a database round-trip.

Vitess (range-based with vtgate routing). Vitess adds a sharding layer on top of MySQL. The vtgate component handles query routing and query rewriting. Sharding is range-based: each shard owns a contiguous range of the VIndex (the sharding key function output). The VSchema defines which columns map to which vindexes, and vtgate rewrites queries to route to the correct shards before they hit MySQL. Vitess handles resharding with online schema changes and a “vertical split” operation that can move a table to a new keyspace while keeping the system live. This is one of the more operationally mature sharding solutions available for relational workloads.

Tradeoffs Comparison

Strategy	Write Distribution	Range Queries	Resharding Cost	Operational Complexity	Best Fit
Range-based	Uneven (hotspot risk on monotonic keys)	Excellent (single-shard range scans)	Medium (split at boundary, migrate half)	Low (deterministic routing)	Time-series, geo-partitioned data, sequential data with non-monotonic access
Hash-based (modulo)	Uniform	Poor (scatter-gather required)	Very high (full rehash on topology change)	Low	Simple point-lookup workloads with fixed shard count
Consistent hashing	Uniform	Poor (scatter-gather required)	Low (only affected slice migrates)	Medium (virtual node management)	High-growth systems, user-keyed data, cloud-native deployments
Directory-based	Configurable	Configurable	Very low (update directory row)	High (directory is critical infrastructure, must be HA)	Multi-tenant SaaS, large-tenant isolation, heterogeneous hardware
Composite (range + hash prefix)	Good	Good within prefix	Medium	Medium	Time-series with high write rate, partitioned by both time and entity

Production Considerations

Hotspot detection. Every sharding setup eventually produces hotspots, either from shard key cardinality issues or organic growth in specific key ranges. Monitor per-shard write throughput, replication lag, and query latency independently. A single shard running at 90% CPU while others idle at 20% is the canonical hotspot signature. Set alerts on per-shard metric divergence rather than cluster-wide averages — cluster averages hide the problem.

Shard size limits. Define a maximum shard size before you deploy. A common threshold is 1 TB per shard for Postgres and MySQL. When a shard crosses 70% of the limit, begin split planning before the shard is actually full. Emergency resharding on a live shard at capacity is the worst time to do it.

Connection pool management. Each shard requires its own connection pool. Applications that connect to many shards can exhaust file descriptors or overwhelm connection limits. Use a connection proxy (PgBouncer for Postgres, ProxySQL for MySQL) in front of each shard. Monitor connection pool saturation per shard as a leading indicator of throughput ceiling.

Schema migrations across shards. Running a DDL migration on a sharded database means running it on every shard, coordinating the rollout, and handling the window during which different shards have different schemas. Vitess has tooling for this. For raw sharded Postgres or MySQL, use online schema change tools (pt-online-schema-change, pgroll, or gh-ost) and apply changes shard-by-shard during low-traffic windows, with the application tolerating both old and new schema shapes simultaneously during the migration window.

Testing shard routing logic. Routing bugs that send writes to the wrong shard are silent data corruption. Test the router with property-based tests that verify: every key routes to exactly one shard, no key is unroutable, the same key routes to the same shard across router instantiations, and resharding events produce valid routing tables with no key gaps. The routing layer is too critical for manual testing only.

The choice of sharding strategy is upstream of every other scaling decision in a distributed system. Range works when your query pattern aligns with natural key order. Hash works when writes must be uniform and range queries are rare. Directory works when you need flexibility above everything else. Most production systems combine two: consistent hashing for the default routing, with directory overrides for large tenants that need custom placement. That combination covers the majority of real-world requirements without the operational overhead of a pure directory approach at scale.