How to Right-Size a PostgreSQL Instance: Read Metrics Before Downsizing

What right-sizing a PostgreSQL instance means, and who should do it

To right-size a PostgreSQL instance is to choose the smallest instance class that still covers peak demand on every resource the database depends on. Those resources are CPU, memory and the cache it holds, storage I/O, and connections. Average CPU is not on that list. A primary that averages 15% CPU can still depend on most of its RAM to keep the working set cached. It can also absorb a nightly batch that saturates its disks, or hold hundreds of backend processes that each reserve memory.

The motivation is usually database cost reduction. Instance class tends to be one of the largest line items for a managed database, so RDS downsizing is a common target after a billing review. Halving memory and vCPUs can cut that line item substantially. That is exactly why the decision needs evidence and not a dashboard glance.

The core rule is that a smaller instance rarely fails because it runs out of CPU. It fails in one of three ways:

• Cache eviction: hot pages no longer fit in memory, so queries go to disk.
• I/O saturation: the extra disk reads hit throughput or IOPS limits.
• Connection and memory pressure: per-connection and per-query memory no longer fits.

Average CPU hides all three.

Right-sizing should be owned by backend and platform engineers who can read both cloud-provider metrics and PostgreSQL's statistics views. Neither source is enough on its own.

When it makes sense

Consider a downsize only when utilization stays low across a full business cycle. That cycle has to include month-end jobs, seasonal peaks and maintenance windows. You also need no significant data or traffic growth on the roadmap.

Choosing the observation window and percentiles before you look at data

Fix the window and statistics before you open a dashboard. If you look first, it is easy to pick the quiet fortnight that supports the downsize you already want.

Pick a window that covers a full cycle

The window must cover at least one full business cycle for this workload. That means every weekday peak, at least one month-end close or billing run, and each scheduled batch, ETL or vacuum window. For many systems a single month is the minimum. If quarterly reporting or annual renewals drive load, use a longer window. Record the dates and the reason for choosing them so reviewers can check your choice.

Judge by p95, p99 and maximum, not the mean

Averages hide the moments that cause outages. Consider a nightly job that pins CPU at 100% for two hours, while the instance idles near 7% for the remaining 22 hours. The daily average is about 15% (illustrative estimate: (2 × 100 + 22 × 7) ÷ 24 ≈ 14.8). Read only the mean and the instance looks oversized. Read p99 and the maximum and you see it already saturates every night. Half the CPU would stretch that job into the morning traffic.

Use p95 to describe sustained load and p99 to show regular peaks. Use the maximum to set the headroom you need to keep. Choose a metric resolution fine enough to catch short bursts. Five-minute averages can flatten a 90-second spike.

List what the window cannot show

Metrics only describe the past. Write down planned launches, expected data growth, migrations and seasonal peaks that fall outside the window. Treat each one as an explicit input to the sizing decision.

How to read each resource: CPU, memory and cache, storage I/O, connections

With those inputs written down, read each resource with one question in mind: how would a smaller box fail on it?

Data path and where each metric is measured

Query → shared_buffers (blks_hit in pg_stat_database) → OS page cache (counted as blks_read by Postgres, but no disk I/O) → block storage (read/write IOPS, throughput, latency, queue depth) → instance network/storage bandwidth cap

High I/O wait is not a CPU problem, so subtract it before judging CPU headroom. Low free memory is normal because Postgres deliberately leaves the OS page cache to fill RAM. Postgres cannot tell a page-cache hit from a disk read: both count as blks_read. Pair the hit ratio with the storage-level read IOPS. If halving RAM would push working-set reads to disk, read IOPS rise and latency follows.

For connections, idle sessions cost little, but active ones can each allocate work_mem per sort or hash node. As an illustrative estimate with assumed inputs, forty active queries with two such nodes each, at 64 MB, can reach about 5 GB. That amount fits on 64 GiB but leaves much less margin at 32 GiB.

Copy+--------------------+     pg_stat_activity:
|  Client sessions   |     peak count, active vs. idle
+---------+----------+
          | query
          v
+--------------------+     CPU: user time (subtract I/O wait)
|  Postgres backend  |     RAM: work_mem per sort/hash node
|  (executor)        |          x concurrent active queries
+---------+----------+
          | page request
          v
+--------------------+     pg_stat_database: blks_hit
|  shared_buffers    |
+---------+----------+
          | miss
          v
+--------------------+     pg_stat_database: blks_read
|  OS page cache     |     (page-cache hit and disk read
+---------+----------+      look identical to Postgres)
          | miss = real disk I/O        CPU: I/O wait rises
          v
+--------------------+     instance network / block-storage
| Instance bandwidth |     bandwidth cap (set by the class;
|        cap         |     check current and target)
+---------+----------+
          |
          v
+--------------------+     read/write IOPS, throughput,
|  Block storage     |     latency, queue depth
+--------------------+

