perf(channels): region-filtered channel list is slow on a cold cache (upstream 2116) — consider stale-while-revalidate

[dborup]

Low priority. This tracks upstream 2116 (a region-filtered channel list takes 62 s cold on a 10 GB database).

Status in this fork

The fork has the same region join in channelsSQL (cmd/server/db.go): transmissions → observations → observers, filtered on obs.iata. It also has the same 60 s cache with singleflight (channelsCache, Kpa-clawbot#762). The index pin from #107 helps the unfiltered list, but not the region join.

Measured on 2026-10-06 with public GET /api/channels?region=<code>. "First" is a cold cache entry, "second" is cached:

instance	region	first	second
production (≈4.4 GB DB, 5.4M observations)	BLL	2.0 s	0.23 s
production	MRW	0.5 s	0.27 s
staging (longer retention)	BLL	15.6 s	0.25 s
staging	MRW	4.7 s	0.20 s

Only the first caller after each per-region cache expiry waits, and that query holds a reader connection meanwhile. The cost grows with the database.

Options (from upstream)

1. Resolve the region part from the packet store. This conflicts with the "full history" choice for channels.
2. Bound the region join in time, for example to the store window or packetDays.
3. Stale-while-revalidate: keep serving the previous result while a refill runs, so no caller waits for the cold query. Preferred here. It is cheap, keeps full history and needs no SQL change.

Plan

Wait for and port upstream's fix if one lands. Otherwise implement option 3 in the channel list cache, with a test showing that an expired entry is served stale while exactly one refill runs. No map[string]interface{} additions; keep cmd/server read-only.