PostgreSQL Performance Tuning

What it is and when to use it

PostgreSQL tuning is the discipline of measuring where time goes and fixing the biggest costs first: slow queries, missing indexes, poor memory settings, table bloat and connection overload. Start tuning when latency percentiles climb, CPU or I/O saturates, or a dashboard shows a handful of queries dominating total execution time.

The rule is measure first. Changing configuration without evidence usually helps less than one correct index.

Quick start

Enable the statistics extension, then ask the database what is slow.

-- postgresql.conf: shared_preload_libraries = 'pg_stat_statements' (restart required)
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

SELECT calls,
       round(total_exec_time::numeric, 1) AS total_ms,
       round(mean_exec_time::numeric, 2)  AS mean_ms,
       left(query, 80)                    AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Then inspect a candidate with its real plan:

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 42 AND status = 'open';

Core concepts

Reading plans. Compare estimated rows to actual rows. A large mismatch means stale or insufficient statistics. A sequential scan on a big table with a selective filter suggests a missing index. High Buffers: read means disk I/O, while shared hit means cache.

Indexes. B-tree handles equality and ranges. Composite indexes should put equality columns first, then range columns. Partial indexes shrink size for common filters, and covering indexes with INCLUDE allow index-only scans.

CREATE INDEX CONCURRENTLY idx_orders_cust_open
  ON orders (customer_id, created_at)
  WHERE status = 'open';

CREATE INDEX CONCURRENTLY idx_orders_cover
  ON orders (customer_id) INCLUDE (total);

Statistics. The planner relies on ANALYZE. For skewed columns raise the target and refresh:

ALTER TABLE orders ALTER COLUMN status SET STATISTICS 500;
ANALYZE orders;

MVCC and vacuum. Updates and deletes leave dead tuples. Autovacuum reclaims them and freezes old transaction ids. If it falls behind, tables bloat and scans slow down.

Memory and configuration

Reasonable starting points for a dedicated 16 GB server:

shared_buffers = 4GB
effective_cache_size = 12GB
work_mem = 32MB
maintenance_work_mem = 1GB
random_page_cost = 1.1        # SSD storage
max_wal_size = 8GB
checkpoint_completion_target = 0.9

work_mem applies per sort or hash node per connection, so multiply it by concurrency before raising it. Per-table autovacuum tuning helps hot tables:

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

Find unused and missing indexes:

SELECT relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

SELECT relname, seq_scan, idx_scan, n_dead_tup
FROM pg_stat_user_tables
ORDER BY seq_scan DESC LIMIT 10;

Pitfalls

• Index everything. Each index slows writes and consumes space. Drop unused ones.
• Functions on indexed columns. A filter such as lower(email) = 'a@b.c' ignores a plain index. Create an expression index instead.
• Large OFFSET pagination. It scans and discards rows. Use keyset pagination with a WHERE on the last seen key.
• Long transactions block vacuum and hold locks. Watch pg_stat_activity for idle in transaction sessions and set idle_in_transaction_session_timeout.
• Too many connections. Each is a process with its own memory. Pool them rather than raising max_connections.
• Blind configuration copy-paste. Calculators give a start, not an answer.
• Building indexes without CONCURRENTLY takes a write-blocking lock.

How it fits an architecture

Tuning is part of a loop: observe with pg_stat_statements and slow query logging (log_min_duration_statement = 500), fix the top offenders, and redeploy. Offload heavy reads to streaming replicas, put a pooler in front for connection scaling, partition very large time-series tables, and cache hot lookups in Redis. Export metrics such as cache hit ratio, replication lag, dead tuples and checkpoint frequency to your monitoring stack and alert on trends.

Key takeaways

• Measure with pg_stat_statements and EXPLAIN (ANALYZE, BUFFERS) before changing anything.
• One well-designed composite or partial index often beats any config change.
• Keep statistics fresh and autovacuum healthy.
• Size work_mem against concurrency, not a single query.
• Pool connections and keep transactions short.
• Create indexes with CONCURRENTLY on live systems.