What is evidence-based Postgres right sizing, and why are average CPU graphs not enough?

Postgres right sizing means choosing the smallest instance class that still covers the workload's peak demand for CPU, memory and I/O, with headroom you have deliberately chosen. Evidence-based right sizing means that choice comes from measured behavior: which queries run, how often, how much work each does, and when they cluster. It does not come from a dashboard average or a guess about what the application "probably needs."

Average database CPU usage is the most common trap. A daily or weekly mean flattens the moments that decide whether an instance is too small. These include a five-minute burst when a cache expires, a nightly batch job, an hourly report, or month-end reconciliation that runs only a few days a year. Suppose an instance averages 20% CPU but reaches 90% for an hour every night (illustrative estimate). That instance is not oversized by a factor of four. Halving its vCPUs could turn that hour into sustained saturation, rising query latency and lock queues. Memory works the same way. A smaller class shrinks the buffer cache, so a working set that fits today may start spilling to disk and raise read I/O during exactly those peaks.

For RDS downsizing and similar decisions, use maximum and high-percentile utilization, such as p95 and p99. Measure them over a window that includes every recurring cycle the business has, including at least one month-end if it matters.

Who should use this checklist?

This checklist is for backend and platform engineers who own cost or capacity. It applies when an instance looks steadily over-provisioned, during a cost review, or when you are cleaning up after a migration where the original sizing was chosen conservatively.

More specifically, it fits you if:

  • You are accountable for a Postgres instance's bill or its incidents. That includes on-call engineers who would handle the fallout of an undersized instance, not only the person who files the resize ticket.
  • You can change server configuration. Loading pg_stat_statements requires editing shared_preload_libraries and restarting. If you cannot do that yourself, you need someone who can.
  • The workload is stable enough to measure. The method assumes that a few weeks of observation reflect what the instance will face after the change. A product mid-launch, or a database whose schema changes daily, will not give you a baseline you can trust.
  • You need to justify the decision to others. Snapshots, deltas and peak attributions give reviewers something concrete to check, instead of a screenshot of an average CPU graph.

It is less useful if the database is tiny and cheap enough that the analysis costs more than any saving, or if the decision is already forced by something other than load, such as a platform migration or an end-of-support instance family.

How does the checklist work?

The checklist turns "this instance looks too big" into a decision you can defend and reverse. It runs in a fixed order, and each step feeds the next:

  1. Instrument. Load pg_stat_statements, create a snapshot table, and copy the view into it on a fixed interval so you can compute per-window deltas instead of reading cumulative counters.
  2. Measure over a representative window. Keep snapshots running long enough to cover every recurring cycle: nightly jobs, weekly reports, month-end.
  3. Find what drives the peaks. Rank statement deltas by execution time, separate heavy queries from frequent ones, and check cache hit ratios, temp spills and wait events.
  4. Correlate with instance metrics. Line up statement deltas with CPU, IOPS, memory and connection metrics on the same clock, and assign each peak to an owner or to non-SQL work such as autovacuum or checkpoints.
  5. Tune before sizing. Fix the cheap wins among the top statements, then re-measure so the sizing decision reflects the workload you will actually run.
  6. Plan the downsize as a reversible change. Choose a target with explicit headroom, recompute memory-derived settings, check per-class storage limits, and define rollback triggers in advance.
  7. Decide whether to downsize at all. If growth, effort, memory sensitivity or a missing baseline argue against it, pick one of the alternatives instead.

The sections below follow this order.

How does pg_stat_statements work, and how do you set it up for a measurement window?

The extension has to be loaded at server start, so pg_stat_statements must be listed in shared_preload_libraries. Changing that setting requires a restart, which on RDS means editing the parameter group and rebooting. Plan for that before the measurement window opens. After the restart, run CREATE EXTENSION pg_stat_statements in the database you query from to expose the view.

The counters are cumulative from the last reset or restart. A row showing a large calls value tells you nothing about last Tuesday's peak. The view also tracks a limited number of normalized statements, set by pg_stat_statements.max. When it fills, the least-executed entries are evicted. An application that generates many distinct query shapes can therefore churn its own history.

