study_area_run.sh: consolidate each cloud host when its own leg finishes, then burn it #369

Status (2026-10-24): built in PR #379; open for the provincial measurement

Built as proposed, with three changes found along the way:

• The backup is taken once, before any leg writes (operator, at the plan gate), not at the first host's copy. The dispatcher's persist is initialised first, then pg_dump runs in the background through spin and prep (7.6 min for fresh_default), and dispatch waits for it. So the dump is a true pre-run snapshot.
• "Nothing new is needed" was not quite true. Two things blocked a copy beside a writing dispatcher: each group's lnk_persist_init() issues CREATE INDEX IF NOT EXISTS / ADD COLUMN IF NOT EXISTS, and both take their table lock before finding the work done, so they waited behind the consolidate COPY (measured; the ALTER also waits behind pg_dump). They now read the catalog first. And schema_consolidate()'s empty-copy test counted whole tables, which the dispatcher's own writes move; it now counts the bucket's rows.
• A host whose bucket overlaps the dispatcher's waits for the dispatcher leg before its copy, so the two never write the same group.

Three small runs on real droplets met acceptance items 1, 2, 4 and 5 (data-raw/logs/consolidate_on_finish_369/). Still owed (item 3, and the numbers at scale): on the next provincial default run, idle cloud minutes per host, consolidate rows inside the modelling window, and the overlapping dispatcher groups' minutes against 2026-10-23. At copies of 1 and 5 groups no slowdown was visible (median 1.11x against 1.19x for groups that did not overlap). That run also fills the runs row on #377.

Problem

study_area_run.sh waits for every leg (the dispatcher's and each cloud host's) before it consolidates anything, then consolidates every host in one call, then burns them (data-raw/study_area_run.sh, the wait $LOCAL_PID / host_wait block before "completeness accounting"). So a cloud host that finished early sits idle, paid, holding the only copy of its groups, until the slowest leg ends and the whole consolidate completes.

Measured on the two provincial default runs:

Each host was holding 3.4 to 4.1 GB of persisted tables (8.2 to 10.1 GB with indexes) for that whole time, with the #347 hold rules as the only thing between a driver fault and losing them.

Proposal: consolidate each cloud host when its own leg finishes, then burn it

Per cloud host, as soon as its host_wait returns: run the completeness accounting for that host, consolidate that one source into the persist schema, apply the #347 release check (no finished group's streams rows left on the host), and burn it. The dispatcher's leg keeps modelling meanwhile; its own groups need no consolidate. The last consolidate, the recompute and the verify run as they do now.

What this buys, from the numbers above:

• Idle cloud time goes to about zero on every run, whichever leg is the long pole. On 2026-10-17 that is about 3 host-hours; on 2026-10-23, about 20.
• Wall clock drops by most of the consolidate. The 46 min of 2026-10-17 overlaps the longest leg instead of following it; only the last host's copy stays on the critical path.
• Exposure shrinks. A host holds the sole copy of its groups for minutes after it finishes, not hours. The study_area_run.sh counts finished groups from log text and burns hosts holding unconsolidated work #347 incident (18 finished groups nearly burned) and the study_area_run.sh: a run that stops before its tail leaves no record of which groups it was running #358 refused-source path both get a smaller window.

Nothing new is needed on the hosts: consolidate already streams COPY over ssh per source (#180, #185), and the persist tables replace per group (DELETE … WHERE watershed_group_code then INSERT), so copying job1's groups into <schema> while the dispatcher is writing its own groups to the same tables is the same per-group replace the design already relies on.

What has to change, and the decisions in it

• study_area_run.sh: the single post-all-legs consolidate becomes a per-host step triggered by that host's host_wait, with the dispatcher's wait $LOCAL_PID moved after them. The completeness accounting, <TS>_consolidate_state.tsv (in_flight before the copy, study_area_run.sh: a run that stops before its tail leaves no record of which groups it was running #358), CONSOLIDATING, and kept_hosts.tsv are already per source and keep working; the CONSOLIDATING flag becomes per host so the EXIT trap can still tell which host has a write in flight.
• The pre-consolidate backup. schema_consolidate(backup = TRUE) dumps the destination before the copy; today that is one dump per run (the one schema_consolidate deletes the destination's log history for every group it copies #354 restored log rows from). Per-host consolidate would take one per host unless the backup is taken once, at the first consolidate. Proposed at filing: once, before the first host's copy. Decided: once, before any leg writes (see Status).
• Load on the dispatcher's database while it models. Each copy is a few GB of COPY in plus index maintenance on tables the dispatcher is writing to. Measure the modelling minutes of the groups that overlap a copy against the same groups' minutes on 2026-10-23; if it costs more than it saves, the copy can be rate-limited or held until the dispatcher's current group finishes.
• --keep-cyphers keeps its meaning: consolidate on finish, do not burn.
• A host that fails its consolidate is kept, as now, and the run goes on. The difference is that the failure surfaces hours earlier, while the operator is still watching.

Why not stage through S3 (#170)

#170 proposed routing dumps through S3 so a host could burn the moment its upload completed. Costed at today's prices it does not pay: about 4 GB of compressed dumps per run is about $0.40 in egress, against $0.75 per idle hour for three hosts, so it breaks even at about 30 minutes of idle, and with consolidate-on-finish the idle is minutes, not hours. It also adds a dump, an upload, a verified download and a restore to a path that today is one streamed COPY, and a second "data is safe" test before burn. The one case it still covers is a dispatcher that cannot accept the copy when a host finishes (database down, disk full); that has not happened, and the #347 hold already keeps the host in that case. #170's premises (a separate consolidate host, serialized scp of dump files, a slow tailnet hop) no longer describe the driver.

Acceptance

• Per-host consolidate and burn on a real multi-host run: each cloud host is burned within minutes of its leg finishing; <TS>_consolidate_state.tsv shows one ok row per host with timestamps inside the dispatcher's modelling window; kept_hosts.tsv empty.
• study_area_verify.sql and verify_extra.sql pass at the run's expected_n with one config_hash and every group's newest log row from the run, so overlapping copies lost nothing.
• The modelling minutes of dispatcher groups that overlapped a copy are reported against the same groups' 2026-10-23 minutes.
• RUNBOOK §8d ("Which host may be burned") and the header of study_area_run.sh say that a host is consolidated and burned when its own leg finishes, and why the backup is taken once.
• A kept host still appears in kept_hosts.tsv with the burn command when its consolidate fails (negative test: refuse one source's consolidate and check the row).

Related

• study_area_run.sh counts finished groups from log text and burns hosts holding unconsolidated work #347 (which host may be burned), study_area_run.sh: a run that stops before its tail leaves no record of which groups it was running #358 (a refused source costs only its groups), S3-based consolidate: route pg_dumps through s3://newgraph/ instead of serial scp #170 (S3 transit, the option costed above), Provincial run: can a drainage component be split across hosts now that the recompute settles access? #361 (the run that measured the idle hours).