PostgreSQL Performance Tuning Guide

Configuration File

# Find the config file
sudo -u postgres psql -c "SHOW config_file;"
# Usually: /etc/postgresql/16/main/postgresql.conf

Memory Settings

shared_buffers

Primary cache for frequently accessed data. Set to 25% of total RAM:

# 4 GB RAM server
shared_buffers = 1GB

# 8 GB RAM server
shared_buffers = 2GB

effective_cache_size

Tells the query planner how much memory is available for caching. Set to 50-75% of total RAM:

effective_cache_size = 3GB

work_mem

Memory per sort/hash operation. Be careful — this is per-operation, not per-query:

# Conservative default (a query with 5 sorts uses 5x this)
work_mem = 32MB

maintenance_work_mem

Memory for maintenance operations (VACUUM, CREATE INDEX):

maintenance_work_mem = 512MB

WAL Settings

# Use larger WAL segments for write-heavy workloads
wal_buffers = 64MB
checkpoint_completion_target = 0.9
max_wal_size = 2GB

Query Optimization

EXPLAIN ANALYZE

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.name, COUNT(o.id)
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.created_at > '2026-01-01'
GROUP BY u.id;

Key things to look for:

Creating Effective Indexes

-- Single column
CREATE INDEX idx_users_email ON users (email);

-- Composite (column order matters!)
CREATE INDEX idx_orders_user_date ON orders (user_id, created_at DESC);

-- Partial index (only index what you query)
CREATE INDEX idx_orders_pending ON orders (created_at)
WHERE status = 'pending';

-- Expression index
CREATE INDEX idx_users_lower_email ON users (lower(email));

Connection Pooling

PostgreSQL creates a process per connection. Use PgBouncer for connection pooling:

# /etc/pgbouncer/pgbouncer.ini
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
listen_port = 6432
pool_mode = transaction
max_client_conn = 200
default_pool_size = 20

Tip Always run EXPLAIN ANALYZE on slow queries before adding indexes. Sometimes the issue is a bad query plan, not a missing index.

Monitoring

-- Active queries
SELECT pid, now() - pg_stat_activity.query_start AS duration, query
FROM pg_stat_activity
WHERE state = 'active' AND query NOT LIKE '%pg_stat%'
ORDER BY duration DESC;

-- Table sizes
SELECT relname, pg_size_pretty(pg_total_relation_size(relid))
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC
LIMIT 10;

-- Index usage
SELECT relname, idx_scan, seq_scan, idx_scan::float / NULLIF(idx_scan + seq_scan, 0) * 100 AS idx_pct
FROM pg_stat_user_tables
ORDER BY seq_scan DESC;