To analyze specific windows, copy the view into a snapshot table every few minutes:

  • Add a captured_at timestamp column to each copied row.
  • Schedule the insert with whatever job runner you already use.
  • Compute deltas by joining two snapshots on userid, dbid and queryid, then subtracting calls, execution time, rows and the shared block counters.
  • Discard negative deltas. They mean the entry was evicted or the stats were reset between snapshots.

Avoid pg_stat_statements_reset() as a substitute for this. It clears the statistics for everyone, including colleagues using them for their own investigations, and the discarded history cannot be recovered.

The extension's overhead is small for most workloads. track_io_timing adds a clock read around each I/O, and its cost depends on how fast the platform's clock source is. Run pg_test_timing before enabling it on a busy instance.

-- Requires PostgreSQL 13+ (total_exec_time column name).
-- Snapshot table: same key and counters as the view, plus capture time.
CREATE TABLE IF NOT EXISTS pgss_snapshot (
  captured_at timestamptz NOT NULL, userid oid, dbid oid, queryid bigint,
  calls bigint, total_exec_time double precision, rows bigint,
  shared_blks_hit bigint, shared_blks_read bigint
);

-- Run every few minutes from your job runner. GROUP BY folds rows that share
-- a key (e.g. top-level vs nested on PG14+) so the join below stays 1:1.
INSERT INTO pgss_snapshot
SELECT now(), userid, dbid, queryid, sum(calls), sum(total_exec_time),
       sum(rows), sum(shared_blks_hit), sum(shared_blks_read)
FROM pg_stat_statements
GROUP BY userid, dbid, queryid;

-- Delta between the last snapshots at or before two placeholder times.
WITH t AS (
  SELECT (SELECT max(captured_at) FROM pgss_snapshot
          WHERE captured_at <= '2026-11-10 09:00+00') AS t0,
         (SELECT max(captured_at) FROM pgss_snapshot
          WHERE captured_at <= '2026-11-10 17:00+00') AS t1
)
SELECT b.queryid,
       b.calls - a.calls                       AS calls,
       b.total_exec_time - a.total_exec_time   AS exec_ms,
       b.rows - a.rows                         AS rows,
       b.shared_blks_read - a.shared_blks_read AS blks_read,
       b.shared_blks_hit - a.shared_blks_hit   AS blks_hit
FROM t
JOIN pgss_snapshot a ON a.captured_at = t.t0
JOIN pgss_snapshot b ON b.captured_at = t.t1
 AND b.userid = a.userid AND b.dbid = a.dbid AND b.queryid = a.queryid
-- Negative deltas mean eviction or reset between snapshots: discard them.
WHERE b.calls >= a.calls AND b.total_exec_time >= a.total_exec_time
ORDER BY exec_ms DESC
LIMIT 20;

A snapshot table, the periodic insert that copies pg_stat_statements into it, and a delta query that ranks statements by execution time between two snapshots without resetting the shared counters.

Which queries actually drive peak load, memory pressure and I/O?

Work from the delta between your two snapshots, not the cumulative counters. Each check below answers a different question about what a smaller instance would have to absorb.

  • Rank by total execution time. Sort the delta by total_exec_time (older releases call it total_time). This shows where the server spent its time during the window. A query's share of that total matters more than its mean latency.
  • Separate heavy queries from frequent ones. Divide time by calls, and look at rows per call. One statement at 4 seconds per call and another at 0.2 ms called millions of times can have the same total. They respond differently to less hardware: the first gets slower, and the second queues once fewer cores are available. A high stddev_exec_time relative to the mean means latency varies a lot. That often points to plan flips, cache misses or lock contention rather than steady cost.
  • Estimate per-query cache behavior. Compute shared_blks_hit / (shared_blks_hit + shared_blks_read). A "read" means the block was not in shared_buffers. It may still have come from the OS page cache, which also shrinks when you reduce RAM. Queries with large hit counts against a big working set are the ones most likely to turn hits into real disk reads.
  • Find spills. A non-zero temp_blks_written means sorts or hashes exceeded work_mem. If you lower work_mem to fit the smaller instance, these spills get larger and more frequent.
  • Treat execution time as wall-clock time, not CPU time. Lock waits and I/O waits inflate it. Sample wait_event_type and wait_event in pg_stat_activity during peaks before deciding a query is CPU-bound.
