Database Sharding: When to Do It, How to Do It, and When You've Gone Too Far

Picture this.

It is 11pm on a Tuesday. You are not on call. You are in bed.
Your phone lights up.

It is your CTO.

"The app is crawling. Users are complaining. The dashboard shows query times above 8 seconds. What is going on?"

You jump on your laptop. You pull up the database metrics. CPU is at 94%. Disk I/O is maxed out. There are 3,000 active connections fighting over the same tables.

You look at the data. Your users table has 180 million rows. Your orders table has 600 million. Every query — no matter how well indexed — is slow because the database is simply carrying too much weight.

You have hit the wall that every growing system eventually hits.

And the word your CTO says next is the one you have been quietly dreading for months:

"I think we need to shard."

This is not a hypothetical. This is a pattern I have seen play out in real teams, real products, real 11pm phone calls. And sharding is the answer — but only if you understand what it actually means, when it actually helps, and where it will quietly make your life harder if you are not careful.

That is what we are covering today.

What Sharding Actually Is

Most engineers have heard the word. Fewer can explain it clearly.
Sharding is the practice of splitting a large database into smaller, independent pieces called shards. Each shard holds a subset of the total data and lives on its own database instance. Together, they hold everything. Individually, each one carries only a fraction of the load.

Think of it like this.

You run a busy post office. Every letter in the country comes through one building. Eventually the building cannot cope — too many letters, too many staff, too many queues. So you split it. Letters A through F go to one building. G through M go to another. N through Z go to a third. Each building now handles a manageable chunk of the total volume.

That is sharding.

The database equivalent is taking your 600 million row orders table and splitting it so that orders for users 1 to 5 million live on shard one, orders for users 5 to 10 million live on shard two, and so on. Each shard is a full, independent database. No shard knows about the others.

The Scenario That Made Sharding Necessary

Let me give you a concrete production scenario to anchor everything that follows.

Imagine you are the lead DevOps engineer at a fintech company. You process payments for businesses across West Africa. You started two years ago with a single PostgreSQL instance on a powerful server — 32 cores, 128GB RAM, fast NVMe storage.

For the first year, it was fine. Queries were fast. The team was happy. You had good indexes, a read replica for reporting, and connection pooling through PgBouncer.

Then growth hit.

Transaction volume tripled in six months. You now have 400 million transaction records. Your payments table is 800GB. Your daily active users crossed 2 million. The read replica helped with reporting but your write load — new transactions, status updates, reconciliation jobs — all still hit the primary.
Here is what you started seeing:

Your P99 query latency went from 40ms to 1.2 seconds. Your autovacuum jobs started falling behind, causing table bloat. Long-running analytical queries locked rows that payment processing needed. Your on-call team was getting paged three nights a week.

You tried everything first. You tuned work_mem. You added partial indexes. You partitioned the payments table by month. You moved analytical workloads to a separate replica. Things improved slightly.

But the write bottleneck remained. One primary database. One write path. No way around it.

That is when sharding became the right conversation to have.

Step One: Choose Your Sharding Key

This is the most important decision in the entire process. Get it wrong and you will rebuild everything six months later.

A sharding key is the field you use to decide which shard a piece of data lives on. In our fintech scenario, the natural candidate is business_id — the ID of the business making payments.
Here is why this works well:

Most queries in a payment system are scoped to a single business. "Show me all transactions for business 1042 this month." That query goes to one shard and one shard only. No cross-shard joins. No scatter-gather across every instance.

Here is what a bad sharding key looks like in the same system:
Sharding by transaction_date. Now a query for a single business's transactions touches every shard because one business has transactions spread across all date ranges. Every read becomes a fan-out across all shards. You have added hardware but made queries slower.

The rule for choosing a sharding key:

Pick the field that appears in the WHERE clause of your most frequent, most critical queries. That field becomes your key.

In most B2B products it is tenant_id or business_id. In consumer products it is often user_id. In time-series systems it is sometimes a combination of entity ID and time bucket.

Step Two: Choose Your Sharding Strategy

Once you have your key, you need to decide how to map a key value to a shard. There are three main approaches:

Range-Based Sharding

You divide the keyspace into ranges. Business IDs 1 to 100,000 go to shard one. 100,001 to 200,000 go to shard two. And so on.

This is simple to understand and easy to implement. But it creates a problem called hotspots. If your newest, most active businesses all have high IDs, shard three is doing all the work while shard one sits quiet. Your load is uneven.

In our fintech scenario, range sharding on business_id would have been a mistake. New business signups were growing fast, which means the highest ID shard would have carried a disproportionate share of new transactions.

Hash-Based Sharding

You run the sharding key through a hash function and use the result to pick a shard. shard = hash(business_id) % number_of_shards.
This distributes data evenly. No hotspots. Every shard gets a roughly equal share.

The tradeoff: range queries become expensive. "Give me all businesses with IDs between 50,000 and 60,000" now potentially touches every shard because hashing destroys the natural ordering of IDs.

