Database scaling

Database scaling is the set of moves that let one logical database serve more queries and more data than it was built for. The moves form a ladder: measure, tune queries, buy a bigger machine, pool connections, cache, add read replicas, partition, split by domain, add specialised stores, shard, and finally adopt a database that shards itself. Each rung buys capacity at a price, and the price rises as you climb, so you take the next rung only when measurements say the current one has run out.

Picture it first: one kitchen at dinner rush

A popular restaurant has one kitchen and one head chef who writes every order into the order book. At 8 pm the queue reaches the street. The owner could open three branches tomorrow, but a sensible owner first stands in the kitchen with a stopwatch.

The fixes come in order of cost: rewrite the two slow recipes, buy a bigger stove, have a host seat diners in waves, keep popular desserts ready on a counter, put copies of the order board at side counters, move the bakery and the bar into their own rooms. Only when the kitchen itself is full do new branches, each owning a set of customers, make sense.

Map it back: the stopwatch is monitoring, recipes are queries and indexes, the stove is the instance size, the host is a connection pool, the dessert counter is a cache, side counters are read replicas, separate rooms are per-domain databases, and branches are shards.

The ladder at a glance

Three principles keep the climb sane:

• Climb on evidence. Each rung answers a specific bottleneck; a cache does nothing for a write-bound primary.
• Exhaust cheap rungs first. Rungs 0 to 4 need little application change and are easy to reverse. Rungs 7 to 10 change your data model, failure modes and often team structure.
• Start the next rung early. A functional split or shard migration takes months; begin while the current rung still has headroom.

Reads and writes are different problems

Most scaling confusion disappears once you separate reads from writes. A read can be answered by any up-to-date copy of the data, so you scale reads by making more copies: caches and replicas. A write must be applied to every copy, so copies never reduce write work; they multiply it. You scale writes only by splitting the data so that each machine owns a fraction of it.

So the first question about any scaling problem is the ratio. A feed or a catalogue might serve 100 reads per write; a payments ledger or an IoT ingest pipeline might be close to 1 : 1. Read-heavy systems can go a very long way on rungs 0 to 5. Write-heavy systems reach the top of the ladder sooner. The patterns for each side are collected in scaling reads and scaling writes.

Rung 0: measure before you climb

Every rung fixes one kind of saturation, so first find which resource runs out at peak, and which query shapes cause it.

Set a headroom target rather than waiting for 100%, because latency turns unpredictable well before saturation; Figma acted when its primary passed 65% CPU at peak. Put the numbers on dashboards (observability) and project them forward: at 3× growth a year, 30% CPU today is 90% in twelve months.

Rungs 1–4: make one primary go further

These four rungs keep a single primary and make each unit of its capacity do more. None of them moves data between servers.

1. Tune queries and indexes

Signal: a few query shapes dominate total database time; plans show sequential scans over big tables, huge sorts, or one query issued a hundred times per page.

Gain: usually the biggest improvement per hour of work, with no new infrastructure. Cost: engineering time, plus a write cost for every index you add.

Rank queries by total time, read their execution plans, and add or fix indexes (in practice). Hunt for the N+1 pattern, deep OFFSET pagination and ORM-generated joins: OpenAI traced past severe incidents to one query joining 12 tables.

2. Scale up the primary

Signal: CPU or memory saturated after tuning; the working set no longer fits in RAM; the volume’s IOPS ceiling is reached.

Gain: more of everything with no code change. Cost: the price, one machine as one failure domain, and a hard ceiling.

The ceiling is high. As of October 2026, the largest Amazon RDS classes for PostgreSQL and MySQL offer 192 vCPUs and 1,536 GiB of memory (db.r8g.48xlarge, db.r7i.48xlarge), or 128 vCPUs and 4,096 GiB for memory-hungry working sets (db.x2iedn.32xlarge). An RDS instance holds up to 64 TiB, and io2 Block Express volumes reach 256,000 IOPS. Aurora’s shared cluster volume grows to 128 TiB, or 256 TiB on recent engine versions.

Scaling up cannot fix contention on one hot row, a noisy neighbour workload, or the fact that every failover still takes the whole database with it.

The scale-out line starts higher because splitting has a fixed cost: routing, more replicas, more on-call surface and the engineering time to build it. Below the wall, scaling up is almost always cheaper; vertical vs horizontal scaling covers the general trade-off.

3. Pool connections

Signal: connection counts near the limit, many idle connections, connection storms when the app tier autoscales, or memory consumed per session.

Gain: the database runs a small, steady number of busy connections however many app instances exist. Cost: a pooler to run, and transaction-mode pooling breaks session state such as SET, LISTEN and session-level advisory locks.

