Part 6 · 2 chapters · ~12 min

Database-Side Diagnosis

Seeing the database's view of an incident: active sessions and wait events, the slowest queries right now, lock waits, plan changes after statistics updates, replication lag, connection counts by client, and correlating database sessions with application traces through application_name and query comments.

What the database sees right now

code

-- Postgres: what is running and what it waits on
SELECT pid, application_name, state, wait_event_type, wait_event, now() - query_start AS running, left(query, 80)
FROM pg_stat_activity WHERE state <> 'idle' ORDER BY running DESC;

-- who holds connections, by client
SELECT application_name, client_addr, state, count(*) FROM pg_stat_activity GROUP BY 1,2,3 ORDER BY 4 DESC;

-- MySQL equivalents
SELECT * FROM sys.processlist WHERE command <> 'Sleep' ORDER BY time DESC;
SELECT * FROM performance_schema.data_lock_waits;

wait event (Postgres)	means
Lock: relation / tuple / transactionid	waiting on another session's lock
IO: DataFileRead	reading pages from disk: working set beyond memory or a bad plan
LWLock: WALWrite, BufferContent	internal contention: heavy writes or hot pages
Client: ClientRead	waiting for the application (idle in transaction): an app bug

Correlating with application traces

code

-- tag every connection and query so the DB view points back to code
postgres://ledger@db/app?application_name=ledger-api-7f9c     (per pod or per service)
/* trace_id=4bf92f3577b34da6 route=POST_/transfers */ SELECT ...   -- sqlcommenter adds these automatically

With sqlcommenter (OpenTelemetry-compatible) every slow query in the database logs carries the trace id and route that issued it, and every span in the trace links to the exact query. Plan changes after an ANALYZE or a version upgrade show up as the same fingerprint suddenly slower in pg_stat_statements; auto_explain captures the new plan.