Database Sharding and Horizontal Scaling

Every time the database slows down a little, someone jumps up and shouts "let's shard!" This is the most classic "hammer mindset" in backend engineering — when all you have is a hammer, everything looks like a nail. The reality: of the sharding projects I've seen, seven out of ten were over-engineered. After splitting, they didn't fix the bottleneck; instead they dragged operations and consistency into an abyss. The ones that truly "must shard" might number just one.

This article isn't rote theory. It's about: when a single database can't keep up, what is the correct scaling path; if you really do end up sharding, how to choose a shard key, how to treat cross-shard queries, how to compromise on consistency; and most importantly — why you should reach that point as late as possible.

1. Squeeze the Single Machine Dry First: The Correct Order of Scaling

Database scaling isn't "split it when performance is bad" — it's a chain with a strict order. Skipping steps is the most expensive mistake.

Index/query optimization → Connection pool/parameter tuning → Read/write splitting → Cache layer → Vertical split → Horizontal sharding
   (almost free)            (free)                        (cheap)              (cheap)       (medium)         (very expensive)

The first step is always optimization, not scaling out. 90% of slow queries have one of three root causes: missing indexes, SELECT *, or N+1. Run EXPLAIN once, eliminate full table scans, filesorts, and temporary tables, and QPS often jumps several-fold immediately. This step is nearly free, yet delivers the biggest payoff.

The second step is connection pools and parameters. Set innodb_buffer_pool_size to about 70% of physical memory, tune the connection pool (HikariCP / PgBouncer) to a sensible ceiling, and don't use the default max_connections — after these changes throughput often climbs again.

The third step is read/write splitting. For read-heavy, write-light workloads, write to the primary and read from replicas. Once read traffic is offloaded, pressure on the primary drops immediately. The cost is primary-replica lag — reads right after a write must hit the primary. This point will come up again and again below.

Only the fourth step is caching. Move hot data into Redis, use cache-aside + reasonable TTL + null-value caching to prevent cache penetration, and read QPS can drop by an order of magnitude. The cost is introducing consistency problems: caching is not a silver bullet; it's another piece of state you have to keep in sync.

After the first four steps are all done and the single machine truly can't keep up, only then consider splitting the database. Skipping straight to sharding turns "something one Redis instance would fix" into "operating three distributed systems".

2. Vertical Splitting: Cut Along Business Lines and Fix the Chaos First

Before horizontal sharding, do vertical splitting first. Its core purpose isn't to handle volume, it's to tame architectural chaos.

Split databases by business: A bloated "big database" crams users, orders, products, comments, messages... all sharing one buffer pool. When one table (say orders) runs a large batch statistic and eats all the IO, user logins slow down too. Cut along business boundaries into independent databases:

        [Monolithic Database]
   Users / Orders / Products / Comments / Messages
            │
   ┌────┬───┴───┬──────┬──────┐
   ▼    ▼       ▼      ▼      ▼
 [Users DB] [Orders DB] [Products DB] [Comments DB] [Messages DB]
   Each is an independent instance with its own failure domain

The benefit of splitting is physical isolation — the orders DB being dragged down by a slow query doesn't affect user logins. The cost is cross-database JOINs are gone; you have to assemble in the application layer or use redundant fields.

Split large fields into separate tables (hot/cold separation): A table stuffed with multi-KB fields like content and extra_json badly drags down buffer pool hit rate. Move large fields to an extension table and keep only frequently accessed small fields in the main table:

[user]          id, name, email, created_at     ← hot table, small rows, stays cached
[user_profile]  user_id, avatar, bio, extra     ← cold table, joined on demand

These two steps carry almost zero risk. Do them thoroughly and much of the "pressure to shard" disappears on its own.

3. Horizontal Sharding: Split One Big Table into N Pieces

After vertical splitting, if some core table (typically orders or a message ledger) still has hundreds of millions of rows in a single table and writes are saturated — that's when horizontal sharding finally comes in: spread one table's data evenly across multiple databases and multiple tables along some dimension.

Three common sharding strategies:

┌─────────────────────────────────────────────────────────┐
│  1. Hash sharding: shard = hash(key) % N                │
│     Even data distribution, but rehashing on scale-out  │
│     (adding shards) is painful                          │
│                                                         │
│  2. Range sharding: by id/time range, e.g. 0-10M → s0   │
│     Scale-out friendly, but hotspots are easy           │
│     (latest data concentrates writes on one shard)      │
│                                                         │
│  3. Geo/business sharding: NA users → US-East DB,       │
│     EU → EU-West DB                                     │
│     Data locality, compliance friendly, but distribution│
│     can be severely uneven                              │
└─────────────────────────────────────────────────────────┘

