Might I Suggest Optimizing Before Scaling Your PostgreSQL

Good evening. I see you have arrived with an upgrade in mind.

I have observed a pattern. It goes like this: an application grows, the database slows down, and someone opens the cloud console to look at larger instance sizes. The reasoning is straightforward — more CPU, more RAM, faster queries. It is also, in the majority of cases, the wrong first move.

I do not say this to be contrarian. I say this because I have watched teams spend $700 more per month on a larger RDS instance when a single CREATE INDEX statement would have solved the problem for $0. I have seen this happen not once, not occasionally, but routinely.

The instinct to scale hardware is understandable. It feels safe. It requires no understanding of the query planner. It doesn't risk breaking anything. You are, in essence, throwing money at the problem — and money, unlike index design, requires no expertise.

But allow me to suggest an alternative sequence of operations. One that begins with a question rather than a credit card.

The scaling instinct and where it comes from

I should be fair to the instinct before I dismantle it. The desire to scale hardware has rational roots.

Cloud providers have made vertical scaling extraordinarily easy. A slider, a button, a brief period of downtime, and your database has twice the resources. The feedback loop is immediate: problem → bigger machine → problem delayed. It requires no debugging, no query analysis, no understanding of B-tree internals. It is, from a management perspective, a clean decision with a predictable outcome.

And the advice to scale arrives from every direction. Monitoring dashboards show CPU at 80% and RAM fully utilized, painted in urgent red. Cloud provider documentation helpfully suggests the next instance size. Team leads who have been burned by production outages prefer the solution that involves the least risk of making things worse.

All of this is reasonable. None of it is wrong, exactly. It is merely incomplete.

The question that goes unasked is: why is the CPU at 80%? Not "what do we do about CPU at 80%" — that question has an obvious answer. But why is a database with 10 million rows consuming the resources of a machine that should comfortably handle 100 million? That question has a much more interesting answer. And a much cheaper one.

What does a larger instance actually give you?

More CPU cores and more RAM help PostgreSQL in specific, well-defined ways: more shared buffers for caching pages in memory, more parallel workers for large sequential scans, and more headroom for concurrent connections.

What a larger instance does not do is fix a query that reads 9 million rows to return 180. No amount of RAM will make a sequential scan on a 10-million-row table fast. It will make it slightly less slow. The difference between "slow" and "slightly less slow" is not, I would suggest, what your users are hoping for.

Consider the arithmetic. Doubling your instance size typically improves query performance by 20-40% on CPU-bound workloads. A query that took 400ms now takes 280ms. Your users, who were waiting 400ms, are now waiting 280ms. They did not notice. They will not send a thank-you card.

Adding the correct index improves performance by 10,000-50,000%. That same query drops from 400ms to 3ms. Your users noticed that. It felt instant. And the cost was precisely $0 per month.

These are not comparable numbers. One is a rounding error. The other is a transformation.

Allow me to demonstrate

I encounter this query frequently — a join with aggregation, the kind every dashboard runs dozens of times per hour:

EXPLAIN (ANALYZE, BUFFERS)
SELECT u.name, COUNT(o.id), SUM(o.total)
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.created_at > NOW() - INTERVAL '7 days'
GROUP BY u.name;

On a db.r5.xlarge instance with 10 million orders, this returns in 412ms. A respectable machine doing its best with the instructions it has been given. The EXPLAIN output tells us exactly what those instructions cost:

HashAggregate  (actual time=412.3..412.5 rows=180 loops=1)
  ->  Hash Join  (actual time=0.4..287.1 rows=312840 loops=1)
        ->  Seq Scan on orders o  (actual time=0.0..178.9 rows=312840 loops=1)
              Filter: (created_at > (now() - '7 days'::interval))
              Rows Removed by Filter: 8687160
              Buffers: shared hit=8421 read=38012
        ->  Hash  (actual time=0.3..0.3 rows=180 loops=1)
              ->  Seq Scan on users u
Planning Time: 0.2 ms
Execution Time: 412.7 ms

