Profile: why is SQLite proptest I/O ~10x slower on windows-latest

[utof]

Summary

On windows-latest GitHub Actions runners the SQLite-backed proptests in crates/db/tests/ run roughly 8–12x slower than on ubuntu-latest and macos-latest, for identical test code and identical case counts. That gap is large enough to suspect a specific, fixable cause rather than generic "Windows is slower", and it is already costing CI reliability: it is what pushed fts_consistent_under_tag_churn past nextest's timeout budget in #186.

This issue tracks the profiling work. The immediate symptom in #186 has been mitigated by halving the Windows case count — that is a coverage trade, not an explanation.

Evidence

CI run 30666964446, one commit across the whole just ci matrix:

Test	cases	macos-latest	ubuntu-latest	windows-latest	win/ubuntu
search_proptests::fts_consistent_under_tag_churn	64	3.10s	5.28s	40.75s	7.7x
search_proptests::fts_matches_ground_truth_under_soft_delete_churn	32	1.29s	1.85s	22.21s	12.0x

The 40.75s figure was already SLOW-flagged by nextest. The very next run, 30668291291, terminated the same test at 80.3s under .config/nextest.toml's slow-timeout = { period = "40s", terminate-after = 2 }. The only source changes between the two runs were to scripts/fetch-ffmpeg-sidecar.sh, so the ~2x swing is ordinary runner variance landing on top of an already-marginal baseline.

Slowest tests on windows-latest in that run — the SQLite proptests take three of the top four slots, and nothing else is close except one image-resize benchmark:

40.748  perima-db::search_proptests      fts_consistent_under_tag_churn
37.530  perima-media                     thumbnail::tests::resize_only_bench_baseline_vs_reused_proves_amortization
22.210  perima-db::search_proptests      fts_matches_ground_truth_under_soft_delete_churn
17.714  perima-db::transcript_proptests  transcript_search_matches_ground_truth_under_soft_delete_churn
10.806  perima-db::bench/rescan          rescan/rescan_1k_files_cache_hit
 4.803  perima-db::bench/fts             fts/search/search_q_match_half_rows

The strongest clue: cost tracks cases, not SQL

Per-case cost on windows-latest:

• fts_consistent_under_tag_churn — 40.748s / 64 cases = 0.637 s/case
• fts_matches_ground_truth_under_soft_delete_churn — 22.210s / 32 cases = 0.694 s/case

Nearly identical, even though the second proptest does substantially more SQL per operation: it runs compute_ground_truth (up to 8 per-hash SELECTs plus a full search_content scan) after every op, against the first one's single attach/detach plus a fixed set of FTS MATCH queries.

On ubuntu the ratio is not flat — 5.283/64 = 0.083 s/case versus 1.848/32 = 0.058 s/case — which is roughly what the op counts predict.

Reading: on Windows the cost is dominated by per-case fixture construction, not by the SQL executed inside the case. Each proptest case builds a fresh fixture via test_db() in crates/db/tests/common/mod.rs, which does:

let td = tempfile::tempdir()?;                       // new directory
let db_path = td.path().join("test.db");
let writer = SqliteWriter::start(&db_path, bus)?;    // migrations + spawn OS thread
let reads = ReadPool::open(&db_path)?;               // r2d2 pool of read connections

plus a raw seed_conn opened per case. At 96 cases across the two tests that is ~96 tempdirs, ~96 writer threads, ~96 migration sweeps, and several hundred SQLite file handles (.db, -wal, -shm) created and torn down per run. That is the surface to profile.

Candidate hypotheses

1. Tempdir volume placement. GitHub's hosted Windows images expose more than one volume, and where TEMP points decides which disk every per-case SQLite file lands on. Worth confirming what std::env::temp_dir() actually resolves to on the runner and whether pinning the test temp root to the faster volume moves the number.
2. Microsoft Defender real-time scanning. Defender is on by default on the hosted Windows images and scans newly created files. This fixture creates thousands of fresh files per run in fresh directories — close to a worst case for on-access scanning. A scoped, CI-only exclusion for the test temp path is the standard mitigation, and measuring with it applied is a cheap way to confirm or kill the hypothesis.
3. busy_timeout / file-locking interaction. The fixture keeps a writer-actor connection, a pooled reader, and a raw seed connection open against the same WAL-mode file. Windows uses a different SQLite VFS with different locking primitives than the unix VFS, so contention that is free on Linux could serialize here. proptest harness hangs after writer-actor migration: two racing SQLite write-connections #124 tested and discarded the lock-race theory — but only on Linux. It has never been re-tested on Windows.
4. Thread and handle churn. Windows thread creation is more expensive than Linux's, and handle teardown on a scanned filesystem would compound with (2).

These are not mutually exclusive; (1) and (2) in particular would multiply.

What would close this

Either a measured explanation with numbers attached, or a change that brings windows-latest per-case fixture cost within ~3x of ubuntu.

"Hosted Windows runners are simply this slow at creating and scanning files" is a legitimate outcome — but write it down with the measurements so nobody re-derives it, and then treat the fixture shape as the lever instead. #124's post-mortem already lists candidates for that: a shared in-memory database (file::memory:?cache=shared) across the writer, pool, and seed connection, or a pure-Rust oracle for the trigger semantics with SQLite reserved for shape-level regression tests.

Explicitly out of scope

Raising slow-timeout in .config/nextest.toml. That budget exists to catch the SQLite deadlock class tracked in #122 — the config comment records the reasoning — and widening it to accommodate slow-but-healthy tests would blind CI to genuine hangs. If a Windows-specific timeout ever becomes unavoidable, nextest supports [[profile.default.overrides]] with platform = "cfg(windows)", but that should follow a measurement, not replace one.

Related

• ci(windows): fts_consistent_under_tag_churn intermittently exceeds nextest slow-timeout #186 — the failure this was split out of. Mitigated by dropping the Windows case count for fts_consistent_under_tag_churn from 64 to 32; the coverage trade is documented inline in crates/db/tests/search_proptests.rs.
• proptest harness hangs after writer-actor migration: two racing SQLite write-connections #124 — where the 64/32 case caps were chosen, plus the post-mortem establishing that per-case fixture overhead (not lock contention) is the dominant cost on Linux.
• manifest_created test races on Windows volume root C:\.perima\manifest.db #138 — the other Windows-only test problem, a race on the shared C:\.perima\manifest.db volume-root manifest. Different root cause, same "only on Windows" shape.