PostgreSQL Performance: Fix the Queries Before You Touch the Config

Almost every PostgreSQL performance problem is a missing index or an N+1 query, and almost every PostgreSQL performance article starts with shared_buffers. That ordering is backwards and it costs teams weeks. Config tuning gets you a percentage. A missing index on a table that has grown past a few hundred thousand rows gets you two orders of magnitude. Find the slow queries first, look at their plans, fix the access path - and only when the queries are sane should you start moving memory settings around.

Table of contents

• Turn on pg_stat_statements and find out what is actually slow
• Read the plan, and look for the sequential scan
• Add the index, but add the right one
• The N+1 is probably in your ORM, not your SQL
• Now you can tune the config
• Vacuum, bloat, and the thing that kills you at 3am
• How this fits the rest of the stack
• FAQ

Turn on pg_stat_statements and find out what is actually slow

You cannot fix what you have not measured, and the guess is almost always wrong. pg_stat_statements is the single highest-value thing you can enable on a Postgres database.

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

You will need shared_preload_libraries = 'pg_stat_statements' in the config and a restart. On most managed platforms it is available as a toggle.

Then ask the only question that matters - not which query is slowest, but which query costs the most in total:

SELECT
  calls,
  round(mean_exec_time::numeric, 2) AS avg_ms,
  round(total_exec_time::numeric / 1000, 1) AS total_sec,
  query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

Sort by total_exec_time, not mean_exec_time. A 2-second report that runs twice a day is not your problem. A 40ms query that runs eight thousand times a minute is, and it will never show up if you sort by average.

This distinction is the reason so many tuning efforts optimise the wrong thing. The query that hurts is usually fast enough to look innocent.

Read the plan, and look for the sequential scan

Take the worst offender and ask Postgres what it is doing:

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id = 42;

ANALYZE actually runs it, so the timings are real. BUFFERS shows what came from cache versus disk. Read it inside-out - the deepest node runs first.

What you are looking for:

• Seq Scan on a large table with a selective filter. This is the missing index, and it is the most common finding by a wide margin. Postgres is reading every row to find a handful.
• A large gap between estimated and actual rows. rows=12 on a node that returned rows=48000 means the planner is working from bad statistics, and every decision downstream of that node is built on a wrong assumption. Run ANALYZE <table>;.
• Nested Loop over a large row count. Fine for a few rows, catastrophic for many.
• Sort with external merge Disk. The sort spilled to disk because work_mem was too small for it.

A sequential scan is not automatically bad, and this is where people over-correct. On a 500-row lookup table, a seq scan is the right plan - reading the whole thing is cheaper than bouncing through an index. Postgres knows this. The problem is a seq scan on a large table where the filter matches a small fraction of rows.

Add the index, but add the right one

The fix for most slow queries is one index. The trick is adding the one the query can actually use.

CREATE INDEX CONCURRENTLY idx_orders_customer_id ON orders (customer_id);

Always use CONCURRENTLY on a live database. A plain CREATE INDEX takes a lock that blocks writes to the table for the duration, which on a large table means an outage. CONCURRENTLY is slower and needs two passes, but it does not take the table down.

The rules that matter:

Column order in a composite index is not cosmetic. An index on (customer_id, created_at) serves WHERE customer_id = 42 and WHERE customer_id = 42 ORDER BY created_at. It does not serve WHERE created_at > now() - interval '7 days' on its own. The leftmost columns must be used.

Partial indexes are underrated. If you always query active rows, index only those:

CREATE INDEX CONCURRENTLY idx_orders_active
  ON orders (customer_id) WHERE status = 'active';

Smaller index, faster to scan, cheaper to maintain on write.

Indexes are not free. Every index slows down every INSERT, UPDATE, and DELETE on that table, and consumes disk. A table with twelve indexes has a write problem waiting to happen. Check for unused ones with pg_stat_user_indexes where idx_scan = 0 and drop them without sentiment.

The N+1 is probably in your ORM, not your SQL

The worst database performance problems usually do not appear in the database as slow queries at all. They appear as thousands of individually fast ones.

The pattern: fetch 100 orders, then loop over them and fetch each order’s customer. That is 101 queries, each taking 1ms, for a total of roughly 100ms of pure round-trip latency to do work that a single join would have done in 3ms.

In pg_stat_statements, this looks like a completely innocent query with an enormous calls count. It is the reason to sort by total_exec_time rather than by average.

The fix is at the application layer, not in Postgres:

• Django: select_related for foreign keys, prefetch_related for reverse and many-to-many relations.
• Rails: includes.
• SQLAlchemy: joinedload or selectinload.
• Prisma: include.

