Database Sharding Strategies Explained with Real Examples

Sharding gets proposed too early. The typical trajectory: a team hits some slow queries, someone suggests sharding, and suddenly you are redesigning your data layer for a problem that a read replica and a few indexes would have solved. Sharding is a last resort for write scale, not a general-purpose performance fix.

That said, when you genuinely need it, sharding is not optional. This guide covers the decision, the patterns, and the operational cost, with concrete examples from multi-tenant SaaS, e-commerce, and social platforms.

When Sharding Becomes Necessary

A single PostgreSQL or MySQL instance, tuned correctly, will handle tens of thousands of transactions per second and terabytes of data. Before you reach that ceiling you will hit other limits first: connection count, memory for working sets, CPU for complex aggregations. Many of these have solutions that are far cheaper than sharding.

The checklist of things to exhaust before sharding:

• Read replicas for read-heavy workloads
• Connection pooling (PgBouncer, ProxySQL) for connection exhaustion
• Vertical scaling (larger instance class)
• Partitioning within a single instance (PostgreSQL native table partitioning)
• Caching at the application layer for hot read paths
• Archiving cold data to cheaper storage

Sharding makes sense when write throughput exceeds what a single primary can handle, or when your dataset is too large to fit on a single machine at the cost structure you need. These are the two real triggers.

For multi-tenant SaaS, there is a third trigger: tenant isolation. A single large tenant causing I/O pressure on shared infrastructure is a product problem, not just a performance problem. Sharding by tenant solves this even when aggregate load is manageable.

Horizontal vs. Vertical Partitioning

These terms are used loosely, so the definitions matter.

Vertical partitioning splits columns. A users table with 40 columns might be split into users (core identity fields, read on every request) and user_preferences (configuration fields, read rarely). Both tables still live on one database. This is normalization at the schema level, not sharding.

Horizontal partitioning (sharding) splits rows. The same users table is split so that users 1-1M live on shard A and users 1M-2M live on shard B. Each shard is a separate database instance with its own storage, compute, and connections.

This guide is about horizontal partitioning. Vertical partitioning is often worth doing first, but it has a much lower ceiling.

Choosing a Shard Key

The shard key is the column (or composite of columns) used to determine which shard a row belongs to. This is the most consequential decision in your sharding design. A bad shard key is very expensive to change later.

Properties of a Good Shard Key

High cardinality: If your shard key has ten distinct values and you have twenty shards, half your shards are empty. User IDs, order IDs, and tenant IDs are good. Booleans and enum columns are bad.

Even distribution: A shard key that produces hot spots defeats the purpose. If 80% of your writes go to one user or one tenant, concentrating them on one shard does not help.

Query alignment: Most queries should be satisfiable within a single shard. If you frequently query across multiple shard key values, you are doing cross-shard joins on every request, and performance will be worse than before sharding.

Immutability: Changing the shard key for a row means moving it to a different shard. This is an expensive operation. The shard key should be something that does not change: user ID, order ID, created timestamp. Not customer email, not status, not country.

Shard Key Examples by Domain

Multi-tenant SaaS: tenant_id is the natural choice. All data for a tenant stays on one shard, cross-tenant queries are rare, and tenant isolation is built in.

E-commerce: customer_id for the order management side. Orders and their line items, returns, and address history all travel together. If you need to query “all orders in a region” frequently, this becomes a cross-shard problem, and you may need a secondary index table.

Social platform: user_id keeps a user’s posts, follows, and activity on one shard. The complication is the social graph: when user A on shard 1 follows user B on shard 2, rendering A’s feed requires data from both shards. Most large platforms accept this and handle it at the application layer.

Sharding Patterns

Range-Based Sharding

Assign ranges of the shard key to specific shards. Users 1-1,000,000 go to shard 1. Users 1,000,001-2,000,000 go to shard 2.

interface ShardConfig {
  shardId: string;
  minKey: number;
  maxKey: number;
  connectionString: string;
}

