What is indexing-driven instance downsizing, and who should use it?

Indexing-driven downsizing treats indexes as a cost lever, not just a latency fix. A query that scans millions of rows to return a few dozen burns CPU on filtering, pulls pages through shared buffers, and evicts data other queries need. Replace that scan with an index lookup and the work per call drops. Across a small set of high-frequency queries, that can lower sustained CPU, read I/O and memory pressure enough to move to a smaller instance class.

The approach is measured and reversible. Each index change has a before-and-after comparison and can be dropped if it doesn't pay for its write overhead and storage. The goal is not a faster query in isolation. The goal is lower utilization at peak, because peak is what sets instance size.

Teams pursue this because instance compute is often the largest recurring cost in a database bill, and storage or I/O charges tend to scale alongside it. It fits backend and platform engineers running read-heavy or mixed workloads with visible slow queries and high read I/O. It fits poorly when the database is already mostly idle, or when write volume dominates.

Start only after a baseline shows that sustained utilization comes from a few identifiable queries rather than general load.

Checklist step 1: Establish a baseline

  • Record CPU utilization, read I/O, buffer cache hit ratio, connection counts, and p95/p99 latency.
  • Use a window that includes peak traffic and scheduled batch jobs. A quiet-hours sample will understate what the smaller instance must absorb.
+-----------------------------------------------------------------+
| 1. Baseline over a window covering peak traffic and batch jobs  |
|    CPU, read I/O, buffer cache hit ratio, connections, p95/p99  |
+-----------------------------------------------------------------+
                                 v
+-----------------------------------------------------------------+
| 2. Is peak load driven by a few identifiable queries?           |
|    no -> stop (idle, general load, or write-dominated)          |
+-----------------------------------------------------------------+
                                 v yes
+-----------------------------------------------------------------+
| 3. Per query: add index, replace scan with index lookup         |
|    less CPU filtering, fewer pages through shared buffers       |
+-----------------------------------------------------------------+
                                 v
+-----------------------------------------------------------------+
| 4. Compare before vs. after at peak, incl. write overhead       |
|    pays off -> keep        does not pay off -> DROP INDEX       |
+-----------------------------------------------------------------+
                                 v
+-----------------------------------------------------------------+
| 5. Lower sustained CPU, read I/O and memory pressure at peak    |
+-----------------------------------------------------------------+
                                 v
+-----------------------------------------------------------------+
| 6. Resize to a smaller instance class (peak sets size)          |
+-----------------------------------------------------------------+

Indexing-driven downsizing as a measured, reversible loop: baseline at peak, confirm a few queries drive load, index them, keep or drop each index based on before-and-after results, then resize once peak utilization falls.

How do you find the queries that actually drive cost with pg_stat_statements?

  • Rank by total_exec_time, not by the slowest single run. A report that takes 40 seconds once a day costs less than a 5 ms lookup called millions of times. Cost tracks cumulative CPU and I/O, so sort by total time and look at calls and mean_exec_time together. The example query that follows selects query, calls, total_exec_time, mean_exec_time, rows, shared_blks_hit and shared_blks_read, orders by total_exec_time descending, and keeps the top 20.
  • Compare shared_blks_read with shared_blks_hit. Hits come from shared buffers. Reads had to be fetched from outside shared buffers, which means the OS page cache or disk. A query with a high read share is scanning more data than fits in memory, and that pressure is what keeps you on a larger instance. Compute the ratio read / (read + hit) for each query and flag any query where reads dominate.
  • Reset deliberately and log the reset. Calling pg_stat_statements_reset() starts a clean measurement window. Record the timestamp and the reason, for example "before index change on orders." Without that record, you cannot tell whether a drop in totals came from your fix or from the counters being cleared.
  • Know what the window misses. The counters cover only the period since the last reset, and only the node where the extension runs. Month-end jobs, quarterly reports and queries routed to replicas can be missing from the data. Capture each replica separately, and keep the window open across at least one full batch cycle before you draw conclusions.
-- Top 20 queries by cumulative execution time since the last reset.
-- Column names assume PostgreSQL 13+ (total_exec_time, mean_exec_time).
SELECT
  left(query, 80)                    AS query,
  calls,
  round(total_exec_time::numeric, 1) AS total_ms,
  round(mean_exec_time::numeric, 2)  AS mean_ms,
  rows,
  shared_blks_hit,
  shared_blks_read,
  -- Share of blocks fetched from outside shared buffers (page cache or disk).
  round(shared_blks_read::numeric
        / NULLIF(shared_blks_read + shared_blks_hit, 0), 3) AS read_ratio
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

-- Log every deliberate reset so later comparisons have a known start.
CREATE TABLE IF NOT EXISTS stats_reset_log (
  reset_at timestamptz NOT NULL DEFAULT now(),
  node     text        NOT NULL DEFAULT inet_server_addr()::text,
  reason   text        NOT NULL
);

BEGIN;
INSERT INTO stats_reset_log (reason)
VALUES ('before index change on orders');
SELECT pg_stat_statements_reset();
COMMIT;

-- PostgreSQL 14+: confirm when the counters were last cleared on this node.
SELECT stats_reset FROM pg_stat_statements_info;

Ranks queries by total execution time with a per-query read ratio, records each deliberate reset in a log table, and checks the reset timestamp on the current node; run it on every replica separately.

How does EXPLAIN (ANALYZE, BUFFERS) show which index a query needs?

Once you have a ranked list, take each top query and run it under EXPLAIN (ANALYZE, BUFFERS) against a production-sized copy or a replica. ANALYZE actually executes the statement, so wrap any write in a transaction you roll back. BUFFERS matters for cost work because it shows how many pages were read from cache or from disk, and page reads are what CPU and I/O sizing pay for.

Look for four signals:

  • Sequential scan with a selective filter. A line such as Seq Scan on orders ... Rows Removed by Filter: 4,980,000 next to rows=20 returned means the table was read in full to find a few rows. This is the clearest case for an index.
  • Estimate far from actual. rows=12 estimated against rows=180000 actual means the planner chose its strategy from bad statistics. Fix the statistics before adding an index.
  • Sorts that spill. Sort Method: external merge Disk: ... means the sort did not fit in work_mem. An index that already returns rows in the needed order can remove the sort step.
  • Nested loops over many rows. A high loops= count on the inner side, with a seq scan inside it, multiplies the cost of each probe.

Plans built on small or stale data can mislead you. On a small table, a seq scan really is the cheapest plan. After a bulk load, run ANALYZE so the planner sees current row counts. When filter columns are correlated, such as city and postcode, use CREATE STATISTICS. Without it, the planner may underestimate selectivity and ignore a new index.

-- Run on a production-sized replica or copy. ANALYZE executes the query.
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total
FROM orders
WHERE customer_id = 4821 AND status = 'open'
ORDER BY created_at DESC;

-- Illustrative output (numbers are made up to show the shape):
-- Sort  (cost=110375.20..110375.23 rows=12 width=24)
--       (actual time=415.10..415.11 rows=20 loops=1)
--   Sort Key: created_at DESC
--   Sort Method: quicksort  Memory: 26kB
--   Buffers: shared hit=1024 read=61476   <- ~62k pages touched for 20 rows
--   ->  Seq Scan on orders  (cost=0.00..110375.00 rows=12 width=24)
--                           (actual time=0.03..415.00 rows=20 loops=1)
--         Filter: ((customer_id = 4821) AND (status = 'open'::text))
--         Rows Removed by Filter: 4999980  <- full read, selective filter

-- Fix: one index matches both the filter and the sort order.
-- CONCURRENTLY avoids blocking writes; it cannot run inside a transaction.
CREATE INDEX CONCURRENTLY orders_customer_status_created_idx
    ON orders (customer_id, status, created_at DESC);

-- Refresh statistics so the planner sees current row counts.
ANALYZE orders;

-- For correlated filter columns, let the planner learn the dependency.
CREATE STATISTICS addresses_city_postcode_stats (dependencies)
    ON city, postcode FROM addresses;
ANALYZE addresses;

-- Re-run the EXPLAIN. Expect an Index Scan using
-- orders_customer_status_created_idx, no Sort node,
-- and a much smaller Buffers count.

An annotated EXPLAIN (ANALYZE, BUFFERS) plan with illustrative numbers. It shows a sequential scan with a selective filter. It then shows the fix: a composite index that also matches the sort order, a fresh ANALYZE and extended statistics for correlated columns.

Which index shape fits which query pattern?

Once the planner's estimates are accurate, an index helps only if its shape matches how the query reads data. A B-tree is sorted by its key columns in order, so a query can use only a contiguous slice of it. Put equality columns first so the scan jumps to one key prefix. Then add the range or sort column, so matching rows sit next to each other and already come out in ORDER BY order. With a LIMIT, the scan can stop early instead of sorting the whole result.

Query pattern Index shape Benefit Caveat
WHERE tenant_id = ? AND created_at > ? ORDER BY created_at CREATE INDEX ON orders (tenant_id, created_at) One contiguous range scan, no sort step Putting created_at first scatters each tenant's rows across the index
WHERE status = 'pending', where few rows are pending CREATE INDEX ON jobs (created_at) WHERE status = 'pending' Small index, cheap to maintain and to keep cached The planner uses it only when the query's WHERE clause implies the index predicate
WHERE lower(email) = ? CREATE INDEX ON users (lower(email)) Lets the planner use an index on a computed value The query must use exactly the same expression
Reads a few extra columns per lookup CREATE INDEX ON orders (customer_id) INCLUDE (total, status) Enables index-only scans that skip the table (heap) Wider index means more write and storage cost

Index-only scans skip the heap only for pages that the visibility map marks all-visible. Vacuum maintains that map, so if autovacuum falls behind on a busy table, Postgres has to check the heap for each row anyway. In EXPLAIN (ANALYZE), a high "Heap Fetches" count is the sign of this problem. Tune autovacuum for that table before adding more covering columns.

-- Equality column first, then the range/sort column.
CREATE INDEX CONCURRENTLY orders_tenant_created_idx
  ON orders (tenant_id, created_at);

-- Partial index: covers only the small 'pending' slice.
CREATE INDEX CONCURRENTLY jobs_pending_created_idx
  ON jobs (created_at) WHERE status = 'pending';

-- Expression index: queries must use lower(email) exactly.
CREATE INDEX CONCURRENTLY users_lower_email_idx
  ON users (lower(email));

-- Covering index: INCLUDE columns allow index-only scans.
CREATE INDEX CONCURRENTLY orders_customer_cov_idx
  ON orders (customer_id) INCLUDE (total, status);

-- Check whether the index-only scan actually avoids the heap.
-- A high "Heap Fetches" value means the visibility map is stale.
EXPLAIN (ANALYZE, BUFFERS)
SELECT total, status FROM orders WHERE customer_id = 4242;

-- See whether autovacuum is keeping up on this table.
SELECT relname, n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
WHERE relname = 'orders';

-- Vacuum this table more often than the global default
-- (illustrative values; tune them for your write rate).
ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_vacuum_threshold = 1000
);

-- One-off vacuum to refresh the visibility map now.
VACUUM (ANALYZE) orders;

Creates one index of each shape from the table, then uses EXPLAIN's Heap Fetches count and per-table autovacuum settings to keep index-only scans from falling back to the heap.

How do you add and remove indexes safely in production?

Build every new index with CREATE INDEX CONCURRENTLY. A plain CREATE INDEX blocks writes to the table for the whole build. The concurrent form allows reads and writes to continue, but it has costs:

  • It cannot run inside a transaction block. Migration tools that wrap each step in BEGIN … COMMIT need that step marked as non-transactional.
  • It takes longer. It scans the table twice, competes for I/O and CPU with live traffic, and waits for transactions that could see the table to finish. A long-running transaction or an idle-in-transaction session will stall the build indefinitely.

If the build fails or is cancelled, Postgres leaves the index behind marked invalid. The planner ignores it, but every write still updates it. Find these with pg_index.indisvalid = false, then remove them with DROP INDEX CONCURRENTLY before retrying.

To remove dead weight, look at pg_stat_user_indexes.idx_scan across a full business cycle. That window should include month-end jobs and rare reports. Check stats_reset in pg_stat_database so you know when the counters started. These statistics are tracked per instance, so query each replica separately. An index that shows zero scans on the primary may serve all of a replica's read traffic.

Before dropping an index, confirm it does not back a constraint. Primary key, unique and exclusion indexes enforce correctness even when idx_scan is zero. Match pg_constraint.conindid against the index's OID. Drop with DROP INDEX CONCURRENTLY.

Fewer indexes also save engineering time. There is less bloat to monitor, fewer REINDEX jobs to schedule, and fewer rebuilds to run under pressure during an incident.

When should you avoid indexing as a cost lever, and what are the alternatives?

Every index is a write tax. Each INSERT updates every index on the table, and so does each UPDATE that cannot use a HOT (heap-only tuple) update. Each of those index writes also generates WAL. HOT updates only apply when no indexed column changes and the page has free space. Indexing a frequently updated column such as status or updated_at can therefore turn cheap in-page updates into full index maintenance. On a write-heavy table, that extra I/O and WAL can cost more than the read savings.

On a smaller instance, indexes also compete with table data for shared_buffers and the OS page cache. Add enough of them and the working set stops fitting in memory. At that point the downsize fails for the same reason you were trying to avoid.

Indexing is the wrong fix when the time goes somewhere other than scans:

  • Connection overhead: too many backends, each with its own memory.
  • Lock contention: queries waiting on locks, not on I/O.
  • Bloat: dead tuples inflating scans and index size.
  • N+1 patterns: thousands of fast queries per request.
  • Weak application caching: the same rows re-read on every request.
Problem Alternative Development cost
Connection overhead Connection pooler in transaction mode Low to medium. Watch session state and prepared statements.
Lock contention Shorter transactions, batched writes Medium. Requires application changes.
Bloat Autovacuum tuning, scheduled repack Low. Operational work.
N+1 queries Rewrite as joins or batched IN lists Medium. Touches ORM code paths.
Repeated reads Application or materialized-view cache Medium to high. Adds invalidation logic.
Wide, mixed-use tables Schema changes, such as splitting hot columns High. Requires migrations and backfills.

How do you prove the workload shrank and size down without risk?

Check two layers against the pre-change baseline. A query that runs faster can still read the same pages, and freed capacity is sometimes absorbed by other queries. Only the instance metrics show whether the server actually got lighter.

Layer Metric What a real win looks like
Per query (pg_stat_statements) Mean time, rows, shared blocks hit + read per call Fewer blocks per call, not only lower latency
Instance CPU, read IOPS, cache hit ratio Sustained drop at peak, not just on average

Headroom math (illustrative estimate)

Assume a 16-vCPU instance whose peak CPU fell from 60% to 25% after indexing. That is about 4 vCPU of work at peak. On 8 vCPU, the same work runs at about 50% at peak. The other half covers burst traffic, autovacuum, index maintenance and a failover in which one node takes all the load. If the result had come out above roughly 60%, stay on the larger size.

Managed services often lower network and storage throughput limits on smaller instance sizes. Check those limits as well as vCPU and RAM.

After the resize

  • Re-tune shared_buffers, effective_cache_size, work_mem and max_connections for the new memory size. work_mem can be allocated several times per query, so multiply it by your realistic concurrency before setting it.
  • Confirm the working set still fits in memory. A falling cache hit ratio means it does not.
  • Stage the resize on a replica or staging copy first, and watch it through a full business cycle, including month-end and batch jobs.
  • Keep a tested rollback plan, such as a scale-up path or failover target.
  • Schedule recurring reviews of the top-N queries and of unused indexes, so the savings don't erode over time.

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 indexing reduce costs, 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.
  • The dependency graph answers "what does this touch?" in seconds instead of a manual audit.

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.
  • You can size a change's blast radius before merging, avoiding surprise cross-team firefighting.
  • 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.