Azpg stat diagnostics

Workload forensics on Azure Database for PostgreSQL Flexible Server

When "the database is slow" but no single query is named, the answer is in the statistics views. pg_stat_statements ranks queries by cumulative cost; pg_stat_activity shows what's running now; pg_stat_user_tables/indexes and the lock views expose bloat, missing indexes, and contention.

This skill is read-write and explicit, but the split is lopsided: almost everything here is read-only (querying stats views changes nothing and runs automatically). Only two actions are guarded writes — enabling pg_stat_statements (allow-list + CREATE EXTENSION) and resetting the statistics (pg_stat_statements_reset() / pg_stat_reset()), which throw away history. See Safety protocol.

Interpreting a single query's plan is azpg-explain-analyze; acting on findings by changing server parameters (e.g. work_mem, autovacuum) is azpg-config-tuning. Deep view-by-view guidance is in reference.md; the Azure specifics (preloaded libraries, Query Store, managed metrics) are in azure-constraints.md.

Live instance context (dynamic injection)

PGCONN is a libpq connection string. All read-only.

• Is pg_stat_statements available? !psql "$PGCONN" -tAc "SELECT extversion FROM pg_extension WHERE extname='pg_stat_statements';" 2>/dev/null || echo "(not created — see enable script)"
• Connections vs cap (saturation check): !psql "$PGCONN" -tAc "SELECT count(*)||' / '||current_setting('max_connections') FROM pg_stat_activity;" 2>/dev/null || echo "(unknown)"
• Anything blocked right now? !psql "$PGCONN" -tAc "SELECT count(*) FROM pg_stat_activity WHERE wait_event_type='Lock';" 2>/dev/null || echo "(unknown)"
• Overall cache hit ratio (want > ~0.99 on OLTP): !psql "$PGCONN" -tAc "SELECT round(sum(blks_hit)::numeric/nullif(sum(blks_hit+blks_read),0),4) FROM pg_stat_database;" 2>/dev/null || echo "(unknown)"

When to use this skill

Trigger on: "the DB is slow", "high CPU / high IOPS on the server", "which queries are worst?", "find slow queries", "what's blocking?", "too many connections", "cache hit ratio", "why is autovacuum behind?", "check for bloat / wraparound". If the outcome is "here's the one query, explain its plan" → azpg-explain-analyze; if it's "raise this parameter" → azpg-config-tuning.

Decision flow

Steps 1–3 read-only (safe/auto); step 0 and stat resets are guarded writes.

0. (Guarded, once) If pg_stat_statements isn't created, enable it with scripts/enable_pg_stat_statements.sh. It's preloaded on Flexible Server but still needs allow-listing + CREATE EXTENSION.
1. Now vs cumulative. For a live incident, look at pg_stat_activity (scripts/inspect_activity.sql); for chronic slowness, rank cumulative cost with scripts/top_queries.sql.
2. Classify using the table below and reference.md: CPU-bound query, I/O-bound query, lock contention, connection saturation, bloat/vacuum, or wraparound risk.
3. Route the fix — plan reading (azpg-explain-analyze), an index (azpg-index-selection), a parameter (azpg-config-tuning), or a query rewrite.

Symptom → where to look → route

Symptom	View / column	Likely fix / route
A few queries dominate CPU	pg_stat_statements ORDER BY total_exec_time	explain + index/rewrite → azpg-explain-analyze
High mean time, high shared_blks_read	pg_stat_statements (I/O columns)	index or more RAM → azpg-index-selection / larger tier
Query stuck, wait_event_type='Lock'	pg_stat_activity + pg_locks	find & (carefully) terminate blocker
Connections near max_connections	pg_stat_activity state counts	PgBouncer / pooling → azpg-config-tuning
Low cache hit ratio	pg_stat_database blks_hit/read	working set > RAM → larger tier / index
Table growing, slow scans	pg_stat_user_tables n_dead_tup, last_autovacuum	autovacuum tuning → azpg-config-tuning
age(datfrozenxid) high	pg_database	wraparound risk — vacuum freeze urgently
Indexes never used	pg_stat_user_indexes idx_scan=0	drop → azpg-index-selection

Killing a blocking session (guarded, deliberate)

When one session blocks others, cancel the blocker, not the victims. Prefer a gentle cancel before a hard terminate:

SELECT pg_cancel_backend(<pid>);      -- asks the query to stop (safe first choice)
SELECT pg_terminate_backend(<pid>);   -- forcibly ends the whole session (last resort)

Identify the real blocker first (see inspect_activity.sql) — terminating the wrong PID just kills a victim. This is a targeted, explicit action, never a scripted sweep.

Safety protocol

1. Reading stats is free and automatic. Every SELECT against pg_stat_*, pg_locks, pg_stat_statements changes nothing.
2. Enabling pg_stat_statements is a guarded, one-time write. It needs an azure.extensions allow-list change + CREATE EXTENSION. Do it via the dry-run-capable script and confirm with the user.
3. Never reset statistics casually. pg_stat_statements_reset() and pg_stat_reset() erase the history you're diagnosing from. Only reset to establish a fresh baseline before a controlled test, with explicit consent.
4. Terminating sessions is targeted and explicit. Cancel the identified blocker with pg_cancel_backend first; pg_terminate_backend only if needed. Never loop-kill by pattern.
5. This skill diagnoses; the write skills act. Parameter and index changes go through azpg-config-tuning / azpg-index-selection, which apply their own guarded, dry-run flow.

Bundled files

• reference.md — each pg_stat_* / lock view explained, the key pg_stat_statements columns, cache-hit and bloat formulas, wraparound check.
• azure-constraints.md — pg_stat_statements preloaded + allow-list, Query Store, pg_stat_statements.track, managed metrics vs in-database stats, no OS access.
• scripts/inspect_activity.sql — read-only; current activity, long-running queries, blocking tree, connection states.
• scripts/top_queries.sql — read-only; cumulative worst queries by total/mean time and by I/O.
• scripts/enable_pg_stat_statements.sh — guarded; allow-list + CREATE EXTENSION, --dry-run.
• examples/find_the_hog.md — "the DB is slow" → rank → identify the offending query → route to the right fix.