[object Object]

← back to Homesonspec

ops: make Phase-2 preflight actually enforce the disk gate (TK-11364)

14ed1904142a89c6a2cd16f8ed466e8689803555 · 2026-09-10 07:44:47 -0700 · Steve

Three defects found while validating the O3 runbook against live prod.

1. The disk gate only PRINTED df and relied on a human doing the math — at
   exactly the spot where Cody's hard "abort if <75GB free" lives. Combined
   with the plan doc's (false) claim that the volume grow had happened, a
   session could read past it and start a rewrite that fills the volume and
   takes prod Postgres down. Now machine-checked, loudly reported, and the
   script exits non-zero with an ABORT banner so no wrapper can proceed on a
   skimmed report. Verified against prod: FAIL at 44GB free, exit 1.

2. Section 6 ("is import traffic quiet?") ran count(*) WHERE createdAt >
   now()-'2 min'. There is no index on createdAt, so that was a full seq scan
   of the 82GB heap on the check you re-run most often while waiting for a
   window to go quiet. Replaced with a pg_stat_user_tables n_tup_ins delta:
   instant, no heap read, and measures live insert RATE. Whole preflight now
   completes in ~80s instead of blowing a 90s budget unfinished.

3. New section 8 surfaces the index inventory and flags the material fact that
   SourceEvidence has NO index on createdAt — the column Phase 2 partitions on.
   partition_data_proc drains by createdAt range, so every batch is a seq scan
   unless an index is built first. That is a build-it-up-front-vs-accept-it
   decision, not something to discover mid-window.

Section 5 also now prints an instant reltuples estimate before the slow exact
count, with the seq-scan cost called out.

Verified read-only against prod; no mutations.

Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>
Claude-Session: https://claude.ai/code/session_01JhnABoxWtpF7mbBowoBWhb

Files touched

Diff

commit 14ed1904142a89c6a2cd16f8ed466e8689803555
Author: Steve <steve@designerwallcoverings.com>
Date:   Thu Sep 10 07:44:47 2026 -0700

    ops: make Phase-2 preflight actually enforce the disk gate (TK-11364)
    
    Three defects found while validating the O3 runbook against live prod.
    
    1. The disk gate only PRINTED df and relied on a human doing the math — at
       exactly the spot where Cody's hard "abort if <75GB free" lives. Combined
       with the plan doc's (false) claim that the volume grow had happened, a
       session could read past it and start a rewrite that fills the volume and
       takes prod Postgres down. Now machine-checked, loudly reported, and the
       script exits non-zero with an ABORT banner so no wrapper can proceed on a
       skimmed report. Verified against prod: FAIL at 44GB free, exit 1.
    
    2. Section 6 ("is import traffic quiet?") ran count(*) WHERE createdAt >
       now()-'2 min'. There is no index on createdAt, so that was a full seq scan
       of the 82GB heap on the check you re-run most often while waiting for a
       window to go quiet. Replaced with a pg_stat_user_tables n_tup_ins delta:
       instant, no heap read, and measures live insert RATE. Whole preflight now
       completes in ~80s instead of blowing a 90s budget unfinished.
    
    3. New section 8 surfaces the index inventory and flags the material fact that
       SourceEvidence has NO index on createdAt — the column Phase 2 partitions on.
       partition_data_proc drains by createdAt range, so every batch is a seq scan
       unless an index is built first. That is a build-it-up-front-vs-accept-it
       decision, not something to discover mid-window.
    
    Section 5 also now prints an instant reltuples estimate before the slow exact
    count, with the seq-scan cost called out.
    
    Verified read-only against prod; no mutations.
    
    Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>
    Claude-Session: https://claude.ai/code/session_01JhnABoxWtpF7mbBowoBWhb
---
 ops/phase2-drafts/preflight.sh | 53 +++++++++++++++++++++++++++++++++++++++---
 1 file changed, 50 insertions(+), 3 deletions(-)

diff --git a/ops/phase2-drafts/preflight.sh b/ops/phase2-drafts/preflight.sh
index 9b68da4b..9dafb3e3 100644
--- a/ops/phase2-drafts/preflight.sh
+++ b/ops/phase2-drafts/preflight.sh
@@ -2,8 +2,25 @@
 # TK-10878 Phase 2 preflight — READ-ONLY. Gathers the facts that finalize the migration.
 # Run: ssh root@45.61.58.125 'bash -s' < preflight.sh
 set -uo pipefail
-echo "==================== 1. DISK (need >=75 GB free) ===================="
+MIN_FREE_GB=${MIN_FREE_GB:-75}
+echo "==================== 1. DISK (need >=${MIN_FREE_GB} GB free) ===================="
 df -h / | tail -1
