Evaluate regional node-query pool pressure and the existing cache proposal (upstream 2101)

[dborup]

Deferred evaluation

Track the relevance of upstream issue 2101: regional /api/nodes requests can occupy the SQLite connection pool with expensive history scans. This is an assessment and coordination issue, not a claim that our production server has experienced the reported outage.

Status checked 2026-10-04: upstream issue 2101 is open; its proposed PR 2102 is closed without merging. Our #176 is open and already contains an adapted implementation candidate. Do not start another competing port.

What it does / potential benefit

The proposed cache reuses regional node membership across count and page requests instead of repeatedly scanning the observation history. It could reduce query work and pool contention on large histories and repeated regional browsing. Query cancellation/deadlines and the remaining cold-cache work need evaluation separately; caching is not proof that every pool-starvation case is solved.

Existing work in our fork

• perf(nodes): port upstream region membership cache (#2102) #176 implements a bounded 32-region-set cache, normalized keys, stale-while-refresh and single-flight refresh work.
• Its stated freshness contract is up to 30 seconds for additions and up to 30 minutes for removals/IATA changes. A cold region set still performs a full membership scan.
• It preserves the legacy decoded_json.pubKey fallback while from_pubkey backfill is incomplete and fixes an upstream cache-replacement problem at the entry limit.
• Open port(upstream#1882): nodes region filter reads the indexed from_pubkey column #38 changes the same regional-query area to use indexed from_pubkey; assess the overlap before merging either.
• Decide: port upstream SQLite planner statistics (ANALYZE in the ingestor)? #100 / merged perf(db): pin GetChannels and CountFloodAdvertsForNode to their indexes (issue #100) #107 addressed other SQLite planner/index choices. Those are related performance work, not evidence this regional workload is already fixed.

Risks / questions for later

• Are the proposed stale-data windows acceptable for Nodes and Live?
• What happens with concurrent cold requests for many different region sets, cache eviction, retention and observer-region changes?
• Does cancellation release database resources promptly? Is a bounded query deadline or concurrency limit needed in addition to caching?
• Preserve result membership, pagination/count consistency, legacy data and the server's read-only database contract.
• The upstream incident and PR-author test results are not measurements of our current staging/production deployment.

Evaluation checklist

• Recheck perf(nodes): port upstream region membership cache (#2102) #176 and port(upstream#1882): nodes region filter reads the indexed from_pubkey column #38 against current master and record one integration path.
• Benchmark current versus proposed code on a realistic database copy: cold/warm latency, concurrent requests, scans and pool wait time.
• Exercise canceled requests and prove unrelated endpoints remain responsive under regional-query load.
• Test TTL/rebuild, cache-cap replacement, pruning, IATA changes and incomplete from_pubkey backfill.
• Choose cache-only, cache plus cancellation/resource limits, another approach, or no change based on evidence.

No merge, production query experiment, configuration change or deployment is authorized by this issue alone.