A sequential scan on orders. PostgreSQL is reading 9 million rows it does not need, discarding them, and keeping the 312,000 that match. The Buffers: shared hit=8421 read=38012 line reveals that over 38,000 pages — approximately 297MB of data — were read from disk. For a single execution of a single query.

If this query runs 500 times per hour on a dashboard, that is 148GB of unnecessary disk reads per hour. The disk is busy. The CPU is busy. The monitoring dashboard is alarmed. And somewhere, an engineer is reaching for the instance upgrade.

If you upgrade to a db.r5.2xlarge, this query will run in perhaps 280-320ms. More RAM means more of those 38,000 pages will be cached in shared buffers on subsequent runs. You have spent $700/month to save 100ms.

If instead you add an index:

-- One line. That is all it takes.
CREATE INDEX idx_orders_created_at ON orders (created_at);

-- Execution time: 412ms → 3.1ms. Same hardware.

3.1ms. Same instance. Same data. Same hardware. The improvement is 133x, and it cost nothing.

PostgreSQL now uses an index scan instead of a sequential scan. It reads only the rows that match the filter, skipping the 8.7 million rows it was previously reading and discarding. The disk reads drop from 38,000 pages to roughly 40. The CPU work is negligible. The query that was consuming measurable resources now consumes effectively none.

But we are not finished optimizing

The index brought us from 412ms to 3.1ms. That alone would justify the argument. But if I may, there are two more levels available, and they illustrate why the optimization path has so much more depth than the scaling path.

Level two: the covering index. Our indexed query still touches the heap — it finds matching rows via the index, then visits the table to fetch user_id and total. A covering index eliminates even that step:

-- A covering index: PostgreSQL never touches the table at all.
CREATE INDEX idx_orders_covering ON orders (created_at)
  INCLUDE (user_id, total);

-- Index-only scan: 1.4ms. The heap is not consulted.

An index-only scan means PostgreSQL answers the query entirely from the index, never touching the table data at all. The visibility map confirms which pages are all-visible, and for a well-vacuumed table, this path is remarkably efficient. From 3.1ms to 1.4ms — a further 55% reduction.

Level three: the materialized view. If this dashboard query runs 500 times per hour and the underlying data changes perhaps once every few minutes, we are computing the same join and aggregation hundreds of times for the same answer:

-- Compute the answer once. Serve it many times.
CREATE MATERIALIZED VIEW mv_weekly_user_sales AS
SELECT u.name, COUNT(o.id) AS order_count, SUM(o.total) AS revenue
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.created_at > NOW() - INTERVAL '7 days'
GROUP BY u.name;

-- Refresh on a schedule or when the data changes.
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_weekly_user_sales;

Now the dashboard reads from the pre-computed view:

-- The dashboard query becomes:
SELECT name, order_count, revenue
FROM mv_weekly_user_sales;

-- Execution time: 0.3ms. No join. No aggregation. Just a read.

0.3ms. No join. No aggregation. No index traversal. Just a sequential read of 180 rows. The query is now 1,373 times faster than the original, and the database does effectively zero work to serve it.

The hardware upgrade would have given you 280ms. Three levels of optimization gave you 0.3ms. On the same hardware you started with.

The arithmetic, if you'll indulge me

I find that presenting the numbers plainly tends to settle the matter rather quickly.

The instance upgrade is the most expensive option with the smallest performance improvement. It is also the only option that recurs every month with no ceiling. And when your data doubles next year, you will be looking at the next instance size, because the underlying problem — the unindexed sequential scan — is still there. You have not fixed the problem. You have merely outrun it temporarily.

A read replica, the next most popular response, doubles the recurring cost and introduces replication lag — a new class of bugs your application must now handle. If a user writes a record and immediately reads it back, they may not find it. You have traded a performance problem for a consistency problem, and I am not convinced that is an improvement.

The cascade: how scaling begets more scaling

This is what concerns me most about the scaling-first approach. It is not merely expensive — it is self-reinforcing. Each scaling decision creates pressure for the next one.

