PostgreSQL performance tuning: where to start

An application being "slow" is usually a problem with a few queries, not the whole database. This post suggests an order you can apply safely in production: measure first, focus on the most expensive work, then verify the change.

1. Measure: find the most expensive queries

The pg_stat_statements extension is the foundation of performance work. The top 20 queries by total time usually represent most of the workload.

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT
  round(total_exec_time::numeric, 1) AS total_ms,
  calls,
  round(mean_exec_time::numeric, 2)  AS mean_ms,
  round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 1) AS pct,
  query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

"Fast on average but called very often" queries matter just as much as "individually slow" ones; the pct column makes that visible.

2. Diagnose: reading EXPLAIN (ANALYZE, BUFFERS)

Run the suspect query with real data and inspect its plan:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT ...;

Main signals to watch:

• A Seq Scan on a large table together with a selective WHERE may mean a missing index.
• High Rows Removed by Filter means the database reads far more rows than it returns.
• A big gap between estimated and actual rows usually points to stale statistics or a correlation issue; run ANALYZE table;.
• A Nested Loop running unexpectedly over a large outer set indicates a join-order or index problem.
• High shared read (BUFFERS) may mean the working set no longer fits in memory.

3. Index strategy

Common, low-risk improvements:

• Composite index order: equality filters first, then range filter and sort column.
• Covering index: add frequently read columns with INCLUDE (...) to enable index-only scans.
• Partial index: when most queries hit a small subset, e.g. WHERE status = 'active'.
• Expression index: if you filter with a function like lower(email), index the same expression — or make the filter sargable.

Add new indexes in production with CREATE INDEX CONCURRENTLY; it avoids a long table lock. Consider the write cost too: every index slows INSERT/UPDATE/DELETE a little. Finding and dropping never-used indexes with pg_stat_user_indexes is a win as well.

4. Query and schema fixes

• Sargable filters: use created_at >= ... AND created_at < ... instead of WHERE date(created_at) = ....
• Keyset pagination: continue from the last seen key instead of deep OFFSET.
• N+1: combine the ORM's one-query-per-loop-iteration into a single JOIN or IN.
• Don't fetch unnecessary columns: SELECT * breaks index-only scans and network efficiency.

5. Autovacuum and statistics

On heavily updated tables the default autovacuum threshold stays too high; dead rows (bloat) accumulate and the planner is misled. Tighten it per table:

ALTER TABLE order_lines SET (
  autovacuum_vacuum_scale_factor  = 0.02,
  autovacuum_analyze_scale_factor = 0.01
);

The n_dead_tup, last_autovacuum and last_autoanalyze columns in pg_stat_user_tables show whether the setting is working.

6. A few configuration parameters

• shared_buffers: typically ~25% of RAM.
• effective_cache_size: the total expected including the OS cache; it affects the planner.
• work_mem: for sorts/hashes; increase carefully, as it is allocated per session and per operation.
• random_page_cost: around 1.1 on SSDs, which encourages index use.

7. Verify and make it stick

Record mean_exec_time and total_exec_time before and after every change. Add an alert to protect the gain, e.g. "notify if this query's p95 exceeds 500 ms". Otherwise you'll meet the same problem again in a few months.

Want us to apply these steps in your environment together? See our software consulting service or book a short call.