Database Sharding

• Sharding : horizontal partitioning that splits a dataset across multiple database instances, each holding a disjoint subset of rows.
• Shard key : the column that determines which shard a row lives on; its choice governs load balance and query efficiency.
• Hash-based sharding : hashes the key to a shard for even distribution but loses range locality.
• Range-based sharding : maps key ranges to shards for range scans but risks hotspots.
• Cross-shard query : a query that must touch multiple shards (scatter-gather), which is slow and complex.
• Resharding : adding capacity by adding shards, which requires moving data and is operationally challenging.

What is database sharding?

Sharding is horizontal partitioning: it splits rows across multiple database instances, each holding a disjoint slice. This lets you scale writes and storage linearly by adding shards, which a single primary database cannot do. It differs from read replicas, which copy the whole dataset to scale reads only.

How do you choose a shard key?

The shard key is the single most important decision. A good key spreads data and load evenly and keeps common queries on a single shard. For a bank, account id or customer id is usually strong because most operations are scoped to one account.

package com.bank.sharding;

public class ShardRouter {
    public int shardFor(String accountId, int shardCount) {
        return Math.abs(accountId.hashCode()) % shardCount;
    }
}

What are hash-based vs range-based sharding?

Hash-based sharding hashes the key to a shard for even distribution but loses range locality. Range-based sharding maps key ranges to shards, which allows range scans but risks hotspots if traffic concentrates on one range.

package com.bank.sharding;

public class ShardingStrategies {
    // Hash-based: even distribution, no range scans
    public int hashBased(String key, int shards) {
        return Math.abs(key.hashCode()) % shards;
    }

    // Range-based: range scans, but hotspot risk
    public int rangeBased(String accountId, int shards) {
        char first = accountId.charAt(0);
        if (first <= 'F') return 0;
        if (first <= 'M') return 1;
        return 2;
    }
}

Why are cross-shard queries expensive?

A query that must touch many shards, like "sum balances across all customers," has to scatter to every shard and gather the partial results, which is slow and complex. Cross-shard transactions are worse, requiring distributed-transaction protocols.

package com.bank.sharding;

import java.math.BigDecimal;
import java.util.List;

public class ScatterGather {
    public BigDecimal totalBalance(List<Shard> shards) {
        return shards.stream()
            .map(Shard::sumBalances)            // scatter: ask each shard
            .reduce(BigDecimal.ZERO, BigDecimal::add); // gather: combine
    }
}

What makes resharding so challenging?

Adding a shard with naive modulo hashing remaps almost all rows. The standard mitigation is consistent hashing, which moves only a small fraction, or virtual shards, where many logical shards are pre-created and simply reassigned to new physical nodes without moving the hashing boundaries.

package com.bank.sharding;

public class VirtualSharding {
    // Pre-create 256 virtual shards, map to 4 physical nodes
    // Adding a 5th node: just reassign some virtual shards
    // No rehashing, no data movement within a virtual shard
    public int physicalNode(int virtualShard, int physicalNodes) {
        return virtualShard % physicalNodes;
    }
}

What shard keys should you avoid?

Avoid keys that create hotspots, such as a monotonically increasing timestamp, which sends all new writes to one shard. Avoid keys with low cardinality, like a country code with only a few values, which limits parallelism.

package com.bank.sharding;

public class BadShardKeys {
    // BAD: timestamp (all new writes go to latest shard)
    // BAD: country_code (only 5 values = 5 shards max)
    // GOOD: account_id (high cardinality, even distribution)
    // GOOD: customer_id (scoped queries stay single-shard)
}

How is sharding different from partitioning and replication?

Sharding splits rows across different database instances for write scalability. Partitioning splits rows within one database for query performance. Replication copies all rows to additional instances for read scalability and availability. These are complementary: you can shard for writes, replicate each shard for reads, and partition within each shard for query speed.

What should a senior engineer know?

A senior engineer plans the shard key before it is desperately needed, because retrofitting onto a live banking system is one of the riskiest migrations. They also know to test with realistic data distribution to verify the key actually balances load, and they monitor shard sizes and hotspots in production.