For our fintech system, hash-based sharding on business_id was the right call. We query by business ID directly, not by ranges of business IDs. Even distribution mattered more than range query support.

Directory-Based Sharding

You maintain a lookup table — a directory — that maps each key to a shard. Business 1042 is on shard three. Business 8891 is on shard one. The directory decides.

This gives you the most flexibility. You can move a business from one shard to another by updating the directory. You can handle uneven data sizes by putting your largest businesses on dedicated shards.

The cost: the directory itself becomes a critical dependency. If it goes down, nothing can route queries anywhere. You need to cache it aggressively and protect it carefully.

For very large businesses that generate 10x the transaction volume of an average customer, we used a hybrid approach — hash sharding for the majority, with directory overrides that placed our top 20 enterprise clients on dedicated shard instances.

Step Three: Build Your Routing Layer
Once you know your sharding key and strategy, you need something that sits between your application and your databases and routes each query to the right shard.
In our fintech system, we built a lightweight routing service in Python. Every database call went through it. The logic was simple:

`pythondef get_shard(business_id: int, num_shards: int = 8) -> str:
shard_index = hash(business_id) % num_shards
return f"shard_{shard_index}"

def get_db_connection(business_id: int):
shard = get_shard(business_id)
return connection_pool[shard].get_connection()`

The application code stayed clean. Engineers called get_db_connection(business_id) and got back a connection to the right shard. They did not need to know which physical database they were talking to.

For teams using Vitess — which is what YouTube built and later open-sourced — the routing layer is handled for you. Vitess sits in front of your MySQL instances, understands your sharding key, and routes queries transparently. If you are on PostgreSQL, Citus is worth looking at. It turns Postgres into a distributed database with sharding built in.

Step Four: Handle the Hard Problems

This is where most sharding articles stop. This is where the real work begins.

Cross-Shard Queries

Some queries need data from multiple shards. In our system, the finance team needed a report of total transaction volume across all businesses for a given day. That data lives across all eight shards.

We solved this by maintaining a separate reporting database — a denormalized aggregate store — that received summarized data from each shard via a message queue. The finance team queried the aggregate store, not the shards directly. Analytical queries never touched the transactional database.

This is a common pattern: shards handle writes and single-entity reads, a separate store handles cross-shard analytics.

Distributed Transactions

In a single database, a transaction is simple. Either all your changes commit or none of them do.

In a sharded system, a transaction that touches two shards becomes a distributed transaction. This is genuinely hard. Two-phase commit exists but it is slow and fragile. Most teams avoid it.

Our approach: we designed the system so that a single business's data never needed to span shards. A payment is created, updated, and settled on the same shard — the one that belongs to that business. We accepted that cross-business operations would be eventually consistent rather than immediately consistent.

For the cases where we truly needed cross-shard consistency — like a reconciliation job that settled balances — we used an outbox pattern.
Each shard wrote events to an outbox table. A separate worker read those events and applied them downstream. Eventual consistency, but with guaranteed delivery.

Rebalancing

You start with eight shards. Business grows. Eight shards become a bottleneck. You need sixteen.
This is called resharding and it is painful if you have not planned for it.

With hash-based sharding, adding a new shard changes the hash mapping. hash(business_id) % 8 and hash(business_id) % 16 produce different results for the same business. Data that was on shard three might now belong on shard eleven. You have to move it.

We planned for this from day one by using consistent hashing instead of simple modulo hashing. Consistent hashing minimizes how much data moves when you add or remove shards. When we went from eight to twelve shards, only about 25% of data needed to move instead of potentially all of it.

The migration itself ran as a background job — copying data shard by shard, verifying checksums, then flipping the routing config. Zero downtime. But it took three weeks of careful execution.

When Sharding Is the Wrong Answer

Here is the part most engineers skip when they are excited about a new architecture.

Sharding is not always the answer. And reaching for it too early is one of the more expensive mistakes a team can make.

Before you shard, you should have already done all of these:
Proper indexing. A missing index on a 200 million row table can make a query run in 8 seconds that should run in 40 milliseconds. Add the index first.

Connection pooling. PgBouncer or pgpool in front of PostgreSQL handles thousands of application connections without overloading the database.
Read replicas. Move your read traffic — reporting, analytics, search — off the primary. A single primary with two or three replicas handles a surprisingly large amount of load.

Table partitioning. PostgreSQL native partitioning by date or ID range gives the query planner a much smaller surface to scan. This is not sharding — it still runs on one instance — but it solves many of the same query performance problems with a fraction of the complexity.

Vertical scaling. Sometimes the right answer is simply a bigger machine. Moving from 32 cores to 64 cores and from 128GB to 256GB RAM is cheaper and faster than a sharding migration.
Sharding makes sense when:

You have exhausted vertical scaling options

Your write throughput has outgrown what a single primary can handle
Your dataset is so large that even with partitioning, query planning and vacuuming are struggling
You have clear, consistent query patterns that map well to a sharding key

Sharding makes your system harder to operate, harder to query, harder to reason about, and harder to debug. You are trading simplicity for scale. Make sure you actually need the scale before you pay that price.