Sign inBook a Demo

How To

Hands-On PostgreSQL Performance Tuning with Native Tools Only

A hands-on PostgreSQL performance tuning audit using only native tools: enable the slow query log with log_min_duration_statement, rank your worst queries with pg_stat_statements, read EXPLAIN (ANALYZE, BUFFERS) output without guessing, and find the unused and duplicate indexes taxing every write. Every command is copy-pasteable — from postgresql.conf settings to the pg_stat_user_indexes sweep, plus auto_explain for catching slow plans in production. Part two of our PostgreSQL performance tuning series, closing with an honest look at where a manual audit stops paying for itself.

ST
·
postgresqldatabaseperformancepostgresperformancetuningpgstatstatementsslowquerylog
Cover Image for Hands-On PostgreSQL Performance Tuning with Native Tools Only

Hands-On PostgreSQL Performance Tuning with Native Tools Only

There is one line in postgresql.conf that separates teams that guess about database performance from teams that measure it:

log_min_duration_statement = 500ms

Everything in this guide flows from data like that. PostgreSQL performance tuning without measurement is how teams end up "fixing" slow queries with a bigger instance — paying every month, forever, for a problem an index would have solved once. In part one of this series we covered the six signals that matter: slow queries, cache hit ratio, unused and bloated indexes, sequential scans, connection saturation, and autovacuum lag.

This article is the audit itself, using only what ships with PostgreSQL: the slow query log, pg_stat_statements, EXPLAIN (ANALYZE, BUFFERS), the pg_stat_user_indexes view, and auto_explain. Nothing to buy, nothing to install beyond two bundled extensions. Budget half a day for a first pass on a production database, and expect the output to be a ranked list of queries and indexes with evidence attached — not a hunch.

Step 1: Turn on the slow query log

The slow query log is your ground truth: it records every statement that exceeds a threshold, with its actual duration (PostgreSQL docs). On a self-managed instance:

ALTER SYSTEM SET log_min_duration_statement = '500ms';
SELECT pg_reload_conf();

No restart needed. On RDS, Aurora, or Cloud SQL, set the same parameter in the parameter group / database flags instead. Start at 500ms on a first audit; once the worst offenders are fixed, tighten to 100–250ms. Setting it to 0 logs every statement — useful for an hour of forensics, ruinous as a steady state on a busy OLTP box.

While you're in there, three companions that cost almost nothing and catch real problems:

ALTER SYSTEM SET log_lock_waits = on;        -- logs waits longer than deadlock_timeout (1s default)
ALTER SYSTEM SET log_temp_files = 0;         -- logs every sort/hash that spilled to disk
ALTER SYSTEM SET log_checkpoints = on;       -- default on since PG 15
SELECT pg_reload_conf();

How to read it. A log entry looks like:

LOG:  duration: 1432.117 ms  statement: SELECT * FROM orders WHERE customer_id = $1 AND status = 'open'

Don't fix queries as you spot them — collect for a few days first. One 1.4-second query that runs hourly is noise; a 600ms query that runs 40,000 times a day is your CPU bill. Which brings us to the tool that does that math for you.

Step 2: Rank the workload with pg_stat_statements

pg_stat_statements aggregates every query shape — normalized, with literals stripped — into calls, total time, rows, and buffer usage (PostgreSQL docs). It ships with PostgreSQL but needs to be preloaded:

# postgresql.conf — requires a restart (parameter group on RDS/Cloud SQL)
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.max = 10000
pg_stat_statements.track = top

Then, in the database you want to audit:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Let it collect at least a full business cycle (a week is ideal), then pull the ranking that matters — total execution time, which is the closest native proxy for "compute you are paying for":

SELECT
  round(total_exec_time::numeric / 60000, 1)  AS total_min,
  calls,
  round(mean_exec_time::numeric, 2)           AS mean_ms,
  round((100 * total_exec_time /
         sum(total_exec_time) OVER ())::numeric, 1) AS pct_of_load,
  rows,
  left(query, 90)                             AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

(On PostgreSQL 12 and earlier the columns are total_time / mean_time.)

How to read it. The distribution is almost always brutal: on a typical mid-market SaaS database, the top 10 query shapes account for 60–90% of pct_of_load. Those ten are your entire tuning backlog — ignore everything else on the first pass. Watch for two patterns: high calls with modest mean_ms (an N+1 or missing cache — fix the application), and modest calls with high mean_ms (a plan problem — fix with the next step).

One more cut worth taking — queries doing the most physical I/O, which are the ones evicting everyone else's cache:

SELECT
  left(query, 60) AS query,
  calls,
  shared_blks_read,
  round(shared_blks_hit * 100.0 /
        nullif(shared_blks_hit + shared_blks_read, 0), 1) AS cache_hit_pct
FROM pg_stat_statements
ORDER BY shared_blks_read DESC
LIMIT 10;

Snapshot the results, then SELECT pg_stat_statements_reset(); so your post-fix numbers start clean.

Step 3: Dissect the top offenders with EXPLAIN (ANALYZE, BUFFERS)

For each of the top queries, get the real execution plan — not the estimate:

BEGIN;
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, o.total
FROM   orders o
WHERE  o.customer_id = 4211 AND o.status = 'open';
ROLLBACK;