const SHARD_RANGES: ShardConfig[] = [
  { shardId: "shard-1", minKey: 0,         maxKey: 1_000_000,  connectionString: process.env.SHARD_1_URL! },
  { shardId: "shard-2", minKey: 1_000_001, maxKey: 2_000_000,  connectionString: process.env.SHARD_2_URL! },
  { shardId: "shard-3", minKey: 2_000_001, maxKey: 3_000_000,  connectionString: process.env.SHARD_3_URL! },
];

function getShardForKey(key: number): ShardConfig {
  const shard = SHARD_RANGES.find(s => key >= s.minKey && key <= s.maxKey);
  if (!shard) throw new Error(`No shard configured for key: ${key}`);
  return shard;
}

Range-based sharding has one major advantage: range queries stay within a single shard. “Give me all orders from customer IDs 50,000 to 60,000” hits exactly one shard.

The problem is uneven growth. Early users are often more active than later users. Shard 1, which holds the oldest accounts, may be significantly hotter than shard 3. You can mitigate this by assigning ranges non-monotonically, but it adds operational complexity.

Use range-based sharding when: your queries are heavily range-oriented and you can reason about how data will grow over time. Time-series data is a good fit; auto-increment primary keys are risky.

Hash-Based Sharding

Apply a hash function to the shard key and use the result to select a shard. The distribution is even by design.

import { createHash } from "crypto";

function hashKey(key: string): number {
  const hash = createHash("sha256").update(key).digest("hex");
  // take the first 8 hex chars as a 32-bit integer
  return parseInt(hash.substring(0, 8), 16);
}

function getShardIndex(key: string, shardCount: number): number {
  return hashKey(key) % shardCount;
}

function getConnectionForTenant(tenantId: string, shards: string[]): string {
  const index = getShardIndex(tenantId, shards.length);
  return shards[index];
}

Hash-based sharding distributes writes evenly and avoids hot spots. The tradeoff: range queries are now cross-shard. “All orders placed in November” requires querying every shard and merging results at the application layer.

The bigger problem with naive modulo hashing is resharding. If you have 4 shards and add a 5th, key % 5 produces completely different assignments than key % 4. Almost every row in your database needs to move. This is why consistent hashing exists.

Use hash-based sharding when: your queries are point lookups (fetch by ID) and distribution is more important than range query performance.

Directory-Based Sharding

Maintain a separate lookup table that maps each shard key value (or range) to a shard. The routing layer consults this table on every request.

// The directory maps tenant IDs to shard identifiers
// This table lives in a dedicated metadata database (not sharded)
interface ShardDirectory {
  tenantId: string;
  shardId: string;
  createdAt: Date;
}

class ShardRouter {
  private cache = new Map<string, string>();

  async getShardForTenant(tenantId: string): Promise<string> {
    // check local cache first — directory lookups are frequent
    if (this.cache.has(tenantId)) {
      return this.cache.get(tenantId)!;
    }

    const entry = await metadataDb.shardDirectory.findUnique({
      where: { tenantId },
    });

    if (!entry) throw new Error(`Tenant ${tenantId} not found in shard directory`);

    // cache with TTL — tenant shard assignments rarely change
    this.cache.set(tenantId, entry.shardId);
    setTimeout(() => this.cache.delete(tenantId), 5 * 60 * 1000);

    return entry.shardId;
  }
}

Directory-based sharding gives you maximum flexibility. You can move a tenant to a different shard by updating one row in the directory table. Large tenants can get dedicated shards. You can split a hot shard without touching the others.

You pay for this with an extra round trip (mitigated by caching) and with the directory itself becoming a critical dependency. If the directory database is unavailable, you cannot route any requests. Multi-region replication and aggressive caching are not optional here.

Use directory-based sharding when: you need operational flexibility, especially in multi-tenant SaaS where tenant size varies dramatically and you need to move tenants between shards over time.

Geographic Sharding

Route data to shards based on geographic location. EU customers go to an EU shard, US customers go to a US shard.

This is often driven by compliance requirements (GDPR data residency) rather than performance. The routing logic is similar to directory-based sharding, but the shard assignment is determined by user location at signup and is essentially immutable (moving a user between geographic shards means crossing data residency boundaries, which is the thing you are trying to avoid).