No amount of index tuning fixes an N+1. The query was already fast - you just ran it a hundred times. Turn on query logging in development and watch the count on a page render; the number is usually shocking the first time you look.

Now you can tune the config

With the queries fixed, config tuning is worth doing - and there are only a handful of settings that matter.

shared_buffers - roughly 25% of system RAM. Postgres relies on the OS page cache as well, so pushing this to 80% is counterproductive rather than aggressive.

effective_cache_size - roughly 50-75% of RAM. This does not allocate anything; it tells the planner how much cache it can assume exists, which changes whether it chooses index scans over sequential ones. Setting it too low makes the planner needlessly pessimistic about indexes.

work_mem - memory per sort or hash operation, per operation, not per connection. A complex query with three sorts can use three times this value, and 200 connections doing that simultaneously can exhaust the machine. Start around 16-64MB and raise it carefully. This is the setting most likely to cause an out-of-memory incident if you are cavalier with it.

max_connections - lower than you think. Each connection is a process with real memory overhead. Above roughly 100-200, use a connection pooler (PgBouncer) instead of raising the limit. An application with 500 idle connections open is not being efficient, it is being wasteful.

random_page_cost - defaults to 4.0, a number calibrated for spinning disks. On SSD or cloud block storage, set it to 1.1. This one change makes the planner far more willing to use indexes, and on modern storage it is simply a more accurate model of reality. It is the highest-value single config change on most cloud databases, and it is frequently overlooked.

Vacuum, bloat, and the thing that kills you at 3am

Postgres does not overwrite rows on update - it writes a new version and marks the old one dead. VACUUM reclaims those dead rows. Autovacuum does this in the background, and on a busy table with default settings it often cannot keep up.

The symptom is bloat: a table where most of the pages are dead rows, so every scan reads far more data than it needs to. Queries get slower for no apparent reason, and no index will fix it.

Check it:

SELECT relname, n_live_tup, n_dead_tup,
       round(n_dead_tup * 100.0 / NULLIF(n_live_tup + n_dead_tup, 0), 1) AS dead_pct
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC;

If a hot table is above 20% dead rows, autovacuum is losing. Make it more aggressive on that table specifically:

ALTER TABLE orders SET (autovacuum_vacuum_scale_factor = 0.02);

The default scale factor is 0.2, which means autovacuum waits until 20% of the table is dead before acting. On a table with 50 million rows, that is 10 million dead rows before anything happens. For high-churn tables, 0.02 is much more sensible.

And never, on a busy production table, run a bare VACUUM FULL to fix bloat - it takes an ACCESS EXCLUSIVE lock and rewrites the entire table, which means the table is unavailable for the duration. That is an outage. Use pg_repack if you need to reclaim space on a live system.

How this fits the rest of the stack

Whatever you decide here, the cost of the decision only shows up as a bill. The RunxBuild hosting calculator is the right place to model that before committing: the compute, the database, the storage, the bandwidth, the worker - each one is a separate line item, and the real cost of a platform is the sum, not the headline number. The RunxBuild dashboard is where the team sees the actual usage once it is running.

Useful related references:

• PostgreSQL Port: 5432 (Default), How to Change, and Multi-Version Setup
• PostgreSQL ‘Not In’ Queries: Why They Stall and How to Fix Them
• PostgreSQL Backup: pg_dump, pg_basebackup, and WAL Archiving
• Managed Postgres on RunxBuild

FAQ

What is the first thing to check for PostgreSQL performance?

pg_stat_statements, sorted by total_exec_time rather than mean_exec_time. The query that hurts is usually a fast one running thousands of times, not a slow one running twice. Sorting by average is the most common way to optimise the wrong thing.

Should I tune shared_buffers first?

No. Config tuning gets you a percentage; a missing index gets you two orders of magnitude. Find the slow queries, read their plans, fix the access paths and the N+1 queries, and only then adjust memory settings.

What is the single most valuable config change on a cloud database?

Setting random_page_cost to 1.1. The default of 4.0 is calibrated for spinning disks and makes the planner unduly reluctant to use indexes. On SSD or cloud block storage, 1.1 reflects reality and often flips plans from sequential scans to index scans.

Why is my query slow even though I added an index?

Common causes: the index column order does not match the query (a composite index only serves queries using its leftmost columns), the planner has stale statistics so it does not know the index is worth using (run ANALYZE), or the table is bloated with dead rows and needs vacuuming.

Is a sequential scan always bad?

No. On a small table, reading everything is genuinely cheaper than bouncing through an index, and Postgres is right to choose it. A sequential scan is only a problem on a large table where the filter matches a small fraction of the rows.