Chapter 9 — Inside the Database

The Pool, the Live Query View, Locks, Slow Queries — and the Migration That Takes the Site Down

Part 2: Instruments. Prerequisites: Chapters 1–8. Background: PostgreSQL From First Principles, especially Ch 3 (the planner), Ch 15 (locks), Ch 17 (connection pooling) and Ch 19 (the slow-query workflow).

Opening

"The API is slow" very often means "the API is waiting." And the thing it waits for most is the database. But waiting for the database is not one thing. The app may be waiting for a free connection from its own pool. The query may be waiting for a lock held by another transaction. The query may be running and simply slow. Or the database may be fine and the app is holding a transaction open while it calls something else.

Each of those has a different fix, and guessing between them wastes hours. This chapter shows you how to tell them apart — from the app's side (the pool) and from the database's side (what is running, waiting and blocking right now). The examples use PostgreSQL; the ideas apply to every relational database.

Part 1: Where "Database Time" Actually Goes

From your code's point of view, await db.query(...) is one step. Physically it is several, and any of them can be the slow one:

The single most common misdiagnosis is blaming step 4 when the real wait is step 1 or step 3. Measure before you optimise a query.

Part 2: The Connection Pool — the App's Side

Opening a database connection is expensive (TCP, TLS, authentication, a server process). So apps keep a connection pool: a fixed set of open connections that requests borrow and return. The pool is also a limit: with a pool of 10, at most 10 queries from this process run at once. The 11th waits.

The pool's symptoms

Pools usually expose counts. In node-postgres: pool.totalCount (open connections), pool.idleCount (free ones), and pool.waitingCount (requests waiting for one). A waitingCount above zero for long periods is pool exhaustion, and it is worth exporting as a metric.

Why pools get exhausted

1. Connection leaks. A connection is checked out and never returned — usually on an error path.

   javascriptCopy

   const client = await pool.connect()
   const result = await client.query(sql)   // if this throws…
   client.release()                         // …this never runs, and the connection is lost forever
   

   The fix is try { … } finally { client.release() }. Each leak permanently removes one connection; after enough errors, the pool is empty and every request times out.

2. Long transactions. A connection inside a transaction is busy until COMMIT or ROLLBACK. If the code does slow work in the middle — calling an external API, sending an email — the connection is held for the whole time. The worked example at the end is exactly this.

3. Slow queries. Each slow query holds a connection for its full duration. Fix the query (Part 5) and the pool frees up.

4. Too much concurrency. Ten thousand promises started at once (Chapter 8) all wait for ten connections.

Many instances, one database

The database has a hard limit on total connections. Every app instance has its own pool. Twenty instances with a pool of 20 each want 400 connections. Autoscaling adds instances — and connections — exactly when load is highest.

This is especially sharp in serverless environments, where every function instance may open its own connections. The usual fix is a connection pooler in front of the database (PgBouncer, or a pooled connection URL from your database provider). In transaction pooling mode, a server connection is assigned to a client only for one transaction at a time, so thousands of clients share a few dozen connections. The trade-off: anything that depends on the same session across transactions — session-level SET, some prepared-statement setups, session advisory locks — may not behave as expected.

Part 3: The Database's Side — pg_stat_activity

Postgres can tell you, right now, what every connection is doing. This one query answers most "is the database the problem?" questions:

SELECT pid,
       application_name,
       state,
       wait_event_type,
       wait_event,
       now() - xact_start  AS xact_age,
       now() - query_start AS query_age,
       left(query, 80)     AS query
FROM pg_stat_activity
WHERE datname = current_database()
  AND pid <> pg_backend_pid()
ORDER BY xact_start NULLS LAST;

Reading state

Reading wait_event_type

When a query is active but not making progress, wait_event_type says what it is waiting for:

Set application_name

Configure every service to set application_name in its connection string (?application_name=checkout-api). Then this view tells you which service owns every connection — the difference between "something is holding 40 connections" and "the checkout API is holding 40 connections."

Part 4: Locks — Who Is Blocking Whom

When a query is waiting on a Lock, find out who holds it:

