Database Query Logs

Slow query logs report duration, not row count or connection time — always read the units carefully (milliseconds here, sometimes microseconds).

Diagnosis workflow:
1. Run EXPLAIN ANALYZE on the exact query to see the execution plan
2. Look for Seq Scan (full table scan) on a large table — a strong signal that an index is missing
3. Check existing indexes: \d orders in psql
4. If customer_id and status are frequently filtered together, a composite index (customer_id, status) is usually more effective than two separate single-column indexes

Vocabulary: slow query log — a log of queries exceeding a configured duration threshold (log_min_duration_statement in Postgres). Not every slow query is a bug — some are legitimately expensive reports — but a slow query on a hot path (like an order lookup) needs urgent attention.

Reading an EXPLAIN ANALYZE plan:
• Seq Scan — Postgres reads every row in the table sequentially; efficient for scanning most/all of a small table, expensive on large tables with a selective filter
• cost=0.00..48291.00 — Postgres's internal cost estimate (startup..total), in arbitrary units, useful for comparing plans, not wall-clock time
• rows=1200000 — the planner's estimate of rows this step will produce (from table statistics, not the real count)
• actual time=0.021..892.442 — the real measured time in milliseconds (start..end) — this is the number that matters for "is it slow"
• rows=3 (in "actual") — only 3 rows actually matched

The tell-tale mismatch: scanning ~1.2 million rows to return 3 means the database had no efficient way to skip to the relevant rows — the fix is almost always adding an index on the columns in the WHERE clause. After adding an index, you'd expect to see Index Scan or Index Only Scan instead of Seq Scan, with dramatically lower actual time.

Lock contention vocabulary:
• Row lock — a lock held on a specific row by a transaction that has modified it, released on COMMIT or ROLLBACK
• Lock wait / blocking — a second transaction trying to modify the same row must wait until the first releases its lock
• Deadlock — two transactions each waiting on a lock the other holds; the database detects this and forcibly rolls back one of them (different from simple blocking, which resolves once the holder commits)

Root causes of long lock holds: a transaction left open by application code (e.g. forgotten COMMIT, an exception path that skips cleanup), a transaction that does slow work — like calling an external API — while still holding the lock, or a long-running batch update touching many rows.

Diagnosis: query pg_locks / pg_stat_activity (Postgres) or SHOW ENGINE INNODB STATUS (MySQL) to find which transaction is blocking, how long it has been open, and what statement it's running.

The "Aborted connection" line is a separate, secondary symptom: the client gave up waiting and its connection was dropped by the server after a timeout — a common downstream effect of a long lock wait, not the root cause itself.

Reading logs "as a sequence" — recognising a pattern, not just a single line — is a core log-reading skill. A single slow query is a curiosity; the same expensive query recurring hundreds of times per minute is a systemic problem.

Why this matters: most databases have a limited connection pool. If each of these queries holds a connection for 15 seconds, the pool fills up quickly, and unrelated (normally fast) queries start queueing behind them — this is how one bad query pattern degrades an entire service, not just the endpoint that issues it.

Communicating impact clearly: in an incident update, name the query pattern, its frequency, its duration, and the likely blast radius ("starving other queries of connections") rather than just "the database is slow" — this gives the team an actionable target (add caching, add an index, rate-limit the endpoint, or add a materialized count) instead of a vague symptom.

Common fixes for this exact pattern: cache the count with a short TTL, maintain a running counter updated incrementally instead of counting on every request, or add an index on created_at if none exists.

Connection pool vocabulary:
• active — connections currently checked out and in use by a query
• idle — connections open but not currently in use, ready to be handed to the next request
• waiting — requests that need a connection but none are available, queued
• max — the configured upper bound on total connections

active == max and idle == 0 with a large waiting count is the signature of pool exhaustion: every connection is busy, and new requests are stacking up rather than being served.

Root causes to investigate, in order:
1. Are queries taking longer than normal (check slow query log) — connections held longer means fewer available
2. Is there a connection leak — code that checks out a connection and never returns it (missing close()/release() on an error path)
3. Is max simply too low for current traffic

In an incident update: "The database connection pool is exhausted — 47 requests are queued because all 20 connections are busy, likely due to [slow queries / a leak]" is far more actionable than "the app is slow."