PostgreSQL runs a process per connection, so thousands of idle clients cost real memory. OpenAI hit Azure PostgreSQL’s 5,000-connection limit in connection storms; PgBouncer fixed it and cut average connection time from 50 ms to 5 ms. Sizing is in connection pooling.

4. Cache hot reads

Signal: the same rows or results are read again and again; reads dominate CPU; you can tolerate a little staleness.

Gain: most read traffic never reaches the database. Cost: invalidation, stale data, a new dependency, and a dangerous failure mode: when the cache empties, every miss lands on the database at once.

See caching 101 and caching strategies. OpenAI serves most reads from a cache and uses leases so only one request per missing key reaches PostgreSQL (stampedes). Size the database to survive a cold cache.

Rung 5: read replicas

Signal: reads still dominate primary CPU after caching, or users far from the primary need lower read latency.

Gain: read capacity grows roughly with the number of replicas, and replicas double as failover candidates and regional read copies. Cost: replication lag, more instances to pay for, and no help at all for writes.

RDS for PostgreSQL and MySQL allows up to 15 read replicas per primary, and an Aurora cluster up to 15 Aurora Replicas, which share the writer’s storage volume, so their lag is usually well under 100 ms. Two costs deserve attention:

• Lag changes behaviour. A user who updates a profile and reads it back from a replica may see the old value. Route read-your-writes paths to the primary; data replication covers the anomalies and fixes.
• The primary pays for every replica. It streams its WAL to each one; OpenAI, at nearly 50 replicas, is testing cascading replication so that intermediate replicas relay WAL instead.

Rungs 6–8: split by table and by workload

Once one primary is doing only necessary work, the next rungs take work away from it, without yet splitting any single dataset across servers.

6. Partition big tables

Signal: one table dwarfs the rest; deleting old rows generates hours of I/O and bloat; vacuum and index builds on it never finish quietly.

Gain: queries prune to the partitions they need, retention becomes dropping a partition, and maintenance runs per partition. Cost: the partition key must appear in every unique key, and queries without it visit every partition. Partitioning stays inside one server, so it adds no write capacity; the details are in table partitioning.

7. Split by domain

Signal: the schema falls into groups of tables that are rarely joined across groups; one feature’s load or migration hurts unrelated features; separate teams own separate tables.

Gain: each group gets a whole primary of its own, a smaller blast radius and independent scaling. Cost: no joins or transactions across the split; cross-domain consistency moves into the application (sagas), and there are more databases to operate.

This vertical partitioning, or functional split, is the rung most teams underrate. Figma instrumented its ORM to log which tables each transaction touched, then moved groups of tables that were always used together onto their own databases, aiming for under a minute of availability impact per move. Boundaries usually follow your domains (finding service boundaries, monolith vs microservices), and the anti-pattern it fixes is monolithic persistence.

8. CQRS and specialised stores

Signal: queries the primary is bad at: substring and relevance search, analytics scanning months of rows, denormalised feeds, counters.

Gain: each query shape runs on an engine built for it, and the primary only takes transactional work. Cost: read stores lag the primary by seconds, a pipeline to operate, and the same fact stored in several places.

The pattern is CQRS: writes go to the system of record, and change data capture or a transactional outbox streams every change to read-optimised stores: a search index such as Elasticsearch, a columnar warehouse, caches and precomputed views.

Rungs 9–10: split the data itself

When one domain’s writes, storage or working set outgrow the biggest primary you can buy, the dataset itself has to be divided.

9. Shard

Signal: after the cheaper rungs, a single domain still needs more write throughput or storage than one primary provides, and projections show the wall within about a year.

Gain: writes, storage and working set scale with the number of shards. Cost: a shard key in nearly every query, scatter-gather without it, cross-shard transactions, resharding, and every chore multiplied by N. Concepts are in database sharding; Vitess, Citus and the Instagram, Notion and Figma migrations are in sharding a relational database.

10. Distributed SQL or managed NoSQL

Signal: you need horizontal write scale but don’t want to own a sharding layer, or you need strongly consistent writes across regions.

Gain: the engine splits, rebalances and replicates data for you, keeping SQL and transactions. Cost: consensus latency on writes, compatibility gaps with the PostgreSQL or MySQL you know, a migration, and usually a higher bill.

