Monitoring & Slow-Query Hunting

Monitoring answers one operational question: "What is the database doing right now, and what changed?" Slow-query hunting is the narrower skill of finding the SQL that is wasting time, then proving whether the fix changed the plan.

This lesson is honest about the lab. Your browser database can run EXPLAIN (COSTS OFF) and small catalog queries, but production tools such as the slow query log, pg_stat_statements, replication lag, and multi-session lock waits are server-side signals, so those appear as static transcript-shaped examples.

Start With The Slow Query Log

The slow query log is PostgreSQL's server log entry for statements that run longer than a configured threshold. In production, you usually set log_min_duration_statement to a value your team can tolerate, then sample enough statements to see what is actually hurting users.

Configuration is outside SQL, so this is a transcript shape:

# example only: exact config location and reload command depend on deployment
psql --dbname="$DATABASE_URL" \
  --command="ALTER SYSTEM SET log_min_duration_statement = '500ms';"
 
psql --dbname="$DATABASE_URL" \
  --command="SELECT pg_reload_conf();"

A slow-log excerpt looks like this:

LOG:  duration: 1482.316 ms  execute <unnamed>:
      SELECT ticket_id, customer_email, created_at
      FROM support_tickets
      WHERE lower(customer_email) = $1
      ORDER BY created_at DESC
      LIMIT 20;
DETAIL:  parameters: $1 = 'ada@example.com'

Do not worship the exact duration in a single line. Treat it as a pointer: this statement was slow in this environment, with this data, at this moment. The next step is to reproduce the query shape, inspect the plan, and look for the boring causes: missing index, non-sargable filter, too many rows, bad join order, repeated queries from an app loop, or a lock wait masquerading as query time.

The log tells you where to start, not what to change. A good investigation keeps the original statement, representative parameters, table size, and before/after plan together so the fix is reviewable.

pg_stat_statements: The Workload View

The slow log catches individual events. pg_stat_statements aggregates similar statements across the server: calls, total time, mean time, rows, and shared buffer activity. It is not available in this browser runtime, so do not put it in live fences here. In real PostgreSQL, a workload triage query often looks like this:

postgres=# SELECT calls,
postgres-#        total_exec_time,
postgres-#        mean_exec_time,
postgres-#        rows,
postgres-#        query
postgres-# FROM pg_stat_statements
postgres-# ORDER BY total_exec_time DESC
postgres-# LIMIT 5;
 calls | total_exec_time | mean_exec_time |  rows  | query
-------+-----------------+----------------+--------+--------------------------------------------
 18420 |        92731.44 |           5.03 | 368400 | SELECT ... FROM support_tickets WHERE ...
   612 |        38109.77 |          62.27 |    612 | SELECT ... FROM invoices WHERE ...
    48 |        11980.20 |         249.59 |     48 | SELECT ... FROM audit_events WHERE ...

The highest mean time is not always the first fire. A query that is merely medium-slow but called thousands of times can consume more database time than one dramatic report. That is why calls, total time, rows, and the normalized query text belong together.

Reproduce The Shape, Then Read The Plan

Here is a small live version of the slow-log pattern. The query asks for one account's latest events. First it scans and sorts; then we add the index that matches the filter and order.

CREATE TABLE app_events (
  event_id integer PRIMARY KEY,
  account_id integer NOT NULL,
  event_type text NOT NULL,
  created_at date NOT NULL
);
 
INSERT INTO app_events (event_id, account_id, event_type, created_at)
SELECT gs AS event_id,
       gs % 10 AS account_id,
       CASE WHEN gs % 5 = 0 THEN 'checkout' ELSE 'view' END AS event_type,
       DATE '2026-01-01' + ((gs % 30) * INTERVAL '1 day') AS created_at
FROM generate_series(1, 2000) AS gs;
 
ANALYZE app_events;
 
SELECT count(*) AS event_rows
FROM app_events;
 
EXPLAIN (COSTS OFF)
SELECT e.event_id,
       e.created_at
FROM app_events e
WHERE e.account_id = 7
ORDER BY e.created_at DESC
LIMIT 5;
 
CREATE INDEX app_events_account_created_idx
ON app_events (account_id, created_at DESC);
 
ANALYZE app_events;
 
