Database Optimization

Database Optimization

Diagnose and fix slow queries, optimize indexes, and tune database performance.

EXPLAIN Analysis

PostgreSQL

# Basic explain
psql $DATABASE_URL -c "EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';"

# With actual execution stats
psql $DATABASE_URL -c "EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT * FROM users WHERE email = 'test@example.com';"

# JSON format for tooling
psql $DATABASE_URL -c "EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT * FROM users WHERE status = 'active';"

MySQL

# Basic explain
mysql -u $DB_USER -p$DB_PASS $DB_NAME -e "EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';"

# Extended explain
mysql -u $DB_USER -p$DB_PASS $DB_NAME -e "EXPLAIN FORMAT=JSON SELECT * FROM users WHERE email = 'test@example.com';"

What to look for

• Seq Scan on large tables = missing index
• Nested Loop with high row counts = consider JOIN optimization
• Sort without index = add index on ORDER BY columns
• Hash Join on large datasets = check memory settings
• Rows estimate far from actual = run ANALYZE to update statistics

Index Management

PostgreSQL

# List all indexes
psql $DATABASE_URL -c "SELECT tablename, indexname, indexdef FROM pg_indexes WHERE schemaname = 'public' ORDER BY tablename;"

# Find unused indexes
psql $DATABASE_URL -c "SELECT relname, indexrelname, idx_scan FROM pg_stat_user_indexes WHERE idx_scan = 0 ORDER BY relname;"

# Find missing indexes (tables with seq scans)
psql $DATABASE_URL -c "SELECT relname, seq_scan, seq_tup_read, idx_scan FROM pg_stat_user_tables WHERE seq_scan > 1000 ORDER BY seq_scan DESC LIMIT 20;"

# Create index
psql $DATABASE_URL -c "CREATE INDEX CONCURRENTLY idx_users_email ON users(email);"

# Create composite index
psql $DATABASE_URL -c "CREATE INDEX CONCURRENTLY idx_orders_user_date ON orders(user_id, created_at DESC);"

# Index size
psql $DATABASE_URL -c "SELECT indexrelname, pg_size_pretty(pg_relation_size(indexrelid)) FROM pg_stat_user_indexes ORDER BY pg_relation_size(indexrelid) DESC LIMIT 10;"

Slow Query Detection

PostgreSQL

# Currently running queries (sorted by duration)
psql $DATABASE_URL -c "SELECT pid, now() - pg_stat_activity.query_start AS duration, query FROM pg_stat_activity WHERE state = 'active' AND query NOT ILIKE '%pg_stat%' ORDER BY duration DESC LIMIT 10;"

# Enable slow query log (in postgresql.conf or per-session)
psql $DATABASE_URL -c "SET log_min_duration_statement = 1000;"  -- log queries > 1s

# Table statistics
psql $DATABASE_URL -c "SELECT relname, n_live_tup, n_dead_tup, last_vacuum, last_analyze FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10;"

MySQL

# Show slow query log status
mysql -e "SHOW VARIABLES LIKE 'slow_query%';"

# Currently running queries
mysql -e "SHOW FULL PROCESSLIST;" | grep -v Sleep

Table Maintenance

# PostgreSQL — update statistics
psql $DATABASE_URL -c "ANALYZE users;"
psql $DATABASE_URL -c "ANALYZE;"  -- all tables

# PostgreSQL — reclaim dead rows
psql $DATABASE_URL -c "VACUUM ANALYZE users;"

# Table sizes
psql $DATABASE_URL -c "SELECT relname, pg_size_pretty(pg_total_relation_size(relid)) AS total_size FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC LIMIT 10;"

Connection Pool Check

# PostgreSQL — current connections
psql $DATABASE_URL -c "SELECT count(*), state FROM pg_stat_activity GROUP BY state;"

# Max connections
psql $DATABASE_URL -c "SHOW max_connections;"

# Connection age
psql $DATABASE_URL -c "SELECT pid, usename, application_name, now() - backend_start AS connection_age FROM pg_stat_activity ORDER BY connection_age DESC LIMIT 10;"

Notes

• Run EXPLAIN ANALYZE on staging, not production — it actually executes the query.
• CREATE INDEX CONCURRENTLY avoids table locks but takes longer.
• Dead rows (n_dead_tup) slow queries. Run VACUUM regularly or ensure autovacuum is tuned.
• Composite indexes: column order matters. Put equality columns first, range columns last.
• Don't index everything — each index slows writes. Index what you query.