SELECT waiting.pid                    AS waiting_pid,
       left(waiting.query, 60)        AS waiting_query,
       blocking.pid                   AS blocking_pid,
       blocking.state                 AS blocking_state,
       now() - blocking.xact_start    AS blocking_xact_age,
       left(blocking.query, 60)       AS blocking_query
FROM pg_stat_activity AS waiting
JOIN pg_stat_activity AS blocking
  ON blocking.pid = ANY (pg_blocking_pids(waiting.pid))
WHERE waiting.wait_event_type = 'Lock';

pg_blocking_pids(pid) returns the processes blocking a given one. The blocker is often not a heavy query at all, but a connection sitting idle in transaction that took a row lock and then went to call an API.

The lock queue — how one migration takes the whole site down

Locks are granted in order. If a request for a strong lock is waiting, every later request that conflicts with it waits behind it — even requests that would not conflict with the lock currently held.

Two lessons from that timeline: set lock_timeout on migrations, and long-running queries and transactions are a risk to everything else, even when they are only reading.

Deadlocks (40P01) are the other lock failure — two transactions each waiting for the other. They are covered step by step in PostgreSQL Ch 15.

Part 5: Slow Queries — Finding the Ones That Matter

pg_stat_statements — the query leaderboard

The pg_stat_statements extension records statistics for every distinct query shape: how many times it ran, total time, mean time, rows returned. It answers "which queries actually cost us the most?"

SELECT calls,
       round(total_exec_time)          AS total_ms,
       round(mean_exec_time::numeric, 1) AS mean_ms,
       rows,
       left(query, 80)                 AS query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Sort by total_exec_time to find what costs the most overall. Two shapes appear again and again:

• A slow query run a few times — high mean_ms. Fix with an index or a rewrite.
• A fast query run enormously often — low mean_ms, huge calls. This is the N+1 pattern (PostgreSQL Ch 11): one query per item in a loop. Fix by batching.

Logging slow queries as they happen

• log_min_duration_statement = 500 logs every statement that takes longer than 500 ms.
• The auto_explain extension logs the plan of slow statements automatically — so you see why a query was slow at the moment it was slow, not only when you rerun it later.

Explaining one query

Once you have the query, EXPLAIN (ANALYZE, BUFFERS) runs it and shows the real plan, the real row counts and the pages read. Reading plans is covered in PostgreSQL Ch 3 and the full workflow in Ch 19. The two signals to look for first: a sequential scan on a large table where you expected an index, and estimated rows far from actual rows (stale statistics or a misleading estimate).

"The same query was fast yesterday"

A query that suddenly becomes slow without any code change usually has one of these causes:

• The data crossed a threshold. The table grew, or one customer's data grew, and the planner switched to a different plan.
• Statistics are stale after a large import or delete — run ANALYZE.
• Parameter-sensitive plans. The same query is fast for most IDs and slow for one huge tenant.
• Bloat from heavy updates and deletes (PostgreSQL Ch 4).
• Something else is competing — a backup, a large report, a vacuum — check pg_stat_activity at the time.

See the SQL your ORM actually sends

ORMs can generate surprising queries: one query per relation, missing WHERE clauses, SELECT * on wide tables. Turn on query logging in the ORM during an investigation (most have an option to log every query with its duration) and read what it actually sends.

Part 6: Timeouts — the Database's Safety Rails

Every one of these turns an invisible, indefinite wait into a fast, visible error. Set them per application role or per connection.

A good rule: the app's request timeout should be longer than statement_timeout, so the database cancels the query (and frees the connection) before the app gives up on the request.

Part 7: Stopping a Runaway Query

Terminating a connection rolls back its open transaction, so any work it did is lost. Check what it is first — and remember that killing the blocker treats the symptom; the code that held the transaction open still needs fixing.

Part 8: The Database Time Bombs

Some database failures arrive with no change at all, because they were counting down the whole time:

