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

Quick answer: Scale up when the hardware is genuinely 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?

They fail differently, and the distinction is the whole article.

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 simply 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.

That is the honest framing for cost, too: 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.

What does scaling up genuinely 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 single best 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. Note this is frequently 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, arriving 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.

What cannot scaling up fix?

Problem	Why capacity does not help
Missing index	The scan still reads every row; it just reads them faster. Volume grows with the table, so the ceiling returns.
N+1 query pattern	The 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 statistics	The planner chose badly. A faster machine executes the bad plan faster. Run ANALYZE.
Connection exhaustion	Connections 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 contention	Blocked sessions are not consuming CPU. They are waiting. Adding CPU adds nothing.

How do I diagnose which one I have?

Three steps, in order.

1. Rank queries by cumulative time

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.

2. Pull a plan for the top offenders

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 genuinely 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.

3. Check whether reads are missing cache

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();

Cumulative since the last stats reset, so it 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 very 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?

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, slower, 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?

Your coding agent (Claude Code, Cursor, Copilot) 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, 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, the exact 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.

What about work_mem and configuration?

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), 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.

How do I make the call?

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–5 tomorrow. Just actually do 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.

How DBGorilla helps

DBGorilla connects to your database read-only and works through your AI coding agent (Claude Code, Cursor), giving it the evidence this decision actually requires: the pg_stat_statements ranking, the real EXPLAIN plans with buffer counts and rows-removed-by-filter, the existing indexes, and the actual table row counts. So instead of your agent guessing from the source, or your dashboard saying only "CPU is high", you get a grounded answer about whether a specific query is doing unnecessary work.

It reads and explains. It does not resize instances, change configuration, create indexes, or write to your database. Get started free →

FAQ

When is scaling up actually the right answer? When the hardware is genuinely the constraint: your working set is slightly larger than RAM so reads keep missing cache. You are pinned at a storage IOPS 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 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, an unbounded query, or a plan flip from stale statistics. More CPU and RAM change how fast the waste happens, not how much waste there is.

How do I tell whether it is hardware or queries? Rank by total_exec_time. If a few statements dominate, pull EXPLAIN (ANALYZE, BUFFERS) and look at Rows Removed by Filter versus rows returned. A node reading a huge number of rows and discarding nearly all of them is a query problem. Time spread evenly across efficient plans with high buffer misses points to hardware, but check the direction of causation first. Scan-heavy queries evict pages other queries needed, so high misses can be a symptom of bad queries rather than proof of too little RAM. Use per-query buffers and Rows Removed by Filter to tell working-set pressure from query-induced cache churn. Note too that a "read" only means the block was not in PostgreSQL's shared buffers; it may still have been served from the OS page cache, not the disk.

Is adding an index free? No. Every index adds work to every write touching its columns, consumes storage and buffer cache, and can suppress HOT updates. A non-concurrent build blocks writes while it runs. Indexes are usually worth it, but as a deliberate trade, not a free win.

Will a read replica help if I'm write-bound? No. Every write is still applied on the primary and replayed on each replica, so replicas add write work rather than removing it. They offload reads only. A write ceiling is addressed by reducing write amplification, batching, faster storage, or partitioning.

Related

• How to identify slow queries in PostgreSQL
• How to read a PostgreSQL EXPLAIN ANALYZE plan
• How to find missing indexes in PostgreSQL
• Why are my queries spilling to disk? (work_mem)