The read path from query to disk, showing where Postgres counts blks_hit and blks_read, where storage and instance-level metrics are measured, and where CPU, connection and work_mem pressure appear.

Implementing the assessment with pg_stat views

Start with pg_stat_statements, which has to be listed in shared_preload_libraries and created as an extension. Order rows by total execution time to find the queries that consume the most CPU overall, then by mean execution time to find the slow individual queries. The two lists answer different questions. A query with a low mean and millions of calls can drive a CPU peak that no slow-query log will capture. Exact column names vary between versions, so check them on your server first.

From pg_stat_database, compute blks_hit / (blks_hit + blks_read) for the cache hit ratio. Read temp_files and temp_bytes to see how often sorts and hashes already spill past work_mem. If spills are frequent on the current instance, less memory will make them worse. In pg_stat_activity, count rows by state. A large number of idle in transaction sessions holds snapshots and locks without using CPU.

Two checks show strain before it reaches a dashboard. In pg_stat_user_tables, look for n_dead_tup growing alongside an old last_autovacuum, which means autovacuum is falling behind. On a primary, pg_stat_replication shows how far each replica's replay position trails the primary.

Most of these counters are cumulative since the last reset. Take snapshots at fixed intervals and compare the differences between them. Alternatively, call pg_stat_statements_reset() at the start of the observation window. Also check wait_event_type in pg_stat_activity. Lock waits and clients queued at a connection pooler use almost no CPU or memory, so they may not appear in those graphs.

Copy-- Top statements by total time (CPU share), with call counts and mean time.
-- Column names are for PostgreSQL 13+; older releases use total_time / mean_time.
SELECT queryid, calls,
       round(total_exec_time) AS total_ms,
       round(mean_exec_time::numeric, 2) AS mean_ms,
       left(query, 60) AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC   -- switch to mean_exec_time for the slow-query list
LIMIT 10;

-- Cache hit ratio and temp-file spills per database (cumulative since stats_reset).
SELECT datname,
       round(blks_hit::numeric / nullif(blks_hit + blks_read, 0), 4) AS hit_ratio,
       temp_files, pg_size_pretty(temp_bytes) AS temp_spilled, stats_reset
FROM pg_stat_database
WHERE datname IS NOT NULL;

-- Client sessions by state and wait type: idle in transaction and Lock waits barely register as CPU.
SELECT state, wait_event_type, count(*)
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY state, wait_event_type
ORDER BY count(*) DESC;

-- Autovacuum falling behind: many dead tuples with an old or NULL last_autovacuum.
SELECT relname, n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

-- Run on the primary: how far each replica's replay position trails.
SELECT application_name, state, replay_lag,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) AS replay_behind
FROM pg_stat_replication;

-- Counters are cumulative: snapshot at fixed intervals and diff, or reset at window start.
CREATE TABLE IF NOT EXISTS stmt_snap AS
  SELECT now() AS taken_at, queryid, calls, total_exec_time FROM pg_stat_statements WITH NO DATA;
INSERT INTO stmt_snap SELECT now(), queryid, calls, total_exec_time FROM pg_stat_statements;
-- SELECT pg_stat_statements_reset();

Diagnostic queries against pg_stat_statements, pg_stat_database, pg_stat_activity, pg_stat_user_tables and pg_stat_replication. The final statements store snapshots so cumulative counters can be compared across an observation window.

Worked example: headroom math for a 64 GiB to 32 GiB move

Illustrative estimate: every input below is assumed, not measured.

The current instance has 64 GiB of RAM and peaks at 35% CPU. The target class has 32 GiB and half the vCPUs. Halving the cores roughly doubles utilization for the same work, so projected peak CPU is about 70% before any margin. That assumes per-core speed stays the same and the load is spread across cores. The headroom target here is peak CPU at or below 70%, plus cache room of at least 25% of the working set.

For memory on the 32 GiB class, the assumptions are 8 GiB for shared_buffers, about 2 GiB for backends (200 connections at roughly 10 MiB each), and 2 GiB for the OS and background processes. That leaves about 28 GiB that shared_buffers and the OS page cache can use to hold data.

Scenario A fits in cache. CPU has no margin, though, so the move only holds if 35% came from a high percentile and load is not growing.

Scenario B is an illustrative estimate built on the same assumed inputs. On the current 64 GiB instance, the assumed 40 GiB working set fits comfortably, because about 60 GiB is available for caching (assumed: 64 GiB minus the same roughly 4 GiB for backends, the OS and background processes). After the move, the assumed working set exceeds the ~28 GiB of cache-capable memory by about 12 GiB. Those hot pages no longer fit in memory, so reads that used to be cache hits go to disk. Disk reads take far longer than memory reads, so query latency rises. Queries also hold connections longer, and more evictions add CPU work, which pushes the projected 70% higher.

