Historian processed reads: bound the data-range lookup cost on QuestDB; trim the SqlServer Start/End query #683

New Issue

2026-10-11T16:38:14-04:00

dohertj2 commented

2026-10-11 16:38:14 -04:00

Found by the #680 lane B review (measured). Not a correctness issue.

QuestDB. Since #680, every per-sample processed read except Average (both directions) runs EarliestSample + LatestSample to find the tag's data range. tag is an unindexed SYMBOL, so these are partition scans, not seeks: on a 52M-row / 100-tag / 60-daily-partition table a live tag answers LatestSample in ~1–3 ms, but a tag that stopped writing, read over an old window, scans backwards through every later partition (~6–8 ms warm, 32 ms cold); EarliestSample for a late-starting tag scans forward from the table start. Cost grows with table size, not window size — at fleet scale (thousands of tags, years of partitions, cold disk) a decommissioned tag's processed read could cost seconds of I/O. TTL bounds it.

Options: derive the in-window latest from the bucket rows and only probe "does any sample exist at/after the window end" (and symmetrically before the start); a cheaper per-tag first/last-write source; or a symbol index on tag.

SqlServer. The StartEnd query now also selects any-quality first/last per bucket; the inlined CTE reads s five times (was three): ~1050 ms vs ~920 ms on 6M rows / 30 days / 720 buckets (~15%). Option: compute MIN/MAX(SourceTimeUtc) in st and join back to Sample on the PK instead of the extra a window CTE.

Found by the #680 lane B review (measured). Not a correctness issue. **QuestDB.** Since #680, every per-sample processed read except Average (both directions) runs `EarliestSample` + `LatestSample` to find the tag's data range. `tag` is an unindexed `SYMBOL`, so these are partition scans, not seeks: on a 52M-row / 100-tag / 60-daily-partition table a live tag answers `LatestSample` in ~1–3 ms, but a tag that stopped writing, read over an old window, scans backwards through every later partition (~6–8 ms warm, 32 ms cold); `EarliestSample` for a late-starting tag scans forward from the table start. Cost grows with table size, not window size — at fleet scale (thousands of tags, years of partitions, cold disk) a decommissioned tag's processed read could cost seconds of I/O. TTL bounds it. Options: derive the in-window latest from the bucket rows and only probe "does any sample exist at/after the window end" (and symmetrically before the start); a cheaper per-tag first/last-write source; or a symbol index on `tag`. **SqlServer.** The `StartEnd` query now also selects any-quality first/last per bucket; the inlined CTE reads `s` five times (was three): ~1050 ms vs ~920 ms on 6M rows / 30 days / 720 buckets (~15%). Option: compute `MIN`/`MAX(SourceTimeUtc)` in `st` and join back to `Sample` on the PK instead of the extra `a` window CTE.

dohertj2 referenced this issue

2026-10-12 08:38:37 -04:00

Time-weighted aggregates: forward differences from the SDK AggregateCalculator (TimeAverage/Total status, Total at start of data, Partial on truncated) #680

dohertj2 commented

2026-10-12 13:59:25 -04:00

Fixed — merged fe644d7e + 70fb1f1f.

QuestDB: the sample table's tag SYMBOL is now INDEXed (new tables created indexed; the bootstrapper adds the index to an existing table once at boot — idempotent, concurrent-safe; a refused ALTER only warns). Measured on 53.6M rows / 120 tags / 61 partitions: data-range lookups 1–3.5 ms (were up to ~11–14 ms; stopped/late-starting tags no longer scan every partition). Trade-offs documented: +12% disk; a tag holding a large share of the table can read ~20% slower on long windows; out-of-order writes rewrite index files. Rejected with measurements: bounded probes, a per-tag side table (not maintainable INSERT-only), LATEST ON. Both backends now read the data range BEFORE the bucket query (a racing append can no longer give a Good Count of 0).

SqlServer: the #680/F1 single-pass StartEnd was ~2× slower on long windows. Replaced with one GROUP BY of per-bucket pick times + a PK join-back; live gate 9 then found a plan (under understated statistics) where the picks range-seeked the window per bucket (1d@1min 20–47 s), so every pick now carries FORCESEEK (PK_Sample (TagId, SourceTimeUtc)) — an exact-key seek under any statistics; hash/merge over the tag's history cannot be produced. Byte-identical output (EXCEPT both ways, all value kinds). Live gate 9 on a 345k-row tag vs the pre-#683 image: 1 d @ 1 min 60 s BadTimeout → 0.13 s, 1 d @ 1 h 2.6 s → 0.12 s, whole 4-day history ~40 s → ~0.5 s. Regression tests: understated-statistics compile-small/reuse-wide (fails on the previous query: 1.6M reads), forced HASH/MERGE refused (8622).

Live gate 9 G3: index added to the rig's old unindexed volume by both centrals booting together (no suspension), results identical to SqlServer; 6-min read-while-writing loop 748k reads / 0 failures.

**Fixed — merged `fe644d7e` + `70fb1f1f`.** **QuestDB:** the sample table's `tag` SYMBOL is now `INDEX`ed (new tables created indexed; the bootstrapper adds the index to an existing table once at boot — idempotent, concurrent-safe; a refused ALTER only warns). Measured on 53.6M rows / 120 tags / 61 partitions: data-range lookups 1–3.5 ms (were up to ~11–14 ms; stopped/late-starting tags no longer scan every partition). Trade-offs documented: +12% disk; a tag holding a large share of the table can read ~20% slower on long windows; out-of-order writes rewrite index files. Rejected with measurements: bounded probes, a per-tag side table (not maintainable INSERT-only), `LATEST ON`. Both backends now read the data range BEFORE the bucket query (a racing append can no longer give a Good Count of 0). **SqlServer:** the #680/F1 single-pass StartEnd was ~2× slower on long windows. Replaced with one GROUP BY of per-bucket pick times + a PK join-back; live gate 9 then found a plan (under understated statistics) where the picks range-seeked the window per bucket (1d@1min 20–47 s), so every pick now carries `FORCESEEK (PK_Sample (TagId, SourceTimeUtc))` — an exact-key seek under any statistics; hash/merge over the tag's history cannot be produced. Byte-identical output (EXCEPT both ways, all value kinds). Live gate 9 on a 345k-row tag vs the pre-#683 image: 1 d @ 1 min 60 s `BadTimeout` → 0.13 s, 1 d @ 1 h 2.6 s → 0.12 s, whole 4-day history ~40 s → ~0.5 s. Regression tests: understated-statistics compile-small/reuse-wide (fails on the previous query: 1.6M reads), forced HASH/MERGE refused (8622). Live gate 9 G3: index added to the rig's old unindexed volume by both centrals booting together (no suspension), results identical to SqlServer; 6-min read-while-writing loop 748k reads / 0 failures.

dohertj2 referenced this issue from a commit

2026-10-12 13:59:26 -04:00

perf(historian): index the QuestDB sample tag column; read the data range before the buckets (#683)

dohertj2 referenced this issue from a commit

2026-10-12 13:59:26 -04:00

fix(historian): #683 review — a refused tag ADD INDEX only warns; document index trade-offs

dohertj2 referenced this issue from a commit

2026-10-12 13:59:26 -04:00

perf(historian): #683 SqlServer StartEnd join-back on PK

dohertj2 referenced this issue from a commit

2026-10-12 13:59:26 -04:00

fix(historian): #683 review — bound the StartEnd pick joins to the window; usable picks re-check usability