Why Your Server Is Slow #5: When the Database Slows Down — Indexes, Locks, Connection Pools

The series closes with the database. Database slowdowns have one trait that sets them apart from the other resources: a system that was fine yesterday gets slow with no code change. Data grows a little every day and traffic patterns drift, while query plans, locks, and connection pools respond to that drift in steps. This post walks the three steps in order — indexes, locks, connection pools. Examples use PostgreSQL; the same concepts apply to MySQL and other engines with the names changed.

Indexes — the day data growth crosses the threshold #

A lookup without an index becomes a full scan of the table. The important part is that a full scan is not a problem at first. Scanning a ten-thousand-row table takes milliseconds, and as long as the whole table fits in cache it survives hundreds of thousands of rows. Trouble starts the day the table outgrows the cache — the working-set story from part 2 applies verbatim — and the full scan turns into disk reads, multiplying the storage latencies from part 3 into every query. “It got slow as data piled up” is usually not linear growth but this kind of threshold crossing.

Once you’ve found the slow query, the verdict comes from EXPLAIN.

=# EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_email = 'a@example.com';
 Seq Scan on orders  (cost=0.00..184230.10 rows=3 width=112)
                     (actual time=2841.334..2841.336 rows=2 loops=1)
   Filter: (customer_email = 'a@example.com'::text)
   Rows Removed by Filter: 4183921

Reading 4 million rows to return 2 — a textbook missing index. Creating one fixes it, but what you run into more often in practice is an index that exists and doesn’t get used. Three patterns dominate.

• Conditions that transform the column: WHERE lower(email) = ... or WHERE created_at::date = ... wrap the column in a function, so the index on the raw column can’t serve them. Build a function-based index or rewrite the condition as a range.
• Type mismatches: comparing a string column to a number triggers an implicit cast with the same effect. Common in ORM-generated queries.
• Stale statistics: the optimizer picks plans from statistics. Right after a bulk load or delete, statistics that no longer match reality can make it choose a full scan over a perfectly good index. Refresh with ANALYZE and confirm autovacuum is actually running on that table.

On the application side, query count matters as much as query speed. A listing page firing hundreds of fast queries through an N+1 pattern is part 4’s round-trip multiplication replayed against the database. N+1 from the ORM’s point of view and practical EXPLAIN usage are covered in Django Advanced #3.

Locks — the wait chain one transaction builds #

If queries themselves are fast but certain requests freeze for seconds, look at lock waits. Row locks line up transactions that touch the same row, and the line becomes a chain: transaction A holds a row for a long time, B waits on A, C waits on a different row B holds, and the waiting propagates. That’s why the symptom surfaces as “only certain features freeze, intermittently.”

The root of the chain is almost always a long transaction. Code that calls an external API while holding a transaction open, a batch job updating millions of rows in one transaction, or someone typing BEGIN in a console and leaving for lunch — all qualify. DDL deserves caution too: ALTER TABLE needs a table lock, and if one long SELECT blocks it, every query behind the ALTER lines up. That’s the classic path by which a deploy-time migration stops a whole service.

Who is blocking whom right now is one query away.

=# SELECT pid, state, wait_event_type, now() - xact_start AS xact_age, query
   FROM pg_stat_activity WHERE state <> 'idle' ORDER BY xact_age DESC;
  pid  | state  | wait_event_type | xact_age  | query
-------+--------+-----------------+-----------+--------------------------
  8123 | idle in transaction |    | 00:41:02  | UPDATE orders SET ...
  9310 | active | Lock            | 00:03:11  | UPDATE orders SET ...

Pid 8123, idle in transaction for 41 minutes, is the root. Confirm the blocking relationships with pg_blocking_pids(), and if it’s urgent, terminate that backend to cut the chain. Prevention lives in the code: no external calls inside transactions, batches committing in small units, and idle_in_transaction_session_timeout plus lock_timeout as baseline settings.

Connection pools — the database is fine, the app is waiting #

The last step sits outside the database. When the application’s connection pool is exhausted, requests queue up waiting for a free connection — while the database-side metrics look positively relaxed. It’s the database edition of the “waiting that leaves no trace” from part 1. One slow query holding connections for too long drains the pool, and from that moment even the fast queries slow down together behind the pool wait, hiding the original cause.

The opposite mistake exists too. If the pool keeps running dry and you blindly raise the maximum, the database’s total throughput starts falling once concurrently running queries far exceed its cores, through context switching and lock contention. Capping the pool at a few multiples of the core count and queueing in front of it beats holding thousands of connections that each get slower. On PostgreSQL, an external pooler like PgBouncer is the standard way to keep database-side concurrency constant as application instances multiply.

The diagnostic signals, side by side: if the application’s “connection acquisition wait” metric spikes while the database shows few active queries and idle CPU, the problem is pool size or connection hogging (slow queries, long transactions). If the database shows active queries far beyond its cores and saturated resources, the prescription is shrinking the pool and fixing queries.

The diagnostic order #

When the symptom is “the database got slow,” the order is:

1. Identify the slow queries — rank by total time with pg_stat_statements (or the slow query log). The suspected culprit and the real one often differ.
2. EXPLAIN ANALYZE — full scan or index? Do estimated and actual row counts diverge (a statistics problem)?
3. pg_stat_activity — look for idle in transaction and Lock waits. For intermittent freezes, start here.
4. Compare pool metrics — put the app’s connection waits next to the database’s active query count, and separate pool exhaustion from database saturation.
5. Check the resources — nothing yet? Apply part 2 (cache and working set) and part 3 (fsync and storage) to the database server itself.

Summary — closing the series #

• Database slowdowns arrive in steps. They start without a code change, the moment data growth crosses a cache, statistics, or plan threshold.
• The index problem is usage, not existence. Transformed columns, implicit casts, and stale statistics disable perfectly good indexes.
• The root of a lock chain is almost always a long transaction. Start from idle in transaction in pg_stat_activity.
• Pool exhaustion looks like a quiet database with a slow application. The usual fix is to shorten connection hold times rather than simply enlarging the pool.

One conclusion runs through the whole series. When “the specs are fine, but it’s slow,” the cause is almost always waiting somewhere — and waiting doesn’t show up on average-utilization graphs. The line in front of the CPU (part 1), the working set outside the cache (part 2), synchronous writes and shallow queues (part 3), the multiplication of round trips (part 4), and locks and pools (this part) — those are the waits. Start from the symptom, find where the waiting accumulates, and the prescription usually turns out to be structural, not an upgrade.