I have watched this sequence unfold more times than I care to count:

By month 14, the team is spending $2,935 per month — $35,220 annually — on infrastructure that exists to compensate for queries that were never optimized. The original dashboard query still performs a sequential scan. It now performs that sequential scan on a much more expensive machine, surrounded by replicas and caches that exist solely because nobody asked why the query was slow.

This is the infrastructure equivalent of turning up the thermostat because the windows are open. Each turn of the dial is rational in isolation. The heating bill, viewed in aggregate, is not.

The optimization path does not cascade. An index, once created, handles 10x more data with the same resources. A materialized view, once built, serves the same query at the same speed regardless of how large the underlying table grows. Optimization bends the cost curve. Scaling shifts it upward.

Finding where the time actually goes

The first step in the optimize-first approach is identifying which queries are consuming your database's time. This is not guesswork. PostgreSQL will tell you directly, if you ask:

-- Enable pg_stat_statements (if not already present):
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Find your most expensive queries by total time:
SELECT
  calls,
  round(total_exec_time::numeric, 1) AS total_ms,
  round(mean_exec_time::numeric, 1) AS mean_ms,
  round((100 * total_exec_time /
    sum(total_exec_time) OVER ())::numeric, 1) AS pct,
  substr(query, 1, 80) AS query_preview
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

The output is illuminating:

calls  | total_ms    | mean_ms | pct  | query_preview
-------+-------------+---------+------+----------------------------------------------
48291  | 19,903,892  | 412.1   | 41.2 | SELECT u.name, COUNT(o.id), SUM(o.total) ...
112040 |  8,963,200  |  80.0   | 18.6 | SELECT * FROM products WHERE category_id ...
 9120  |  5,107,200  | 560.0   | 10.6 | SELECT d.name, SUM(s.amount) FROM departm...

That first row — our dashboard query — has consumed 19.9 million milliseconds of execution time. That is 5.5 hours of CPU time, spent answering the same join-and-aggregate question 48,291 times. It accounts for 41.2% of all database work.

This is the number that matters. Not CPU utilization. Not memory pressure. Not the monitoring dashboard's color scheme. The question is: what is the database spending its time on? And the answer, almost always, is a small number of unoptimized queries consuming a disproportionate share of resources.

I have seen databases where three queries account for 80% of total execution time. Fix those three queries, and the machine that was "at capacity" is suddenly idle. No upgrade required. No replica. No cache layer. Just three CREATE INDEX statements and perhaps a materialized view.

The connection count illusion

Allow me to address a related misunderstanding that frequently accompanies the scaling discussion. "We need a bigger instance because we are running out of connections."

-- Check active connections vs. actual work being done:
SELECT state, count(*)
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY state;

--  state  | count
-- --------+------
--  active |    3
--  idle   |   87
--
-- 87 idle connections. 3 doing actual work.
-- This is not a capacity problem. This is a pooling problem.

Eighty-seven idle connections. Three doing work. The database is not running out of connections because the workload demands 90 concurrent queries — it is running out because the application opens connections and holds them idle. Each idle connection consumes roughly 10MB of RAM on the PostgreSQL side. Eighty-seven idle connections represent 870MB of RAM doing nothing.

The solution to this is not a larger instance with more RAM for more idle connections. The solution is a connection pooler — PgBouncer, Pgpool-II, or a proxy that manages the pool for you. I have prepared a thorough guide to connection pooling for precisely this situation. A pooler allows 500 application connections to share 20 database connections, because at any given moment, only a handful are executing queries.

I mention this because connection exhaustion is the second most common reason teams upgrade their database instance, after slow queries. And like slow queries, it is almost never a hardware problem. It is a configuration problem masquerading as a capacity problem.

When you genuinely do need to scale

I would be a poor waiter indeed if I suggested that optimization alone solves everything. It does not. There are situations where larger hardware is the correct answer, and I should be forthright about them.