• Google Spanner splits data automatically, replicates it synchronously with Paxos and orders transactions with TrueTime clocks; Google advertises up to 99.999% availability, with GoogleSQL and PostgreSQL dialects.
• CockroachDB breaks tables into ranges, replicates each range three ways by default with Raft, splits ranges as they grow, and exposes a PostgreSQL-compatible SQL API.
• YugabyteDB (architecture) is PostgreSQL-compatible on a Raft-replicated, sharded storage layer; TiDB (overview) is MySQL-compatible, with a row store (TiKV) and a columnar replica (TiFlash) for analytics.
• Aurora PostgreSQL Limitless Database keeps one endpoint in front of a shard group of routers and shards; you declare tables as hash-sharded, collocated with another table on the same key, or reference tables copied to every shard.

Managed NoSQL stores such as DynamoDB and Cassandra scale out from day one, at the price of modelling data around known access patterns with limited joins (Cassandra and DynamoDB).

How far can one primary go?

Interviewers like a number here. The honest answer is a range you derive, not a benchmark quoted from memory.

Reads, when they are CPU-bound. Assume a simple indexed lookup on cached pages costs 100 to 200 µs of server CPU (an assumption; measure your own). On the largest 192-vCPU instance at a 60% CPU target, you have 192 × 0.6 ≈ 115 core-seconds per second, so 115 ÷ 0.0002 ≈ 575,000 to 115 ÷ 0.0001 ≈ 1.15 million simple reads per second at best. Real mixes with joins and sorts cost far more, so plan for a fraction of that.

Writes, when indexes don’t fit in memory. Suppose each insert updates four B-tree indexes on random keys and their leaf pages are usually not cached: about 4 random page reads now and about 4 page writes later, roughly 8 I/Os per insert. At 64,000 IOPS (the top of RDS gp3) that is 64,000 ÷ 8 = 8,000 inserts a second; at io2’s 256,000 IOPS, 32,000. With time-ordered keys and cached indexes, an insert costs little more than its share of sequential WAL, and the limit moves to CPU, which only your own benchmark can measure.

A worked example. A UPI-style payments service handles 10 million payments a day:

• Average: 10M ÷ 86,400 ≈ 116 payments a second; plan for a 10× peak, about 1,160 a second.
• Assume each payment writes about 5 rows (the payment, two ledger entries, a status change, an audit row): 1,160 × 5 ≈ 5,800 row writes a second at peak. That fits under even the uncached-index ceiling of 8,000, but with thin headroom, so keep indexes few and keys time-ordered, and partition the big tables.
• Assume about 20 reads per payment (status polls, history, balance): about 23,000 reads a second at peak, a job for a cache and a few replicas.
• At roughly 250 bytes a row plus indexes, the payments table grows about 105 GB a month, about 1.3 TB a year: decades from a 64 TiB limit.

Most products never outgrow one well-run primary plus replicas and caches. Those that do usually hit write throughput on one domain, one huge table, or operational limits (vacuum, index builds, multi-hour restores) long before raw storage. Practise the arithmetic in back-of-envelope estimation.

Choosing the next rung

When you know which resource saturates, the order of questions matters more than the table. Ask about query cost and connections before anything structural, then split the problem into reads and writes.

Going deeper: real ladders and common mistakes

Figma: up the ladder in order

In 2020 most of Figma’s metadata lived in one large RDS PostgreSQL database, above 65% CPU at peak, with traffic tripling yearly. The team bought a year of runway with rungs 2 to 5: the largest instance available (r5.12xlarge to r5.24xlarge), read replicas, new databases for new features, and PgBouncer in front of thousands of connections. Writes were still heavy and some reads were too lag-sensitive for replicas, so, after ruling out NoSQL, Vitess and managed distributed PostgreSQL, it chose rung 7 and moved groups of tables to their own databases. Only when single tables reached terabytes and billions of rows did it shard.

OpenAI: one primary, very far

OpenAI’s 2026 write-up describes PostgreSQL serving ChatGPT’s 800 million users from a single primary and nearly 50 read replicas, at millions of queries per second, after load grew more than 10× in a year. The ladder shows in the details: query tuning and timeouts, PgBouncer, a cache with leases, replicas now limited by WAL fan-out, separate instances for high- and low-priority traffic, rate limits per query shape, and schema changes capped at a 5-second timeout. For writes it took the rung-7 and rung-9 route: shardable, write-heavy workloads moved to sharded systems such as Azure Cosmos DB, and new tables are no longer allowed in PostgreSQL. Its one severe PostgreSQL incident that year came from a write surge of more than 10× during a viral launch, exactly where a single primary is weakest.

Common mistakes

• Sharding too early. You pay the shard-key tax on every query and run N databases for years before you need them.
• Caching before indexing. The cache hides a missing index until it goes cold, and then the full scan arrives thousands of times at once.
• Replicas for a write problem. Every replica applies every write; more replicas add WAL shipping load and nothing for a write-bound primary.
• Scaling up a contention problem. More cores don’t help transactions queueing on one counter row; fix the hot row (contention patterns) or batch updates.
• Forgetting the connections. Scaling the app tier from 20 to 200 pods multiplies connections tenfold; pool first.
• Climbing too late. A split or shard migration needs spare capacity to copy data while serving traffic. Notion’s sharding retrospective lists “shard earlier” first; the same holds for every structural rung.

