PostgreSQL Performance Tuning

EXPLAIN ANALYZE

-- Basic explain
EXPLAIN SELECT * FROM orders WHERE user_id = 123;

-- With actual execution stats
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.total, u.email
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 'pending'
  AND o.created_at > NOW() - INTERVAL '30 days';

Key terms to look for:

• Seq Scan = full table scan (often bad on large tables)
• Index Scan = uses an index
• Nested Loop = good for small datasets, can be slow for large
• Hash Join = better for larger dataset joins
• actual rows vs estimated rows = large difference means stale stats

Index Types

-- B-tree (default) - good for equality and range queries
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_status_created ON orders(status, created_at DESC);

-- Partial index - only index rows matching condition
CREATE INDEX idx_pending_orders ON orders(created_at)
WHERE status = 'pending';

-- Expression index
CREATE INDEX idx_lower_email ON users(LOWER(email));
-- Query must use same expression:
SELECT * FROM users WHERE LOWER(email) = 'alice@example.com';

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

SELECT * FROM articles
WHERE to_tsvector('english', title || ' ' || body) @@ plainto_tsquery('database performance');

-- GIN index for JSONB
CREATE INDEX idx_metadata ON products USING gin(metadata);
SELECT * FROM products WHERE metadata @> '{"color": "red"}';

Common Slow Query Patterns

-- Bad: Function on indexed column prevents index use
SELECT * FROM users WHERE YEAR(created_at) = 2024;
-- Good: Range query uses index
SELECT * FROM users WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';

-- Bad: Leading wildcard prevents index use
SELECT * FROM products WHERE name LIKE '%phone%';
-- Good: Use full-text search for contains queries
SELECT * FROM products WHERE name_search @@ plainto_tsquery('phone');

-- Bad: SELECT * fetches unnecessary columns
SELECT * FROM orders WHERE user_id = 123;
-- Good: Only fetch needed columns
SELECT id, total, status, created_at FROM orders WHERE user_id = 123;

-- Bad: N+1 queries
SELECT * FROM orders;  -- then for each order:
SELECT * FROM users WHERE id = $order.user_id;

-- Good: Single JOIN
SELECT o.*, u.email, u.name
FROM orders o
JOIN users u ON u.id = o.user_id;

Connection Pooling with PgBouncer

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

[pgbouncer]
pool_mode = transaction   ; most efficient
max_client_conn = 1000
default_pool_size = 20
min_pool_size = 5
server_idle_timeout = 600

Configuration Tuning

-- Check current settings
SHOW shared_buffers;
SHOW work_mem;
SHOW max_connections;

-- postgresql.conf tuning for 16GB RAM server:
-- shared_buffers = 4GB          (25% of RAM)
-- effective_cache_size = 12GB   (75% of RAM)
-- work_mem = 64MB               (for complex sorts)
-- maintenance_work_mem = 1GB    (for VACUUM, CREATE INDEX)
-- max_connections = 200         (use PgBouncer for more)
-- wal_buffers = 64MB
-- checkpoint_completion_target = 0.9
-- random_page_cost = 1.1        (for SSDs)

Autovacuum and Statistics

-- Check table bloat
SELECT schemaname, tablename, n_dead_tup, n_live_tup,
       round(n_dead_tup::numeric/NULLIF(n_live_tup,0)*100, 2) AS bloat_pct
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

-- Manually vacuum analyze
VACUUM ANALYZE orders;

-- Update statistics
ANALYZE orders;

-- Check index usage
SELECT indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE relname = 'orders'
ORDER BY idx_scan DESC;

-- Find unused indexes
SELECT schemaname, tablename, indexname
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelname NOT LIKE 'pg_%';

Always benchmark before and after optimizations in a production-like environment.