PostgreSQL Performance Tuning: From Slow to Lightning Fast

Why PostgreSQL Performance Matters

Database performance directly impacts user experience. Slow queries frustrate users, increase infrastructure costs, and limit scalability. The good news: PostgreSQL offers extensive tuning options that can deliver dramatic performance improvements—often 10x or more.

This guide covers practical tuning techniques from quick wins to advanced optimizations.

Configuration Tuning

Start by adjusting key postgresql.conf settings:

Memory Settings

# Shared memory for caching
shared_buffers = 4GB  # 25% of RAM for dedicated DB servers

# Memory for complex operations
work_mem = 256MB  # Per-operation memory
maintenance_work_mem = 1GB  # For VACUUM, CREATE INDEX

# Effective cache size (OS cache estimate)
effective_cache_size = 12GB  # 75% of RAM

Write-Ahead Log (WAL)

# Optimize WAL for performance
wal_buffers = 64MB
checkpoint_completion_target = 0.9
max_wal_size = 4GB

Query Planning

random_page_cost = 1.1  # For SSD storage (default 4.0 for HDD)
effective_io_concurrency = 200  # For SSDs
default_statistics_target = 200  # More accurate plans

Index Optimization

Finding Missing Indexes

-- Find slow queries
SELECT query, calls, mean_time, total_time
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;

-- Find unused indexes (candidates for removal)
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

Index Types

• B-tree: Default, good for equality and range queries
• GIN: Full-text search, arrays, JSONB
• GiST: Geometric data, full-text
• BRIN: Large tables with natural ordering

Partial and Covering Indexes

-- Partial index: only active records
CREATE INDEX idx_orders_pending 
ON orders (created_at) 
WHERE status = 'pending';

-- Covering index: includes all needed columns
CREATE INDEX idx_users_email_name 
ON users (email) 
INCLUDE (first_name, last_name);

Query Optimization

Using EXPLAIN ANALYZE

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders 
WHERE customer_id = 123 
AND created_at > '2024-01-01';

Key metrics to watch:

• Seq Scan: Full table scans (often slow)
• Index Scan: Using indexes (usually fast)
• Nested Loop: Can be slow for large datasets
• Hash Join: Efficient for large joins

Common Query Improvements

-- Bad: Function on indexed column prevents index use
SELECT * FROM users WHERE LOWER(email) = 'test@example.com';

-- Good: Create functional index
CREATE INDEX idx_users_email_lower ON users (LOWER(email));

-- Bad: OR can prevent index use
SELECT * FROM orders WHERE status = 'pending' OR status = 'processing';

-- Good: Use IN instead
SELECT * FROM orders WHERE status IN ('pending', 'processing');

VACUUM and Maintenance

PostgreSQL's MVCC requires regular maintenance:

# Autovacuum settings
autovacuum_max_workers = 4
autovacuum_naptime = 30s
autovacuum_vacuum_scale_factor = 0.1
autovacuum_analyze_scale_factor = 0.05

Manual Maintenance

-- Full vacuum (reclaims space, locks table)
VACUUM FULL table_name;

-- Update statistics
ANALYZE table_name;

-- Reindex for better performance
REINDEX INDEX CONCURRENTLY index_name;

Connection Pooling

PostgreSQL creates a new process per connection. Use connection pooling:

• PgBouncer: Lightweight, transaction pooling
• Pgpool-II: More features, load balancing

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

[pgbouncer]
listen_port = 6432
max_client_conn = 1000
default_pool_size = 50
pool_mode = transaction

Monitoring

Essential monitoring queries:

-- Active connections
SELECT count(*) FROM pg_stat_activity WHERE state = 'active';

-- Table bloat
SELECT schemaname, tablename, 
  pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename))
FROM pg_tables WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC;

-- Cache hit ratio (should be > 99%)
SELECT 
  sum(blks_hit) * 100.0 / sum(blks_hit + blks_read) as cache_hit_ratio
FROM pg_stat_database;

Conclusion

PostgreSQL performance tuning is iterative. Start with configuration, add appropriate indexes, optimize slow queries, and monitor continuously. Small improvements compound into significant gains.

Our PostgreSQL eBooks provide comprehensive coverage of performance tuning with real-world examples and advanced techniques.