Signal Likely bottleneck Risk after downsizing
High total time, low calls Single heavy query Longer latency per call
High total time, very high calls Per-call overhead, concurrency CPU saturation, queueing
Low hit ratio, large reads Working set exceeds memory More disk I/O
temp_blks_written > 0 Spilling sorts/hashes More temp I/O
High stddev, lock/IO waits Contention, not compute Fewer cores won't help
-- Assumes two snapshots taken with:
--   CREATE TABLE pgss_before AS SELECT * FROM pg_stat_statements;
--   CREATE TABLE pgss_after  AS SELECT * FROM pg_stat_statements;
-- Use total_time instead of total_exec_time on PostgreSQL 12 and older.
WITH d AS (
  SELECT a.queryid, left(a.query, 60) AS query,
         a.calls - coalesce(b.calls, 0)                         AS calls,
         a.total_exec_time - coalesce(b.total_exec_time, 0)     AS exec_ms,
         a.rows - coalesce(b.rows, 0)                           AS rows,
         a.shared_blks_hit - coalesce(b.shared_blks_hit, 0)     AS hit,
         a.shared_blks_read - coalesce(b.shared_blks_read, 0)   AS rd,
         a.temp_blks_written - coalesce(b.temp_blks_written, 0) AS temp_w,
         a.mean_exec_time, a.stddev_exec_time  -- cumulative, not windowed
  FROM pgss_after a
  LEFT JOIN pgss_before b USING (userid, dbid, queryid)
)
SELECT queryid, query, calls,
       round(exec_ms::numeric, 0)                                 AS exec_ms,
       round((100 * exec_ms / sum(exec_ms) OVER ())::numeric, 1)  AS pct_of_total,
       round((exec_ms / nullif(calls, 0))::numeric, 3)            AS ms_per_call,
       rows / nullif(calls, 0)                                    AS rows_per_call,
       -- stddev well above mean suggests plan flips, misses or lock waits
       round((stddev_exec_time / nullif(mean_exec_time, 0))::numeric, 2) AS cv,
       -- a 'read' missed shared_buffers; it may still have hit the OS page cache
       round((hit::numeric / nullif(hit + rd, 0)), 4)             AS hit_ratio,
       rd AS blks_read,
       temp_w AS temp_blks_written                     -- non-zero: work_mem spills
FROM d
WHERE calls > 0
ORDER BY exec_ms DESC
LIMIT 20;

-- Run repeatedly during peaks: exec time includes lock and I/O waits.
SELECT wait_event_type, wait_event, count(*)
FROM pg_stat_activity
WHERE state = 'active' AND pid <> pg_backend_pid()
GROUP BY 1, 2
ORDER BY 3 DESC;

Computes per-query deltas between two pg_stat_statements snapshots and ranks them by share of execution time, with per-call cost, variability, buffer hit ratio and temp spills. A second query samples wait events so wall-clock time is not mistaken for CPU time.

How do you correlate top statements with instance metrics?

Because pg_stat_statements counters are cumulative, a single read tells you nothing about when load happened. Take snapshots on a fixed interval, for example every five minutes. Diff consecutive snapshots by queryid, userid and dbid. Then plot those deltas on the same clock as your instance metrics.

 snapshot job (every N min)        instance metrics (same N min)
 pg_stat_statements -> deltas      CPU, read/write IOPS,
 per queryid: time, calls,         freeable memory, connections
 blks read/written, temp blks
          \                              /
           +------> shared timeline <---+
                         |
               peak windows identified
                         |
          attribute: report | migration | cron job | app traffic

For each CPU or IOPS peak, rank statements by total_exec_time delta and block-read delta inside that interval. A CPU spike every Monday at 02:00 that lines up with one aggregate query is a scheduled report. A one-off write-IOPS spike alongside DDL and large shared_blks_written is usually a migration or backfill. A drop in freeable memory together with a rise in connections points to connection count and per-backend memory, not query cost. Tag each peak with its owner. Those owners decide whether the load can move, be throttled or be tuned.