ANALYZE actually executes the query, so wrap anything that writes in BEGIN/ROLLBACK. Full reference: Using EXPLAIN.

How to read it. Four things, in order:

  1. Estimated vs actual rows. rows=12 estimated against rows=48000 actual means the planner is flying blind — run ANALYZE orders; first and re-check before touching anything else. Persistent misestimates on correlated columns are a case for extended statistics (CREATE STATISTICS).
  2. Seq Scan on a large table with a selective filter. If a scan reads 4M rows to return 200, that's your index candidate. Note the exact filter columns and their order.
  3. The BUFFERS line. shared hit is cache, shared read is disk. A query showing read=180000 on every execution isn't just slow — it's flushing hot data out of shared_buffers and slowing everything around it.
  4. Sort Method: external merge Disk: .... The sort spilled past work_mem. Sometimes the fix is a modest work_mem bump; more often it's an index that returns rows already ordered.

When a plan suggests an index, build it without locking writes:

CREATE INDEX CONCURRENTLY idx_orders_customer_status
  ON orders (customer_id, status);

Then re-run the EXPLAIN (ANALYZE, BUFFERS) and keep the before/after — that pair of plans is the evidence your findings doc is built from.

Step 4: Audit indexes against the real workload

Adding indexes is half of index strategy. The other half is removing the ones your workload never uses, because every index taxes every INSERT, UPDATE, and vacuum, and occupies cache that working data needs. PostgreSQL counts actual index usage in pg_stat_user_indexes (monitoring docs):

SELECT
  schemaname,
  relname                                        AS table,
  indexrelname                                   AS index,
  pg_size_pretty(pg_relation_size(indexrelid))   AS size,
  idx_scan
FROM pg_stat_user_indexes
JOIN pg_index USING (indexrelid)
WHERE idx_scan = 0
  AND NOT indisunique
  AND NOT indisprimary
ORDER BY pg_relation_size(indexrelid) DESC;

How to read it. Two cautions before you drop anything. First, these counters accumulate since the last stats reset — check stats_reset in pg_stat_database and make sure the window covers month-end jobs and other periodic workloads (PostgreSQL 16+ also gives you last_idx_scan, a timestamp, which settles arguments fast). Second, statistics are per-instance: an index unused on the primary may be what your read replicas live on. Check every node.

Duplicate indexes — same table, same columns, same order — are safer kills. The standard detection query from the PostgreSQL wiki:

SELECT pg_size_pretty(sum(pg_relation_size(idx))::bigint) AS wasted,
       (array_agg(idx))[1] AS keep,
       (array_agg(idx))[2] AS drop_candidate
FROM (
  SELECT indexrelid::regclass AS idx,
         (indrelid::text || E'\n' || indclass::text || E'\n' ||
          indkey::text  || E'\n' ||
          coalesce(indexprs::text, '') || E'\n' ||
          coalesce(indpred::text, '')) AS key
  FROM pg_index
) sub
GROUP BY key
HAVING count(*) > 1
ORDER BY sum(pg_relation_size(idx)) DESC;

Drop with DROP INDEX CONCURRENTLY, one at a time, with a week of monitoring between drops. On a database that's been growing for a few years, reclaiming 10–30% of total index storage in this pass is common — and write latency improves with it.

Step 5: Leave auto_explain running for what you can't reproduce

Some queries are only slow in production, under production data and concurrency. auto_explain logs the actual plan of any statement that crosses a threshold, automatically (PostgreSQL docs):

# postgresql.conf
session_preload_libraries = 'auto_explain'
auto_explain.log_min_duration = '500ms'
auto_explain.log_analyze = on
auto_explain.log_buffers = on
auto_explain.log_timing = off          # per-node timing is the expensive part
auto_explain.log_nested_statements = on

New sessions pick it up after pg_reload_conf(). With log_timing = off, the overhead of log_analyze is modest enough for most production workloads; if yours is latency-critical, sample instead with auto_explain.sample_rate = 0.1. The payoff: next time someone reports "checkout was slow at 14:32", the plan that actually ran at 14:32 is sitting in your log — plan regressions stop being unreproducible mysteries.

Where the DIY audit stops paying

Run this audit; it works. But be honest about its structural limits before making it your strategy:

It's a snapshot. pg_stat_statements rankings drift with every deploy, data-growth milestone, and planner statistics change. The index that was justified in July is unused by November. A quarterly audit catches a quarter's drift, after it has been burning compute the whole time.

It costs senior engineer hours. Reading plans and judging index trade-offs isn't junior work. The first pass is half a day; keeping it current is a recurring tax that quietly loses to feature work — most teams' "monthly review" is dead by the third month.

Detection is not remediation. Every finding above becomes a ticket: an index to build CONCURRENTLY, a drop to stage across replicas, a query to send back to the application team. The gap between "found" and "fixed" is where most of the value evaporates — and where the next unnecessary instance upgrade gets approved.

What comes next

Everything in this guide can run continuously instead of quarterly. Tony, CloudThinker's database agent, watches pg_stat_statements, index usage, and bloat through a read-only connection, ranks findings by compute impact, and proposes indexes with the evidence attached — no DDL ever executes without your approval. That's part three of this series.

Or point it at your own database today: try CloudThinker free — 100 premium credits, no card required.