Database query performance is one of the most impactful — and consistently underinvested — areas of application performance. A 3-second API response that should be 100ms is almost always a database problem: missing indexes, sequential scans on large tables, N+1 query patterns, or a lock contention bottleneck. The good news is that query optimization is tractable — unlike architectural debt, most query problems can be diagnosed and fixed in days.
Finding the Slow Queries
The first step in any database optimization engagement is identifying what's actually slow. Intuition is unreliable; logs tell you.
PostgreSQL slow query log:
-- Enable slow query logging (>100ms threshold)
ALTER SYSTEM SET log_min_duration_statement = 100; -- milliseconds
ALTER SYSTEM SET log_statement = 'none';
SELECT pg_reload_conf();
-- View recent slow queries from pg_stat_statements
SELECT
query,
calls,
total_exec_time / calls AS avg_ms,
total_exec_time,
rows / calls AS avg_rows,
stddev_exec_time AS stddev_ms
FROM pg_stat_statements
WHERE calls > 100 -- Only queries called at least 100 times
ORDER BY avg_ms DESC
LIMIT 20;
pg_stat_statements aggregates query performance across all executions — it shows you average execution time, total execution time, and row counts for every query. The queries at the top of the list sorted by total_exec_time are costing you the most, even if individual executions are "only" 100ms.
MySQL/Aurora:
-- Enable slow query log
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.1; -- 100ms threshold
-- Query the Performance Schema for top slow queries
SELECT
DIGEST_TEXT as query,
COUNT_STAR as calls,
AVG_TIMER_WAIT / 1000000000 AS avg_ms,
SUM_TIMER_WAIT / 1000000000 AS total_sec
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 20;
Reading EXPLAIN ANALYZE
Once you have the slow queries, EXPLAIN ANALYZE tells you exactly where time is being spent:
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.name, COUNT(o.id) as order_count, SUM(o.total) as total_spent
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.created_at > NOW() - INTERVAL '30 days'
AND o.status = 'completed'
GROUP BY u.id, u.name
ORDER BY total_spent DESC;
Key things to look for in the output:
Seq Scan (Sequential Scan): The database is reading every row in the table. On small tables, this is fine. On tables with millions of rows, it means a missing index.
Hash Join vs. Index Scan: Hash joins are often acceptable for large table joins. If you see a nested loop join with a sequential scan inside it, that's an N+1 pattern in SQL.
Actual Rows vs. Estimated Rows: Large discrepancies between PostgreSQL's estimate and the actual row count indicate stale statistics. Run ANALYZE table_name to update statistics.
Buffers shared hit/read: High "read" count relative to "hit" count means the working set doesn't fit in memory — buffer cache hit ratio is low.
Index Strategy
Composite indexes for multi-column WHERE clauses:
-- Slow: no index for this common query pattern
SELECT * FROM orders WHERE user_id = $1 AND status = $2 AND created_at > $3;
-- Add composite index matching the query pattern
CREATE INDEX CONCURRENTLY idx_orders_user_status_date
ON orders (user_id, status, created_at DESC);
-- Column order matters: most selective + equality columns first,
-- range columns last
Partial indexes for filtered queries:
-- If 90% of queries filter on active orders only:
CREATE INDEX CONCURRENTLY idx_orders_active
ON orders (user_id, created_at DESC)
WHERE status IN ('pending', 'processing'); -- Partial index — much smaller, faster
-- Index only covers the filtered subset — perfect for common case queries
INCLUDE for covering indexes (PostgreSQL):
-- If you frequently query id + total for a user's orders:
CREATE INDEX CONCURRENTLY idx_orders_user_covering
ON orders (user_id, created_at DESC)
INCLUDE (total, status); -- Include these columns to avoid table lookup
A covering index stores the indexed columns plus the INCLUDE columns — queries that need only those columns can be satisfied from the index without touching the main table.
Rewriting Common Problem Patterns
Anti-pattern: SELECT *:
-- BAD: fetches all columns including BLOBs and large text fields
SELECT * FROM products WHERE category_id = $1;
-- GOOD: fetch only what you need
SELECT id, name, price, thumbnail_url FROM products WHERE category_id = $1;
Anti-pattern: NOT IN on subqueries:
-- SLOW: NOT IN has poor optimization path on NULLable columns
SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM orders WHERE status = 'completed');
-- FAST: Use NOT EXISTS or LEFT JOIN / IS NULL
SELECT u.*
FROM users u
LEFT JOIN orders o ON o.user_id = u.id AND o.status = 'completed'
WHERE o.id IS NULL;
Anti-pattern: Function calls on indexed columns:
-- SLOW: function on column prevents index use
SELECT * FROM orders WHERE DATE(created_at) = '2024-01-15';
-- FAST: range comparison allows index use
SELECT * FROM orders WHERE created_at >= '2024-01-15' AND created_at < '2024-01-16';
Lock Contention and Long-Running Transactions
Lock contention causes queries that look fast in isolation to queue up in production under concurrent load. Diagnosis:
-- Find currently blocking locks
SELECT
blocked_locks.pid AS blocked_pid,
blocked_activity.usename AS blocked_user,
blocking_locks.pid AS blocking_pid,
blocking_activity.query AS blocking_query,
blocked_activity.query AS blocked_query
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_locks.pid = blocked_activity.pid
JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype
AND blocking_locks.granted AND NOT blocked_locks.granted
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_locks.pid = blocking_activity.pid;
Long-running transactions from application code (transactions opened but not committed until the end of a complex operation) hold locks that block concurrent writers. Ensure transactions are as short as possible — open late, close early.
Connection Pool Configuration
Poor connection pool sizing shows up as latency under load even when individual queries are fast. With PgBouncer (transaction pooling mode):
[databases]
mydb = host=postgres_primary port=5432 dbname=mydb
[pgbouncer]
pool_mode = transaction # Most efficient for typical web apps
max_client_conn = 1000 # Max connections from application servers
default_pool_size = 25 # Connections to actual PostgreSQL
server_lifetime = 3600 # Recycle connections hourly
Transaction pooling allows many application connections to share a smaller pool of actual PostgreSQL connections — ideal for typical web workloads where transactions are short and connections are frequently idle.
Rutagon Provides Database Query Optimization
If your database is the bottleneck in your API performance or your query costs have grown with traffic, Rutagon's engineering team diagnoses and remediates. Contact us at rutagon.com/contact.
See also: API performance optimization and engineering capabilities.
FAQ
How do you know when to add an index vs. when to rewrite a query?
Both often apply. If a query is structurally sound (no N+1, minimal columns fetched, appropriate WHERE clause) but missing an index, add the index. If the query has fundamental structural problems (SELECT *, function on indexed column, bad JOIN strategy), rewrite first, then index. Adding indexes to poorly written queries sometimes helps, sometimes doesn't — rewrite the query first to ensure the index will be used correctly.
Does adding many indexes slow down writes?
Yes — indexes must be updated on every INSERT, UPDATE, and DELETE. Tables with 10+ indexes on frequently written rows have measurably slower write performance. Audit your indexes: remove indexes not used by any query (pg_stat_user_indexes shows index scan counts). For write-heavy tables, prefer fewer, carefully chosen composite indexes over many single-column indexes.
What's the difference between EXPLAIN and EXPLAIN ANALYZE?
EXPLAIN shows the query plan without executing the query — it uses PostgreSQL's statistics to estimate costs and row counts. EXPLAIN ANALYZE actually executes the query and shows actual vs. estimated row counts and timing. Always use EXPLAIN ANALYZE when debugging slow queries — the actual execution data reveals where estimates are wrong and where the real time is spent.
How do you handle schema migrations without downtime on large tables?
Use CREATE INDEX CONCURRENTLY (PostgreSQL) which builds the index without holding a full table lock. For column additions, ALTER TABLE ADD COLUMN with a default value requires a table rewrite in older PostgreSQL (avoid on large tables); PostgreSQL 11+ handles this without a full rewrite. For constraint additions, add as NOT VALID first, then VALIDATE CONSTRAINT separately — validating without locking. Use tools like pgcopycat or pt-online-schema-change for complex migrations on very large tables.
What are signs that a database needs vertical scaling vs. query optimization?
Vertical scaling (larger instance) is indicated when: CPU is consistently high (>70% sustained) under optimized queries, buffer cache hit ratio is consistently below 95% (working set doesn't fit in memory), or IO throughput is at the instance limit with optimized access patterns. Query optimization is indicated when: CPU spikes correlate with specific query types, individual query execution plans show sequential scans on large tables, or connection pool exhaustion is a primary symptom. Most databases need query optimization before they need scaling.