Database Sharding Strategies: When and How to Scale Horizontally

Database Sharding Strategies: A Practical Guide to Horizontal Scaling

Your PostgreSQL database handles 10,000 queries per second beautifully. Then your application grows, the table hits 500 million rows, and suddenly queries that used to take 5ms take 500ms. Vertical scaling — bigger hardware — buys time, but database sharding is the long-term answer for horizontal scaling. Therefore, this guide covers when to shard, how to choose a sharding strategy, and the tools that make it manageable.

Sharding is conceptually simple — split one large dataset across many independent databases — but the operational reality is anything but. Once a single logical table lives on multiple physical machines, every assumption your application made about transactions, joins, and unique constraints needs re-examination. Consequently, teams that succeed treat sharding as an architectural commitment rather than a configuration flag, and they plan for the cross-shard edge cases before flipping the switch.

When to Shard (And When Not To)

Sharding adds enormous complexity to your application. Before you shard, exhaust these alternatives: optimize queries and add indexes, implement read replicas for read-heavy workloads, add caching with Redis or Valkey, archive old data to cold storage, partition tables (single database, multiple physical segments), and vertically scale your hardware. Moreover, many applications never need sharding — a properly optimized PostgreSQL instance on modern hardware can handle billions of rows and tens of thousands of queries per second.

Shard when: a single database server cannot handle your write throughput, your dataset exceeds what fits in a single server’s storage, regulatory requirements mandate data residency in specific regions, or you need sub-millisecond latency for geographically distributed users. Additionally, if your write volume exceeds what a single primary can handle and read replicas do not help (because your bottleneck is writes, not reads), sharding becomes necessary.

A useful litmus test is whether your problem is fundamentally about reads or writes. Read pressure has cheap answers — replicas, materialized views, and caching layers absorb it without changing your data model. Write pressure, by contrast, eventually saturates a single primary because every write must be durably committed in one place. When you see replication lag climbing, WAL volume overwhelming your disks, or vacuum struggling to keep up on a hot table, those are the signals that horizontal partitioning of writes — not another replica — is the real fix.

Hash Sharding vs Range Sharding

The sharding key determines which shard holds each row. Choosing the right key and strategy is the most critical decision.

HASH SHARDING:
  shard_id = hash(tenant_id) % num_shards

  Pros:
  - Even data distribution across shards
  - No hotspots (assuming good hash function)
  - Simple to implement

  Cons:
  - Range queries across shards are expensive
  - Adding shards requires resharding (rehashing all data)
  - Cannot colocate related data easily

  Best for: Multi-tenant SaaS, user-partitioned data

RANGE SHARDING:
  shard_id = range_lookup(created_date)
  Example: Jan-Mar → shard1, Apr-Jun → shard2, ...

  Pros:
  - Range queries hit a single shard
  - Easy to add new shards (just extend the range)
  - Natural data archiving (old shards become cold storage)

  Cons:
  - Hotspots on the latest shard (all new writes go there)
  - Uneven data distribution over time
  - Requires rebalancing as data grows

  Best for: Time-series data, log storage, IoT data

DIRECTORY/LOOKUP SHARDING:
  shard_id = lookup_table[entity_id]

  Pros:
  - Complete control over placement
  - Can move individual entities between shards
  - Supports complex sharding logic

  Cons:
  - Lookup table is a single point of failure
  - Extra network hop for every query
  - Lookup table itself needs to be highly available

  Best for: Complex multi-tenant systems with varying sizes

For most applications, hash sharding by tenant_id or user_id is the right starting point. It distributes data evenly and ensures all data for a single tenant lives on one shard, eliminating cross-shard queries for tenant-scoped operations.

Consistent Hashing and Avoiding Full Resharding

The naive modulo approach (hash(key) % num_shards) has a brutal failure mode: adding even one shard changes the modulus, which remaps almost every row. In practice teams avoid this with consistent hashing, where keys and shards are placed on a logical ring and each key is owned by the next shard clockwise. Adding a shard then moves only the keys in one arc of the ring rather than reshuffling everything.

An even more common production pattern is to introduce a layer of indirection through virtual buckets. You hash keys into a large fixed number of buckets — say 4,096 — and then map buckets to physical shards. Growing the cluster becomes a matter of reassigning some buckets to a new shard, which is cheap to plan and easy to reason about because the bucket count never changes.

// Virtual-bucket routing: stable bucket count, movable bucket->shard map
public class BucketShardRouter {

    private static final int VIRTUAL_BUCKETS = 4096;
    // bucketToShard is loaded from a small, replicated config store (etcd, ZooKeeper)
    private final int[] bucketToShard;

    public BucketShardRouter(int[] bucketToShard) {
        this.bucketToShard = bucketToShard;
    }

    public int routeToShard(String shardKey) {
        // Murmur/xxHash gives good distribution; avoid String.hashCode (weak)
        int bucket = Math.floorMod(
            Hashing.murmur3_32().hashUnencodedChars(shardKey).asInt(),
            VIRTUAL_BUCKETS);
        return bucketToShard[bucket];
    }
    // Rebalancing = reassign a subset of buckets and migrate only their rows.
}

This indirection is what makes online resharding tractable. Because only a fraction of buckets move during a scale-out, you can migrate their rows in the background, flip the bucket map atomically, and keep the blast radius small if something goes wrong.

Cross-Shard Queries: The Hard Problem

The moment you shard, any query that spans multiple shards becomes expensive. A simple SELECT COUNT(*) FROM orders now requires querying every shard, collecting results, and aggregating them in your application layer. Joins across shards are even worse — they require fetching data from multiple shards and joining in memory.

