Exercise 2 — Performance Incident

Relax. We'll talk through this in plain words — no textbook voice.

What you'll walk away with

Explain the core ideas behind Exercise 2 — Performance Incident
Run the sample SQL or command and verify its output
Apply the technique to the Tutorial Platform and production scenarios

Build the mental model

During incidents, avoid random index or configuration changes. First capture symptoms, scope, recent changes, and safe evidence. Inspect plans, active sessions, blockers, table statistics, and resource saturation, separating immediate mitigation from root correction.

Connect it to a real scenario

For a catalog p95 regression, write a timeline covering read-only diagnostics, approval to terminate a long transaction, a missing-index test, and rollback criteria. Keep sensitive query text and parameters out of incident notes.

Do not change configuration before gathering evidence
Separate mitigation from root fix
Give every change rollback criteria

Try the working example

sql

SELECT pid, usename, state, wait_event_type, wait_event,
       now() - query_start AS running_for,
       left(query, 120) AS query_sample
FROM pg_stat_activity
WHERE datname = current_database()
ORDER BY query_start;

SELECT relname, seq_scan, idx_scan, n_live_tup, n_dead_tup
FROM pg_stat_user_tables
WHERE schemaname = 'app'
ORDER BY seq_scan DESC;

You should see

You produce an incident report with evidence, mitigation, a root-cause hypothesis, and rollback.

5-minute try-it

Define three metrics, two alert thresholds, and three safe mitigation steps for connection-pool saturation.

One important caution

Running `pg_terminate_backend` without confirming the blocker can destroy legitimate user work.

PostgreSQL — Monitoring Database Activity — PostgreSQL Global Development Group

Easy traps

Running `pg_terminate_backend` without confirming the blocker can destroy legitimate user work.
Validate sample code on a local or test database with recoverable backups before applying it to production data.

Exercise

Define three metrics, two alert thresholds, and three safe mitigation steps for connection-pool saturation.

You'll know it worked when: You produce an incident report with evidence, mitigation, a root-cause hypothesis, and rollback.