Cruxly › System design › Databases: Choosing One

Scaling Postgres and MySQL before you leave them

a ladder of cheaper, reversible steps, climbed in order, each with a signal

"My Postgres is at 80% CPU" is a symptom, not a reason to change databases. Between that symptom and a new system sit about nine cheaper steps: fix queries, buy a bigger machine, pool connections, cache, add replicas, partition tables, archive and split analytics, split by function, shard. Each has a signal that says climb and a cost you can name. Real companies (OpenAI, Notion, Figma, and Shopify for sharding) followed much of this path and went a long way on relational databases.

The ladder: cheapest and most reversible first

The choosing lesson ended with a rule: stay on a relational primary until a measured limit says otherwise. This lesson is everything between "one primary is fine" and "we need a different database". Climb in this order, and stop as soon as the signal clears.

The order follows two things: cost, and how easily you can undo it. Rungs 1 to 5 are mostly easy to back out of. Rung 9 changes how every query is routed, and rung 10 is a migration. Skipping to either is the classic interview mistake.

Rungs are not strictly sequential. The signal picks the rung: refused connections go to rung 3 whatever the CPU says, and a 3 TB events table goes to rung 6. Rung 1 never really ends. Teams also swap 4 and 5: replicas are simpler to run than a cache with invalidation, and many add them first.

:::key Reads have many cheap answers. Writes have few. Rungs 3, 4 and 5 relieve reads and connections. For writes, only fixing queries (fewer, cheaper writes), a bigger box, a functional split, sharding or moving a workload give the primary relief. Partitioning and archiving make vacuum and retention cheaper but do not cut write volume. That is why writes decide when you reach the top of the ladder. :::

Rung 1: fix the queries, the indexes and the bloat

Start where the load comes from. In Postgres, pg_stat_statements ranks statements; in MySQL use the slow query log and performance_schema. Rank by total time (calls x average), not the slowest single call. For example, a 2 ms query run 50,000 times a second (assumed numbers) can cost more than a 3 second report.

Then read the plan with EXPLAIN (ANALYZE, BUFFERS). What you usually find:

Bloat is the quiet one. Postgres MVCC writes a new copy of the row on update, so heavy updates leave dead tuples that queries must skip and autovacuum must clean. OpenAI names this write and read amplification as the weak spot of its setup. A transaction left open and idle holds vacuum back, so set idle_in_transaction_session_timeout. If vacuum cannot keep up on your largest tables, that points at later rungs, not at tuning forever (Notion sharded for exactly this reason).

:::key Stop condition Rung 1 is done when the top few statements are no longer most of the load and the plans use indexes. If CPU is still high with healthy queries, you are really out of capacity and can climb. :::

Illustrative example (assumed numbers): one statement is 70% of total time because a filter on customer_id scans 40 million rows. An index drops CPU from 80% to roughly 30%. That is a day of work, and it avoided a migration.

Rungs 2 and 3: a bigger machine, and the connection wall

A bigger machine is the honest bridge. Figma's April 2023 post, describing its 2020 fixes, lists it first: its single RDS Postgres was at upwards of 65% CPU at peak, and it moved from r5.12xlarge to r5.24xlarge to buy about a year, together with replicas, new databases for new use cases, and PgBouncer. Do it when queries are already healthy and you need time.

Its limits:

• A ceiling. Figma's later post says it was on the largest instance AWS offered.
• Soft limits arrive first. Notion (2021) said that query performance and upkeep, such as autovacuum, degrade well before a table reaches the hardware maximum. Its joke about clicking the RDS "Resize Instance" button like a clicker game is the right attitude: it works until it does not.
• IOPS caps. Figma's highest-write tables were approaching the maximum IOPS RDS supports, and a bigger CPU does not move that.
• A resize can mean a restart or failover. Schedule it.

Connections are the first wall. Postgres starts one process per connection, and the documented default max_connections is typically 100. Forty pods with a pool of 20 each is already 800 connections. OpenAI notes a 5,000 connection maximum on its Azure instance and had incidents from connection storms. The earlier operations lesson explains pooling; here are the scale facts:

• OpenAI measured average connection time falling from 50 ms to 5 ms with PgBouncer in statement or transaction mode, and keeps the proxy, clients and replicas in the same region.
• Figma had connections in the thousands before adding PgBouncer.
• PgBouncer uses about 2 kB per connection by default, so it can hold far more client connections than Postgres could.
• MySQL uses a thread per connection too. ProxySQL multiplexes many client connections onto fewer backend ones. Its docs say multiplexing is disabled during an active transaction and while table locks are held, and stays off for the rest of the connection after GET_LOCK().
• The pooler can become the next limit. In its 2023 re-shard (problems seen from late 2022), Notion hit connection limits scaling PgBouncer and had to shard it too.