Blind spots

  • Missing statements: statements that fail are not recorded. Entries evicted under pg_stat_statements.max lose their history, so a check of the dealloc count belongs in every snapshot.
  • Non-SQL work: autovacuum, replication, WAL archiving and checkpoints consume CPU and I/O but never appear as statements. If a peak has no matching statement delta, look at these first.
  • Unrepresentative windows: a measurement window that misses quarter-end, a backfill or incident traffic will understate the peak your smaller instance must survive.
+---------------------------+      +---------------------------+
| snapshot job (every N min)|      | instance metrics (N min)  |
| pg_stat_statements        |      | CPU, read/write IOPS,     |
| + dealloc / evicted count |      | freeable memory,          |
+-------------+-------------+      | connection count          |
              |                    +-------------+-------------+
              v                                  |
+---------------------------+                    |
| diff consecutive snapshots|                    |
| by queryid, userid, dbid  |                    |
| -> exec time, calls,      |                    |
|    blks read/written, temp|                    |
+-------------+-------------+                    |
              v                                  v
+--------------------------------------------------------------+
| shared timeline (same clock): statement deltas + metrics     |
| -> per peak: rank statements by exec-time and blk-read delta |
+--------------+--------------------------------+--------------+
               v                                v
+-----------------------------+  +-----------------------------+
| attribute and tag an owner: |  | no match: non-SQL work      |
| report, migration,          |  | autovacuum, replication,    |
| cron job, app traffic       |  | WAL archiving, checkpoints  |
+-----------------------------+  +-----------------------------+
+--------------------------------------------------------------+
| blind spots: failed / evicted statements (check dealloc),    |
| windows missing quarter-end, backfills, incident traffic     |
+--------------------------------------------------------------+

Periodic pg_stat_statements deltas and instance metrics on the same interval feed a shared timeline, where each peak is assigned to an owner or, if no statement delta matches, checked against non-SQL work, within the limits of the listed blind spots.

Should you tune queries or downsize first?

Tune first. The statements at the top of pg_stat_statements usually include cheap wins: a missing index forcing sequential scans, an N+1 pattern issuing thousands of near-identical calls per request, an unbounded query with no LIMIT, or an endpoint fetching wide rows it never uses. These fixes change the load profile your sizing decision depends on. If you downsize first, you size for waste. Fix them, take a fresh snapshot over a comparable window, and only then pick an instance class.

Re-measuring matters because the post-fix numbers can make a smaller instance look safer than it is.

Illustrative estimate (assumed inputs)

  • Current instance: 8 vCPUs, 60% peak CPU, which is about 4.8 vCPUs of work.
  • A fix removes one third of peak execution time, leaving about 3.2 vCPUs of work.
  • On 8 vCPUs that is 40% peak. On 4 vCPUs it is 80% peak.

At 80% there is little room for anything else. Autovacuum, checkpoints, a connection surge or a deploy that sends a bad plan all compete for the remaining capacity. Queueing delay also rises sharply as utilization approaches saturation, so p99 latency degrades well before CPU reads 100%. Treat 80% peak as a warning, not a target. In this example, a 6-vCPU class, if your platform offers one, or staying at 8 vCPUs may be the better choice.

Development cost

Engineering hours spent on analysis and tuning are a real cost. Weigh them against the monthly savings from the smaller instance. A fix that takes a week to save a small amount each month may not pay back for a long time. Script the snapshot, reset and diff steps once. Later reviews then cost hours instead of days, and each one builds on a known baseline.

How can this checklist save engineering time?

