Database Sharding & Scaling Strategies

Sharding is the last scaling option you should reach for, because it is the one you cannot easily undo. Every query afterwards must either name the shard key or pay to visit every shard.

Before sharding#

Native partitioning deserves particular attention, because it is often mistaken for sharding. It splits one table across several physical partitions within the same database — by date range, typically — so queries touching recent data scan a fraction of the rows and dropping old data is a partition drop rather than a long DELETE. It requires no application change at all.

Read replicas#

public class ReadWriteRoutingDataSource extends AbstractRoutingDataSource {
    @Override
    protected Object determineCurrentLookupKey() {
        // Spring marks read-only transactions; route those to a replica.
        return TransactionSynchronizationManager.isCurrentTransactionReadOnly()
                ? "replica" : "primary";
    }
}
 
@Configuration
public class DataSourceConfig {
    @Bean
    public DataSource dataSource(@Qualifier("primary") DataSource primary,
                                 @Qualifier("replica") DataSource replica) {
        var routing = new ReadWriteRoutingDataSource();
        routing.setTargetDataSources(Map.of("primary", primary, "replica", replica));
        routing.setDefaultTargetDataSource(primary);
        // Lazy connection acquisition matters: the routing decision must be
        // made after the transaction's read-only flag is set, not before.
        return new LazyConnectionDataSourceProxy(routing);
    }
}

@Transactional(readOnly = true)
public Page<OrderSummary> search(OrderFilter filter, Pageable pageable) {
    return orders.findAll(filter.toSpecification(), pageable);
}

The trap is replication lag. A write to the primary followed immediately by a read from a replica may not see it — the classic "I saved it and it disappeared" bug. Route reads that must observe a just-completed write to the primary, either by omitting readOnly on that path or with an explicit routing hint.

Choosing a shard key#

This is the decision you live with. Three properties matter.

Cardinality must be high enough for even distribution. Sharding by country puts most of your data on one shard; sharding by customer id does not.

Query alignment — the key should appear in most queries. If nearly every query filters by tenant_id, sharding on it means nearly every query hits one shard. If queries mostly filter by something else, every query scatters.

Stability — a row should never need to move. Sharding on a mutable column means an update becomes a cross-shard delete and insert, which is not atomic and is a genuinely unpleasant thing to operate.

Consistent hashing is what makes hash sharding operable. Plain hash(key) % n remaps almost every key when n changes; consistent hashing with virtual nodes moves only 1/n of the data.

ShardingSphere#

dataSources:
  ds0: { url: jdbc:postgresql://shard-0:5432/orders, ... }
  ds1: { url: jdbc:postgresql://shard-1:5432/orders, ... }
  ds2: { url: jdbc:postgresql://shard-2:5432/orders, ... }
 
rules:
  - !SHARDING
    tables:
      orders:
        actualDataNodes: ds${0..2}.orders
        databaseStrategy:
          standard:
            shardingColumn: customer_id
            shardingAlgorithmName: customer_hash
      order_lines:
        actualDataNodes: ds${0..2}.order_lines
        databaseStrategy:
          standard:
            # Same key as orders, so an order and its lines always live
            # together and the join never crosses a shard.
            shardingColumn: customer_id
            shardingAlgorithmName: customer_hash
    bindingTables:
      - orders,order_lines
    # Small reference data replicated to every shard so joins stay local.
    broadcastTables:
      - currencies
      - countries
    shardingAlgorithms:
      customer_hash:
        type: HASH_MOD
        props: { sharding-count: 3 }

ShardingSphere-JDBC sits behind the JDBC driver, so application code and JPA are unchanged — it parses the SQL, routes to the right shard, and merges results. The three configuration ideas above are the ones that matter: binding tables keep related data co-located, broadcast tables replicate small lookups everywhere, and the sharding column must be in the query or it scatters.

Cross-shard queries#

Some queries cannot be shard-local: "all orders placed today across every tenant", or any report. Three approaches, in increasing order of preference.

Scatter-gather queries every shard and merges. It works and it scales badly — latency becomes the slowest shard's latency, and load multiplies by shard count. Acceptable for rare administrative queries, not for anything on a user path.

Broadcast tables replicate small, slow-changing reference data to every shard so joins stay local. Cheap and effective for currencies, categories, feature configuration.

A separate read store is usually the right answer for reporting and search. Stream changes into Elasticsearch or a warehouse and serve cross-cutting queries from there. The sharded database handles transactional work scoped by the shard key; the read store handles everything else.

Resharding without downtime#

Step four is the one people skip and the one that matters. Comparing counts and checksums per key range before switching reads is the difference between a migration and an incident. Build the verification tooling before the migration, not during it.

Plan for extra shards from the start using virtual nodes: shard into 256 logical buckets mapped onto 3 physical databases. Growing to 6 means remapping buckets, which moves data but changes no key mapping — dramatically simpler than rehashing.

What to take away#

Do not shard until indexing, caching, replicas, archiving and a bigger instance are exhausted. Choose a shard key with high cardinality that appears in most queries and never changes. Co-locate related tables, broadcast small lookups, and move cross-cutting queries to a separate read store. Then plan resharding with dual-write and verification before you need it.