// Cross-shard query example with a sharding middleware
public class ShardedOrderRepository {

    private final Map shards;
    private final ShardRouter router;

    // Single-shard query: fast, simple
    public Order findByTenantAndId(String tenantId, String orderId) {
        DataSource shard = router.getShard(tenantId);
        return jdbcTemplate(shard).queryForObject(
            "SELECT * FROM orders WHERE id = ?", orderId
        );
    }

    // Cross-shard query: scatter-gather pattern
    public long countAllOrders() {
        return shards.values().parallelStream()
            .mapToLong(shard ->
                jdbcTemplate(shard).queryForObject(
                    "SELECT COUNT(*) FROM orders", Long.class
                ))
            .sum();
    }

    // Cross-shard search: fan out, merge, sort
    public List searchOrders(String query, int limit) {
        return shards.values().parallelStream()
            .flatMap(shard ->
                jdbcTemplate(shard).query(
                    "SELECT * FROM orders WHERE description ILIKE ? ORDER BY created_at DESC LIMIT ?",
                    orderMapper, "%" + query + "%", limit
                ).stream())
            .sorted(Comparator.comparing(Order::getCreatedAt).reversed())
            .limit(limit)
            .collect(Collectors.toList());
    }
}

The best strategy is to design your sharding key so that most queries hit a single shard. If you shard by tenant_id, then all queries within a tenant are single-shard queries. Cross-tenant analytics go through a separate data warehouse (like ClickHouse or BigQuery) that receives data via CDC, not through the sharded operational database. Consequently, your operational database stays fast and your analytics are handled by purpose-built tools.

Two edge cases bite teams repeatedly. First, pagination over a scatter-gather result is subtle: an OFFSET 1000 LIMIT 20 across ten shards forces each shard to return 1,020 rows so the coordinator can sort and slice — favor keyset (cursor) pagination instead. Second, globally unique identifiers can no longer come from a single auto-increment sequence; teams typically switch to UUIDv7 or a Snowflake-style ID that embeds a timestamp and shard number, preserving rough ordering without a central counter.

Sharding Tools: Vitess and Citus

You do not need to build sharding infrastructure from scratch. Vitess (originally built by YouTube for MySQL) and Citus (PostgreSQL extension) handle the hard parts — routing queries, managing schema changes across shards, and rebalancing data.

Vitess sits between your application and MySQL shards. Your application connects to Vitess as if it were a single MySQL instance. Vitess parses every query, determines which shard(s) to hit, executes the query, and merges results. It handles online resharding (splitting one shard into two without downtime) and supports VReplication for cross-shard data movement.

Citus extends PostgreSQL with distributed tables. You call SELECT create_distributed_table('orders', 'tenant_id') and Citus transparently distributes the data across worker nodes. Standard PostgreSQL queries work — Citus rewrites them into distributed query plans. It supports colocated joins (joining two tables sharded by the same key) and reference tables (small tables replicated to every shard).

The decisive design choice with either tool is colocation. When two tables share the same distribution key — orders and order_items both sharded by tenant_id — joins between them stay local to a single node and run at near single-server speed. Reference tables solve the opposite problem: small, rarely-changing lookup tables (currencies, plan tiers) get replicated to every shard so they can participate in any join without a network round trip. Getting colocation right up front is what separates a sharded system that feels fast from one that constantly fans out.

Migration Strategy: Monolith to Sharded

Never migrate to a sharded architecture in a single step. Use a phased approach:

• Phase 1: Add the sharding key to every table that will be sharded. Deploy application changes to always include this key in queries.
• Phase 2: Set up the sharding proxy (Vitess/Citus) with a single shard containing all data. Verify everything works identically.
• Phase 3: Split into two shards. Route a small percentage of tenants to the new shard. Monitor for correctness and performance.
• Phase 4: Gradually rebalance tenants across shards until load is even. Add more shards as needed.

Throughout this process, maintain the ability to roll back. Keep the original single database available until you are confident the sharded setup handles your workload correctly. Additionally, invest heavily in integration tests that exercise cross-shard scenarios — these are the queries most likely to break.

When NOT to Shard: Trade-offs and Honest Alternatives

Sharding is frequently adopted prematurely, and the cost is paid in developer velocity for years afterward. If your dataset is under a few hundred gigabytes and your write rate is in the low thousands per second, you almost certainly do not need it — a tuned primary with replicas and a cache will outlast that workload. Distributed SQL databases like CockroachDB or managed Citus can also give you horizontal scale without hand-rolled routing, trading some per-query latency for far less application complexity.

The honest downsides are concrete: cross-shard transactions either disappear (forcing you into sagas and eventual consistency) or rely on slow two-phase commit; foreign keys across shards become advisory at best; schema migrations must roll out to every shard; and operational tooling — backups, monitoring, and on-call runbooks — multiplies with shard count. Therefore, reach for sharding only after read replicas, caching, partitioning, and a hardware upgrade have genuinely failed. For the cutover mechanics that make any of this safe, the companion guide on zero-downtime migrations is worth reading alongside this one.

Related Reading:

• Zero-Downtime Database Migrations
• Connection Pooling with PgBouncer
• SQL Query Optimization for PostgreSQL
• CockroachDB Distributed SQL in Production

Resources:

• Vitess Documentation
• Citus Documentation

In conclusion, database sharding enables horizontal scaling but comes with significant complexity. Exhaust simpler alternatives first — indexing, caching, read replicas, and partitioning. When you do shard, choose hash sharding by tenant or user ID, use Vitess or Citus to handle the infrastructure, and design your queries so most operations hit a single shard.