Lower autovacuum_analyze_scale_factor from the postgres default of 0.1 to 0.01 on the cardano-db-sync tables midnight-node queries (block, tx, tx_out, tx_in, ma_tx_out, datum). The default 10% growth threshold means autoanalyze never fires for big append-heavy tables, leaving the planner on stale statistics and producing extreme worst-case plans (observed >400s queries on otherwise idle preview/preprod DBs) for the cnight-observation lookups.

Applied alongside the existing index creation in
create_cnight_observation_indexes, idempotent.

Refs: #1298

Overview

🗹 TODO before merging

• Ready

📌 Submission Checklist

• All commits are signed off (git commit -s) for the DCO
• Changes are backward-compatible (or flagged if breaking)
• Pull request description explains why the change is needed
• Self-reviewed the diff
• I have included a change file, or skipped for this reason:
• If the changes introduce a new feature, I have bumped the node minor version
• Update documentation (if relevant)
• Updated AGENTS.md if build commands, architecture, or workflows changed
• No new todos introduced

🧪 Testing Evidence

Mainnet sync — before vs after autovacuum tuning

Before (default autovacuum_analyze_scale_factor = 0.1 on cexplorer_mainnet, schema otherwise identical, all indexes from create_cnight_observation_indexes present):

2026-04-27 15:54:56  INFO substrate: ⚙️  Syncing  0.0 bps, target=#569462 (9 peers), best: #27
2026-04-27 15:55:01  INFO substrate: ⚙️  Syncing  0.0 bps, target=#569463 (9 peers), best: #27
2026-04-27 15:55:05  WARN sqlx::query: slow statement ... elapsed=432.22s slow_threshold=1s
2026-04-27 15:55:11  INFO substrate: ⚙️  Syncing  0.0 bps, target=#569464 (9 peers), best: #27

best stuck at #27 for many intervals; one query took 432 s. pg_stat_user_tables showed tx_out and ma_tx_out had never been autoanalyzed — last_autoanalyze NULL, analyze_count=1 from initial schema load.

After (this PR's tuning applied via ALTER TABLE + one-shot vacuumdb --analyze-only to backfill stats):

2026-04-27 16:32:27  INFO substrate: ⚙️  Syncing 38.5 bps, target=#569837 (8 peers), best: #67126
2026-04-27 16:32:32  INFO substrate: ⚙️  Syncing 48.9 bps, target=#569838 (8 peers), best: #67371
2026-04-27 16:34:22  INFO substrate: ⚙️  Syncing 43.9 bps, target=#569856 (9 peers), best: #72402

best advanced from #66933 → #72402 in 2 minutes — ~5500 blocks / 120 s ≈ 46 bps sustained vs 0.0 before. One slow statement warning across the entire run, and that one was 2.6 s rather than 400+ s.

Tracking

• Upstream issue: Very slow initial sync (0.2 BPS) #1298
• SRE side ticket (mitigation already applied to scotty): shieldedtech/shielded-sre#181

Please describe any additional testing aside from CI:

• Additional tests are provided (if possible)

🔱 Fork Strategy

• Node Runtime Update
• Node Client Update
• Other:
• N/A

Links