Sharding Strategies for Large Tables: Planning Horizontal Partitioning Right

1. What sharding actually solves

Sharding splits a large table horizontally across multiple independent database instances, called shards, where each shard holds only a portion of the rows. Unlike vertical partitioning, which splits columns, or replication, which copies the entire table, sharding actually reduces the data volume per instance. That matters once a single database instance hits its limits, whether in storage space, write throughput, or index size that no longer fits in memory.

The decisive difference from simply scaling with bigger hardware: sharding scales horizontally, by adding more instances, instead of vertically through stronger single hardware. This approach has no fundamental limit, but brings considerable complexity that needs to be carefully weighed against actual need. The following sections show how to choose a shard key, which sharding strategies exist and how cross shard queries and resharding work in practice.

2. Choosing the shard key decides everything

The shard key is the column or column combination used to decide which shard a row ends up on. This decision is the single most important one in sharding, because it can only be changed afterward with an expensive data migration. A good shard key distributes data evenly across all shards, is included in most queries (to avoid cross shard queries) and never changes once a row is created.

A common mistake is choosing a shard key that changes later, for instance a customer account's status. If the shard key's value changes, the entire row has to be physically moved to a different shard, an expensive and error prone operation. For multi tenant SaaS applications, a tenant ID or customer ID is usually the natural shard key, because virtually all queries are filtered by tenant anyway and the value stays stable.

-- Good shard key: stable, present in most queries, evenly distributed
CREATE TABLE orders (
  order_id BIGINT NOT NULL,
  tenant_id INT NOT NULL,  -- shard key: stable, never changes after creation
  customer_id INT NOT NULL,
  total_amount NUMERIC(10,2),
  created_at TIMESTAMP NOT NULL,
  PRIMARY KEY (tenant_id, order_id)
);

-- Every query should include the shard key to stay single-shard
SELECT * FROM orders
WHERE tenant_id = 4821 AND created_at > '2026-07-01';

-- Anti-pattern: querying without the shard key requires
-- fanning out to every shard (avoid in hot paths)
SELECT * FROM orders WHERE customer_id = 998877;

3. Hash sharding vs. range sharding

Hash sharding applies a hash function to the shard key and uses it to determine the target shard, usually via a modulo over the number of shards. This sharding method distributes data very evenly, because a good hash function scatters values essentially at random. The downside: range queries over the shard key (for instance "all orders from customer 100 to 200") are no longer possible, because neighboring values can end up on completely different shards.

Range sharding splits the shard key's value range into contiguous intervals, for instance customer IDs 1 through 1,000,000 on shard 1, 1,000,001 through 2,000,000 on shard 2. This sharding method allows efficient range queries but carries a risk: uneven data distribution when certain value ranges see significantly more activity than others, for instance with time based shard keys, where the most recent range always carries the most write load.

-- Hash-based shard assignment (application layer, pseudocode as SQL comment)
-- shard_id = hash(tenant_id) % number_of_shards

-- Range-based sharding: explicit shard map table
CREATE TABLE shard_map (
  range_start BIGINT NOT NULL,
  range_end BIGINT NOT NULL,
  shard_id SMALLINT NOT NULL
);

INSERT INTO shard_map (range_start, range_end, shard_id) VALUES
  (1, 1000000, 1),
  (1000001, 2000000, 2),
  (2000001, 3000000, 3);

-- Lookup which shard a given tenant_id belongs to
SELECT shard_id FROM shard_map
WHERE 1542871 BETWEEN range_start AND range_end;

4. Detecting and avoiding hotspots

A hotspot occurs when a single shard carries significantly more load than the others, usually because the shard key is unevenly distributed. A classic example: a monotonically increasing order value used as a shard key concentrates all new write operations on the most recently created shard, while older shards effectively see only read operations. This pattern undermines the entire purpose of sharding, because write load simply is not distributed evenly.

A hotspot can be detected by monitoring requests per shard, usually by aggregating database metrics per instance. A good early warning sign is significantly higher CPU or I/O utilization on a single shard compared to the average across all shards. The problem can usually be avoided with a composite shard key that combines a time based component with an evenly distributed component, for instance tenant ID plus timestamp instead of a plain timestamp.

5. Cross shard queries and their cost

A query that does not include the shard key in its WHERE clause has to be fanned out to every shard (scatter gather), and the results then have to be merged in the application layer. These cross shard queries are significantly more expensive in sharding than single shard queries, because they inherit the latency of the slowest shard and generate additional network round trips.

