Hands-On PostgreSQL Performance Tuning with Native Tools Only

A product manager posts in the incident channel: "checkout was slow around 14:32, can someone look?" You open the database dashboard. CPU was a little high, connections a little high, nothing alarming. There's no record of which query was slow, what plan it used, or whether it's still slow now. You can't reproduce it on staging because staging has a tenth of the data. The thread goes quiet, and the same complaint comes back next week.

One line in postgresql.conf 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 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.

01Collect

Slow query log

Every statement over a threshold, with its real duration.

02Rank

pg_stat_statements

Total time per query shape, so frequency and cost are visible together.

03Explain

ANALYZE, BUFFERS

The real plan for each top offender, with cache and disk reads.

04Prune

Index audit

Zero-scan and duplicate indexes, checked on every node.

05Keep

auto_explain

Plans for the slow queries you can only see in production.

---

Why dashboards can't do this part

Infrastructure monitoring sees the database as a box: CPU, memory, IOPS, connection count. Those numbers tell you that the database is working hard. The question tuning needs answered is which statement is making it work hard, and why the planner chose to do it that way. That information exists only inside Postgres, and it disappears unless you ask Postgres to keep it.

That's what the five steps below do. The first two make the workload visible and ranked. The third explains the expensive queries. The fourth removes dead weight. The fifth keeps evidence around for the slow queries you'll never reproduce on staging.

---

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 or database flags instead. Start at 500ms on a first audit; once the worst offenders are fixed, tighten to 100 to 250ms. Setting it to 0 logs every statement, which is useful for an hour of forensics and 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 or 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. The log can't do that math for you. The next tool can.

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 or 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 for at least a full business cycle (a week is ideal), then pull the ranking that matters. Total execution time is the closest native proxy for "compute you're 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 and 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 to 90% of pct_of_load. Those ten are your entire tuning backlog, so ignore everything else on the first pass. Watch for two patterns: high calls with modest mean_ms (an N+1 loop or a missing cache, so fix the application), and modest calls with high mean_ms (a plan problem, so go to the next step).

One more cut worth taking: the 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 run 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 and ROLLBACK. Full reference: Using EXPLAIN.

How to read it. Four things, in order:

01First

Estimate vs actual

rows=12 against rows=48000 means stale statistics. ANALYZE before anything else.

02Second

Seq Scan, few rows out

Four million rows read to return 200 is an index candidate.

03Third

Buffers read

High shared read on every run means the query is flushing everyone else out of cache.

04Fourth

Sort on disk

external merge means the sort spilled past work_mem. Often an index fixes the order.

1. Estimated vs actual rows. rows=12 estimated against rows=48000 actual means the planner is flying blind. Run ANALYZE orders; 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.

A worked example

Say the orders query above came back like this (trimmed, numbers illustrative):

Seq Scan on orders o  (cost=0.00..412883.10 rows=180 width=16)
                      (actual time=0.041..1398.552 rows=212 loops=1)
  Filter: ((customer_id = 4211) AND (status = 'open'::text))
  Rows Removed by Filter: 4180911
  Buffers: shared hit=1204 read=180311
Execution Time: 1398.701 ms

Read it against the four checks. The estimate (180) is close to the actual (212), so statistics are fine and this isn't a planner misjudgment. The planner simply has no index to use. Rows Removed by Filter: 4180911 says it read 4.2 million rows to return 212. read=180311 says most of that came from disk. That's a missing index, and the filter tells you which one.

When a plan points at 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 and after. That pair of plans is the evidence your findings doc is built from. If the new plan shows an Index Scan using idx_orders_customer_status with a handful of buffers, you're done. If it still shows a Seq Scan, check that the column order matches the most selective filter first.

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, so check stats_reset in pg_stat_database and make sure the window covers month-end jobs and other periodic workloads. PostgreSQL 16 and later also give 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. Keep the CREATE INDEX statement for each one (pg_get_indexdef(indexrelid) prints it) so a rollback is a copy-paste. On a database that's been growing for a few years, reclaiming 10 to 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. That's the "checkout was slow at 14:32" problem from the top of this post. 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(). On RDS and Aurora, add auto_explain to shared_preload_libraries in the parameter group instead. With log_timing = off, the overhead of log_analyze is modest enough for most production workloads; if yours is latency-critical, sample 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, and plan regressions stop being unreproducible mysteries.

---

What each native tool answers

Every row in the last column is the same gap: time. Each tool is a point-in-time answer, and tuning is a question about change.

Where the DIY audit stops paying

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

01Limit

A snapshot

Rankings drift with every deploy and every table that crosses a size threshold.

02Limit

Senior hours

Reading plans and judging index trade-offs is expensive work that loses to feature work.

03Limit

Found, not fixed

Every finding becomes a ticket, and the gap between found and fixed is where value leaks.

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. CloudThinker watches pg_stat_statements, index usage and bloat through a read-only connection, ranks findings by compute impact, and proposes indexes with the before-and-after evidence attached. No DDL executes without your approval. That's part three of this series.

Related reading

• Part one: the 6 PostgreSQL metrics that matter
• Neon Postgres tuning with branches, stats and sizing
• MySQL slow query triage with native tools only

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