• Your working set genuinely exceeds available RAM. If your actively-queried data no longer fits in shared_buffers — even after you have eliminated unnecessary full-table scans — more RAM directly reduces disk I/O. The operative phrase is "even after." A 50GB table that is only queried through indexed lookups on recent data may have an effective working set of 2GB. Measure the working set after optimization, not before.
• CPU saturation at peak concurrency with optimized queries. If all cores are busy during traffic spikes and every query is already using indexes, more cores are the answer. But verify this with pg_stat_activity first. Many teams assume CPU saturation when the actual bottleneck is lock contention from concurrent writes to the same rows, or idle-in-transaction sessions holding locks that force other queries to wait. More CPU does not help a query that is waiting for a lock.
• Write throughput at IOPS limits. Heavy INSERT and UPDATE workloads can saturate storage I/O. Faster storage (provisioned IOPS, io2 volumes) helps here. Optimization helps reads far more than writes — this is a genuine boundary of the optimize-first thesis, and I would not pretend otherwise.
• Compliance or isolation requirements. Some workloads must run on dedicated hardware for regulatory reasons, or require physical separation between tenants. This is a governance decision, not a performance one, and no amount of indexing changes it.
• Geographic distribution. If your users are on three continents and your database is in us-east-1, latency is physics. A read replica in eu-west-1 solves a problem that no index can.

The key distinction: scale when your optimized workload outgrows the hardware. Not when your unoptimized workload does. The order matters enormously, because it determines whether you are spending $700/month on genuine capacity or on compensating for a missing index.

I have seen teams upgrade to a 4xlarge instance, then optimize their queries, then realize they could downgrade back to an xlarge. The waste was not just the money — it was the months of engineering time spent managing infrastructure complexity that need not have existed.

The honest counterpoint: when optimization is the wrong first move

I should be candid about the situations where my advice does not apply cleanly.

If the database is literally falling over right now — queries timing out, connections refused, users unable to load pages — you do not have time to run EXPLAIN ANALYZE and design indexes. You need the fire out before you investigate the cause. Scaling up buys you breathing room. Use it. But treat it as triage, not treatment. Once the immediate crisis is resolved, go back and optimize. Then scale back down.

If nobody on the team understands PostgreSQL internals — if the terms "B-tree," "sequential scan," and "query planner" are unfamiliar — the optimization path has a learning curve. A DBA consultant for a week, or a tool that handles optimization automatically, may be more practical than expecting the team to develop database expertise overnight. The knowledge is valuable and I would encourage acquiring it, but I would not pretend it is free or instant.

If your workload is genuinely write-heavy. An application that performs 10,000 inserts per second and 50 reads per second has a fundamentally different optimization profile. Indexes slow down writes, because each insert must update every index on the table. For write-dominated workloads, the optimization conversation is about batch sizes, partitioning, and COPY commands — not indexes. The read optimization path I have described is the majority case, but it is not the only case.

A waiter who overstates his case is no waiter at all. These boundaries are real, and acknowledging them is what makes the rest of the argument credible.

The optimize-first procedure

The procedure itself is not complicated. Before reaching for the instance upgrade:

1. Enable pg_stat_statements and identify your most expensive queries by total execution time. Not mean time — total time. A query that averages 5ms but runs 100,000 times per day consumes more resources than one that averages 2 seconds but runs 50 times.
2. Run EXPLAIN (ANALYZE, BUFFERS) on each of the top offenders. Look for sequential scans on large tables, Rows Removed by Filter numbers that dwarf the actual result set, and Buffers: read counts in the thousands. These are the signatures of missing indexes.
3. Add the appropriate indexes. This alone resolves the majority of performance complaints. A B-tree index on the filtered column is almost always the right starting point. For queries with multiple filter conditions, a composite index in the correct column order can eliminate the need for any table access at all.
4. For repeated aggregation queries, consider materialized views — compute the answer once, serve it many times. If a dashboard query runs 500 times per hour and the data changes once per minute, you are doing 499 unnecessary computations.
5. Address connection management. If pg_stat_activity shows a majority of connections sitting idle, add a connection pooler before adding RAM.
6. Then evaluate whether the hardware is insufficient. Measure the optimized workload against the current hardware. If CPU, memory, or IOPS are still constrained, scale with confidence that the money is buying genuine capacity.