Aggregations across all shards (for instance a global sum) are a particularly expensive case of cross shard queries, because every shard has to compute a partial sum and the application then merges these partial sums. For frequently needed cross shard aggregations, a separate analytics system (such as a data warehouse) that periodically consolidates data from all shards is worth it, instead of repeating cross shard queries in live operation.

-- Cross-shard aggregation: each shard computes a partial sum,
-- application layer combines the results
-- Shard 1
SELECT tenant_id, SUM(total_amount) AS partial_sum
FROM orders WHERE created_at > '2026-07-01'
GROUP BY tenant_id;

-- Shard 2 (same query, different shard connection)
SELECT tenant_id, SUM(total_amount) AS partial_sum
FROM orders WHERE created_at > '2026-07-01'
GROUP BY tenant_id;

-- Application layer merges partial sums per tenant_id
-- total = shard1.partial_sum + shard2.partial_sum + ...

6. Resharding: splitting shards later

Resharding becomes necessary when a single shard grows too large or is unevenly loaded despite careful shard key selection. The process involves splitting an existing shard into two or more new shards and migrating the affected data, ideally while the system keeps running. Without careful planning, resharding is the most involved and riskiest maintenance operation in any sharding architecture.

A proven strategy is consistent hashing, which, when adding new shards, only needs to redistribute a fraction of the existing data instead of rehashing everything from scratch. Another proven approach: create more logical shards than physical instances from the start (for instance 1024 logical shards spread across 4 physical instances), so that resharding only changes the mapping of logical to physical shards without redistributing individual rows.

-- Logical shard mapping: 1024 logical shards spread over 4 physical instances
CREATE TABLE logical_shard_map (
  logical_shard_id SMALLINT PRIMARY KEY,
  physical_instance VARCHAR(50) NOT NULL
);

-- shard_id for a given key is always: hash(shard_key) % 1024
-- resharding only updates this mapping table, not the data rows themselves
UPDATE logical_shard_map
SET physical_instance = 'shard-db-05'
WHERE logical_shard_id IN (768, 769, 770, 771);

7. Shard routing in the application layer

The application layer needs to know, for every query, which shard is responsible for the given shard key. This routing logic belongs in a central, well tested library, instead of being duplicated in every individual code location. A central shard router reduces the risk that a developer accidentally accesses the wrong shard or writes a query without a shard key that gets unnecessarily fanned out to every shard.

Some database systems offer native sharding support with transparent routing (such as Vitess for MySQL or Citus for PostgreSQL), so that application code does not need to worry about shard assignment. These solutions significantly reduce implementation effort, but come with their own operational complexity, for instance running the routing layer itself.

-- Citus (PostgreSQL extension): distribute a table transparently by shard key
SELECT create_distributed_table('orders', 'tenant_id');

-- Queries look identical to a single-instance table,
-- Citus routes them to the correct physical shard automatically
SELECT tenant_id, SUM(total_amount)
FROM orders
WHERE tenant_id = 4821
GROUP BY tenant_id;

8. Alternatives to manual sharding

Before introducing sharding, it is worth checking simpler alternatives. Vertical scaling, meaning bigger hardware for a single instance, solves many capacity problems far more simply, as long as the limits of available hardware have not yet been reached. Read replicas solve read load problems without introducing sharding's complexity, as long as the actual problem is read load and not write load or storage space.

Archiving old data into separate, less frequently queried tables also reduces the effective size of the main table without introducing sharding. Only once these simpler measures no longer suffice and write load or data volume structurally exceeds a single instance's capacity is sharding the right choice, because the additional complexity is only justified by genuine need.

9. Sharding strategies compared

The table below compares common sharding approaches by their key properties.

For most applications, a combination of composite shard key and consistent hashing is the most pragmatic way to combine even distribution with reasonable resharding effort. Native sharding solutions pay off once the operational overhead of a hand built solution exceeds the learning curve of an established system.

10. Summary

Sharding distributes a large table horizontally across several independent instances, solving capacity limits that vertical scaling can no longer handle. Choosing the shard key is the single most important decision in the entire architecture, because it can only be changed afterward with considerable migration effort. Hash sharding distributes evenly but loses range query capability, range sharding retains range queries but risks hotspots.

Cross shard queries are structurally expensive in sharding and should be limited to exceptions, while frequent aggregations across all shards are better offloaded to a separate analytics system. Resharding capability should be planned for from the start, for instance through consistent hashing or more logical than physical shards, to enable later growth without a complete rebuild.

11. FAQ: Sharding Strategies for Large Tables