Cheat sheet

How to say it in the interview

“I’d start with one PostgreSQL primary, because it will carry us further than people expect, and climb only when metrics say so. First I’d find what saturates at peak. The cheap rungs come first: fix the heavy queries and indexes, scale up the instance, put PgBouncer in front, and cache hot reads with protection against stampedes. Reads beyond that go to replicas, with read-your-writes paths pinned to the primary. Writes are the real limit, so I’d partition the big time-ordered tables, split independent domains into their own databases, and feed search and analytics from CDC instead of querying the primary. If one domain still outgrows the biggest instance, I’d shard that domain by its natural key, or use distributed SQL if the team doesn’t want to own sharding.”

Why not shard from day one? Sharding taxes every query and operation for years, and most products never need it. Shard the one domain whose writes outgrow a single machine, when projections show that within about a year.

The primary is at 90% CPU and most of it is reads. What now? Check the top queries first; one bad plan can be most of it. Then cache hot reads and move lag-tolerant reads to replicas, keeping read-your-writes on the primary. A bigger instance buys time meanwhile.

The primary is saturated by writes. Do replicas help? No. Every replica replays every write. Reduce the writes (fewer indexes, batching, removing redundant updates), partition so indexes stay small and deletes become drops, scale up for time, then split by domain or shard.

When would you choose distributed SQL over sharding PostgreSQL yourself? When you need horizontal writes or multi-region strong consistency without building routing, resharding and cross-shard transactions yourself. You accept consensus latency, compatibility gaps and cost; for a large existing PostgreSQL estate, Citus may be the smaller migration.

How do you know which rung comes next? Measure at peak: CPU by query shape, cache hit ratio, IOPS against limits, lock waits, connections and lag. The saturated resource picks the rung.

Where to go next

• Database sharding: shard keys, placement, hot shards and resharding, the top rungs in depth.
• Sharding a relational database: Vitess, Citus and how Instagram, Notion and Figma did it.
• Data replication: how replicas work and the lag anomalies you must design around.
• Table partitioning: pruning and retention inside one database.
• Connection pooling: PgBouncer, RDS Proxy and pool sizing.
• Caching strategies: cache-aside, read-through and write-through.
• Change data capture: feeding search, warehouses and caches from the primary’s log.
• Scaling reads and scaling writes: the interview patterns for each side.

References and further reading

1. Scaling PostgreSQL to power 800 million ChatGPT users (OpenAI, 2026)openai.comOne primary and nearly 50 read replicas serving millions of QPS; offloading writes, PgBouncer, cache leases, workload isolation and WAL fan-out limits.
2. The growing pains of database architecture (Figma, 2023)figma.comFigma’s climb: a bigger RDS instance, read replicas, PgBouncer and new databases, then vertical partitioning by groups of tables.
3. Amazon RDS: Hardware specifications for DB instance classesdocs.aws.amazon.comvCPU and memory of the largest classes (db.r8g.48xlarge, db.r7i.48xlarge, db.x2iedn.32xlarge), checked against the engine-support tables.
4. Amazon RDS: DB instance storagedocs.aws.amazon.com64 TiB maximum storage for MySQL and PostgreSQL, gp3 up to 64,000 IOPS and io2 Block Express up to 256,000 IOPS.
5. Amazon RDS: Quotas and constraintsdocs.aws.amazon.comUp to 15 read replicas per primary DB instance.
6. Amazon Aurora: Replication with Auroradocs.aws.amazon.comUp to 15 Aurora Replicas on a shared cluster volume, with replica lag usually well under 100 ms.
7. Amazon Aurora: Quotas and size limitsdocs.aws.amazon.comCluster volume limit of 256 TiB on recent Aurora PostgreSQL and MySQL versions, 128 TiB on earlier ones.
8. Aurora PostgreSQL Limitless Database architecturedocs.aws.amazon.comShard groups of routers and shards, hash-sharded, collocated and reference tables behind one endpoint.
9. Google Cloud Spanner overviewcloud.google.comAutomatic splits, synchronous Paxos replication, TrueTime-ordered transactions, up to 99.999% availability, GoogleSQL and PostgreSQL dialects.
10. CockroachDB architecture overviewcockroachlabs.comData split into ranges replicated three ways by default with Raft, automatic range splits, and SERIALIZABLE and READ COMMITTED isolation.