Most of the time a careless downsize costs is spent after the change: debugging latency regressions, handling pages during the first month-end on the smaller class, and running an unplanned resize back. The checklist moves that work earlier, where it is cheaper and scheduled. It saves time in these specific ways:

  • Fewer incidents and unplanned rollbacks. Sizing against measured peaks, rather than averages, catches the nightly batch or month-end job that would have saturated the smaller instance. Each avoided rollback saves a second change window, a second restart and the investigation that precedes it.
  • Tuning effort goes where it matters. Ranking statements by their share of execution time during peak windows tells you which few queries are worth fixing. Engineers do not spend days optimizing a slow query that runs twice a day off-peak while a cheap, high-frequency statement drives the CPU spike.
  • Peaks are routed to the right people. Tagging each peak with an owner (a report, a migration, a cron job, application traffic) means the team that can move or throttle that load gets the request directly, instead of the database owner chasing it through several teams.
  • Faster diagnosis of non-query load. A peak with no matching statement delta points straight at autovacuum, replication, WAL archiving or checkpoints. That rules out a long search through query logs for a cause that is not SQL.
  • Reusable tooling. The snapshot table, the periodic insert and the delta queries are written once. Later cost reviews, post-migration checks and capacity planning reuse them, and each review starts from an existing baseline instead of a fresh investigation.
  • Decisions that do not need to be re-argued. Written headroom targets and rollback triggers give reviewers and on-call engineers agreed criteria. When something looks off after the change, the team checks the triggers rather than debating whether to revert.
  • Early exits. The "when to avoid downsizing" checks can end the effort before a change window is booked. Finding out that growth will force an upsize within months, or that the saving is smaller than the effort, is itself time saved.

The checklist does not make the analysis free. The first pass, including the restart to load the extension and a measurement window long enough to cover business cycles, takes calendar time. The saving comes from avoiding rework later and from making each subsequent review cheaper.

How do you plan a downsize as a reversible change?

Treat the resize as a change you expect to reverse, and plan the way back before you go forward.

  • Pick the target with explicit headroom. Size against the peak windows you identified earlier, not the daily mean. Write down the margin you are keeping, for example "peak CPU under 60% on the new class," so the decision can be checked later.
  • Recompute memory-derived settings. On many managed services, defaults for max_connections, shared_buffers and related parameters are formulas based on instance memory. A smaller class can silently lower your connection ceiling below what your pools open at peak.
  • Check storage limits per class. Managed services often cap throughput and IOPS by instance size, independent of the volume. A query mix that was CPU-bound can become I/O-bound after the move.
  • Use a maintenance window. Resizing usually means a restart or failover, and open connections are dropped.
  • Verify retries. Confirm that applications reconnect and retry idempotent work cleanly. Test this with a forced failover in staging.
  • Define rollback triggers in advance:
Signal Baseline source Example trigger (illustrative)
p95 latency of top statements pg_stat_statements diff More than 20% above baseline for 30 min
Peak CPU Instance metrics Sustained above the headroom target
Connection errors App logs Any rejections at max_connections
Read latency / queue depth Storage metrics Consistently above pre-change peak

Rollback is a second resize, so it brings a second restart and another round of dropped connections. Budget a window for it as well.

When should you avoid downsizing, and what are the alternatives?

Skip the downsize, or postpone it, when any of these apply:

  • Growth shortens the runway. If traffic is climbing fast enough that you would need to upsize again within a few months, you pay for two restarts and two rounds of risk for a short stretch of savings.
  • The savings are smaller than the effort. Measurement, a change window, rollback rehearsal and on-call attention all cost engineering hours. Compare that cost to the monthly price difference before you schedule anything.
  • The workload is memory- or I/O-sensitive. A smaller instance class usually has less RAM, so less of the working set fits in shared_buffers and the OS page cache. Reads that used to hit memory start hitting disk, and latency can rise even when CPU looks fine.
  • You lack a representative window. If pg_stat_statements was reset recently, or the window missed month-end or seasonal peaks, you would be sizing to an incomplete picture.

If one of these applies, these alternatives are often a better fit:

Alternative What it addresses Trade-off
Query and index tuning only CPU and I/O per call Shows up as headroom, not a smaller bill; each index adds write and storage overhead
Read replica for reporting Long scans competing with OLTP Replica cost, replication lag, application routing changes
Move batch jobs off peak Peak load, which drives sizing Needs agreement with job owners; quiet windows shrink as traffic spreads across time zones
Connection pooling Memory used by each backend connection, connection churn Transaction pooling breaks session state such as SET, session advisory locks and some prepared-statement usage
Adjust storage, not compute I/O throughput or IOPS limits On many managed services, allocated storage cannot be shrunk later

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 postgres right sizing, 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.
  • Changing a shared variable is one edit and one review instead of editing every service's .env by hand.

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.
  • A wrong or stale value is caught at review time by the services it affects, not by a failed deploy.
  • 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.