The operational challenge: your application now needs to handle shards with different data visibility. A support engineer in the US cannot query EU customer data without the appropriate access controls. Your admin tooling, data pipelines, and observability need to be geo-aware.

Consistent Hashing

The resharding problem with naive modulo hashing is severe enough that most production systems use consistent hashing instead.

In consistent hashing, both shard keys and shards are mapped onto a ring (a 0 to 2^32 circle). A key is assigned to the nearest shard clockwise on the ring.

import { createHash } from "crypto";

class ConsistentHashRing {
  private ring = new Map<number, string>(); // position -> shardId
  private sortedPositions: number[] = [];
  private readonly virtualNodes: number;

  constructor(virtualNodes = 150) {
    // more virtual nodes = more even distribution, more memory
    this.virtualNodes = virtualNodes;
  }

  addShard(shardId: string): void {
    for (let i = 0; i < this.virtualNodes; i++) {
      const position = this.hash(`${shardId}:${i}`);
      this.ring.set(position, shardId);
    }
    this.sortedPositions = [...this.ring.keys()].sort((a, b) => a - b);
  }

  removeShard(shardId: string): void {
    for (let i = 0; i < this.virtualNodes; i++) {
      const position = this.hash(`${shardId}:${i}`);
      this.ring.delete(position);
    }
    this.sortedPositions = [...this.ring.keys()].sort((a, b) => a - b);
  }

  getShard(key: string): string {
    if (this.ring.size === 0) throw new Error("No shards in ring");

    const position = this.hash(key);
    // find the first shard clockwise from this position
    const idx = this.sortedPositions.findIndex(p => p >= position);
    const targetPos = idx === -1
      ? this.sortedPositions[0]   // wrap around to start of ring
      : this.sortedPositions[idx];

    return this.ring.get(targetPos)!;
  }

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

const ring = new ConsistentHashRing(150);
ring.addShard("shard-1");
ring.addShard("shard-2");
ring.addShard("shard-3");
ring.addShard("shard-4");

// Adding shard-5 only redistributes ~20% of keys, not all of them
ring.addShard("shard-5");

When you add a new shard to a consistent hash ring with N existing shards, only 1/(N+1) of the keys need to move. When you remove a shard, only that shard’s keys need to be redistributed to neighbors.

Virtual nodes solve the uneven distribution problem that naive consistent hashing has with small numbers of shards. With 150 virtual nodes per shard, distribution stays within a few percent of even.

Cross-Shard Queries and Joins

Cross-shard queries are the main operational cost of sharding. They require scatter-gather: fan out the query to all relevant shards, collect results, merge and sort at the application layer.

interface OrderSummary {
  orderId: string;
  customerId: string;
  total: number;
  createdAt: Date;
}

// This is expensive: every shard, every time
async function getRecentOrders(limit: number): Promise<OrderSummary[]> {
  const shardConnections = getAllShardConnections();

  // fan out to all shards in parallel
  const results = await Promise.all(
    shardConnections.map(db =>
      db.query<OrderSummary>(
        `SELECT order_id, customer_id, total, created_at
         FROM orders
         WHERE created_at > NOW() - INTERVAL '24 hours'
         ORDER BY created_at DESC
         LIMIT $1`,
        [limit]
      )
    )
  );

  // merge and re-sort: each shard returned its local top N,
  // but the global top N requires sorting all results together
  return results
    .flat()
    .sort((a, b) => b.createdAt.getTime() - a.createdAt.getTime())
    .slice(0, limit);
}

The merge step is correctness-critical. If you ask each shard for the top 10 results and merge, you get the correct global top 10. If you ask for page 2 (rows 11-20), you need to fetch at least the top 20 from every shard and re-paginate, because row 11 globally might be row 1 on some shards. Cursor-based pagination across shards is significantly more complex than offset pagination.

For queries that are inherently cross-shard (analytics, admin reporting, billing aggregations), consider a separate read path: a data warehouse or a denormalized reporting table that aggregates across shards. This keeps your sharded primary database doing what it does well (high-throughput keyed reads and writes) while offloading cross-shard analytical queries to a system designed for them.

Resharding and Zero-Downtime Migrations

At some point your shard configuration needs to change: you have grown past your original shard count, a shard is running hot, or a tenant needs to move to dedicated infrastructure.

The naive approach (stop writes, migrate data, update routing, restart) works but requires downtime. For most systems, this is not acceptable.

The double-write pattern avoids downtime at the cost of complexity:

1. Add the new shard to your routing layer. Mark it as “write-only” initially.
2. Begin writing all new data to both the old shard and the new shard.
3. Backfill historical data from the old shard to the new shard in the background, using a low-priority process that does not impact production traffic.
4. Once backfill is complete, verify consistency between shards.
5. Switch reads to the new shard.
6. Remove double-writes. The old shard is now the backup.
7. After a validation period, decommission the old shard.

class DoubleWriteShardRouter {
  constructor(
    private readonly primaryShard: DatabaseConnection,
    private readonly newShard: DatabaseConnection,
    private readonly isDoubleWriteEnabled: () => boolean,
    private readonly isNewShardReadsEnabled: () => boolean
  ) {}

