Skip to content

When should I scale up my database instead of optimizing queries?

Last updated

View as Markdown

Quick answer: Scale up when the hardware is the limit: your working set is just past RAM, you are pinned at a storage IOPS ceiling, or CPU is saturated by concurrent queries that are already efficient. Optimize when a query does work it should not: a missing index, an N+1, an unbounded scan. A bigger instance makes wasted work faster; it never makes it stop.

What is the actual difference between the two problems?

Section titled “What is the actual difference between the two problems?”

They fail differently, and the right response depends on which one you have.

A hardware limit means the work is real and the machine cannot keep up. Every row you read is a row you need. Every query has a sane plan. There is more demand than the box can serve.

A query problem means the machine is busy doing work that has no reason to exist. A query that scans ten million rows to return twelve is doing the same amount of pointless work on any instance size. Double the CPU and you halve the wall-clock time of the waste. You do not remove it. The next data-volume increase puts you right back where you were, at a higher recurring bill.

The same applies to cost: doubling an instance roughly doubles what you pay for it. It does not halve the work an unindexed scan performs. If the underlying work is unbounded relative to your data growth, buying capacity is buying time, not a fix.

Three cases, all real:

Your working set nearly fits in RAM. Postgres serves reads from shared buffers and the OS page cache. When the actively-queried slice of your data is a little larger than available memory, you get thrash: pages are evicted just before they are needed again, and read volume climbs out of proportion to traffic. This is the strongest case for more memory, because the fix is a step change: once the working set fits, misses collapse. No amount of query rewriting produces that effect if the queries are already reading only what they need.

You are at a storage IOPS or throughput ceiling. Managed volumes have hard limits, often tied to provisioned size or tier. If you are pinned at the ceiling with efficient plans, more IOPS is the fix. This is often the symptom of a query problem rather than a cause: an unindexed scan generates enormous read volume, so check plans before you conclude the ceiling is the constraint.

CPU is saturated by legitimate concurrent work. Many well-planned queries, all doing necessary work, arrive faster than the cores can retire them. Time is spread across the workload rather than concentrated in a few statements. That is a capacity problem.

There is a fourth, situational case: an incident. If you are actively down or degrading and a resize buys you an hour, take the resize. Optimizing under production pressure with an audience is how you ship a worse fix. Scale up, get stable, then diagnose properly, and write down that you owe the fix, because the cost is now recurring and the cause is still there.

ProblemWhy capacity does not help
Missing indexThe scan still reads every row; it just reads them faster. Volume grows with the table, so the ceiling returns.
N+1 query patternThe cost is thousands of round trips, each individually trivial. Latency per round trip barely moves with instance size.
Unbounded query (no LIMIT, no time bound)Rows scanned grow with your data forever. Any fixed capacity is eventually exceeded.
Plan flip from stale statisticsThe planner chose badly. A faster machine executes the bad plan faster. Run ANALYZE.
Connection exhaustionConnections cost memory and scheduler pressure per connection. This is a pooling problem: put PgBouncer (or your platform’s pooler) in front. Do not assume a bigger instance raises your usable connection ceiling.
Lock contentionBlocked sessions are not consuming CPU. They are waiting. Adding CPU adds nothing.

Three steps, in order.

SELECT
calls,
round(total_exec_time::numeric, 1) AS total_ms,
round(mean_exec_time::numeric, 2) AS mean_ms,
round(
(100 * total_exec_time / sum(total_exec_time) OVER ())::numeric,
1
) AS pct_of_total,
query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

total_exec_time is double precision, and there is no round(double precision, integer) in PostgreSQL. Cast to numeric first, as above, or you will get a function-does-not-exist error. On PostgreSQL 12 and earlier these columns are named total_time and mean_time; PG13 renamed them to total_exec_time / mean_exec_time.

These counters are cumulative since the last reset. To reason about a specific window, call pg_stat_statements_reset() and let it accumulate.

