# 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

```sql
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

```sql
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](https://www.dbgorilla.com/learn/postgres/how-to-read-a-postgres-explain-analyze-plan/).

### 3.  Check whether reads are missing cache

```sql
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 →](https://app.dbgorilla.com/signup)

## 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](https://www.dbgorilla.com/learn/postgres/how-to-identify-slow-queries-in-postgresql/)
- [How to read a PostgreSQL EXPLAIN ANALYZE plan](https://www.dbgorilla.com/learn/postgres/how-to-read-a-postgres-explain-analyze-plan/)
- [How to find missing indexes in PostgreSQL](https://www.dbgorilla.com/learn/postgres/how-to-find-missing-indexes-in-postgresql/)
- [Why are my queries spilling to disk? (work_mem)](https://www.dbgorilla.com/learn/postgres/why-are-my-queries-spilling-to-disk-work-mem/)