Transaction pooling breaks session features. PgBouncer's docs list SET/RESET, LISTEN, PREPARE/DEALLOCATE, WITH HOLD cursors, and session-level advisory locks (among others) as not working in transaction mode. Protocol-level prepared statements work only if max_prepared_statements is non-zero.

Rungs 4 and 5: a cache, then read replicas

A cache in front removes repeat reads entirely, which beats making them cheaper. The cost is staleness, invalidation, and one nasty failure. OpenAI describes the pattern behind several SEVs: a caching-layer failure causes widespread misses, the misses hit Postgres, CPU saturates, requests time out, and retries amplify the load. Their fix is a lock or lease on a missed key: one request fetches from Postgres and refills the cache, and the rest wait. Pair it with rate limits and with retry intervals that are not too short.

Read replicas are the next rung when reads dominate. They work: OpenAI runs one primary with nearly 50 replicas across regions for millions of queries per second (January 2026 post). What breaks as you add them:

Figma found that not all reads could move to replicas because parts of its application were sensitive to lag. Plan for that: replicas take the reads that tolerate staleness, and the rest stay on the primary.

:::key Replicas help reads, not writes If the primary's write load is the problem, replicas leave it untouched, and each one adds WAL work. That points to rungs 6 to 9. :::

Rungs 6 and 7: partition the table, archive the old data

Partitioning splits one logical table into many physical tables that Postgres treats as one (range, list or hash). Use it on big tables with a natural key, usually time: events, logs, orders by month.

What you get, from the Postgres docs:

• Partition pruning. If the WHERE clause constrains the partition key, the planner skips partitions that cannot match (enable_partition_pruning is on by default). Queries without the key scan every partition.
• Cheap retention. Dropping a partition with DROP TABLE, or ALTER TABLE ... DETACH PARTITION, is far faster than a bulk DELETE and avoids the VACUUM work a bulk delete creates. DROP takes an ACCESS EXCLUSIVE lock on the parent; DETACH PARTITION ... CONCURRENTLY exists as an alternative.
• Detach, then archive. A detached partition is a normal table you can dump, aggregate or ship before dropping it.

What you pay:

• A maintenance job. Someone must create next month's partition before it is needed and drop the old ones.
• Key rules. A primary key or unique constraint on a partitioned table must include all the partition key columns.
• It is not sharding. All partitions sit on the same server, share its CPU, IOPS, WAL and connection limit. Partitioning shrinks the unit of maintenance, not the machine. MySQL has range partitioning with the same trick (ALTER TABLE ... DROP PARTITION).

Archive and separate analytics (rung 7). Keep the hot window your queries actually need, usually weeks or months, and move the rest to cheaper storage: object storage or a warehouse. Detach a partition, copy it out, then drop it. Feed dashboards and long scans from a copy made through change data capture, not from the primary. Uber's 2016 post noted that long reads on Postgres replicas can lag or be killed, so a report replica alone is not enough.

Time-series workloads show the trap: one row per reading in an unpartitioned table, with retention done by DELETE. Partition by time and drop partitions instead.

Rung 8: split by function and move analytics out

A functional split (Figma calls it vertical partitioning) moves whole groups of tables to their own database. It gives a heavy table family its own machine without changing how any single table is stored. By the end of 2022, Figma had caching, read replicas and a dozen vertically partitioned databases, with families such as files and organizations on their own. Its later post calls this a relatively easy and very impactful lever.

How Figma chose tables (April 2023 post):

1. Impact. Move tables that carry a lot of load. They measured average active sessions per query, sampling pg_stat_activity every 10 ms and grouping by table.
2. Isolation. Move tables that are rarely joined with others. Ruby and ActiveRecord hid which tables a query touched, so they added runtime validators that logged queries and transactions into their warehouse.

What it costs. Once tables live in different databases you lose atomic transactions between them, foreign key checks and joins. The app must join in code and check references itself. A migration without downtime needs every app instance to switch routing at the right moment; Figma's goals were under a minute of availability impact, automation, and the ability to undo.

Limit. The smallest unit is a table. When one table outgrows a machine (several TB, billions of rows, vacuum trouble, near the IOPS cap), a functional split cannot help.

Isolate workloads, not only tables. OpenAI splits requests into low and high priority and sends them to separate instances, and does the same per product, so a new feature's slow query cannot starve a critical one. That is the same idea at the instance level.

Analytics belongs elsewhere. Move reports to a warehouse, fed by change data capture. Reporting on the primary competes for IO and locks, and a replica has the replay-conflict limit from the previous rung.

Rung 9: shard, and know the bill before you start

Shard when a signal shows that no single machine will do, not before:

Choose the route.

