Database Scaling Techniques for Millions of Users
One primary database works until it does not. At roughly tens of millions of users, the same box that once felt fine shows the classic symptoms: queries crawl, CPU sits near 100%, and the app waits on a growing request queue.
Big products do not keep a single database forever. They combine read replicas, sharding, partitioning, and caching so reads, writes, and hot data each have a path. Architecture — not a bigger VM alone — is how Facebook, Uber, Amazon, and Netflix survive traffic. For scale-up vs scale-out at the machine layer, see [Horizontal vs Vertical Scaling](/blog/horizontal-vs-vertical-scaling).
Running example: an e-commerce catalog and orders service approaching ~10 million users — reads dominate product pages; writes spike at checkout.
When one database is not enough
A single primary owns every read and write. As concurrent queries grow, locks and I/O stack up: queries slow down, CPU saturates, and the application feels slow even when app servers are fine. Vertical scaling (bigger instance) buys time — then you hit a ceiling or a single point of failure.
The goal of the four techniques below is not magic speed for one query; it is optimized queries, balanced CPU, and no endless request queue under load.
Quick reference
- Symptom triad: slow queries, CPU ~100%, app latency up.
- Bigger hardware helps until cost or failure domain blocks you.
- Measure first: p95 query time, connections, CPU, disk I/O.
- Most teams add cache + replicas before sharding.
Remember this
I can recognize single-DB overload and why architecture beats only buying a larger instance.
Read replicas: offload SELECT traffic
A read replica is a copy of the primary that serves reads. The app sends SELECTs to replicas and writes to the primary. Replication lag means replicas can be slightly behind — fine for product pages, risky for "read your write" right after checkout unless you route that path to primary.
When to use: read-heavy workloads (catalogs, feeds, reports). When to skip: write-dominated systems where the primary is already the bottleneck — replicas do not multiply write capacity.
Quick reference
- Primary = writes; replicas = reads.
- Watch replication lag for freshness-sensitive reads.
- Connection routing: read/write splitting in the app or proxy.
- Replicas improve availability for reads if the primary dies (failover still needs a plan).
Remember this
I know replicas scale reads, not write throughput, and when lag matters.
Partitioning: split large tables inside one database
Partitioning divides a large table into pieces inside the same database — by range (date), list (region), or hash. Queries that prune partitions scan less data; old partitions can be archived or dropped cheaply.
Unlike sharding, you still have one logical database and usually one primary for writes. Partitioning is often the right move before sharding when a single huge table (events, orders by month) dominates I/O.
When to use: time-series or huge fact tables with predictable filters. When not to: small tables or random access by keys that never prune.
Quick reference
- Same DB, smaller physical pieces of a table.
- Partition pruning = fewer blocks scanned.
- Common: monthly order or event partitions.
- Sharding ≠ partitioning — shards are separate DBs; partitions are intra-DB.
Remember this
I can contrast table partitioning (one DB) with sharding (many DBs).
Caching: serve hot data from memory
Caching keeps pre-fetched hot rows in memory (Redis, Memcached, or app memory) so the database is not hit on every product page. Flow: app → cache → on miss → DB → store in cache. Hit rates of 80–95% on catalogs are common.
When to use: frequently read, slowly changing data. When not to: every write-sensitive balance without invalidation — see [Caching 101](/blog/caching-101) for layers and eviction.
Quick reference
- Cache hit = skip DB; miss = load and populate.
- TTL + invalidation keep freshness honest.
- Fails open: if cache is down, still read the DB.
- Pairs well with replicas — fewer reads reach any database.
Remember this
I can place cache on the app→DB path and know what belongs in memory.
Choose the order that matches the bottleneck
A practical progression for most apps: (1) cache hot reads, (2) read replicas when SELECT load still saturates the primary, (3) partition huge tables, (4) shard only when data or writes outgrow one primary. That order optimizes queries, balances CPU, and avoids request queues without early distributed-database pain.
Outcomes you want: faster queries, load spread across nodes, resilience (no single overloaded box), and room to add capacity. Architecture design is the key — companies at global scale never bet everything on one database instance.
Quick reference
- Cache first for read-heavy pages.
- Replicas next for SELECT scale-out.
- Partition fat tables before cross-machine sharding.
- Shard last — measure write/storage pain first.
- Practice: diagram your app’s read/write ratio and pick the next technique only.
Remember this
I can order cache → replicas → partition → shard based on the real bottleneck.
Scaling databases for millions of users is a stack of techniques: replicas for reads, partitioning for huge tables, sharding for multi-node data, and caching so most traffic never touches disk. Start from measured pain — CPU, slow queries, queue depth — not from a fashion for shards.
Your homework: take one production-like schema. Label each table read-heavy or write-heavy. Write a one-page plan: what you would cache, what would go to replicas, which table you would partition, and whether you truly need a shard key yet. Compare notes with [Horizontal vs Vertical Scaling](/blog/horizontal-vs-vertical-scaling) and [Caching 101](/blog/caching-101).
Related Articles
Explore this topic