In my experience, step 6 is rarely reached. The queries that were consuming 80% of database time are now consuming 2%. The instance that was "too small" is suddenly more than adequate. The monitoring dashboards have calmed down. The on-call engineer sleeps through the night.

Why this keeps happening

If optimization is so clearly superior, why do teams keep reaching for the scaling lever first? The answer, I believe, is structural rather than individual.

Monitoring tools show symptoms, not causes. CloudWatch shows you CPU utilization at 85%. It does not show you that one query accounts for 60% of that utilization, and that a single index would reduce it to 3%. The dashboard presents the problem in hardware terms, so the solution naturally follows in hardware terms.

Cloud providers are incentivized to sell larger instances. I do not mean this as a criticism — it is simply the business model. AWS earns more when you run a db.r5.4xlarge than when you run a db.r5.xlarge. The documentation, the upgrade paths, the one-click scaling — all of it is frictionless by design. The path to a larger instance is paved. The path to a better index is a trail through the woods.

Optimization requires database-specific knowledge. Adding an index requires understanding what a B-tree is, how the query planner chooses between sequential and index scans, and what EXPLAIN output means. This is not arcane knowledge, but it is specialized knowledge, and many teams — particularly those whose expertise is in application development — do not have it. The cloud console, by contrast, requires no specialized knowledge at all.

Scaling feels reversible. Optimization feels risky. If a bigger instance doesn't help, you can scale back down. If a new index causes unexpected locking during creation, or slows down writes, or changes the query plan in an unexpected way — that feels like a thing you broke. The asymmetry of perceived risk favors the safer-feeling option, even when the safer option is the more expensive one.

Understanding these structural pressures is not an excuse. It is a diagnosis. And the prescription is straightforward: before every scaling decision, spend one hour with pg_stat_statements and EXPLAIN ANALYZE. One hour. If the answer is "we genuinely need more hardware," you will know with confidence. If the answer is "we need three indexes," you will have saved your organization thousands of dollars per year.

The role of automation

I have described a manual procedure — enable pg_stat_statements, identify expensive queries, run EXPLAIN, add indexes, build materialized views, manage connection pools. Each step is straightforward. Together, they represent an ongoing discipline that most teams, if they are honest, will not sustain.

The initial optimization is exciting. You find the offending queries, add the indexes, watch the latency drop by two orders of magnitude, and feel the satisfaction of a problem properly solved. But optimization is not a one-time event. New features add new queries. Data grows. Traffic patterns shift. The queries that were efficient six months ago may not be efficient today.

This is the gap that Gold Lapel exists to fill. It sits between your application and PostgreSQL as a transparent proxy, observes query patterns as they happen in production, and applies the optimize-first approach continuously — creating indexes for unindexed filters, materializing repeated aggregations, rewriting queries to use them, and managing connection pooling. It does this without code changes, without a DBA on staff, and without anyone remembering to run EXPLAIN ANALYZE quarterly.

I mention this not as a sales pitch but as an acknowledgment that the optimize-first philosophy, applied manually, has a maintenance cost. Applied automatically, it does not. And the choice between manual optimization and no optimization should not be the only two options available.

A closing thought on the order of operations

Every engineering decision has an order of operations, and the order determines the outcome. Deploy then test produces different results than test then deploy. Build then measure produces different results than measure then build. And scale then optimize — the order most teams follow — produces profoundly different results than optimize then scale.

The difference is not marginal. It is the difference between $35,000 per year in cascading infrastructure costs and $0 per year in well-placed indexes. It is the difference between an architecture built on compensating mechanisms — replicas, caches, larger machines — and an architecture built on queries that use the database the way it was designed to be used.

The next time someone suggests upgrading the database instance, I would gently recommend running one EXPLAIN ANALYZE first. You may find that the problem was never the size of the house. It was that someone left all the windows open.

And that, if you'll permit me, is a much more satisfying fix.