Read the shape, not a target number:

  • Time concentrated in a handful of statements → look at those statements. That is a query problem until a plan proves otherwise.
  • Time spread broadly across many statements with sane per-call times → that is a capacity shape. Keep going, but scaling is now on the table.
EXPLAIN (ANALYZE, BUFFERS)
SELECT ...;

The decisive signal is not “is there a Seq Scan.” A sequential scan is the correct plan when a query reads a large fraction of the table, and on small tables it is cheaper than any index. What matters is the ratio of rows returned to rows examined:

Seq Scan on events (cost=... rows=... width=...)
(actual time=0.031..4102.9 rows=12 loops=1)
Filter: (account_id = 91823)
Rows Removed by Filter: 9847113
Buffers: shared hit=1204 read=182339

Twelve rows returned, nearly ten million discarded. That is the definition of work that should not exist, and it costs the same on any instance. Contrast with a Seq Scan returning eight million of ten million rows: that plan is fine, and if it is slow, it is slow because there is a lot of legitimate data to move.

Rows Removed by Filter relative to actual rows is your query-vs-hardware discriminator. High ratio → index or predicate problem. Low ratio with big Buffers numbers → you are moving a lot of data, which is where memory and I/O capacity start to matter.

Also compare estimated to actual rows on each node. A large gap means the planner optimized for a table that does not exist; the fix is usually ANALYZE or extended statistics, not hardware. See how to read an EXPLAIN ANALYZE plan.

SELECT
datname,
blks_hit,
blks_read,
round(
100.0 * blks_hit / nullif(blks_hit + blks_read, 0),
2
) AS hit_pct
FROM pg_stat_database
WHERE datname = current_database();

The counters are cumulative since the last stats reset, so this is a coarse instrument: a database that was healthy for six months and started thrashing yesterday still shows a flattering ratio. Use it directionally, alongside per-query BUFFERS output, which is scoped to the statement you actually care about.

A persistently low hit ratio has two different causes: the working set does not fit (memory case, scale up), or a few queries are dragging enormous amounts of data through the cache and evicting everyone else’s pages (query case, fix the queries, and the ratio recovers without new hardware). Step 2 tells you which.

Are indexes the free lunch they look like?

Section titled “Are indexes the free lunch they look like?”

No, and this is where “just add an index” advice goes wrong.

An index is a permanent write-side tax. Every INSERT, UPDATE, or DELETE that touches indexed columns must maintain every relevant index, forever, on every write, whether or not the index is ever used for a read. Indexes also occupy storage and compete with table data for the same buffer cache you may be trying to conserve. And an index on a frequently-updated column can suppress HOT updates, which increases the volume of dead tuples autovacuum has to clean up.

Building one is not free either: a plain CREATE INDEX takes a lock that blocks writes on the table for the duration. Use CREATE INDEX CONCURRENTLY in production. It is slower, makes two table passes, cannot run inside a transaction block, and can leave an invalid index if it fails, but it does not stop your writes.

None of this means do not index. It means an index is a trade: you are buying read performance with write performance and memory. Make that trade deliberately, on a query you have actually profiled, and drop indexes nothing uses, because you are paying for those on every write with no return.

Why does AI-generated code push you toward scaling up?

Section titled “Why does AI-generated code push you toward scaling up?”

Your coding agent writes plausible SQL against a schema, not against your data. It cannot see that events has a hundred million rows, that account_id has no index, or that the loop it just wrote will issue one query per item in a collection that is small in dev and large in production. Every query it produces is locally correct and syntactically clean.

So the failure does not surface as “bad SQL.” It surfaces as CPU saturation, cache thrash, or I/O saturation: symptoms that look like a capacity problem. The dashboard says the instance is at its limit, and the fastest apparent fix is a bigger instance. Nobody in that loop has looked at a plan.

