perf(store): move database work outside PacketStore locks #412

Context

Assessment baseline: dborup/CoreScope master 4a5ec5e231dd97ed7a4392e50e5f409f4dc5fab0 (13 October 2026). Recheck current master and competing work before implementation.

Some packet/detail and cache paths still perform SQL while holding PacketStore read locks. Slow database work can form a reader convoy and delay store writers and unrelated traffic. Our previous path-query optimizations do not cover every remaining call site.

Source for later comparison

Verified from GitHub's PR metadata and complete changed-file listing on 13 October 2026. The identifiers below are literal text, not hyperlinks or cross-repository references.

Source repository: Kpa-clawbot/CoreScope
Pull request number: 2147
PR head commit SHA: 4094d215ce2463a015a53998fdc03c136acc0f10
Upstream merged SHA (merge_commit_sha): f50312767fcb5addf636a2a4d44cc1f40628986d
Files changed in this PR:
  - cmd/server/cache_refresh.go
  - cmd/server/cache_swr_test.go
  - cmd/server/coverage_test.go
  - cmd/server/db.go
  - cmd/server/db_test.go
  - cmd/server/issue810_repro_test.go
  - cmd/server/paths_anchor_bias_test.go
  - cmd/server/paths_observations_race_test.go
  - cmd/server/resolved_index.go
  - cmd/server/resolved_path_unlocked_test.go
  - cmd/server/routes.go
  - cmd/server/store.go
  - cmd/server/store_lock_convoy_test.go
  - cmd/server/store_lock_sql_invariant_test.go
  - cmd/server/store_memory_diagnostics_test.go

These are historical upstream comparison sources, not proof of equivalence with our fork and not a request to cherry-pick blindly. Recheck our current implementation and preserve fork-specific behavior.

Proposed work

• Inventory SQL and potentially blocking cache refreshes reached under PacketStore.mu, including packet-by-ID/hash and cold/expired cache paths.
• Copy the minimal stable state under lock, release it, then perform SQL or bounded refresh work; define generation/invalidation handling for concurrent changes.
• Preserve our LRU expiry, relay indexes, filtering, canonical-path semantics and post-startup refresh behavior.
• Add deterministic tests that hold a database operation and prove unrelated store writers are not blocked by the store lock; add race coverage.
• Benchmark before/after on representative store sizes and distinguish upstream measurements from our own results.

Acceptance criteria

No identified database operation remains inside the protected store sections, and a delayed query does not cause an unrelated store writer to wait behind it. Cold-cache behavior and stale-response contracts are documented.

Existing work / dependencies

Related but distinct from #221 (regional node-query pool pressure) and the existing path-query cache work.

Scope

This issue records evaluation/follow-up work in our fork. It does not authorize deployment, production/staging changes, mail delivery, automatic key promotion or other external actions. Preserve fork-specific correctness, bounded work and the read-only measurement-server database contract; any separate account database exception requires explicit review.

Source performance numbers are not measurements of our deployment. Any claimed improvement requires our own benchmark and an independently reviewed implementation.