At 40 million rows your single PostgreSQL instance handled queries without complaint. At 400 million rows, write latency crossed 200ms and your on-call engineer started sleeping with a laptop. At 4 billion rows, connection pools saturated during peak hours and the business started losing orders. That trajectory — familiar to any team running a high-growth product — is the moment sharding stops being an architecture diagram on a whiteboard and becomes an urgent production decision. Sharding splits your data across multiple database nodes, each owning a subset, so write load is distributed and no single machine becomes the bottleneck. Choosing the wrong sharding strategy, however, trades one problem for three: hot shards, cross-shard query nightmares, and a resharding event that takes a weekend and causes an outage.
- Hash sharding distributes rows evenly using a hash of the shard key — good for write throughput, terrible for range scans.
- Range sharding keeps sequential data co-located — ideal for time-series and geographic queries, but vulnerable to hot shards on monotonic keys.
- Directory sharding uses a lookup table to map keys to shards — maximally flexible, but the lookup table itself becomes a critical dependency.
- Cross-shard queries and distributed transactions are the hardest part; minimize them through careful shard key design, not after the fact.
- Before sharding, exhaust vertical scaling, read replicas, and table partitioning — sharding multiplies operational complexity by the number of shards.
- Vitess (MySQL) and Citus (PostgreSQL) are the most production-proven sharding layers; PlanetScale and CockroachDB offer managed alternatives.
Background
Database sharding — sometimes called horizontal partitioning — predates cloud infrastructure by decades. The core idea is straightforward: instead of one database holding all rows of a table, you divide rows across N databases (shards) according to a sharding key. Each shard is an independent database instance with its own storage, connections, and compute. Applications either consult a routing layer to determine which shard to query, or the database middleware handles routing transparently.
The distinction between partitioning and sharding matters. Partitioning splits a table across multiple storage units within a single database engine — PostgreSQL's native partitioning, for example. Sharding splits data across separate database servers. Partitioning is easier to implement and reason about. Sharding is what you reach for when a single server, fully optimized, is still not enough.
The three classical sharding strategies — hash, range, and directory — each make a different tradeoff between write distribution, query locality, and operational simplicity. Understanding those tradeoffs before you choose a shard key is the most important architectural decision you will make, because changing it later is expensive.
When You Actually Need Sharding
Sharding is often reached for prematurely. Before committing to it, confirm that you have exhausted the following in order:
- Vertical scaling. A machine with 96 cores, 768 GB RAM, and NVMe storage handles workloads that would require 20+ application-layer shards. Cloud instances (AWS r7g.16xlarge, Google Cloud n2-highmem-128) make vertical scaling surprisingly cost-competitive at moderate scale.
- Read replicas. If your bottleneck is read throughput rather than write throughput, adding read replicas costs a fraction of a sharding migration. Most OLTP workloads are read-heavy.
- Table partitioning. PostgreSQL's declarative partitioning and MySQL's partitioning syntax allow the query planner to skip entire partitions, dramatically reducing I/O. Combined with proper indexing, partitioning frequently defers the need for sharding by years.
- Connection pooling. PgBouncer and ProxySQL reduce the connection overhead that mimics a scaling problem at the database layer when the real bottleneck is connection handling.
The clear signals that sharding is warranted: write throughput that saturates the primary even with all optimizations applied; dataset size that exceeds practical single-node storage economics; regulatory requirements that mandate data residency across geographic boundaries; or multi-tenant architectures where tenant isolation at the database level is a hard requirement.
Sharding Strategies
Hash Sharding
Hash sharding computes a hash of the shard key and uses the result to determine which shard a row belongs to. The classic implementation uses modulo arithmetic: shard_id = hash(key) % num_shards. Because hash functions produce uniformly distributed outputs, writes spread evenly across all shards — eliminating the hot-shard problem that plagues range sharding on monotonic keys.
-- Conceptual routing logic for hash sharding on user_id
-- shard_count = 8
SELECT shard_id
FROM shard_routing
WHERE shard_id = MOD(CONV(SUBSTRING(MD5(CAST(user_id AS CHAR)), 1, 8), 16, 10), 8);
-- Or in application code (Python-style pseudocode):
-- shard = hashlib.md5(str(user_id).encode()).hexdigest()
-- target_shard = int(shard[:8], 16) % NUM_SHARDSConsistent hashing solves the catastrophic resharding problem of simple modulo. In a consistent hashing ring, each shard owns an arc of the ring, and adding or removing a shard only requires remapping the keys in the affected arc — not rehashing the entire dataset. Vitess uses a variant of consistent hashing for its keyspace ranges. DynamoDB uses consistent hashing internally.
Shard key choice is the most consequential decision in hash sharding. A good shard key has high cardinality (many distinct values), correlates with the access pattern (so that the data a single request needs is co-located on one shard), and is immutable (so rows do not need to migrate shards when the key changes). User ID, order ID, and tenant ID are common choices. Timestamps and status fields are almost always poor choices.
Hash sharding's primary weakness is range queries. A query for WHERE created_at BETWEEN '2024-01-01' AND '2024-01-31' must fan out to every shard, merge results, and sort — a scatter-gather operation whose cost scales linearly with the number of shards.
Range Sharding
Range sharding assigns contiguous ranges of the shard key to specific shards. Time-series data is the canonical use case: shard 1 holds January data, shard 2 holds February data, and so on. Geographic sharding is another natural fit — shard keys derived from region codes or ZIP code prefixes keep geographically local data on the same shard.
-- Range shard routing table example
CREATE TABLE shard_ranges (
shard_id INT NOT NULL,
range_start BIGINT NOT NULL, -- inclusive
range_end BIGINT NOT NULL, -- exclusive
shard_host VARCHAR(255) NOT NULL,
PRIMARY KEY (shard_id)
);
INSERT INTO shard_ranges VALUES
(1, 0, 100000000, 'shard-1.db.internal'),
(2, 100000000, 200000000, 'shard-2.db.internal'),
(3, 200000000, 300000000, 'shard-3.db.internal');
-- Routing query
SELECT shard_host
FROM shard_ranges
WHERE range_start <= :key_value AND :key_value < range_end
LIMIT 1;Range sharding enables efficient range queries — a query that spans a time window or an ID range touches only the relevant shards, not all shards. This makes it dramatically more efficient than hash sharding for analytics queries and time-based data retention workloads.
The weakness is write skew on monotonic keys. If your shard key is an auto-incrementing ID or a timestamp, all new writes land on the last shard. That shard becomes a write bottleneck while all other shards sit idle. Solutions include using UUIDs v4 (random) rather than sequential IDs, adding a random prefix to the shard key, or combining range sharding with hash sharding (shard by year-month range, then hash within each month partition).
Directory Sharding
Directory sharding uses an explicit lookup table — the directory — that maps each shard key value (or group of values) to a specific shard. Unlike hash and range sharding, there is no formula: the routing decision is a database lookup.
-- Directory sharding lookup table
CREATE TABLE shard_directory (
tenant_id BIGINT NOT NULL,
shard_id INT NOT NULL,
shard_host VARCHAR(255) NOT NULL,
created_at TIMESTAMP DEFAULT NOW(),
PRIMARY KEY (tenant_id),
INDEX idx_shard_id (shard_id)
);
-- Route a query for a specific tenant
SELECT shard_host
FROM shard_directory
WHERE tenant_id = :tenant_id;
-- Moving a tenant to a new shard (online migration)
UPDATE shard_directory
SET shard_id = 5, shard_host = 'shard-5.db.internal'
WHERE tenant_id = :tenant_id;Directory sharding's advantage is operational flexibility. Moving a tenant from one shard to another requires updating a single row in the directory — no data rehashing, no range boundary adjustments. This makes it particularly well-suited for multi-tenant SaaS architectures where individual tenants grow unevenly and large tenants occasionally need dedicated shard isolation.
The cost is that the directory table becomes a critical hot path. Every read and write must first consult the directory, adding a network round-trip and a query under the hood. Mitigation strategies include aggressive caching of the directory in application memory, replicating the directory across all data centers, and maintaining a local cache with TTL-based invalidation. If the directory is unavailable, the entire application is unavailable — a single point of failure that requires its own high-availability design.
Resharding Challenges
Adding shards to a running system — resharding — is one of the most operationally difficult database operations you will encounter. The naive approach (stop the world, dump data, redistribute, restart) requires planned downtime that is often unacceptable for production systems. Online resharding strategies allow data to be moved while the system continues serving traffic, but they require careful orchestration.
Vitess's VReplication is the most mature open-source online resharding implementation for MySQL. VReplication streams binlog events from source shards to target shards while a bulk copy of existing data runs in the background. Once the target shard catches up to the primary, a cutover happens with a brief (<1 second) write hold. The approach handles large tables — PlanetScale, which is built on Vitess, uses VReplication to reshard tables with billions of rows online.
Cross-shard queries require a scatter-gather layer. A query that spans multiple shards must be issued to each relevant shard in parallel, results merged in the routing layer, and final ordering or aggregation applied. This works reasonably well for simple queries but becomes problematic for complex aggregations, JOINs across sharded tables, and queries with non-sharding-key filters. The standard recommendation is to design your data model so that the most common query patterns are shard-local — meaning all the data a request needs lives on one shard.
Tools and Frameworks
Several mature tools abstract sharding complexity. Understanding their tradeoffs helps you choose based on your existing stack and operational capabilities.
Vitess is the most battle-tested MySQL sharding layer, originally built at YouTube and now a CNCF graduated project. It provides connection pooling, query rewriting, schema management, and VReplication-based resharding. The learning curve is steep — Vitess introduces its own topology management, VSchema, and vtgate routing layer — but at large scale it handles complexity that would otherwise require substantial custom infrastructure. PlanetScale is Vitess as a managed service.
Citus (now part of Microsoft Azure) extends PostgreSQL with distributed table support. You designate a distribution column and Citus transparently shards rows across worker nodes. It handles most SQL including JOINs between distributed tables, provided the join key matches the distribution column. Co-location — placing related tables on the same shards — is the key design concept. Citus works well for multi-tenant SaaS and real-time analytics workloads.
CockroachDB takes a different approach: it is a distributed SQL database that handles sharding (it calls them ranges) internally and automatically. The application uses standard PostgreSQL wire protocol. CockroachDB's automatic range splitting and rebalancing removes resharding from the operator's concern entirely, at the cost of higher per-node overhead and latency on single-node operations compared to PostgreSQL.
Key Takeaways
- Hash sharding distributes writes evenly but requires scatter-gather for range queries; use consistent hashing to avoid full reshuffles when adding shards.
- Range sharding enables efficient range queries and clean data lifecycle management but creates write hot spots on monotonic keys — avoid auto-increment IDs as shard keys.
- Directory sharding offers maximum flexibility for moving data between shards but introduces a critical dependency on the lookup table's availability and performance.
- Shard key selection is irreversible in practice — choose a key that keeps the majority of your query patterns shard-local before you commit.
- Exhausting vertical scaling, read replicas, and table partitioning before sharding is not premature optimization; it is the correct sequence of increasing complexity.
- Online resharding (Vitess VReplication, CockroachDB automatic rebalancing) is achievable but requires planning; design for it from day one rather than retrofitting.
- Cross-shard transactions should be treated as an architectural smell — if your domain model requires them frequently, your shard key choice may be wrong.
Working with JusDB on Database Scaling
Designing a sharding strategy for a production system involves tradeoffs that play out over years — in query patterns, migration complexity, operational burden, and team expertise. JusDB works with engineering teams to assess whether sharding is actually warranted, choose the right strategy and shard key for the access patterns you have (not the ones you imagine you might have), and implement the routing layer and migration tooling with production safety in mind.
Whether you are running MySQL on-premise, PostgreSQL on RDS, or evaluating a managed distributed SQL option, we can help you make the call before it becomes urgent. See our MySQL optimization and scaling services or get in touch to discuss your specific scaling situation.
Related reading: