How to Reduce Database Costs: Query Tuning, Indexing and Sizing

What workload-first cost reduction is and who should use it

Workload-first cost reduction means fixing what the database is asked to do before changing what it runs on. The order is queries first, then indexes, then storage, and only then instance size. Instance size is a consequence of the workload. CPU, memory pressure and IOPS all come from the rows your queries read, the indexes every write must maintain, and the data you keep on disk. Shrink that work and the right instance often turns out to be smaller than the one you have.

Buying a bigger instance first treats the symptom. A sequential scan that reads millions of rows to return a few dozen still does that work on larger hardware. It just finishes sooner, so the waste stays in the bill. As data grows, the same query degrades again and the next upgrade costs more. Resizing also hides the evidence: once latency drops, nobody goes back to find the query that caused it. Fixing the workload first gives you a stable baseline, so a later sizing decision rests on real demand.

This guide is for backend and platform engineers running PostgreSQL whose bills scale with CPU, provisioned IOPS, storage volume or instance class. Use it when any of these apply:

• Spend is rising faster than traffic or data volume.
• Slow queries show up in latency dashboards or user complaints.
• A resize decision is coming, whether an upgrade request or a renewal of reserved capacity. Tune the workload before you commit to a size.

Copy WORKLOAD-FIRST: fix the work, then size the hardware

 +-------------+    +-------------+    +-------------+    +---------------+
 | 1. Queries  |--->| 2. Indexes  |--->| 3. Storage  |--->| 4. Instance   |
 | rows read   |    | index upkeep|    | data kept   |    | follows demand|
 +-------------+    +-------------+    +-------------+    +---------------+
        |                  |                  |                   ^
        v                  v                  v                   |
 +----------------------------------------------------------+     |
 |  Resource demand: CPU, memory pressure, IOPS, storage    |-----+
 +----------------------------------------------------------+
        less work -> lower demand -> smaller instance often fits

 BIGGER-INSTANCE-FIRST: treats the symptom

 +-------------+    +----------------+    +----------------------------+
 | Slow query  |--->| Larger instance|--->| Same rows scanned, faster  |
 | seq scan    |    | higher bill    |    | waste stays in the bill    |
 +-------------+    +----------------+    +-------------+--------------+
        ^                                               |
        |   data grows, same query degrades again       |
        +-----------------------------------------------+

Workload-first tuning reduces the CPU, memory and IOPS demand that determines instance size, while upgrading first keeps the wasted work and repeats the cycle as data grows.

How workload-first cost reduction works

The approach is a measurement loop, not a one-off cleanup. Each pass has four steps.

1. Measure where the work goes. pg_stat_statements shows which normalized statements consume the most total execution time and read the most pages from outside shared buffers. pg_stat_user_indexes and pg_stat_user_tables show which indexes are used and how tables grow.
2. Diagnose why. EXPLAIN (ANALYZE, BUFFERS) on a top statement shows the plan the server actually ran: how many rows each node produced, how many pages it touched, and whether sorts or hashes spilled to disk.
3. Remove the work. Rewrite the query so it reads fewer rows and makes fewer round trips, add an index that matches its predicates, drop indexes that only add write cost, and stop storage from growing without a reason.
4. Re-measure, then size. After the fixes, the same statistics show lower total time, fewer buffer reads and less storage growth. Only these post-fix numbers, taken at peak over a full business cycle, go into the instance sizing decision.

The cost saving follows a direct chain. Fewer pages read means fewer buffer lookups and disk reads, which means less CPU time and fewer IOPS. Fewer indexes on a write-heavy table means less work per insert or update, less WAL and less vacuum activity. Less retained data, bloat and WAL means less billed storage. When peak CPU, memory pressure and I/O drop, a smaller instance class or a lower provisioned IOPS tier can carry the same traffic. Because the work itself is removed rather than moved to other hardware, the saving holds as data grows, which is not true of an upgrade.

The steps are ordered so that each one changes the inputs to the next. An index you add for a rewritten query may differ from the one the original query needed. Dropping unused indexes changes storage. All of it changes the resource peaks that sizing depends on, which is why sizing comes last.

Finding the queries that cost the most

Rank queries by the total load they put on the server, not by how slow their worst run was. The pg_stat_statements extension groups statements by normalized query text and records calls, mean_exec_time and total_exec_time. Sort by total_exec_time, and check shared_blks_read to see which queries pull the most data from disk rather than from cache.

Total time is what you pay for. It is roughly calls × mean time, and frequency usually matters more than duration.

Illustrative estimate (assumed numbers, not a benchmark):

• Query A runs 2 times per hour at 30 s each: 2 × 30 s = 60 s per hour.
• Query B runs 40,000 times per hour at 25 ms each: 40,000 × 0.025 s = 1,000 s per hour.

Query A shows up in the slow-query log. Query B uses about 16 times more execution time and keeps CPU and I/O busy all day.

For each top query, run EXPLAIN (ANALYZE, BUFFERS) on a representative input. Look for these warning signs:

• Seq Scan on a large table where the filter keeps only a few rows. Buffer counts show how many pages were read to find them.
• Estimated rows far from actual rows, by an order of magnitude or more. The planner chose its join and scan strategy based on the wrong numbers. Stale or missing statistics are a common cause.
• Spills to disk: Sort Method: external merge with a Disk: figure, or a Hash node with Batches greater than 1.
• Nested Loop with a large outer input, where the inner node's loops count is in the thousands or more. The inner work is repeated once per outer row.

Copy-- Requires shared_preload_libraries = 'pg_stat_statements' and a restart, then:
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Rank normalized statements by total execution time (calls x mean), not worst run.
-- Column names below are for PostgreSQL 13+ (older versions use total_time/mean_time).
SELECT
  left(query, 80)                         AS query_snippet,
  calls,
  round(mean_exec_time::numeric, 2)       AS mean_ms,
  round(total_exec_time::numeric / 1000)  AS total_s,
  round(100 * total_exec_time
        / sum(total_exec_time) OVER (), 1) AS pct_of_total,
  shared_blks_read,                       -- pages read from outside shared buffers
  shared_blks_hit                         -- pages served from shared buffers
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- Inspect a top query with a representative input.
-- ANALYZE really executes the statement: wrap writes in BEGIN ... ROLLBACK.
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, o.created_at, c.email
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'pending'
ORDER BY o.created_at DESC;

-- In the output, look for:
--   Seq Scan on orders ... Rows Removed by Filter: (large)  -> missing/unused index
--   rows=50 (estimate) vs actual rows=48000                 -> stale stats; run ANALYZE orders;
--   Sort Method: external merge  Disk: ...kB                -> sort spilled past work_mem
--   Hash ... Batches: 8                                     -> hash table spilled to disk
--   Nested Loop with inner node loops=48000                 -> inner work repeated per outer row

Ranks statements by total execution time with pg_stat_statements, then runs EXPLAIN (ANALYZE, BUFFERS) on a top query, with comments marking the plan lines that signal trouble.

Query-level fixes that cut rows read and round trips

Once the expensive statements are identified, most fixes reduce how much work the database does per request. Lower latency follows from that, but the cost saving comes from fewer rows read, fewer pages pulled into memory and fewer network round trips.

Remove N+1 access

A loop that runs SELECT ... WHERE order_id = $1 once per order turns one page view into hundreds of statements. Each one pays parse, plan, execute and network overhead. A single query using WHERE order_id = ANY($1) or a join does the same work in one round trip and lets the planner choose a set-based plan.

Select only needed columns

SELECT * reads wide rows and moves unused bytes over the network. It can also prevent an index-only scan, because the index no longer covers the columns requested. Name the columns the caller actually uses.

Replace OFFSET with keyset pagination

OFFSET 50000 LIMIT 50 still reads and discards the first 50,000 rows, so deep pages get steadily more expensive. Keyset pagination filters on the last value seen, as in WHERE (created_at, id) < ($1, $2) ORDER BY created_at DESC, id DESC LIMIT 50. An index on those columns makes each page cost about the same.

Batch writes

Use multi-row INSERT, or COPY for bulk loads, and group statements into fewer transactions. This cuts round trips and per-commit WAL flushes.

Verify at production scale

Plans tested on small staging data can change at production scale or after a statistics update. Confirm each fix with EXPLAIN (ANALYZE, BUFFERS) against realistic row counts.

Copy-- N+1: one statement per order (run in an app loop)
-- SELECT id, sku, qty FROM order_items WHERE order_id = $1;

-- Fixed: one round trip for all orders on the page
SELECT order_id, id, sku, qty
FROM order_items
WHERE order_id = ANY($1::bigint[]);

-- OFFSET: reads and discards 50,000 rows before returning 50
SELECT id, created_at, total
FROM orders
ORDER BY created_at DESC, id DESC
OFFSET 50000 LIMIT 50;

-- Keyset: index supports both the filter and the sort
CREATE INDEX orders_created_id_idx ON orders (created_at DESC, id DESC);

SELECT id, created_at, total
FROM orders
WHERE (created_at, id) < ($1, $2)  -- last row of previous page
ORDER BY created_at DESC, id DESC
LIMIT 50;

-- Batch writes: one statement, one commit, fewer WAL flushes
INSERT INTO order_items (order_id, sku, qty) VALUES
  (1001, 'SKU-A', 2),
  (1001, 'SKU-B', 1),
  (1002, 'SKU-A', 5);

-- Verify against production-sized data, not staging
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, created_at, total
FROM orders
WHERE (created_at, id) < ('2026-01-01', 900000)
ORDER BY created_at DESC, id DESC
LIMIT 50;

PostgreSQL before-and-after examples for removing N+1 lookups, switching from OFFSET to keyset pagination, batching inserts, and checking the resulting plan with EXPLAIN (ANALYZE, BUFFERS).

Indexing deliberately: what to add, what to drop

Add indexes that match real predicates

In a composite index, put equality columns first and the range or sort column last. An index on (tenant_id, status, created_at) serves WHERE tenant_id = ? AND status = ? AND created_at > ? with a single contiguous range scan. If created_at comes first, the scan reads every row in the date range and filters the rest. When queries only target a hot subset, such as unprocessed jobs, a partial index with a WHERE clause stays small and cheap to maintain. INCLUDE adds non-key columns so the query can be answered by an index-only scan. That only skips the heap if vacuum keeps the visibility map current. Build each index with CREATE INDEX CONCURRENTLY so writes are not blocked. If the build fails, it leaves an INVALID index that you must drop.

Drop what nothing uses

Look in pg_stat_user_indexes for indexes with idx_scan = 0. Skip any that back primary key or unique constraints. Check every replica, because each server keeps its own counters. Find duplicates by comparing definitions in pg_index. Also look for left-prefix overlap: (a) is redundant when (a, b) exists.

On write-heavy tables, the total cost can exceed what the indexes save. Statistics also reset after a crash or a manual pg_stat_reset(). Observe across at least one full business cycle so you don't drop an index that a monthly job depends on.

Copy-- 1. Equality columns first, range column last. CONCURRENTLY avoids blocking writes.
CREATE INDEX CONCURRENTLY idx_orders_tenant_status_created
  ON orders (tenant_id, status, created_at);

-- 2. Partial index: covers only the hot subset that workers poll.
CREATE INDEX CONCURRENTLY idx_jobs_pending
  ON jobs (created_at) WHERE processed_at IS NULL;

-- 3. Covering index: INCLUDE lets the query use an index-only scan
--    (heap access is skipped only for pages vacuum has marked all-visible).
CREATE INDEX CONCURRENTLY idx_orders_tenant_total
  ON orders (tenant_id, created_at) INCLUDE (total_amount);

-- A failed concurrent build leaves an INVALID index. Find and drop these.
SELECT indexrelid::regclass AS invalid_index
FROM pg_index WHERE NOT indisvalid;

-- Unused indexes on this server, excluding primary key and unique indexes.
-- Run on every replica too, because each server keeps its own counters.
SELECT s.relname, s.indexrelname, s.idx_scan,
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS size
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE s.idx_scan = 0
  AND NOT i.indisunique AND NOT i.indisprimary
ORDER BY pg_relation_size(s.indexrelid) DESC;

-- Exact duplicates: same table, columns, opclasses, expressions and predicate.
SELECT indrelid::regclass AS table_name,
       array_agg(indexrelid::regclass) AS duplicate_indexes
FROM pg_index
GROUP BY indrelid, indkey::text, indclass::text,
         coalesce(indexprs::text, ''), coalesce(indpred::text, '')
HAVING count(*) > 1;

-- When dropping, also avoid blocking the table:
-- DROP INDEX CONCURRENTLY idx_name_here;

PostgreSQL statements that build composite, partial and covering indexes concurrently, then find invalid builds, unused non-constraint indexes, and exact duplicate definitions.

Controlling storage growth

Storage is billed for capacity and often for provisioned IOPS, so growth you don't control becomes a recurring cost. It usually has four sources.

Table and index bloat. PostgreSQL's MVCC leaves dead tuples behind after every UPDATE and DELETE. Autovacuum marks that space reusable but rarely returns it to the operating system. The default autovacuum_vacuum_scale_factor of 0.2 waits until about 20% of a table is dead. On a large, frequently updated table that is a lot of wasted space, so lower the scale factor per table with ALTER TABLE ... SET (...) rather than globally. Indexes bloat as well. REINDEX CONCURRENTLY rebuilds them without blocking writes, but it still does a full rebuild.

Retained WAL. An inactive or lagging replication slot stops WAL from being recycled, and disk use grows until the slot catches up or is dropped. Check pg_replication_slots and set max_slot_wal_keep_size to cap how much WAL a slot can hold.

Unrotated logs. Verbose statement logging fills disks quietly. Configure log_rotation_age and log_rotation_size, and give the files an expiry.

Old data. Deleting old rows in bulk creates more dead tuples and more WAL. If you range-partition time-series tables by date, expiring a month becomes DROP TABLE or DETACH PARTITION, which is a near-instant metadata change. Write the retention period down as policy so that expiry runs on a schedule instead of waiting for someone to notice the disk is full.

Trade-offs. Partitioning adds schema management, and queries that don't filter on the partition key scan every partition. Aggressive vacuuming and reindexing use I/O and CPU while they run, so schedule them in low-traffic windows.

Right-sizing the instance from measured signals

Sizing is the last step because every earlier fix changes the numbers. Measure after tuning and indexing, over at least one full business cycle, and use peak values rather than averages.

Each PostgreSQL connection is a separate backend process with its own memory. Hundreds of mostly idle connections can make an instance look memory-bound. A pooler that multiplexes them onto a small number of server connections is cheaper than a larger memory class.

Illustrative estimate. These inputs are assumed, not measured. A query runs 200 times per second. Each call reads 12,000 buffers and uses 8 ms of CPU, which totals 1.6 cores. After you add an index, each call reads 6 buffers and uses 0.1 ms, which totals 0.02 cores. On an 8-vCPU instance peaking at 4.8 cores (60%), the peak drops to about 3.2 cores. On a 4-vCPU instance that load would run at 80%, which is too tight. The safer move is a smaller memory class or a deferred downsize.

Don't downsize to fit average load. Size for the worst sustained window. Leave headroom for traffic spikes, a replica absorbing load during failover, and VACUUM, reindexing and backups, which all compete for the same CPU and I/O.

When to use workload-first cost reduction

Use this approach when the conditions that make it pay off are present:

• Your bill tracks resource consumption. If you pay for instance class, provisioned IOPS or storage volume, reducing rows read, index upkeep and retained data translates into a smaller resource footprint you can actually buy less of.
• Load is concentrated in a few statements. When the top handful of entries in pg_stat_statements account for most of the total execution time, fixing those few gives most of the benefit for little effort.
• You can change the queries and schema. The fixes in this guide require control over application SQL, ORM usage, indexes and table layout.
• You are about to commit to a size. Before an upgrade, a reserved-capacity purchase or a migration to a new instance class, tuning first means you commit to a size based on the workload you actually need to run.
• Data is growing. Queries that scan more rows as tables grow will keep getting more expensive on any hardware. Fixing them stops that growth from compounding.
• Storage is climbing without a matching rise in live data. That pattern points to bloat, retained WAL, logs or missing retention, all of which are cheap to address.

When to avoid it or defer it

Workload-first tuning is not always the right first move:

• During an active incident. If the database is saturated and users are affected, scale up or shed load first to restore service. Tune afterwards, then scale back down once the workload is fixed.
• When engineering time costs more than the saving. A small database whose bill is already low rarely justifies days of tuning. Use the break-even calculation in the next section before starting.
• When you cannot change the SQL. If the queries come from third-party software you don't control, your options are limited to indexes, configuration and storage. Some vendors also restrict schema changes, so check before adding or dropping indexes.
• When the system is about to be retired or replaced. Tuning a database that will be migrated or decommissioned in a few months rarely pays back.
• When the cost driver is elsewhere. If most of the bill comes from something outside the workload, such as backup retention or network transfer, reducing rows read won't move it much. Look at the billing breakdown before choosing where to spend effort.
• When load is spread thinly across many statements. If no single statement or small group dominates total execution time, each fix yields little, and a sizing or architecture change may be the more efficient option.

Alternatives, and the impact on cost and engineering time

Before you tune further, compare the three options teams usually reach for first.

Caches and replicas spread a slow query's load across more hardware. They do not reduce the rows it reads. As data grows, the same query slows down on every copy, and you pay for each copy.

Engineering time is also a cost. Stop tuning when the next improvement is worth less than a modest upgrade.

Illustrative estimate (assumed numbers, not a benchmark): break-even months = (engineer hours × hourly cost) ÷ monthly saving. Assume 40 hours at $100/hour to save $250/month: 4,000 ÷ 250 = 16 months. If the workload is stable and the upgrade is that cheap, buying the upgrade may be the better trade.

Making savings stick

• In CI or staging, compare query plans for critical queries. Fail the check when a sequential scan replaces an index scan or the estimated cost jumps.
• Alert on the rate of storage growth, not only on free space, so a runaway table or bloat shows up weeks early.
• Alert when a new statement enters the top queries by total time.
• After every downsize, review the workload again. Less memory changes cache hit ratios, and that can change query plans.

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 reduce database 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.
• 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.