PostgreSQL Performance Tuning: From Slow Queries to Sub-Second Response

A poorly tuned PostgreSQL database can turn a fast application into a crawling mess. This guide covers the most impactful optimizations — from reading EXPLAIN output to configuring shared_buffers correctly.

Diagnosing Slow Queries

-- Enable slow query logging
ALTER SYSTEM SET log_min_duration_statement = '500';  -- Log queries > 500ms
SELECT pg_reload_conf();

-- EXPLAIN ANALYZE (actually runs the query)
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2024-01-01'
GROUP BY u.name
ORDER BY order_count DESC
LIMIT 20;

-- Find slow queries
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 20;

Index Strategies

-- B-tree (default, most queries)
CREATE INDEX idx_users_email ON users(email);

-- Partial index (smaller, faster)
CREATE INDEX idx_active_users ON users(email) WHERE is_active = true;

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

-- GIN for JSONB
CREATE INDEX idx_users_metadata ON users USING gin(metadata);

-- GIN for full-text search
CREATE INDEX idx_posts_search ON posts USING gin(to_tsvector('english', title || ' ' || body));

-- Concurrent (no table lock)
CREATE INDEX CONCURRENTLY idx_users_name ON users(name);

-- Check unused indexes
SELECT indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

Configuration Tuning

# postgresql.conf — for a server with 16GB RAM

# Memory
shared_buffers = 4GB              # 25% of RAM
effective_cache_size = 12GB       # 75% of RAM
work_mem = 64MB                   # RAM / max_connections / 4
maintenance_work_mem = 1GB        # For VACUUM, CREATE INDEX

# WAL
wal_buffers = 64MB
checkpoint_completion_target = 0.9
max_wal_size = 2GB

# Planner
random_page_cost = 1.1            # SSD (default 4.0 for HDD)
effective_io_concurrency = 200    # SSD

# Connections
max_connections = 200

Connection Pooling with PgBouncer

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

[pgbouncer]
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25
reserve_pool_size = 5
server_lifetime = 3600

PostgreSQL performance tuning is an iterative process. Start by finding your slowest queries, add the right indexes, tune your configuration for your hardware, and implement connection pooling. Most databases can be made 10-100x faster with these techniques.