• ID exhaustion. An integer (int4) primary key tops out at 2,147,483,647. When a sequence passes it, inserts fail with integer out of range (SQLSTATE 22003) or nextval: reached maximum value of sequence. Use bigint for IDs on any table that might grow large.
• Disk full. Writes fail; on some setups the database stops accepting connections entirely. Large indexes, bloat and unrotated logs are the usual culprits.
• Replication lag. Reads from a replica return stale data — a user saves something and does not see it. See PostgreSQL Ch 17A.
• Transaction ID wraparound. If vacuum cannot keep up for a very long time, Postgres eventually refuses writes to protect data. Rare, but catastrophic — never disable autovacuum (PostgreSQL Ch 4).

Chapter 14 returns to time bombs in general.

The Worked Example — "Checkout Is Timing Out"

Symptom. POST /checkout returns 504 for many users. Other endpoints that use the database are also slow. Database CPU is only 15%.

1. Which step? Low database CPU with everything slow suggests the database is not doing much work — the wait is somewhere else. The app's pool metrics show waitingCount at 80 and idleCount at 0. Pool exhausted (step 1 of Part 1).

2. What is holding the connections? pg_stat_activity shows 40 connections from application_name = checkout-api in state idle in transaction, with xact_age between 20 and 30 seconds.

3. What are they doing? idle in transaction means the app opened a transaction and is busy elsewhere. In the checkout code:

await db.transaction(async (tx) => {
  await tx.query('UPDATE inventory SET reserved = reserved + 1 WHERE sku = $1', [sku])
  const charge = await payments.charge(order)          // HTTP call — 25 s today
  await tx.query('INSERT INTO payments …', [charge.id])
})

The transaction stays open during the payment provider call. Today the provider is slow — 25 seconds per call — so each checkout holds a connection and a row lock on the inventory row for 25 seconds. Forty slow checkouts use every connection; other endpoints wait for the pool; some checkouts also queue on the inventory row lock.

4. Mitigate. Lower the payment call's timeout so connections are released faster, and temporarily raise the pool size if the database can take it.

5. Fix. Never hold a transaction open across a network call. Reserve inventory in one short transaction, call the payment provider outside any transaction, then record the result in a second short transaction — with an idempotency key so retries are safe (Chapter 4).

6. Prevent. Set idle_in_transaction_session_timeout so a transaction left open by mistake cannot hold locks for long, and alert on the pool's waitingCount.

The Causal Chains

The Rule You Keep Forever

"Waiting for the database" is four different waits: for a connection, for a lock, for execution, or for the app itself mid-transaction. Look at the pool and pg_stat_activity first, find which wait it is, and only then touch a query — and never hold a transaction open across a network call.

Vocabulary Map

Practice Problems

Problem 1 — Which wait? For each, name the step from Part 1 and your first check: (a) latency rises with traffic, database CPU stays at 10%; (b) one UPDATE takes 40 s, but EXPLAIN ANALYZE of the same statement takes 3 ms; (c) a report query takes 40 s even when it is the only query running.

Problem 2 — Read the view. pg_stat_activity shows 25 sessions idle in transaction from notifications-worker, each with xact_age of several minutes. What is the worker almost certainly doing, and what two changes (one in code, one in database settings) would you make?

Problem 3 — The migration. You need to add a column to a 200-million-row events table that is queried constantly, and a nightly analytics query on it runs for 20 minutes. Describe exactly how you would run the migration safely, including the settings you would use and when.

Problem 4 — The leaderboard. pg_stat_statements shows the top query by total time is SELECT * FROM users WHERE id = $1, with 48 million calls today and a mean of 0.4 ms. Is this query slow? What is actually wrong, and how would you find the code responsible?

Problem 5 — Serverless. After moving an API to serverless functions, the database starts refusing connections at peak traffic. Explain why the move caused it and what you would put in place.

What's Coming Next

Each instrument so far looks at one layer up close. In production you rarely start there — you start with an alert that says "something is wrong" and a dashboard full of lines. Chapter 10 closes Part 2: how to read metrics without being fooled by averages, what to watch (rate, errors, duration, saturation), how traces show where one request's time went across services, and the step-by-step path from "the alert fired" to "this is the root cause."