PostgreSQL Performance Tuning: Advanced Techniques for High-Traffic Applications

PostgreSQL powers some of the world's most demanding applications. When performance issues arise, knowing how to diagnose and resolve them is essential. This guide covers advanced optimization techniques for high-traffic scenarios.

Query Analysis with EXPLAIN ANALYZE

The first step in optimization is understanding how queries execute:

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 > '2024-01-01'
GROUP BY u.id
ORDER BY order_count DESC
LIMIT 100;

Key Metrics to Watch

• Seq Scan: Table scans—often indicate missing indexes
• Nested Loop: Can be expensive for large datasets
• Sort: Sorts in memory vs. on disk
• Buffers: How much data was read from disk vs. cache

Indexing Strategies

B-tree Indexes (Default)

-- Simple index for equality and range queries
CREATE INDEX idx_users_email ON users(email);

-- Composite index for multi-column queries
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- Partial index for filtered queries
CREATE INDEX idx_active_users ON users(email)
WHERE is_active = true;

Specialized Index Types

-- GIN index for JSONB and full-text search
CREATE INDEX idx_products_attrs ON products USING GIN(attributes);

-- GiST index for geometric data and ranges
CREATE INDEX idx_events_duration ON events USING GIST(duration);

-- BRIN index for large, naturally ordered tables
CREATE INDEX idx_logs_created ON logs USING BRIN(created_at);

Configuration Tuning

Memory Settings

# postgresql.conf
shared_buffers = 4GB           # 25% of RAM
effective_cache_size = 12GB    # 75% of RAM
work_mem = 256MB               # Per-operation memory
maintenance_work_mem = 1GB     # For VACUUM, CREATE INDEX

Connection Settings

max_connections = 200          # Depends on your needs
superuser_reserved_connections = 3

Connection Pooling with PgBouncer

Direct database connections are expensive. Use PgBouncer for connection pooling:

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

[pgbouncer]
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20
min_pool_size = 5

Vacuum and Maintenance

Regular maintenance is crucial for PostgreSQL performance:

-- Check for bloated tables
SELECT
    schemaname || '.' || relname as table,
    n_dead_tup,
    n_live_tup,
    round(n_dead_tup * 100.0 / nullif(n_live_tup, 0), 2) as dead_pct
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

-- Manual vacuum for specific tables
VACUUM (VERBOSE, ANALYZE) orders;

Autovacuum Tuning

# postgresql.conf
autovacuum_vacuum_scale_factor = 0.1
autovacuum_analyze_scale_factor = 0.05
autovacuum_vacuum_cost_limit = 1000

Monitoring Queries

-- Find slow queries
SELECT
    query,
    calls,
    total_exec_time / 1000 as total_seconds,
    mean_exec_time as avg_ms,
    rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

PostgreSQL optimization is an ongoing process. Monitor, measure, and iterate to keep your database running smoothly.