Diagnose a Database That Is Up

Goal

You receive a database that shows three symptoms at the same time, work out the cause of each with numbers, and finally tie it together into a single diagnostic report. The task in this lab is diagnosis, not fixing.

Why it matters

A database usually does not die. It stays up and goes strange, and the place where the symptom shows up is almost never the place where the cause lives. The pid that a blocked session points to is itself a blocked victim, the cause of a slow query is not in that query, and the cause of a table that does not shrink is not in that table.

So this lab does not make you memorize commands. It asks only that you extract evidence and write it down, and the grader pulls those numbers again from the database that is alive right now and checks them. How you found them is up to you, and you pass when the diagnosis is correct.

Environment

This Pod runs as the postgres account. Just type psql and you connect straight to labdb.

export PATH=/usr/lib/postgresql/16/bin:$PATH
export PGHOST=127.0.0.1 PGUSER=lab PGDATABASE=labdb
psql

Put all output files under /root/inc/. Run mkdir -p /root/inc first.

You need several sessions. There is only one terminal, so start them in the background. The form below creates a session that sits idle with a transaction open — if you keep stdin open, psql waits for the next command and stays as idle in transaction.

( { printf 'begin;\nupdate orders set status = status where id = 1;\n'; sleep 3600; } \
    | PGAPPNAME=nightly-batch psql -X -q ) >/dev/null 2>&1 </dev/null &

Attach all three redirections. If even one is missing, the shell waits for that session.

Steps

1. Reproduce the incident + activity snapshot → /root/inc/01-activity.txt
2. End of the lock chain → /root/inc/02-root.txt
3. Connections eaten by waiting and lock_timeout → /root/inc/03-waiting.txt
4. Collapsed execution plan → /root/inc/04-plan.txt
5. After fixing the statistics → /root/inc/05-stats.txt
6. Dead tuples and vacuum's answer → /root/inc/06-bloat.txt
7. The session holding the horizon → /root/inc/07-horizon.txt
8. Diagnostic report → /root/inc/08-report.md

Notes

• Keep the sessions that caused the incident alive to the end. Grading checks against the live database, so if you cut them off midway, the earlier steps are not graded again. The actual remedy (whom to disconnect) goes in writing in the diagnostic report in step 8.
• For the events table, only automatic statistics updates have been turned off. In real work this state arises by itself for a few minutes right after a bulk load, before autoanalyze runs, but during the lab those few minutes could pass and you would not be able to see step 4. Autovacuum itself is on.
• The aggregation query in step 4 takes a few seconds. Slow is normal, and that time is the evidence.

Reproduce the incident and capture it in one shot

Reproduce the incident + activity snapshot → /root/inc/01-activity.txt

First, create the incident. Load 60,000 rows for tenant 41 into the events table without running ANALYZE, then start one session that modifies orders and does not commit, and two sessions that touch the same row.

A session that sits idle with a transaction open is created by keeping stdin open: ( { printf 'begin;\nupdate orders set status = status where id = 1;\n'; sleep 3600; } | PGAPPNAME=nightly-batch psql -X -q ) >/dev/null 2>&1 </dev/null &

Then save the whole of pg_stat_activity to /root/inc/01-activity.txt. Be sure to include state, wait_event_type, and pg_blocking_pids.

Find the end of the chain

End of the lock chain → /root/inc/02-root.txt

The pid that a blocked session points to may itself be a blocked victim. Find a pid that is blocking others while not being blocked by anyone.

Expand with unnest(pg_blocking_pids(pid)) and keep only those where cardinality(pg_blocking_pids(b)) = 0.

In /root/inc/02-root.txt, write three lines: root_pid=, root_app=, and root_state=. The grader pulls these three values again from the database that is alive right now and checks them.

What is waiting eating up

Connections eaten by waiting and lock_timeout → /root/inc/03-waiting.txt

Count the blocked sessions (cardinality(pg_blocking_pids(pid)) > 0) and write the count together with show max_connections. Those sessions are not slow — they are stopped, each holding one connection.

Then set set lock_timeout = '1s'; and try touching the same row. Instead of waiting forever, it comes back with an error after 1 second. Leave that error line in the file as is.

File: /root/inc/03-waiting.txt

A query that did not change got slow

Collapsed execution plan → /root/inc/04-plan.txt

Run the query that joins events with customers and aggregates tenant 41 using EXPLAIN (ANALYZE, BUFFERS). It takes a few seconds — that is the point of this step.

It is more accurate to measure the estimated and actual row counts separately with parallelism off, because when loops is not 1 the displayed values are per-loop averages: after set max_parallel_workers_per_gather = 0;, run explain (analyze) select count(*) from events where tenant_id = 41

In /root/inc/04-plan.txt, write est_rows=, actual_rows=, and exec_ms= and leave the full plan alongside. Do not run ANALYZE yet in this step.

What needed fixing was not the query

After fixing the statistics → /root/inc/05-stats.txt

Do not create an index; run only analyze events. Then measure again exactly as in step 4.

In /root/inc/05-stats.txt, write est_rows_after=, exec_ms_after=, and join_after= and leave the full plan. The grader produces the plan again itself and checks whether the optimizer's estimate matches the actual, so if you do not fix the statistics, nothing you write will pass.

It got updated and the table grew

Dead tuples and vacuum's answer → /root/inc/06-bloat.txt

Change just one column, like update events set kind = 'view' where tenant_id = 41, and measure pg_total_relation_size('events') in bytes before and after the update. Then run vacuum (verbose) events and vacuum will tell you the reason itself.

In /root/inc/06-bloat.txt, write dead_tuples=, removable_cutoff=, size_before=, and size_after= and leave the vacuum output alongside. You must not make up the cutoff — the grader checks it against the transactions that are open right now.

What is blocking the cleanup

The session holding the horizon → /root/inc/07-horizon.txt

Find the session that holds the removable cutoff from step 6. If you scan only backend_xmin, you will not find it — a write session that sits idle with a transaction open has an empty xmin, and it is that session's backend_xid that actually holds the horizon.

where backend_xid is not null order by age(backend_xid) desc limit 1 gives you the answer.

In /root/inc/07-horizon.txt, write holder_pid=, holder_xid=, and holder_app=. holder_xid must be the same value as the cutoff in step 6, and compare holder_pid with the pid you found in step 2.

A one-page diagnostic report

Diagnostic report → /root/inc/08-report.md

Combine the numbers you extracted in the first seven steps and write the diagnostic report in /root/inc/08-report.md. Include sections named 증상, 원인, and 조치 (the Korean words for "symptoms", "cause", and "action"), and put in the pid at the end of the chain, the vacuum cutoff value, and the actual row count as the numbers themselves.

The key is to separate out that, of the three symptoms, two share a cause and one is separate. To prevent recurrence, be sure to mention lock_timeout. The grader checks the numbers written in the report against the database that is alive right now.