This is why AI-heavy codebases drift toward over-provisioned databases: the code review layer never sees row counts, and the infrastructure layer never sees the query. The diagnostic step that connects them (rank by total time, pull the plan, check rows-removed-by-filter) is the step that gets skipped.

Configuration sits between the two options and is worth checking before either.

work_mem is the most commonly misunderstood setting. It is not per connection, and hash nodes get work_mem x hash_mem_multiplier (default 2.0 since PostgreSQL 15, 1.0 before), so a hash join may use twice what you budgeted. It is a limit applied per sort or hash node, per execution: a single query with several sorts and hashes can allocate it multiple times over, and a parallel query can allocate it per worker. So the popular “RAM ÷ max_connections” formulas are wrong in both directions: too generous for simple queries, dangerously optimistic for complex parallel ones. Raise it targeted (per session or per role) for the specific queries spilling to disk rather than globally.

The practical point for this decision: a query spilling a sort to disk looks like an I/O problem on your monitoring. It may just need a few more megabytes of work_mem for that one statement. Rule that out before concluding you need a bigger machine.

  1. Rank by total_exec_time. Concentrated → query problem. Diffuse → maybe capacity.
  2. Pull EXPLAIN (ANALYZE, BUFFERS) on the top statements. High rows-removed-to-rows-returned → query problem, full stop.
  3. Check estimated vs actual rows. Big gap → run ANALYZE before doing anything else.
  4. Check whether sorts are spilling. If so, try targeted work_mem first.
  5. Check connection count and whether you are pooling. If not, pool before you resize.
  6. If plans are clean, work is necessary, and you are pinned on memory, IOPS, or CPU, scale up. That is what capacity is for.
  7. If you are mid-incident, do step 6 first and steps 1 to 5 tomorrow. Do not skip them.

And run the loop in the other direction occasionally. After a real optimization pass, the instance you are paying for may be larger than the workload now needs. Right-sizing down is the same decision with the sign flipped, and the same evidence supports it.

  • When is scaling up a Postgres database actually the right answer?

    When the hardware is genuinely the constraint: your working set is slightly larger than RAM so reads keep missing the buffer cache, you are pinned at a storage IOPS or throughput ceiling, or CPU is saturated by many concurrent queries that are each already efficient. It is also the right short-term move during an incident, to buy time while you fix the real cause.

  • What can a bigger database instance never fix?

    Anything where the query does work it should not do at all: a missing index that forces a scan of millions of rows to return a handful, an N+1 loop issuing thousands of round trips, an unbounded query with no LIMIT, or a plan flip caused by stale statistics. More CPU and RAM change how fast the waste happens, not how much waste there is.

  • How do I tell whether my problem is hardware or queries?

    Rank queries by total_exec_time in pg_stat_statements. If a small number of statements dominate, pull EXPLAIN (ANALYZE, BUFFERS) for each and look at Rows Removed by Filter and buffer counts. A node that reads a large number of rows and discards nearly all of them is a query problem. If time is spread evenly across efficient plans and buffer misses are high, that points to hardware, but confirm the direction first, because scan-heavy queries evict pages other queries needed, so high misses can be a symptom of bad queries rather than proof of insufficient RAM. Use per-query buffer counts and Rows Removed by Filter to separate working-set pressure from query-induced cache churn. A read also only means the block was not in PostgreSQL shared buffers; it may still have come from the OS page cache rather than disk.

  • Is adding an index free?

    No. Every index adds work to every INSERT, UPDATE and DELETE that touches its columns, consumes storage and buffer cache that competes with your table data, and can suppress HOT updates. A non-concurrent build also takes a lock that blocks writes for the duration. Indexes are usually worth it, but they are a permanent write-side cost, not a free win.

  • Will a read replica help if my database is write-bound?

    No. Every write still has to be applied on the primary and replayed on each replica, so replicas add write work rather than removing it. Read replicas offload read traffic only. A write-throughput ceiling is addressed by reducing write amplification, batching, faster storage, or partitioning, not by adding replicas.