Why Your Query Is Slow (And It's Not the Index)

When a query is slow, the first reflex is to check the index. Add an index, check EXPLAIN, see the sequential scan change to an index scan, problem solved. Except when it isn't. The most common cause of database slowness is not missing indexes — it is the systems that sit between the index and your application: the buffer pool, the write-ahead log, lock contention, and the connection pool. These are the causes that EXPLAIN cannot show you and that most developers never look at.

Buffer pool: the memory between you and disk

A database does not read from disk on every query. It reads into a buffer pool — a region of memory that caches data pages. The buffer pool is the single most important performance structure in any database:

query arrives
  → check buffer pool for page
    ├─ hit: return data from memory (~100 ns)
    └─ miss: read page from disk (~5–10 ms)
            → insert into buffer pool
            → if pool full, evict LRU page
            → return data

The hit ratio is everything. A buffer pool hit ratio of 99.9% means 1 in 1,000 queries hits disk. At 99%, it's 1 in 100. At 95%, it's 1 in 20. The difference between 99.9% and 95% is the difference between 10 microsecond queries and 500 microsecond queries — a 50× difference that no index can fix.

Why the buffer pool misses:

• Working set exceeds buffer pool size. If your hot data is 50 GB and your buffer pool is 32 GB, 18 GB of pages must be evicted and re-read constantly. This is the most common cause of "sudden" slowdowns — the data grew past the buffer pool, and the hit ratio collapsed.

• Scan pollution. A sequential scan (reporting query, full table scan) reads every page into the buffer pool, evicting the hot pages. After the scan, the hot pages must be re-read from disk. PostgreSQL's shared_buffers and MySQL's innodb_buffer_pool_size must be tuned to withstand scan workloads.

• Cold restart. After a database restart, the buffer pool is empty. The first few minutes are slow as the pool warms up. This is why "restart the database" is a bad solution to performance problems — it trades a persistent issue for a temporary one.

-- PostgreSQL: check buffer pool hit ratio
SELECT
  sum(blks_hit) / (sum(blks_hit) + sum(blks_read)) AS hit_ratio
FROM pg_stat_database
WHERE datname = current_database();
 
-- MySQL: check InnoDB buffer pool hit ratio
SHOW STATUS LIKE 'Innodb_buffer_pool_read%';
-- hit ratio = 1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)

Write-ahead log: the write bottleneck

Every write in a database goes through the write-ahead log (WAL) before touching the data files. The WAL is a sequential append-only log that ensures durability — if the database crashes, the WAL can replay committed transactions.

write path:
1. acquire row lock
2. write to WAL (sequential, fast)
3. return to client (WAL write is durable)
4. background: apply WAL changes to data files (checkpoint)
 
WAL write: ~1–5 ms (sequential disk write)
checkpoint: background, amortized over time

The WAL is fast for sequential writes but creates contention when many transactions write concurrently:

• WAL buffer full. The WAL buffer (typically 16–64 MB) batches WAL writes. If the buffer fills before the background writer can flush, every writer blocks waiting for WAL space. This causes write stalls that appear as sudden latency spikes.

• Checkpoint pressure. When the WAL accumulates too much data, the database must checkpoint (write dirty pages to disk) to reclaim WAL space. Checkpointing competes with queries for I/O bandwidth, causing read latency to spike.

• Replication lag. In replicated databases, the WAL is shipped to replicas. If the replica cannot apply WAL entries as fast as the primary produces them, replication lag grows and read-your-writes consistency breaks.

-- PostgreSQL: check WAL write latency
SELECT
  mean_exec_time, calls
FROM pg_stat_statements
WHERE query LIKE '%WAL%';
 
-- check replication lag
SELECT now() - pg_last_xact_replay_timestamp() AS replication_lag;

Lock contention: the invisible serialization

Databases serialize access to data through locks. When two transactions need the same row, one waits. The wait is invisible in EXPLAIN output but visible in lock monitoring:

-- PostgreSQL: check lock waits
SELECT
  blocked.pid AS blocked_pid,
  blocked.query AS blocked_query,
  blocking.pid AS blocking_pid,
  blocking.query AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_locks blocked_locks ON blocked.pid = blocked_locks.pid
JOIN pg_locks blocking_locks ON
  blocked_locks.locktype = blocking_locks.locktype
  AND blocked_locks.relation = blocking_locks.relation
  AND blocked_locks.pid != blocking_locks.pid
JOIN pg_stat_activity blocking ON blocking_locks.pid = blocking.pid
WHERE NOT blocked_locks.granted;

Common lock contention patterns:

• Hot row updates. A counter table where every update touches the same row — the transactions serialize on the row lock, creating a queue. The fix: optimistic locking, partitioned counters, or in-memory aggregation with periodic flush.

• DDL locks. ALTER TABLE acquires an ACCESS EXCLUSIVE lock, blocking all reads and writes. A migration that takes 10 seconds blocks the table for 10 seconds. Online schema change tools (gh-ost, pt-online-schema-change) avoid this by creating a shadow table.

• Deadlocks. Two transactions each hold a lock the other needs. The database detects the deadlock and rolls back one transaction. Deadlocks are intermittent and hard to reproduce because they depend on precise timing.

Connection pooling: the hidden queue

Every database connection is a thread (or process) in the database. Each thread consumes memory (typically 5–10 MB of stack and per-connection state). The database has a maximum connection count, and when all connections are busy, new connections queue — waiting for a connection to become available.

application: 100 concurrent requests
database:    20 connections, each taking 10ms per query
             → 20 queries execute in parallel
             → 80 requests wait in queue
             → average wait: 40ms
             → total latency: 10ms (query) + 40ms (queue) = 50ms

The queue time is the hidden latency. The query takes 10ms. The connection wait takes 40ms. The application sees 50ms and blames the query. The real problem is connection pool exhaustion.

The paradox of more connections: increasing the connection count from 20 to 100 does not help if the database is bottlenecked on I/O or locks. More connections mean more context switches, more lock contention, and more memory pressure. The optimal connection count is usually equal to the number of CPU cores plus a small buffer for I/O-bound queries — typically 2 × cores + disk_spindles for PostgreSQL.

PgBouncer solves this by sitting between the application and database, multiplexing hundreds of application connections onto a small number of database connections:

application (100 conns) → PgBouncer (100 conns) → database (20 conns)
                          multiplexes                executes

The application thinks it has 100 connections. The database sees 20. Query execution is serialized by the database's actual capacity, not masked by connection queuing.

What this means for your code

1. Check the buffer pool hit ratio first. If it's below 99%, the database is reading from disk too often. This is the #1 cause of "sudden" slowdowns.

2. Monitor lock waits, not just query duration. A query that takes 50ms might spend 45ms waiting for a lock. pg_stat_activity shows the wait; the slow query log doesn't.

3. Size your connection pool correctly. Too few connections: requests queue. Too many connections: the database thrashes. The sweet spot is 2 × CPU cores + overhead.

4. WAL contention causes write stalls. If writes are slow, check WAL buffer size, checkpoint frequency, and replication lag — not just indexes.

5. The slow query log is not enough. It shows queries that exceed a time threshold. It does not show queries that are fast in isolation but slow under contention. Use pg_stat_statements for aggregate latency, pg_stat_activity for real-time waits.