In practice there are usually two routing layers: database = hash(key) / table_count, table = hash(key) % table_count. For example, with 4 databases and 8 tables (32 physical tables total), user u42's orders → hash(42) % 32 = 10 → order_2 in DB_1.

For the actual routing, the industry generally doesn't hand-roll it — they use middleware: ShardingSphere, Vitess, MyCAT, or Citus for PostgreSQL. If you can avoid writing your own routing layer, don't. The pitfalls in this area are far more numerous than you imagine.

4. The Shard Key: The Field That Decides Life or Death

Whether sharding succeeds depends 90% on whether the shard key is chosen correctly. Choose wrong and data skew and a cross-shard query disaster will blow up at the same time.

Principle: choose by the high-frequency query condition on the read path, so most queries hit only one shard.

For e-commerce orders, almost every query starts from "a certain user's orders" — view my orders, view my refunds, view my shipments. So the shard key should be user_id. That way over 90% of queries naturally land on a single shard, with no cross-shard work.

Order query:    WHERE user_id = ?      ← hits one shard, fast
Merchant query: WHERE merchant_id = ?  ← scans all shards, slow!
Ops dashboard:  WHERE created_at > ?   ← scans all shards, slow!

The merchant and backend paths are the hard ones. A common solution is to dual-write a heterogeneous index table sharded by merchant_id specifically for the merchant dimension. The cost is doubled writes and more complex consistency — this is an unavoidable tax of horizontal scaling.

