PostgreSQL Performance Tuning: Indexes, Query Plans, and Configuration

PostgreSQL Performance Fundamentals

Slow queries are the #1 cause of application performance issues. Here's how to systematically diagnose and fix them.

EXPLAIN ANALYZE

-- Always use EXPLAIN (ANALYZE, BUFFERS) for real execution data
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.created_at > '2026-01-01'
GROUP BY u.id, u.name
ORDER BY order_count DESC
LIMIT 20;

-- Key metrics to look at:
-- Seq Scan vs Index Scan
-- Actual rows vs estimated rows (large discrepancy = stale stats)
-- Buffers: shared hit vs read (high read = disk I/O)
-- Loops: nested loop count

Index Types

-- B-tree (default): equality, range, ORDER BY
CREATE INDEX idx_users_email ON users (email);
CREATE INDEX idx_orders_created ON orders (created_at DESC);

-- Composite index: left-to-right prefix rule
CREATE INDEX idx_orders_user_status ON orders (user_id, status, created_at DESC);
-- Useful for: WHERE user_id = X AND status = 'pending'
-- Also: WHERE user_id = X ORDER BY created_at DESC

-- Partial index: index a subset of rows
CREATE INDEX idx_orders_pending ON orders (user_id, created_at)
WHERE status = 'pending';
-- Much smaller, faster for pending-only queries

-- GIN index: full-text search, JSONB, arrays
CREATE INDEX idx_posts_search ON posts USING gin(to_tsvector('english', title || ' ' || content));
CREATE INDEX idx_products_tags ON products USING gin(tags);  -- array column

-- BRIN: time-series data with physical correlation
CREATE INDEX idx_events_timestamp ON events USING brin(timestamp);
-- Very small, effective for append-only time-series

Query Optimization Patterns

-- Use CTEs for readability but be careful (CTE fence in PG < 12)
WITH user_stats AS MATERIALIZED (
  SELECT user_id, COUNT(*) as total, SUM(amount) as revenue
  FROM orders
  WHERE status = 'completed'
  GROUP BY user_id
)
SELECT u.name, s.total, s.revenue
FROM users u
JOIN user_stats s ON s.user_id = u.id
ORDER BY s.revenue DESC
LIMIT 10;

-- Avoid N+1: use window functions
SELECT
  id, name,
  COUNT(*) OVER (PARTITION BY department) AS dept_size,
  AVG(salary) OVER (PARTITION BY department) AS avg_dept_salary,
  salary - AVG(salary) OVER (PARTITION BY department) AS salary_diff
FROM employees;

-- Efficient pagination (cursor-based)
-- Bad: OFFSET 10000 scans 10000 rows
SELECT * FROM posts ORDER BY created_at DESC OFFSET 10000 LIMIT 20;

-- Good: cursor pagination
SELECT * FROM posts
WHERE created_at < '2026-05-01T12:00:00'
ORDER BY created_at DESC
LIMIT 20;

Key Configuration Parameters

# postgresql.conf tuning

# Memory
shared_buffers = 25% of RAM           # e.g., 4GB for 16GB server
effective_cache_size = 75% of RAM     # for query planner estimates
work_mem = 64MB                       # per sort/hash operation
maintenance_work_mem = 512MB          # for VACUUM, CREATE INDEX

# Checkpoints
checkpoint_completion_target = 0.9
wal_buffers = 64MB

# Parallelism
max_parallel_workers_per_gather = 4   # parallel query workers
max_parallel_workers = 8

# Logging
log_min_duration_statement = 1000     # log queries > 1 second
log_checkpoints = on
log_lock_waits = on

Connection Pooling with PgBouncer

# pgbouncer.ini
[databases]
myapp = host=127.0.0.1 port=5432 dbname=myapp

[pgbouncer]
pool_mode = transaction          # Best for most web apps
max_client_conn = 1000
default_pool_size = 25           # Actual PG connections
reserve_pool_size = 5
server_idle_timeout = 600

Autovacuum Tuning

-- Check table bloat
SELECT
  tablename,
  n_dead_tup,
  n_live_tup,
  round(n_dead_tup::numeric / NULLIF(n_live_tup, 0) * 100, 2) AS bloat_pct
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

-- Per-table autovacuum settings for high-write tables
ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.01,    -- vacuum at 1% dead tuples
  autovacuum_analyze_scale_factor = 0.01,
  autovacuum_vacuum_cost_delay = 2
);

Monitoring Queries

-- Find slow queries (requires pg_stat_statements)
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- Find missing indexes
SELECT relname, seq_scan, idx_scan,
  seq_scan - idx_scan AS diff
FROM pg_stat_user_tables
WHERE seq_scan > 100
ORDER BY diff DESC;

-- Find index usage
SELECT indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE idx_scan = 0  -- unused indexes
AND NOT indisprimary
ORDER BY pg_relation_size(indexrelid) DESC;