pg tuning: SSD planner-cost defaults in docker-compose.yml

[NewGraphEnvironment]

Problem

fresh/docker/docker-compose.yml doesn't set random_page_cost or effective_io_concurrency, so postgres falls through to PostgreSQL defaults of 4.0 and 1 — both calibrated for spinning rust. All NewGraph hosts run on SSD (M4 NVMe, M1 Colima virtiofs over APFS, cypher DO block storage). With random_page_cost=4, the planner systematically biases away from index scans for segment-keyed lookups in link's pipeline (WHERE blue_line_key = … AND drm <= … against streams_breaks is the hot path).

Verified on M4 + M1 + cypher today (2026-05-04) — all three show random_page_cost=4, effective_io_concurrency=1, temp_buffers=8MB.

Proposed

Add to the db.command block in base docker-compose.yml:

-c random_page_cost=1.1
-c effective_io_concurrency=200
-c temp_buffers=64MB

Document in fresh/docker/tuning.md alongside the existing memory rationale: SSD cost values, why they matter for link's segment-heavy SQL.

Verification

Settings applied locally on M4 (2026-05-04, post-issue-filing) and the change is correctness-driven (planner cost matches actual hardware), not solely performance-driven. ADMS warm-cache wall: 126 s post-tuning vs 129.5 s pre-tuning (M4 morning provincial run) — within ~3 % noise. M4 (Apple Silicon NVMe + 32 GB shared_buffers + 2 GB work_mem) was already fast enough at random IO that the planner-cost hint doesn't materially shift query plans.

Bigger payoff is expected on cypher (DO block storage — slower random IO, larger gap between random_page_cost=4 and reality). Cypher empty post-2026-05-04 disk-full incident; can't measure until fwapg reload.

Acceptance (revised):

• M4: no regression on per-WSG wall (within ±5 % of pre-tuning baseline).
• Cypher (next provincial after fwapg reload): median per-WSG wall reduced ≥ 10 % vs pre-tuning baseline.
• Document the asymmetry in fresh/docker/tuning.md so future readers don't expect identical wins on different hardware.

Cross-ref

Companion rtj issue for the M1/cypher override file — the override's command: list REPLACES the base under docker-compose's merge semantics, so settings added here don't propagate to the 32 GB hosts. Same change must land in both.

[NewGraphEnvironment]

Reopening — new evidence M4 IS over-tuned (memory/parallelism, not just SSD)

The 2026-05-04 closure noted "M4 was already fast enough at random IO" based on a 3% wall delta. Today's provincial run (2026-05-13, link 0.36.0, model_run_id 122) provides cleaner evidence that M4 is over-tuned on shared_buffers / max_parallel_workers / work_mem — separate concern from the SSD planner costs this issue originally addressed.

Empirical: M1 (32 GB MBP) beats M4 (M4 Max) per-WSG

Median per-WSG wall on today's dispatch (217 WSGs across 5 hosts, post-LPT balanced):

Host	Median elapsed	Notes
M4 Max	101 s	docker-compose base defaults (128GB-host tuning)
M1 MBP	80 s	compose.override.m1.yml (32GB-host preset)
cypher (8vCPU/32GB DO droplet)	116-129 s	same override as M1

M1 is 1.26× M4 speed. Counterintuitive — M4 Max has 16 high-perf cores + NVMe vs M1's older silicon + virtiofs-over-APFS. Yet M1 wins on the per-WSG link pipeline.

PG settings diff that likely drives it

Setting	M4 (base)	M1 (override)	Ratio
shared_buffers	32 GB	8 GB	4×
work_mem	2 GB	1 GB	2×
effective_cache_size	96 GB	24 GB	4×
max_parallel_workers	14	4	3.5×
max_worker_processes	16	6	2.7×

Docker fresh-db on M4 currently holding 41.6 GB / 109.9 GB allocated.

Likely mechanism

1. 14 parallel workers too aggressive for per-WSG link queries. The pipeline runs many small-to-medium-cost queries; parallel coordination overhead exceeds benefit. Pairs with NewGraphEnvironment/rtj#99 (jit=off evaluation) — same "compile/parallel overhead exceeds amortization" pattern.
2. work_mem=2GB × N sorts × 14 workers triggers more aggressive in-memory plans that may swap. M1's smaller work_mem keeps the planner conservative, leaning on cached data instead.
3. Docker Desktop VM overhead scales poorly with high memory + worker counts on macOS.

Fix-up plan

Two-step, smallest first:

1. Create rtj/scripts/fwapg/compose.override.m4.yml with conservative defaults closer to M1's preset but scaled for M4's larger RAM:

   • shared_buffers=16GB
   • work_mem=1GB (or even 512MB)
   • effective_cache_size=48GB
   • max_parallel_workers=8 (= M4 Max P-core count)
   • max_worker_processes=10

2. Benchmark with the same WSG bucket (re-run today's M4 33-WSG bucket against the new override). Acceptance: per-WSG median drops ≥15% (target ~85s — below current M1 median of 80s).

3. If win confirmed: update fresh/docker/tuning.md with M4-specific preset rationale, document the "smaller is faster" finding for Docker-on-macOS workloads.

Cross-ref

• Concrete timing data: ~/Projects/repo/link/data-raw/logs/provincial_parity/20260513_1839_per_wsg_times.csv (post-run aggregated)
• Related open issue: NewGraphEnvironment/rtj#99 (jit=off — same mechanism, parallel/compile overhead)
• Run: link 0.36.0, bcfp v0.7.14-141-g05057f9 (model_run_id 122)