+# Machine-checked, not eyeballed. Cody's standing gate: converting an ~82 GB table needs
+# ~table-sized scratch DURING partition_data_proc; starting below the floor risks filling
+# the volume mid-rewrite and taking prod Postgres down. This is the one that must not be
+# "read past" — the plan doc previously claimed a volume grow that never happened.
+FREE_GB=$(df -BG --output=avail / 2>/dev/null | tail -1 | tr -dc '0-9')
+[ -z "${FREE_GB:-}" ] && FREE_GB=$(df -k / | tail -1 | awk '{print int($4/1048576)}')
+if [ -z "${FREE_GB:-}" ]; then
+  DISK_OK=unknown
+  echo ">> DISK GATE: UNKNOWN — could not parse free space. Treat as FAIL; do not convert."
+elif [ "$FREE_GB" -ge "$MIN_FREE_GB" ]; then
+  DISK_OK=yes
+  echo ">> DISK GATE: PASS — ${FREE_GB} GB free (floor ${MIN_FREE_GB} GB)."
+else
+  DISK_OK=no
+  echo ">> DISK GATE: **FAIL** — only ${FREE_GB} GB free, floor is ${MIN_FREE_GB} GB. DO NOT RUN PHASE 2."
+fi
 echo
 echo "==================== 2. DB holding SourceEvidence ===================="
 DB=$(sudo -u postgres psql -Atqc "SELECT datname FROM pg_database WHERE datistemplate=false AND datname<>'postgres'" \
@@ -24,13 +41,43 @@ echo "   (confirm nothing FK-references SourceEvidence:)"
 sudo -u postgres psql -d "$DB" -c "SELECT conrelid::regclass AS referencing_table, conname FROM pg_constraint WHERE confrelid='public.\"SourceEvidence\"'::regclass AND contype='f'"
 echo
 echo "==================== 5. p_start_partition + total rows ===================="
-sudo -u postgres psql -d "$DB" -c "SELECT date_trunc('month',min(\"createdAt\"))::date AS p_start_partition, count(*) AS total_rows, pg_size_pretty(pg_total_relation_size('public.\"SourceEvidence\"')) AS table_size FROM public.\"SourceEvidence\""
+echo "   NOTE: there is NO index on \"createdAt\" (see section 8), so min()+count() below are"
+echo "   SEQ SCANS over the whole ~82 GB heap. Expect MINUTES, not seconds. Instant estimate first:"
+sudo -u postgres psql -d "$DB" -c "SELECT reltuples::bigint AS est_rows, pg_size_pretty(pg_total_relation_size(oid)) AS table_size FROM pg_class WHERE oid='public.\"SourceEvidence\"'::regclass"
+echo "   exact (slow, required for p_start_partition — run once, don't re-run casually):"
+sudo -u postgres psql -d "$DB" -c "SELECT date_trunc('month',min(\"createdAt\"))::date AS p_start_partition, count(*) AS total_rows FROM public.\"SourceEvidence\""
 echo
 echo "==================== 6. import-sweep activity (should be ~0 in the window) ===================="
-sudo -u postgres psql -d "$DB" -c "SELECT count(*) AS inserts_last_2min FROM public.\"SourceEvidence\" WHERE \"createdAt\" > now() - interval '2 minutes'"
+# WAS: count(*) ... WHERE "createdAt" > now()-'2 min'. With no index on "createdAt" that is a
+# full seq scan of the 82 GB heap EVERY time — minutes of I/O, on the one check you re-run most
+# often while waiting for the window to go quiet. Replaced with a catalog delta: pure stats
+# read, instant, no heap touched, and it measures live insert RATE rather than a wall-clock slice.
+INS1=$(sudo -u postgres psql -d "$DB" -Atqc "SELECT n_tup_ins FROM pg_stat_user_tables WHERE relname='SourceEvidence'")
+sleep 10
+INS2=$(sudo -u postgres psql -d "$DB" -Atqc "SELECT n_tup_ins FROM pg_stat_user_tables WHERE relname='SourceEvidence'")
+echo "   inserts over a 10s sample: $(( ${INS2:-0} - ${INS1:-0} ))   (want 0 before converting)"
+echo "   (n_tup_ins resets on a stats reset; the DELTA is what matters, not the absolute)"
 echo
 echo "==================== 7. newest backup (this IS the rollback) ===================="
 ls -lt /var/backups/*homesonspec* /root/backups/* /var/lib/postgresql/backups/* 2>/dev/null | head -3 \
   || echo "   no obvious dump found in the usual dirs — confirm where the pg_dump lands + that one is fresh since the last import"
 echo
+echo "==================== 8. indexes on SourceEvidence (partition key = createdAt) ===================="
+echo "   MATERIAL: as of 2026-09-10 the only indexes are pkey(id), (entityType,entityId), (stagedRecordId)."
+echo "   There is NO index on \"createdAt\" — the column Phase 2 partitions on. partition_data_proc"
+echo "   drains rows by createdAt range, so every batch is a seq scan unless an index exists first."
+echo "   Decide before converting: build \"createdAt\" idx CONCURRENTLY up front (costs space+time,"
+echo "   speeds the drain) vs accept seq-scan batches. Do NOT discover this mid-window."
+sudo -u postgres psql -d "$DB" -c "SELECT indexname, pg_size_pretty(pg_relation_size(quote_ident(indexname)::regclass)) AS size FROM pg_indexes WHERE tablename='SourceEvidence'"
+echo
 echo "==================== PREFLIGHT DONE — paste all of the above back ===================="
+echo
+if [ "${DISK_OK:-unknown}" != "yes" ]; then
+  echo "########################################################################"
+  echo "#  ABORT: DISK GATE ${DISK_OK:-unknown} (${FREE_GB:-?} GB free < ${MIN_FREE_GB} GB floor)."
+  echo "#  Phase 2 conversion is NOT authorized to run. Facts above are still"
+  echo "#  valid for planning; the conversion itself must wait for headroom."
+  echo "#  Exiting non-zero so no wrapper can proceed on a skimmed report."
+  echo "########################################################################"
+  exit 1
+fi

← 88e393b7 docs: correct stale/false facts in O3 partition plan (TK-113  ·  back to Homesonspec  ·  ops: fix preflight rollback check reporting a false "no back 616032a6 →