PostgreSQL Performance Tuning in Production

Start with EXPLAIN ANALYZE

Before tuning anything, you need to understand where time is being spent. EXPLAIN ANALYZE is your most important tool:

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.id
ORDER BY order_count DESC
LIMIT 10;

Look for sequential scans on large tables, high buffer reads, and nested loops with many iterations. These are usually your performance bottlenecks.

Index Strategy

Indexes are the most impactful optimization. But more isn't always better - each index slows down writes and uses disk space.

• *Index columns used in WHERE clauses
• *Index foreign keys (JOIN performance)
• *Consider partial indexes for filtered queries
• *Use INCLUDE for covering indexes

-- Partial index for active users only
CREATE INDEX idx_users_active_email ON users(email)
WHERE status = 'active';

-- Covering index to avoid table lookups
CREATE INDEX idx_orders_user_covering ON orders(user_id)
INCLUDE (total, created_at);

Connection Pooling

PostgreSQL creates a new process for each connection. With many concurrent connections, this becomes a bottleneck. Use PgBouncer or similar for connection pooling:

[databases]
mydb = host=localhost port=5432 dbname=mydb

[pgbouncer]
listen_port = 6432
listen_addr = *
auth_type = md5
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20

Configuration Tuning

PostgreSQL's default configuration is conservative. For production workloads, these settings usually need adjustment:

• *shared_buffers - 25% of RAM (up to ~8GB)
• *work_mem - 256MB-1GB for analytics workloads
• *effective_cache_size - 75% of RAM
• *random_page_cost - 1.1-1.5 for SSDs