Stage 3 · Build
Performance & Caching at Scale
Database Query Tuning
Analyze PostgreSQL plans with EXPLAIN ANALYZE, covering indexes, slow query logs, and pg_stat_statements.
Why Query Tuning
Slow queries cause high latency, connection pool exhaustion, and cascading failures. Most performance problems in backend applications are database problems. Tuning queries is the highest-impact optimization you can do.
EXPLAIN ANALYZE
EXPLAIN ANALYZE
SELECT u.id, 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, u.name
ORDER BY order_count DESC
LIMIT 20;EXPLAIN shows the query plan without executing. EXPLAIN ANALYZE executes and shows actual timings. Look for sequential scans on large tables and high row estimates.
-- Bad: Seq Scan on large table
Seq Scan on users (cost=0.00..35432.00 rows=1000000)
Filter: (created_at > '2024-01-01')
Rows Removed by Filter: 800000
-- Good: Index Scan
Index Scan on idx_users_created (cost=0.43..8432.00 rows=200000)
Index Cond: (created_at > '2024-01-01')Seq Scan reads every row. Index Scan reads only matching rows. If you see Seq Scan on a filtered query, you need an index on the filtered column.
Covering Indexes
-- Standard index
CREATE INDEX idx_users_created ON users(created_at);
-- Covering index (includes additional columns)
CREATE INDEX idx_users_created_covering ON users(created_at)
INCLUDE (id, name, email);
-- Partial index for common filter
CREATE INDEX idx_orders_pending ON orders(created_at)
WHERE status = 'pending';
-- Expression index
CREATE INDEX idx_users_email_lower ON users(LOWER(email));Covering indexes include the columns needed by the query, enabling index-only scans. The database never touches the table. Partial indexes are smaller and faster for filtered queries.
Slow Query Logs
-- Log queries taking more than 200ms
ALTER SYSTEM SET log_min_duration_statement = 200;
SELECT pg_reload_conf();
-- Log all queries in development
ALTER SYSTEM SET log_statement = 'all';
SELECT pg_reload_conf();The slow query log captures queries that exceed a duration threshold. Review it regularly. Focus on the slowest queries first — they have the highest impact.
pg_stat_statements
-- Enable the extension
CREATE EXTENSION pg_stat_statements;
-- Top 10 queries by total time
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;
-- Top 10 queries by calls (high frequency)
SELECT query, calls, mean_exec_time
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 10;
-- Queries with high variance (inconsistent performance)
SELECT query, calls, mean_exec_time, stddev_exec_time
FROM pg_stat_statements
WHERE calls > 100
ORDER BY stddev_exec_time DESC
LIMIT 10;pg_stat_statements tracks all query performance. Order by total time for the biggest impact. Order by calls for high-frequency queries. High variance means inconsistent performance.
Common Fixes
| Problem | Symptom | Fix |
|---|---|---|
| Missing index | Seq Scan on filtered query | CREATE INDEX on filtered column |
| N+1 queries | Many small queries in a loop | JOIN or batch query |
| SELECT * | Transferring unnecessary data | Select only needed columns |
| No pagination | Returning millions of rows | Add LIMIT and cursor |
| Cartesian join | Exponentially growing results | Fix JOIN conditions |
pgBadger parses PostgreSQL logs and generates HTML reports with query statistics, index usage, and slow query analysis. Run it weekly to track query performance trends.
Mark this lesson complete to store local progress and unlock a cleaner resume path the next time you visit.