Scaling databases

Vertical scaling — bigger machine: more CPU, RAM, faster disks. Simple (no application changes, no distributed-systems complexity, transactions stay trivially local) but has a hard ceiling — the largest available instance, and cost grows faster than linearly as you approach it. Horizontal scaling — more machines, splitting either reads (replicas) or data itself (sharding) — has no inherent ceiling but introduces genuine distributed-systems complexity: cross-node consistency, network latency between nodes, and operational overhead that a single machine never had. Vertical scaling is the right default until you actually hit its ceiling or its cost curve, because it avoids paying horizontal scaling’s complexity tax for as long as possible; it becomes unavoidable once a single machine’s compute, memory, storage, or I/O throughput genuinely cannot serve the workload regardless of budget, or once availability requirements (surviving a single machine’s failure) demand more than one node exists in the first place.

A read replica is a copy of the primary database that continuously applies the primary’s write stream (via replication) and serves read traffic, so read load can scale independently of write load by adding more replicas, without adding write contention. Replication is typically asynchronous — the primary acknowledges a write once it has durably applied it, without waiting for replicas to catch up — which means there is always some replication lag, and a read hitting a replica shortly after a write can return stale data. Concrete breakage: a user updates their profile, immediately reloads the page, and the read hits a lagging replica and shows the old value (a read-your-own- writes violation); two reads in the same user session hit different replicas with different lag and appear to go backward in time relative to each other. Fixes: route reads that must be fresh (right after a write, or anything correctness-sensitive) to the primary; use synchronous replication for specific replicas when you need a guarantee, accepting its latency cost; or track a “read-your-writes” token (e.g. the write’s log position) and have the client’s subsequent read wait for a replica to catch up to at least that point.

• Range-based — partition by key ranges (users A–M on shard 1, N–Z on shard 2; or by date range for time-series data). Enables efficient range queries (scan a contiguous range on one shard), but is prone to hot spots — if writes concentrate in one range (all new users get sequential IDs, all recent data lands in the newest date range), one shard takes disproportionate load while others sit idle.
• Hash-based — partition by hash(key) mod N. Distributes load much more evenly (a good hash function scatters keys uniformly regardless of their natural distribution), but destroys range queries — “all users created this week” now scatters across every shard, requiring a fan-out query and merge instead of one contiguous scan.
• Directory-based — an explicit lookup service maps each key (or key range) to its shard, decoupling the partitioning scheme from a fixed formula. Most flexible — supports rebalancing by updating the directory rather than re-hashing everything, and supports uneven/custom partitioning (e.g. large customers get a dedicated shard) — but the directory itself becomes a critical, must- scale, must-not-be-a-single-point-of-failure piece of infrastructure.

The choice hinges on whether range queries matter more than uniform load distribution, and how much operational flexibility you need for future rebalancing.

Resharding is changing the partitioning scheme (adding shards, changing the hash function, moving key ranges between shards) after data already exists and is being actively written to. It is dangerous because it must move live data between machines while writes keep arriving, with the system remaining correct and available throughout: a naive hash(key) mod N scheme means changing N remaps almost every key to a different shard simultaneously, requiring a massive one-time data migration with no natural incremental path. Mitigations: consistent hashing (keys and shards both map onto a hash ring; adding a shard only remaps the keys between its neighbors on the ring, not the whole keyspace) specifically exists to make resharding an incremental, bounded-blast-radius operation instead of an all-at-once one; dual-write / shadow migration (write to both old and new shard layouts during a transition window, backfill history, then cut reads over once verified) is the standard safe pattern when consistent hashing was not designed in from the start. Underestimating resharding’s operational risk — treating it as “just update a config and redeploy” — is a real, recurring cause of production incidents.

A single-node database gives you transactions and joins “for free” because everything lives in one place with one lock manager and one query planner. Sharding breaks both:

• Cross-shard transactions — an operation touching data on two different shards (transferring funds between two accounts that happen to hash to different shards) can no longer rely on a single local ACID transaction; it needs a distributed transaction protocol (two-phase commit, or a saga-style compensating-transaction pattern) with real latency and failure-mode cost, or the schema must be designed so operations that need to be transactional together are co-located on the same shard in the first place (a strong argument for choosing a partition key aligned with your actual transaction boundaries, e.g. shard by account-owner so a user’s own transfers stay single-shard).
• Cross-shard joins — a join across data on different shards cannot be pushed down to a single query planner; it requires fetching from each shard and joining in the application layer (or a federation layer), which is slower and loses the database’s own join optimization. The common mitigation is denormalization — duplicate the data you would have joined onto, accepting the extra storage and write-side consistency work, so reads stay single-shard.

This is why the sharding key is one of the most consequential decisions in the whole schema design — get it wrong and every subsequent query pays for it.

Manual application-level sharding gives you full control but pushes all of the complexity above (routing, resharding, cross-shard transactions/joins) into your own application and operational code. A distributed SQL system bakes horizontal scaling, sharding, and — critically — genuine distributed transactions with strong consistency guarantees into the database engine itself, so the application can largely keep writing normal SQL and relying on ACID semantics across what is transparently a multi-node, multi-region cluster underneath. Reach for it when: you genuinely need both horizontal scale and strong cross-partition transactional guarantees (the case manual sharding handles worst, per the question above), you want to avoid building and operating custom sharding infrastructure, or you are starting a new system and can pick infrastructure that scales without a later painful migration. Trade-offs: it typically costs more in latency for that stronger consistency (the CAP/PACELC trade-off from the consistency subtopic still applies underneath, it is just handled by the database rather than your code), fewer engineers have deep operational experience with it compared to a mainstream RDBMS, and for workloads that are naturally partition-able with no real cross-shard transaction need, manual sharding of a well-understood database can be simpler and cheaper.

1. Diagnose before scaling — check whether it is genuinely throughput-bound (CPU/IO saturated) or a specific bad query/missing index causing lock contention or sequential scans; a missing index masquerading as “needs more hardware” is a common false alarm.
2. Vertical scaling — if genuinely resource-bound and not yet near a practical ceiling, the simplest fix: more CPU/RAM/faster disk, zero application changes.
3. Connection pooling (PgBouncer or similar) — if the bottleneck is connection overhead rather than query execution itself, pooling can materially help before touching the schema at all.
4. Read replicas — if the workload is read-heavy, offload reads to replicas so the primary’s capacity is spent on writes, being explicit about which read paths can tolerate replication lag (see the read-replica question).
5. Partitioning within one instance (e.g. Postgres table partitioning by date) — reduces the working set any single query touches, without yet involving multiple machines.
6. Sharding / horizontal scaling — the last resort once vertical scaling, pooling, replicas, and in-instance partitioning are genuinely exhausted, specifically because of everything sharding costs in transaction/join complexity and operational risk covered above — reach for it because the simpler levers ran out, not first.