EXPLAIN (COSTS OFF)
SELECT e.event_id,
       e.created_at
FROM app_events e
WHERE e.account_id = 7
ORDER BY e.created_at DESC
LIMIT 5;

CREATE TABLE
INSERT 0 2000
ANALYZE
 event_rows
------------
       2000
(1 row)
               QUERY PLAN
----------------------------------------
 Limit
   ->  Sort
         Sort Key: created_at DESC
         ->  Seq Scan on app_events e
               Filter: (account_id = 7)
(5 rows)
CREATE INDEX
ANALYZE
                              QUERY PLAN
-----------------------------------------------------------------------
 Limit
   ->  Index Scan using app_events_account_created_idx on app_events e
         Index Cond: (account_id = 7)
(3 rows)

The fix was not "add an index somewhere." It was "make the access path match the question": equality on account_id, then rows already ordered by created_at DESC. In production you would also compare the real EXPLAIN ANALYZE before and after on a safe copy or during a controlled investigation; in course output we avoid asserting timings because they are environment-dependent.

Try the same shape below. Change the account_id and look at whether the plan still uses the index.

Connections, Locks, And Replication Lag

A database dashboard should not be a wall of every possible metric. Watch the signals that explain user-facing pain:

• Connections: current sessions, active sessions, idle-in-transaction sessions, and pool saturation. Too many connections can waste memory and scheduler time; too few pool slots can make app requests wait before SQL even starts.
• Locks: blocked sessions, blockers, and how long waits have lasted. This lesson only treats locks as an operational signal; 12.3 already taught the concurrency mechanics.
• Replication lag: how far a replica trails the primary. Lag matters when reads go to replicas, failover depends on them, or PITR/WAL pipelines from 19.2 are part of recovery.
• Transaction age: long-running transactions can hold old snapshots and interfere with cleanup.

Production lock triage is usually a catalog query plus judgment. A transcript might look like this:

postgres=# SELECT blocked.pid AS blocked_pid,
postgres-#        blocker.pid AS blocker_pid,
postgres-#        blocked.query AS blocked_query,
postgres-#        blocker.query AS blocker_query
postgres-# FROM pg_catalog.pg_stat_activity blocked
postgres-# JOIN LATERAL unnest(pg_catalog.pg_blocking_pids(blocked.pid)) AS b(pid)
postgres-#   ON true
postgres-# JOIN pg_catalog.pg_stat_activity blocker
postgres-#   ON blocker.pid = b.pid;
 blocked_pid | blocker_pid |          blocked_query           |       blocker_query
-------------+-------------+----------------------------------+---------------------------
       18241 |       18110 | ALTER TABLE support_tickets ... | UPDATE support_tickets ...

The right action is rarely "kill whatever is visible first." Identify the blocker, the business operation behind it, and whether retrying is safe.

Vacuum, Bloat, And Autovacuum

PostgreSQL uses MVCC, so old row versions can remain after updates and deletes until cleanup. Vacuum is the housekeeping process that marks dead row versions reusable. Autovacuum is PostgreSQL's background system that runs that cleanup for you. Bloat is table or index space that has grown because old versions or page layout are not being reclaimed effectively.

Your dashboard does not need to make everyone a storage engineer, but it should show warning signs: tables with high dead tuples, autovacuum falling behind, transaction IDs aging dangerously, and indexes growing much faster than the data they serve. Those signals turn "the database feels slow" into specific maintenance questions.

The Dashboard That Matters

A useful database dashboard fits on one page:

• request-facing symptoms: query latency, error rate, timeouts
• workload: top statements by total time and calls
• capacity: CPU, memory, disk space, I/O pressure
• connection health: active, waiting, idle in transaction, pool saturation
• lock health: blocked sessions and oldest wait
• replication: lag, WAL archiving health, replica replay status
• maintenance: autovacuum activity, dead tuples, transaction age, bloat suspects

Keep the loop disciplined: observe the symptom, find the statement or wait, reproduce the query shape, inspect the plan, make one change, and verify the signal moved. Monitoring is not a substitute for knowing SQL; it is how production tells you which SQL deserves attention first.

You have now operated the database from three angles: permissions limit damage, backups make recovery possible, and monitoring tells you where the system is hurting. Next, the course zooms out to dialects so you can carry these habits beyond PostgreSQL.