Repository navigation
Decide: port upstream SQLite planner statistics (ANALYZE in the ingestor)? #100
Description
Decision needed
Should we port upstream's SQLite planner-statistics work (ANALYZE in the ingestor) to this fork? This issue tracks the decision only; nothing is implemented yet.
Upstream
Kpa-clawbot/CoreScope#2072(merged 2026-09-24): the ingestor refreshes SQLite planner statistics periodically with a boundedANALYZE. Its first run happens 2 minutes after start.Kpa-clawbot/CoreScope#2074(open at the time of writing): builds the statistics at startup whensqlite_stat1is missing, and logs a warning explaining the pause.- Both address upstream
Kpa-clawbot/CoreScope#2058: without statistics, the planner picks a bad plan forGetChannels/GetEncryptedChannels, and a cold query takes about 10–13 s instead of under 1 s.
Why it matters here
- This fork has neither change. There is no
ANALYZEorsqlite_stat1handling on master (fa5bf4f7). - The affected queries back the Channels page. That is the same area as the channels-cache fix and the shared-channel-proposals work.
- It is unknown whether our staging or production databases already contain
sqlite_stat1.
Known cost
Upstream measured the first ANALYZE on a 9.4 GB database at 3 min 44 s cold, holding the write connection.
- Ingest stalls and buffers for about 4 minutes. No data was lost in their test.
- The statistics persist in the database file, so this happens once per database, not on every restart.
cmd/serveropens the database read-only (mode=ro) and reads the statistics fine. All writing stays incmd/ingestor/, as the read/write invariant requires.
Proposed plan (if we decide yes)
- Wait until
Kpa-clawbot/CoreScope#2074is merged or closed upstream. - Check read-only whether staging and production databases already have
sqlite_stat1. - Port
#2072and#2074together as one PR incmd/ingestor/. - Measure the channel queries before and after on a copy of the staging database, and document the one-time ingest pause.
- Test on staging with a rollback image and DB snapshot, and check MQTT and write errors afterwards.
- Plan the production rollout separately, accounting for the one-time pause.
Decision options
- Port as proposed after upstream settles.
- Port only
#2072and accept that the first refresh happens 2 minutes after start. - Skip if our databases already have statistics and the channel queries are fast enough.
Activity
dborup commentedon Sep 26, 2026
Update: another query in this fork is exposed to the same missing-statistics risk
While fixing review findings on #97 (Recent Adverts split), a related planner issue turned up.
Finding
ANALYZEstatistics (sqlite_stat1), SQLite's index choice for "a node's ADVERT rows" depends on the SQLite version and index order.payload_typeindex instead ofidx_transmissions_from_pubkey. Withpayload_type, the query visits every advert in the database instead of only that node's rows.from_pubkeycorrectly on all real databases tested. So nothing is known to be slow today.What #97 does about it
from_pubkey(thepayload_typeterm is written as+payload_type, so the planner cannot use that index).Still exposed on master
CountFloodAdvertsForNode(cmd/server/advert_stats.go, backsflood_advert_count_7don node detail) runs the same kind of query. It is not pinned and was not changed in feat(nodes): Recent Adverts split into flood / zero-hop / mixed with per-class counts #97.Relevance to this issue
ANALYZEwork would give the planner real statistics and reduce this whole class of plan flips, not just the Channels query from upstreamKpa-clawbot/CoreScope#2058.Suggested addition to the plan, whichever option is chosen:
CountFloodAdvertsForNodelike the one in feat(nodes): Recent Adverts split into flood / zero-hop / mixed with per-class counts #97, or pin its index the same way.test(ingestor): follow the backfill start into the startup sequence (…
Merge pull request #107 from dborup/codex/pin-channel-query-index
dborup commentedon Sep 28, 2026
Decision: do not port the upstream
ANALYZEplanner statistics (Kpa-clawbot/CoreScope#2072/#2074). The affected queries are pinned instead, merged in #107 (1bdf088e). #106 (theANALYZEport) is closed unmerged.Why:
ANALYZEon the 5 GB staging database: 63–67 s, reading every index in full even withanalysis_limit(it only limits sampling, not I/O), under the ingestor's write lock.Staging-iso measurement of #107 (copy of the staging DB, staging hardware, master
b3e44761vs588a52ba, 126 interleaved runs, identical results by sha256 in every case, 0 pin fallbacks):GetChannelsCountFloodAdvertsForNodealready usesidx_transmissions_from_pubkeyon staging, so the change is neutral there. It protects databases whereidx_transmissions_payload_typeis the newer index; that case was not measured on real data.