  async write(query: string, params: unknown[]): Promise<void> {
    // primary write is always synchronous
    await this.primaryShard.execute(query, params);

    if (this.isDoubleWriteEnabled()) {
      // new shard write can fail without blocking the request
      // failures are logged and retried asynchronously
      this.newShard.execute(query, params).catch(err => {
        logger.error({ err }, "Double-write to new shard failed, queuing retry");
        retryQueue.push({ query, params });
      });
    }
  }

  async read(query: string, params: unknown[]): Promise<unknown[]> {
    const db = this.isNewShardReadsEnabled()
      ? this.newShard
      : this.primaryShard;
    return db.execute(query, params);
  }
}

The feature flags (isDoubleWriteEnabled, isNewShardReadsEnabled) let you control the cutover incrementally and roll back if you find consistency problems.

This is solvable, but it is significantly more complex than the initial sharding setup. Budget time for testing, observability, and rollback.

The Operational Complexity You Are Accepting

Sharding is not a technical decision in isolation. It reshapes your entire engineering operation.

Schema migrations now require coordination across multiple databases. A column addition that took one ALTER TABLE now requires applying the migration to every shard, ideally in a rolling fashion. Tools like Flyway and Liquibase can handle this, but you need to build the orchestration.

Backup and restore must now cover every shard. Point-in-time recovery across shards is complicated by the fact that shards may not be at exactly the same transaction timestamp.

Monitoring needs to track shard health individually. A hot shard or a shard with a slow replica will not show up in aggregate dashboards. You need per-shard query latency, replication lag, and disk usage.

Transactions across shards are effectively impossible without a distributed transaction coordinator. Most teams either redesign to avoid cross-shard transactions or accept eventual consistency for the rare cases where data must cross shard boundaries.

Developer experience degrades. Local development that uses a single database now needs to simulate the full shard topology, or developers work against a simplified local setup and hit edge cases only in staging.

At Let’s Build Solutions, when sharding comes up as a proposal, the first question is always: which specific metric is exceeding the capacity of a single instance? If the answer is anything other than write throughput or total data volume, there is usually a cheaper solution worth exploring first.

Closing Thoughts

The patterns above are not interchangeable. Range-based sharding suits time-series and sequential data. Hash-based sharding suits even distribution of point-lookup workloads. Directory-based sharding suits multi-tenant SaaS where operational flexibility matters more than routing simplicity. Geographic sharding suits compliance-driven data residency.

The common mistake is choosing a sharding strategy based on elegance rather than query patterns. Before writing any sharding code, list your ten most frequent query types and check how many of them would become cross-shard. If more than two or three would, reconsider your shard key. The best shard key is the one that keeps your most common queries on a single shard, even if it means occasionally doing a scatter-gather for the less common ones.