Running the downsize as an experiment, and when to avoid it

Treat the resize as an experiment with a hypothesis and an exit condition. Move down one instance class at a time. A single step changes one variable, and if things degrade, the cost of being wrong is one step's worth of headroom.

Before the change, recalculate shared_buffers, effective_cache_size, work_mem, maintenance_work_mem and max_connections. Custom parameter groups often hold absolute values, and those values do not shrink with the instance. A work_mem sized for 64 GiB, multiplied by many concurrent sorts and hashes, can push a 32 GiB host into memory pressure. If no connection pooler sits in front of the database, add one first. Fewer backends means less per-connection memory competing with the cache.

Rehearse on a replica or staging environment under representative load. Schedule the production change in a maintenance window. If the platform supports it, use a failover-based resize so the outage is a reconnect rather than a restart. Confirm that clients retry after dropped connections.

Write rollback thresholds down before you start. Examples are p99 latency above an agreed limit for a sustained period, or a cache hit ratio below an agreed floor. A resized instance starts with a cold cache, so the first hours look worse than steady state. A short test also misses weekly peaks. Judge the result only after the cache is warm and the test has run through a full traffic cycle.

When not to downsize

• The working set is close to the target's RAM.
• Temp file spills or autovacuum lag already exist.
• The target is a burstable class and the load is spiky.
• Failover headroom is already thin.
• Significant growth is expected soon.

Copy-- Pre-downsize baseline: run before the change, save the output,
-- and run again after the cache is warm to compare.

-- 1. Memory settings. Absolute values in a custom parameter group
--    will not shrink with the instance, so recalculate them for the target.
SELECT name, setting, unit, source
FROM pg_settings
WHERE name IN ('shared_buffers', 'effective_cache_size', 'work_mem',
               'maintenance_work_mem', 'max_connections');

-- 2. Cache hit ratio and temp spills. The hit ratio sets the rollback floor.
--    Existing temp spills are a reason not to downsize.
--    Counters are cumulative since stats_reset, so diff two snapshots.
SELECT datname,
       round(100.0 * blks_hit / nullif(blks_hit + blks_read, 0), 2) AS cache_hit_pct,
       temp_files,
       pg_size_pretty(temp_bytes) AS temp_spilled,
       stats_reset
FROM pg_stat_database
WHERE datname = current_database();

-- 3. Autovacuum lag. Many dead tuples, or an old or NULL last_autovacuum,
--    on busy tables means vacuum is already falling behind.
SELECT relname, n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

-- 4. Backend count by state. A large share of idle connections means
--    a pooler would cut per-connection memory before the resize.
SELECT state, count(*) AS backends
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY state
ORDER BY backends DESC;

Baseline queries to run before and after a one-step downsize: the memory settings to recalculate, plus the cache hit ratio, temp spills, autovacuum lag and connection counts that set rollback thresholds and flag cases where you should not downsize.

Alternatives and the impact on cost and engineering time

If any of those conditions apply, other levers can cut cost without reducing headroom. They also make a later downsize safer, because they lower the load the smaller instance has to carry.

The query-level levers carry a hidden cost. If you downsize on the strength of a tuning win, the smaller instance now depends on that optimization. Record which changes the new size relies on, so reviewers know which code paths they must not regress.

A metrics-first, single-step process saves engineering time in three ways:

• Fewer incidents. Each resize costs a restart or failover window.
• Fewer revert-and-restart cycles. A failed aggressive step costs two windows, not one.
• Reusable artifacts. The assessment queries and rollback criteria carry over to the next review.

A conservative step that holds also builds team trust. A step that has to be rolled back loses it, and the team becomes more reluctant to approve the next resize.

How OneEnv can help

Disclosure: OneEnv is the product behind this blog.

OneEnv (oneenv.io) is a workspace-based governance platform for the things services share — environment variables, database structure, API routes and validation rules. A change to a shared value is staged on a branch, reviewed once, and then every affected service explicitly approves or rejects it before it goes live, so you can see who a change touches before it ships instead of finding out in production.

Applied to right-size postgresql instance, this is where it helps:

Saves time

• Every service that inherits a shared value is listed in one place, so nobody searches repos and chats to find out who is affected.
• One review per branch replaces coordinating each team separately; each service still keeps its own approve/reject.
• Reviewers see the exact diff and the list of affected services up front, so approval is a decision rather than an investigation.

Saves cost

• Fewer production incidents and rollbacks caused by a shared value, route or column changing under a consumer unannounced.
• Less duplicated configuration to maintain: one shared definition inherited by many services.
• Per-service approve/reject means a bad shared value can be refused where it would break something, before it is live.
• OneEnv has a free plan (1 workspace, unlimited services and members, full governance), so trying it costs nothing.

These are qualitative benefits, not measured results — how much you save depends on how many services share the value.

Want to try it? Start at oneenv.io.