Counterexamples: sharding by auto-increment id (read requests don't carry the id, so each one has to reverse-lookup the route first, an extra hop for nothing); sharding by created_at (latest data concentrates on one hotspot shard, severe write amplification); random sharding (degenerates directly into a "full-shard scanner").

Also watch out for skew: hashing by user_id looks even, but if a few large B2B customers have millions of rows per record, they still pile onto the same shard. These long-tail users need to be handled separately via dedicated hotspot tables.

5. Cross-Shard Queries: The Real Pain After Sharding

Once sharding goes live, the following capabilities all degrade:

JOINs are gone. What used to be done in one SELECT ... FROM order JOIN user now has the two tables potentially in different databases, so you can only query one side in the application layer, then batch-query the other side by ID, then assemble in memory.

Aggregations have to be merged. COUNT, SUM can't be pushed down directly; you have to send them to all shards, compute each, then merge. Most middleware can do this automatically, but performance is O(N shards).

Global pagination is extremely hard. This is the nastiest one. LIMIT 10000, 20 in a sharded scenario means: each shard has to fetch its first 10020 rows, merge them, then sort and take 20. The deeper the page, the worse the amplification. Common compromises: allow only "next page" cursor pagination (WHERE id > last_id ORDER BY id LIMIT 20); or sync the fields that need complex sorting/pagination to Elasticsearch, query ES, get the IDs, then go back to the DB for details — this is the industry mainstream.

Globally unique IDs. Auto-increment primary keys no longer work — N databases each running auto_increment will collide. Either use an independent ID generator, or use the Snowflake algorithm:

┌── 1 bit ──┬── 41 bit ─────┬── 10 bit ──┬── 12 bit ───┐
│  sign bit │ ms timestamp  │ machine/   │ per-machine │
│           │               │ datacenter │ sequence #  │
└──────────┴───────────────┴───────────┴────────────┘
              ↑ monotonically increasing, good for B+Tree sequential writes

The benefit of Snowflake is that IDs are monotonically increasing (friendly to InnoDB primary keys, avoiding page splits), generated locally with no network overhead, and globally unique. The pitfall is clock dependency — clock rollback generates duplicate IDs. In production, either disable NTP step-back, or back it up with a ZK-coordinated ID generator like Leaf.

6. Distributed Transactions: Don't Insist on Strong Consistency

After sharding, what used to be done in one BEGIN ... COMMIT now spans multiple databases — the distributed transaction nightmare begins. Remember first: if the business can tolerate eventual consistency, never impose strong consistency.

Four mainstream approaches, ordered from highest to lowest cost:

┌──────────┬──────────────────────┬───────────────────────────┐
│ Approach │ Consistency/Perf     │ Use case                  │
├──────────┼──────────────────────┼───────────────────────────┤
│ 2PC      │ Strong / very poor   │ Hardly ever; blocks all   │
│          │                      │ participants              │
│ TCC      │ Strong / medium      │ Core trades: money, stock │
│ Saga     │ Eventual / good      │ Long flows, compensatable │
│ Local msg│ Eventual / best      │ Async decoupling; 90% of  │
│          │                      │ cases are covered         │
└──────────┴──────────────────────┴───────────────────────────┘

2PC (Two-Phase Commit): The coordinator has all participants lock resources first, then commit uniformly. Perfect in theory, a disaster in practice — if any participant stalls, all databases are locked; if the coordinator dies, everything hangs. Unless cross-database strong consistency is a life-or-death requirement (core bank accounting), don't touch it.

TCC (Try-Confirm-Cancel): The business layer splits each operation into three steps: reserve, confirm, rollback. Performance is much better than 2PC, but every service has to write three sets of logic, the development cost is extremely high, and it's reserved for can't-fail scenarios like funds and inventory.

Saga: Split a long transaction into a string of local transactions, each paired with a compensating operation that reverse-compensates on failure. Suited to long chains across multiple services like order flows; the cost is that intermediate states are visible externally.

Local message table / reliable messaging (the practical first choice): The idea is to "downgrade a distributed transaction into a local transaction + async message" — business data and the message table live in the same database, written in one local transaction; a background poller delivers messages to the MQ, and consumers consume idempotently. Paired with RocketMQ transactional messages, Kafka transactional producers, or the Outbox pattern, this is enough for 90% of internet businesses.

Idempotency is the floor: In distributed systems, messages will always have "at least once" redelivery. Consumers that aren't idempotent will sooner or later have data problems — use unique keys, state machines, or dedup tables, but you can't skip it.

7. Don't Rush to Shard: For Most Teams

After all this detail on sharding, here's a bucket of cold water to close with: the vast majority of projects never reach the point of needing to shard.

I've seen too many teams start planning "sharding" at a few thousand daily QPS. Do the math: a single MySQL/PostgreSQL instance on SSD + 64GB RAM can steadily handle tens of thousands of QPS, no exaggeration; add one primary, two replicas, and a Redis cache, and read QPS can climb another order of magnitude; before you hit a "hundreds of millions of rows in one table" bottleneck, indexes and partition tables (PARTITION) can carry you a long way.

In other words, before most businesses reach the sharding threshold, the product has long since iterated or died. Sharding early is prepaying a massive operations tax for a future that may never arrive.

The real cost of sharding:

1. Operations explosion: N instances × primary-replica × backups × monitoring. DBA workload multiplies several-fold.
2. Consistency nightmare: cross-database transactions, message compensation, idempotency design — each one can keep a team working overtime for a month.
3. Migration difficulty: How do you cut over smoothly? Dual-write with gradual rollout? Downtime? No sharding migration is ever easy.
4. Query capability regression: Complex reports, JOINs, and statistics have to move to a data warehouse or OLAP engine, lengthening the pipeline.

So the right mindset is: treat sharding as the "last bullet". Only when indexes, read/write splitting, caching, partition tables, vertical splitting, and hardware upgrades are all exhausted and the business is still accelerating — only then seriously evaluate sharding. When evaluating, first ask whether you can switch to a more powerful database (move analytical queries to ClickHouse, time-series to TimescaleDB). Often a tool change is much cheaper than splitting the database.

Summary

1. Scaling has a strict order: indexes → parameters → read/write splitting → caching → vertical splitting → horizontal sharding. Skipping levels is the most expensive backend mistake; the first four steps are nearly free and deliver the biggest payoff.
2. The shard key decides life or death: Always choose by "the high-frequency condition on the read path", so that 90%+ of queries hit a single shard. For e-commerce orders choose user_id, not id or created_at.
3. Cross-shard queries are the real pain: JOINs degrade, aggregations have to be merged, global pagination is almost unsolvable — disable deep pagination or fall back to a search engine.
4. Don't insist on strong consistency for distributed transactions: In 90% of cases "local message table + idempotency" for eventual consistency is enough; reserve TCC for life-or-death cases like funds and inventory.
5. The vast majority of projects don't need sharding: A single machine + read/write splitting + caching can handle tens of thousands of QPS. Sharding is the last bullet; firing early just leaves you running several more distributed systems.

One closing line: Horizontal database scaling is never "split when performance is bad"; it's "split only when all the cheap options are exhausted and there's truly no other way". If you can avoid splitting, don't; if you must split, first figure out how to get past these three gates: shard key, cross-shard queries, and consistency.