The shard key decides everything. Notion keyed by workspace id and sharded every table reachable from block by foreign key, so a workspace's rows live together and most joins and transactions stay local. Shopify keys by shop id. For B2B SaaS, tenant id is the default. The earlier lesson on partitioning and sharding covers cardinality, skew and hot keys.

What you lose. Cross-shard joins, foreign keys and atomic commits (or you pay for TwoPC). Reports that span tenants fan out to every shard. Unique ids need a scheme.

Resharding is a feature you must build. Notion used logical shards (15 schemas per database, on 32 databases) and in 2023 moved from 32 to 96 machines by dividing those schemas, 15 per host down to 5. Shopify balances shards online: before rebalancing, its least and most used shards differed by almost 4x, and a simulation of planned shop moves brought that closer to 2x. Pick many logical shards on few hosts so growth is a move, not a rehash.

OpenAI's counterpoint. It left Postgres unsharded because sharding existing workloads would mean changes to hundreds of endpoints, taking months or years. It no longer allows new tables on the main cluster, defaults new workloads to sharded systems such as Azure Cosmos DB, and has migrated its shardable write-heavy workloads there. It does not rule out sharding Postgres later.

Rung 10 and the real ladders, with dates

Public stories show the ladder in use. Read them with their dates, because the tools changed.

OpenAI reports low double-digit millisecond p99 latency, five-nines availability, and one SEV-0 Postgres incident in 12 months. Its SEVs follow a pattern: an upstream spike (a cache failure, a join storm, a write storm) pushes Postgres past its limit, and retries make it worse.

Uber's 2016 post is version-dated. Uber moved from Postgres to MySQL, citing write amplification from indexes pointing at physical row locations, physical WAL replication cost across data centers, replicas that lag or cancel long reads, process-per-connection limits, and painful major upgrades. The analysis was based mainly on Postgres 9.2 (released in 2012), and the post itself is now about ten years old. This lesson did not verify which of those points later versions fixed, so check current versions before you quote it. Use it as a list of weaknesses to measure, not as proof that Postgres cannot scale.

Rung 10 is real. Leave when the signal names something relational does badly: sustained writes past what one box (or a sharded fleet you can run) can take, multi-region active-active, huge scans better served by columnar storage, search relevance, or traversals. Notice that even OpenAI took this step partially: one workload class moved, the rest stayed. The earlier lessons on choosing a database and on distributed SQL cover how to decide.

The interview answer, and wrong statements to avoid

Question: "Your Postgres is at 80% CPU. What do you do?" Say it in this order.

1. Ask what the 80% is. Peak or all day? Primary or replica? Reads or writes? How fast is it growing? Figma acted at 65% peak because latency gets unpredictable near the limit, so the answer depends on the trend.
2. Find the cause. Rank statements by total time, read the plans, check connection counts, idle-in-transaction sessions, dead tuples and vacuum, and whether the time is query work or waiting on IO.
3. Apply the cheap reversible fixes. Fix the top queries and indexes, remove N+1 patterns, set timeouts, add a pooler if connections are the issue, cache the hottest reads with a lock on misses.
4. Buy time and offload. Move tolerant reads to replicas and keep read-after-write on the primary. Resize if needed, as a bridge with a date.
5. Plan the structural step from the signal. Big time-ordered table: partition and archive. One table family dominates: split it out. Writes, vacuum or IOPS beyond the biggest instance: shard by tenant, and say what you give up. Only a shape relational does badly justifies a different system.

Add the guard rails: rate limits and load shedding for surges, avoid schema changes that rewrite a big table, and a number that would make you revisit (for example, sustained CPU above 80% after rungs 1 to 5, or vacuum falling behind).

Common wrong statements

How to say it in the interview

"Before I change anything I'd find out what the 80% is: which queries, reads or writes, and whether it's the peak or all day. Then I climb the cheapest reversible step that matches the signal: fix the top queries, pool connections, cache hot reads, add replicas. A bigger machine buys time. Partitioning suits big time-ordered tables. A functional split and sharding come when writes, vacuum or IOPS outgrow the biggest box, and I'd say what each costs."

Checklist

• Refused to change databases on '80% CPU' alone and asked what the 80% is
• Listed the ladder in order with the signal that triggers each rung
• Ranked queries by total time and read the plan before adding hardware
• Handled connections with a pooler and named what transaction mode breaks
• Said what breaks with replicas: lag, read-your-writes, replay cancellations, WAL fan-out
• Separated partitioning from sharding and used drop-partition for retention
• Named the shard key and the bill: cross-shard queries, transactions, resharding
• Used company examples with dates, and called Uber 2016 version-dated

Related lessons

• How to choose a database
• ACID, precisely
• Isolation levels, in depth
• Data models: what each family is built for, and where it fails
• Storage engines: B-trees, LSM-trees, row vs column
• NoSQL modelling in practice: DynamoDB, Cassandra, MongoDB