Show progress and lock waits for long-running statements during build

[damusix]

Problem

A noorm run build file with a long-running statement looks frozen in the UI. Examples: CREATE INDEX on a large table (HNSW, GIN, btree), VACUUM FULL, a large COPY. The file shows as running with no phase, no percentage and no elapsed time, so there's no way to tell whether it's working or stuck.

Two cases look the same today:

1. Slow but progressing. For example, an HNSW index build over several hundred thousand rows with the default maintenance_work_mem, which takes tens of minutes.
2. Blocked. A second build of the same file queues behind the first one's lock (wait_event = transactionid) and waits silently.

The only way to tell them apart is to open another client and query pg_stat_activity / pg_stat_progress_create_index by hand.

Proposal: a progress watcher per file

Postgres already reports progress for these operations. noorm could poll it on a side connection while a file executes:

file start -> record the executing backend's pid (pg_backend_pid())
poll every ~2s on a second connection:
    pg_stat_activity          -> elapsed, state, wait_event
    pg_blocking_pids(pid)     -> blockers (their pid, query, age)
    leader_pid = pid          -> parallel workers
    pg_stat_progress_*        -> phase + done/total
emit file:progress events -> logger + UI line under the running file

Example UI line:

02_tables/06_items.sql   17m05s   CREATE INDEX on items
    building index: loading tuples   4210/9800 blocks (43%)   2 parallel workers
02_tables/06_items.sql    1m40s   waiting on pid 4411 (CREATE INDEX, 17m) — lock: transactionid

Progress views to cover:

Operation	View	Percent from
CREATE INDEX / REINDEX	pg_stat_progress_create_index	blocks_done/blocks_total, or tuples_done/tuples_total when set (HNSW reports tuples_total = 0)
VACUUM	pg_stat_progress_vacuum	heap_blks_scanned/heap_blks_total
CLUSTER / VACUUM FULL	pg_stat_progress_cluster	heap_blks_scanned/heap_blks_total
COPY	pg_stat_progress_copy	bytes_processed/bytes_total
ANALYZE	pg_stat_progress_analyze	sample_blks_scanned/sample_blks_total
anything else	pg_stat_activity	elapsed time + wait_event only

Design notes

• Show lock waits, not just percentages. A blocked file is the more confusing case. pg_blocking_pids() turns it into "waiting on pid X (query, age)". The same line also shows application writers queued behind a build's SHARE lock.
• The statement text isn't available. A file runs as one sql.raw, so pg_stat_activity.query holds the whole file text, usually its first statement, not the one executing. The progress view's command and relid say what's actually running. Splitting files into separate statements isn't needed.
• Parallel workers show up in pg_stat_activity with leader_pid = the file's pid. Show their count, and fold their waits into the file's status.
• Cost: one extra connection per running build, one small query every ~2s. The watcher should be off for --json and headless runs unless enabled, or emit events only.

Optional: warn before running

Before a file containing CREATE INDEX runs against a non-test config, check the target table's size (pg_class.reltuples, pg_relation_size). For example:

items: ~1M rows, 12 GB. Building an index blocks writes to it for the duration. maintenance_work_mem=64MB.

That warning, shown before the build starts, would catch the slow case.

Other dialects

The same watcher applies:

• MSSQL: sys.dm_exec_requests.percent_complete + blocking_session_id
• MySQL: performance_schema.events_stages_current (WORK_COMPLETED / WORK_ESTIMATED)