Real-time monitoring dashboard for Proxmox VE, PBS, Docker, Kubernetes, TrueNAS and vSphere. Self-hosted, with alerts for failed backups, full disks, unhealthy containers and offline nodes.
Go aialertsdashboarddockerdocker-monitoringgohomelabinfrastructure-monitoringkubernetesmonitoringmspproxmoxproxmox-backup-serverproxmox-mail-gatewayproxmox-veself-hostedtruenastypescriptvspherewebhooks
package metrics
import (
"database/sql"
"fmt"
"net/url"
"reflect"
"strings"
"testing"
"time"
pdb "github.com/rcourtman/pulse-go-rewrite/pkg/db"
_ "modernc.org/sqlite"
)
// TestQueryPlansUseIndexes validates that all critical metrics-store SQL queries
// use indexed lookups (SEARCH) rather than full table scans (SCAN TABLE).
// This catches index regressions — if a schema migration drops an index or a
// query change breaks index eligibility, these tests fail immediately.
//
// Each sub-test runs EXPLAIN QUERY PLAN on SQL the store uses at
// runtime. Queries are classified by how they should access the table:
// - searchRequired: the plan must use SEARCH (B-tree point/range lookup)
// - allowCoveringIndexScan: the plan may use a covering-index scan, which
// reads only the index without touching the table — acceptable when no
// index has the query's filter columns as a prefix
//
// In all cases, a bare "SCAN TABLE metrics" (full table scan) is rejected.
//
// NOTE: Maintenance SQL is duplicated from store.go. Retained reads exercise
// the shared runtime builder directly in TestRetainedQueryPlansUseIndexes.
// If store.go's schema or queries change, these tests must be updated in
// lockstep — a mismatch means they validate stale SQL.
func TestQueryPlansUseIndexes(t *testing.T) {
db := newPlanTestDB(t)
// farFuture is an int64 timestamp safe on 32-bit platforms.
const farFuture int64 = 9999999999
tests := []struct {
name string
query string
args []any
// wantIndex, if non-empty, asserts this index name appears on the
// same plan line as a SEARCH on the metrics table.
wantIndex string
// allowCoveringIndexScan permits "SCAN metrics USING COVERING INDEX ..."
// for queries where the planner correctly chooses a covering-index
// scan over a table scan. When false, only SEARCH is accepted.
allowCoveringIndexScan bool
// allowIndexScan permits "SCAN metrics USING INDEX ..." (index-ordered
// scan) for queries where the planner uses an index to satisfy GROUP BY
// or ORDER BY without a separate sort, but still reads table rows.
allowIndexScan bool
}{
{
name: "retention delete by tier+time",
query: `DELETE FROM metrics WHERE tier = ? AND timestamp < ?`,
args: []any{"raw", farFuture},
wantIndex: "idx_metrics_tier_time",
},
{
name: "rollup candidate discovery (legacy per-candidate path)",
query: `SELECT DISTINCT resource_type, resource_id, metric_type
FROM metrics
WHERE tier = ? AND timestamp >= ? AND timestamp < ?`,
args: []any{"raw", int64(0), farFuture},
// This query filters on (tier, timestamp) which aren't the leading
// columns of most indexes. The planner correctly uses a covering-
// index scan — reading only the index, never the table.
allowCoveringIndexScan: true,
},
{
name: "rollup aggregation insert (legacy per-candidate path)",
query: `INSERT OR IGNORE INTO metrics (resource_type, resource_id, metric_type, value, min_value, max_value, timestamp, tier)
SELECT
resource_type, resource_id, metric_type,
AVG(value) as value,
MIN(COALESCE(min_value, value)) as min_value,
MAX(COALESCE(max_value, value)) as max_value,
(timestamp / ?) * ? as bucket_ts,
?
FROM metrics
WHERE resource_type = ? AND resource_id = ? AND metric_type = ?
AND tier = ? AND timestamp >= ? AND timestamp < ?
GROUP BY resource_type, resource_id, metric_type, bucket_ts`,
args: []any{int64(60), int64(60), "minute", "vm", "vm-1", "cpu", "raw", int64(0), farFuture},
wantIndex: metricsIdentityIndex,
},
{
name: "batched rollup aggregation insert",
query: `INSERT OR IGNORE INTO metrics (resource_type, resource_id, metric_type, value, min_value, max_value, timestamp, tier)
SELECT
resource_type,
resource_id,
metric_type,
AVG(value) as value,
MIN(COALESCE(min_value, value)) as min_value,
MAX(COALESCE(max_value, value)) as max_value,
(timestamp / ?) * ? as bucket_ts,
?
FROM metrics
WHERE tier = ? AND timestamp >= ? AND timestamp < ?
GROUP BY resource_type, resource_id, metric_type, bucket_ts`,
args: []any{int64(60), int64(60), "minute", "raw", int64(0), farFuture},
// The SELECT filters on (tier, timestamp) without resource/metric
// columns. SQLite may use an index-ordered scan (SCAN USING INDEX)
// on the identity index. A TEMP B-TREE may still be used for
// GROUP BY. The INSERT side uses the same unique index for ON CONFLICT.
allowIndexScan: true,
},
{
name: "max timestamp for tier (rollup scheduling)",
query: `SELECT MAX(timestamp) FROM metrics WHERE tier = ?`,
args: []any{"raw"},
wantIndex: "idx_metrics_tier_time",
},
{
name: "tier count stats (GetStats)",
query: `SELECT tier, COUNT(*) FROM metrics GROUP BY tier`,
args: []any{},
// GROUP BY tier scans all rows — a covering-index scan on
// idx_metrics_tier_time reads only the index (tier, timestamp),
// avoiding the full table row fetch. A SEARCH is also acceptable
// if the planner chooses a different strategy.
allowCoveringIndexScan: true,
},
}
for _, tt := range tests {
t.Run(tt.name, func(t *testing.T) {
plan := explainQueryPlan(t, db, tt.query, tt.args)
// Reject a bare full table scan — this is always bad.
if containsFullTableScan(plan) {
t.Errorf("query uses full table scan on metrics; expected indexed access\nPlan:\n%s", plan)
}
if tt.allowIndexScan {
// Must use SEARCH, covering-index scan, OR index-ordered scan.
if !containsMetricsSearch(plan) && !containsCoveringIndexScan(plan) && !containsIndexScan(plan) {
t.Errorf("query plan has neither SEARCH on metrics nor index scan\nPlan:\n%s", plan)
}
} else if tt.allowCoveringIndexScan {
// Must use EITHER a SEARCH on metrics or a covering-index scan on metrics.
if !containsMetricsSearch(plan) && !containsCoveringIndexScan(plan) {
t.Errorf("query plan has neither SEARCH on metrics nor covering-index scan\nPlan:\n%s", plan)
}
} else {
// Must contain at least one SEARCH on the metrics table.
// We scope to "metrics" to avoid false passes from constraint-
// handling SEARCH nodes in INSERT ... ON CONFLICT plans.
if !containsMetricsSearch(plan) {
t.Errorf("query plan does not contain SEARCH on metrics table; may not be using an index\nPlan:\n%s", plan)
}
}
// If a specific index is expected, verify that the same plan line
// that does a SEARCH on metrics also references that index.
// This prevents false passes where one node SEARCHes via
// conflict handling and a different node uses the expected index.
if tt.wantIndex != "" && !containsMetricsSearchWithIndex(plan, tt.wantIndex) {
t.Errorf("expected SEARCH on metrics using index %q not found in plan\nPlan:\n%s", tt.wantIndex, plan)
}
})
}
}
// Query, QueryAll and both batch variants share this exact runtime SQL. The
// display step is applied in SQLite after tier reconciliation.
func TestRetainedQueryPlansUseIndexes(t *testing.T) {
db := newPlanTestDB(t)
store := &Store{}
for _, window := range []time.Duration{time.Hour, 24 * time.Hour, 7 * 24 * time.Hour, 30 * 24 * time.Hour} {
for _, filtered := range []bool{false, true} {
for _, step := range []int64{0, 60} {
t.Run(fmt.Sprintf("%s/filtered_%v/step_%d", window, filtered, step), func(t *testing.T) {
var metrics []string
if filtered {
metrics = []string{"cpu", "memory"}
}
end := time.Unix(2000000000, 0)
sql, args := retainedQuerySQL("vm", []string{"vm-1", "vm-2", "vm-3"}, metrics, end.Add(-window), end, step, store.tierFallbacks(window), false, false)
plan := explainQueryPlan(t, db, sql, args)
searches := 0
for _, line := range strings.Split(plan, "\n") {
if strings.Contains(line, "SCAN m ") || strings.Contains(line, "SCAN h ") {
t.Fatalf("unbounded metrics read: %s", plan)
}
if strings.Contains(line, "SEARCH m ") || strings.Contains(line, "SEARCH h ") {
searches++
for _, constraint := range []string{"resource_type=?", "resource_id=?", "tier=?", "timestamp>?", "timestamp<?"} {
if !strings.Contains(line, constraint) {
t.Fatalf("retained search missing indexed %s: %s", constraint, line)
}
}
}
}
n := len(store.tierFallbacks(window))
if searches != n*(n+1)/2 {
t.Fatalf("expected every tier and overlap probe indexed, got %d: %s", searches, plan)
}
})
}
}
}
}
// A single present tier uses direct aggregation. It must retain the same
// bounded identity/tier/time lookup as the reconciled multi-tier query.
func TestRetainedSingleTierQueryPlansUseIndexes(t *testing.T) {
db := newPlanTestDB(t)
end := time.Unix(2000000000, 0)
for _, tier := range []Tier{TierRaw, TierMinute, TierHourly, TierDaily} {
for _, filtered := range []bool{false, true} {
t.Run(fmt.Sprintf("%s/filtered_%v", tier, filtered), func(t *testing.T) {
var metrics []string
if filtered {
metrics = []string{"cpu"}
}
query, args := retainedQuerySQL("vm", []string{"vm-1"}, metrics, end.Add(-time.Hour), end, 60, []Tier{tier}, false, false)
plan := explainQueryPlan(t, db, query, args)
if strings.Contains(plan, "SCAN m ") || !strings.Contains(plan, "SEARCH m ") {
t.Fatalf("single tier must use a bounded lookup: %s", plan)
}
for _, constraint := range []string{"resource_type=?", "resource_id=?", "tier=?", "timestamp>?", "timestamp<?"} {
if !strings.Contains(plan, constraint) {
t.Fatalf("missing indexed %s: %s", constraint, plan)
}
}
})
}
}
}
func TestAvailabilityHistoryQueryPlansUseIndexes(t *testing.T) {
db := newPlanTestDB(t)
tests := []struct {
name string
query string
args []any
wantIndex string
}{
{
name: "fleet rollup batch",
query: `SELECT target_id, bucket_start, reachable_millis, unreachable_millis,
indeterminate_millis, latency_count, latency_sum,
COALESCE(latency_min, 0), COALESCE(latency_max, 0)
FROM availability_history_buckets
WHERE target_id IN (?, ?, ?) AND tier = ? AND bucket_start >= ? AND bucket_start < ?
ORDER BY target_id, bucket_start`,
args: []any{"target-1", "target-2", "target-3", "minute", int64(0), int64(9999999999)},
wantIndex: "sqlite_autoindex_availability_history_buckets_1",
},
{
name: "latest live tail batch",
query: `SELECT target_id, timeline_at_ns, valid_until_ns
FROM availability_observations
WHERE target_id IN (?, ?, ?)
ORDER BY target_id, timeline_at_ns DESC, id DESC`,
args: []any{"target-1", "target-2", "target-3"},
wantIndex: "idx_availability_observations_target_timeline",
},
{
name: "revision boundary batch",
query: `SELECT target_id, revision, started_at_ns
FROM availability_revision_boundaries
WHERE target_id IN (?, ?, ?) AND started_at_ns >= ? AND started_at_ns < ?
ORDER BY target_id, started_at_ns`,
args: []any{"target-1", "target-2", "target-3", int64(0), int64(9999999999)},
wantIndex: "idx_availability_boundaries_target_time",
},
{
name: "raw retention",
query: `DELETE FROM availability_observations WHERE timeline_at_ns < ?`,
args: []any{int64(9999999999)},
wantIndex: "idx_availability_observations_retention",
},
}
for _, tt := range tests {
t.Run(tt.name, func(t *testing.T) {
plan := explainQueryPlan(t, db, tt.query, tt.args)
if !strings.Contains(plan, "SEARCH") || !strings.Contains(plan, tt.wantIndex) {
t.Fatalf("expected indexed availability-history search using %s\nPlan:\n%s", tt.wantIndex, plan)
}
})
}
}
// lineRefersToMetrics returns true if a plan line references the "metrics"
// table (not metrics_meta). SQLite may emit "SEARCH metrics USING ...",
// "SEARCH TABLE metrics ...", "SCAN metrics ...", or "SCAN TABLE metrics ...".
func lineRefersToMetrics(line string) bool {
// Match "metrics " (with space after), "metrics(" (function-like),
// or "metrics" at end of line. Reject "metrics_meta".
for _, needle := range []string{"metrics ", "metrics("} {
if strings.Contains(line, needle) && !strings.Contains(line, "metrics_meta") {
return true
}
}
// Handle "metrics" at end of line (e.g., bare "SCAN metrics").
if strings.HasSuffix(strings.TrimSpace(line), "metrics") && !strings.Contains(line, "metrics_meta") {
return true
}
return false
}
// containsMetricsSearch returns true if any plan line contains a SEARCH
// specifically on the metrics table (e.g., "SEARCH metrics USING INDEX ..."
// or "SEARCH TABLE metrics USING INDEX ...").
func containsMetricsSearch(plan string) bool {
for _, line := range strings.Split(plan, "\n") {
if strings.Contains(line, "SEARCH") && lineRefersToMetrics(line) {
return true
}
}
return false
}
// containsMetricsSearchWithIndex returns true if a single plan line contains
// a SEARCH on the metrics table AND the given index name. This ensures the
// expected index is used on the metrics-table read path, not on a separate node.
func containsMetricsSearchWithIndex(plan, indexName string) bool {
for _, line := range strings.Split(plan, "\n") {
if strings.Contains(line, "SEARCH") && lineRefersToMetrics(line) && strings.Contains(line, indexName) {
return true
}
}
return false
}
// containsFullTableScan returns true if the plan contains a full table scan
// on the metrics table — "SCAN TABLE metrics" or "SCAN metrics" without a
// "USING ... INDEX" qualifier. A covering-index scan is not a full table scan.
func containsFullTableScan(plan string) bool {
for _, line := range strings.Split(plan, "\n") {
if !strings.Contains(line, "SCAN") || !lineRefersToMetrics(line) {
continue
}
// A covering-index scan is acceptable — it reads only the index.
if strings.Contains(line, "USING") && strings.Contains(line, "INDEX") {
continue
}
// Bare SCAN TABLE metrics or SCAN metrics without index = full scan.
return true
}
return false
}
// containsCoveringIndexScan returns true if any plan line shows a covering-
// index scan on the metrics table (e.g., "SCAN metrics USING COVERING INDEX ...").
func containsCoveringIndexScan(plan string) bool {
for _, line := range strings.Split(plan, "\n") {
if strings.Contains(line, "SCAN") && lineRefersToMetrics(line) && strings.Contains(line, "COVERING INDEX") {
return true
}
}
return false
}
// containsIndexScan returns true if any plan line shows an index-ordered scan
// on the metrics table (e.g., "SCAN metrics USING INDEX idx_metrics_query_all").
// This differs from a covering-index scan in that it reads table rows via the
// index, but still avoids a full table scan.
func containsIndexScan(plan string) bool {
for _, line := range strings.Split(plan, "\n") {
if strings.Contains(line, "SCAN") && lineRefersToMetrics(line) && strings.Contains(line, "USING") && strings.Contains(line, "INDEX") {
return true
}
}
return false
}
// newPlanTestDB creates an in-memory SQLite database with the metrics store
// schema. It uses the same schema and indexes as store.initSchema().
func newPlanTestDB(t *testing.T) *sql.DB {
t.Helper()
dsn := ":memory:?" + url.Values{
"_pragma": []string{
"busy_timeout(5000)",
"journal_mode(WAL)",
},
}.Encode()
db, err := sql.Open("sqlite", dsn)
if err != nil {
t.Fatalf("open in-memory db: %v", err)
}
// Pin to a single connection — a second connection to :memory: would get
// a different database, causing "no such table" failures.
db.SetMaxOpenConns(1)
t.Cleanup(func() { db.Close() })
schema := `
CREATE TABLE IF NOT EXISTS metrics (
id INTEGER PRIMARY KEY AUTOINCREMENT,
resource_type TEXT NOT NULL,
resource_id TEXT NOT NULL,
metric_type TEXT NOT NULL,
value REAL NOT NULL,
min_value REAL,
max_value REAL,
timestamp INTEGER NOT NULL,
tier TEXT NOT NULL DEFAULT 'raw'
);
CREATE INDEX IF NOT EXISTS idx_metrics_tier_time
ON metrics(tier, timestamp);
CREATE UNIQUE INDEX IF NOT EXISTS idx_metrics_query_all
ON metrics(resource_type, resource_id, tier, timestamp, metric_type);
CREATE TABLE IF NOT EXISTS metrics_meta (
key TEXT PRIMARY KEY,
value TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS availability_observations (
id INTEGER PRIMARY KEY AUTOINCREMENT,
observation_id TEXT NOT NULL UNIQUE,
target_id TEXT NOT NULL,
config_revision INTEGER NOT NULL,
outcome TEXT NOT NULL,
observed_at_ns INTEGER NOT NULL,
timeline_at_ns INTEGER NOT NULL,
ingested_at_ns INTEGER NOT NULL,
valid_until_ns INTEGER NOT NULL,
execution_source TEXT NOT NULL,
latency_millis INTEGER
);
CREATE INDEX IF NOT EXISTS idx_availability_observations_target_timeline
ON availability_observations(target_id, timeline_at_ns, id);
CREATE INDEX IF NOT EXISTS idx_availability_observations_retention
ON availability_observations(timeline_at_ns);
CREATE TABLE IF NOT EXISTS availability_history_buckets (
target_id TEXT NOT NULL,
tier TEXT NOT NULL,
bucket_start INTEGER NOT NULL,
reachable_millis INTEGER NOT NULL DEFAULT 0,
unreachable_millis INTEGER NOT NULL DEFAULT 0,
indeterminate_millis INTEGER NOT NULL DEFAULT 0,
reachable_count INTEGER NOT NULL DEFAULT 0,
unreachable_count INTEGER NOT NULL DEFAULT 0,
indeterminate_count INTEGER NOT NULL DEFAULT 0,
latency_count INTEGER NOT NULL DEFAULT 0,
latency_sum INTEGER NOT NULL DEFAULT 0,
latency_min INTEGER,
latency_max INTEGER,
PRIMARY KEY(target_id, tier, bucket_start)
);
CREATE INDEX IF NOT EXISTS idx_availability_buckets_retention
ON availability_history_buckets(tier, bucket_start);
CREATE TABLE IF NOT EXISTS availability_revision_boundaries (
target_id TEXT NOT NULL,
revision INTEGER NOT NULL,
started_at_ns INTEGER NOT NULL,
PRIMARY KEY(target_id, revision)
);
CREATE INDEX IF NOT EXISTS idx_availability_boundaries_target_time
ON availability_revision_boundaries(target_id, started_at_ns);
`
if _, err := db.Exec(schema); err != nil {
t.Fatalf("create schema: %v", err)
}
// Insert a small amount of seed data so the query planner has statistics
// to reason about. Without data, SQLite may choose different plans.
for i := 0; i < 100; i++ {
_, err := db.Exec(
`INSERT INTO metrics (resource_type, resource_id, metric_type, value, timestamp, tier) VALUES (?, ?, ?, ?, ?, ?)`,
"vm", fmt.Sprintf("vm-%d", i%10), "cpu", float64(i), int64(1000000+i*5), "raw",
)
if err != nil {
t.Fatalf("seed data: %v", err)
}
targetID := fmt.Sprintf("target-%d", i%10)
_, err = db.Exec(`INSERT INTO availability_observations
(observation_id, target_id, config_revision, outcome, observed_at_ns, timeline_at_ns,
ingested_at_ns, valid_until_ns, execution_source, latency_millis)
VALUES (?, ?, 1, 'reachable', ?, ?, ?, ?, 'local', 12)`,
fmt.Sprintf("observation-%d", i), targetID, int64(1000000+i*5), int64(1000000+i*5),
int64(1000000+i*5), int64(1000060+i*5))
if err != nil {
t.Fatalf("seed availability observations: %v", err)
}
_, err = db.Exec(`INSERT OR IGNORE INTO availability_history_buckets
(target_id, tier, bucket_start, reachable_millis, latency_count, latency_sum, latency_min, latency_max)
VALUES (?, 'minute', ?, 60000, 1, 12, 12, 12)`, targetID, int64(1000000+i*60))
if err != nil {
t.Fatalf("seed availability buckets: %v", err)
}
_, err = db.Exec(`INSERT OR IGNORE INTO availability_revision_boundaries
(target_id, revision, started_at_ns) VALUES (?, 1, ?)`, targetID, int64(1000000+i*5))
if err != nil {
t.Fatalf("seed availability boundaries: %v", err)
}
}
// Run ANALYZE so the planner has real statistics.
if _, err := db.Exec("ANALYZE"); err != nil {
t.Fatalf("analyze: %v", err)
}
return db
}
// explainQueryPlan runs EXPLAIN QUERY PLAN on the given query and returns
// the full plan output as a single string.
func explainQueryPlan(t *testing.T, db *sql.DB, query string, args []any) string {
t.Helper()
rows, err := db.Query("EXPLAIN QUERY PLAN "+query, args...)
if err != nil {
t.Fatalf("EXPLAIN QUERY PLAN: %v\nQuery: %s", err, query)
}
defer rows.Close()
var lines []string
for rows.Next() {
var id, parent, notused int
var detail string
if err := rows.Scan(&id, &parent, ¬used, &detail); err != nil {
t.Fatalf("scan explain row: %v", err)
}
lines = append(lines, detail)
}
if err := rows.Err(); err != nil {
t.Fatalf("explain rows: %v", err)
}
return strings.Join(lines, "\n")
}
func TestRetainedUnaggregatedQueryAvoidsMetricSort(t *testing.T) {
db := newPlanTestDB(t)
store := &Store{}
end := time.Unix(2000000040, 0)
for _, valuesOnly := range []bool{false, true} {
for _, window := range []time.Duration{time.Hour, 24 * time.Hour, 7 * 24 * time.Hour, 30 * 24 * time.Hour} {
for _, ids := range [][]string{{"a"}, {"a", "b"}} {
for _, metrics := range [][]string{nil, {"cpu"}, {"cpu", "memory"}} {
t.Run(fmt.Sprintf("values_%t/%s/resources_%d/metrics_%d", valuesOnly, window, len(ids), len(metrics)), func(t *testing.T) {
query, args := retainedQuerySQL("node", ids, metrics, end.Add(-window), end, 0, store.tierFallbacks(window), false, valuesOnly)
plan := explainQueryPlan(t, db, query, args)
for _, line := range strings.Split(plan, "\n") {
if strings.Contains(strings.ToUpper(line), "TEMP B-TREE") {
t.Fatalf("unaggregated read sorts despite time-ordered index: %s", plan)
}
if len(ids) > 1 && strings.Contains(line, "MERGE (UNION ALL)") {
t.Fatalf("raw fleet read merges repeated identity strings across tiers: %s", plan)
}
}
})
}
}
}
}
}
func TestRetainedResourceNumbersPreserveScopeAndOrderedSeries(t *testing.T) {
for _, clustered := range []bool{false, true} {
t.Run(fmt.Sprintf("clustered_%t", clustered), func(t *testing.T) {
db := newPlanTestDB(t)
if clustered {
if _, err := db.Exec(`DROP TABLE metrics; CREATE TABLE metrics (` + metricsTableColumns + `) WITHOUT ROWID;
CREATE INDEX idx_metrics_tier_time ON metrics(tier,timestamp)`); err != nil {
t.Fatal(err)
}
}
store := &Store{db: pdb.Wrap(db, "retained-resource-number-test")}
store.clusteredMetrics.Store(clustered)
start := time.Unix(1_800_003_600, 0)
end := start.Add(3 * time.Hour)
ids := make([]string, queryAllBatchChunkSize+3)
tx, err := db.Begin()
if err != nil {
t.Fatal(err)
}
insert, err := tx.Prepare(`INSERT INTO metrics(resource_type,resource_id,metric_type,value,min_value,max_value,timestamp,tier) VALUES (?,?,?,?,?,?,?,?)`)
if err != nil {
t.Fatal(err)
}
for i := range ids {
// Deliberately reverse lexical order, including IDs beyond the
// chunk boundary. A resource number belongs only to its statement.
ids[i] = fmt.Sprintf("guest-'-%04d", len(ids)-i)
for _, metric := range []string{"cpu", "memory", "unrequested"} {
for _, row := range []struct {
offset int64
tier Tier
value float64
low, high any
}{
{60, TierMinute, 10, float64(0), float64(20)},
{65, TierRaw, 999, nil, nil}, // owned by the minute bucket
{180, TierRaw, 30, nil, nil},
{300, TierMinute, 50, nil, nil},
{15, TierRaw, 5, nil, nil}, // emitted after minute, must sort
{0, TierHourly, -100, nil, nil},
{-1, TierRaw, -999, nil, nil},
{3*3600 + 1, TierRaw, -999, nil, nil},
} {
if _, err := insert.Exec("vm", ids[i], metric, row.value+float64(i), row.low, row.high, start.Unix()+row.offset, string(row.tier)); err != nil {
t.Fatal(err)
}
}
}
if _, err := insert.Exec("container", ids[i], "cpu", 9999, nil, nil, start.Unix()+60, string(TierMinute)); err != nil {
t.Fatal(err)
}
}
if _, err := insert.Exec("vm", "outside-scope", "cpu", 9999, nil, nil, start.Unix()+60, string(TierMinute)); err != nil {
t.Fatal(err)
}
if err := insert.Close(); err != nil {
t.Fatal(err)
}
if err := tx.Commit(); err != nil {
t.Fatal(err)
}
check := func(requested, filters []string, step int64) {
t.Helper()
full, err := store.QueryMetricTypesBatch("vm", requested, filters, start, end, step)
if err != nil {
t.Fatal(err)
}
values, err := store.QueryMetricValuesBatch("vm", requested, filters, start, end, step)
if err != nil {
t.Fatal(err)
}
assertMetricValueProjection(t, full, values)
seen := make(map[string]bool)
for _, id := range requested {
id = normalizeMetricIdentifier(id)
if id == "" || seen[id] {
continue
}
seen[id] = true
one, err := store.QueryMetricTypesBatch("vm", []string{id}, filters, start, end, step)
if err != nil {
t.Fatal(err)
}
if !reflect.DeepEqual(full[id], one[id]) {
t.Fatalf("scope/order changed for %q step=%d: batch=%+v single=%+v", id, step, full[id], one[id])
}
}
if _, exists := full["outside-scope"]; exists {
t.Fatal("unrequested resource returned")
}
if len(full) != len(seen)-1 { // one requested ID is deliberately absent
t.Fatalf("resource count=%d requested distinct=%d", len(full), len(seen))
}
}
for _, filters := range [][]string{nil, {"cpu"}, {"memory", "cpu", "absent"}} {
for _, step := range []int64{0, 1, 60} {
check([]string{ids[2], ids[0], ids[1], ids[2], "", "missing"}, filters, step)
check([]string{ids[1], ids[2], ids[0], "missing"}, filters, step)
}
}
check(append(append([]string{}, ids...), "missing", ids[0]), nil, 0)
check(append(append([]string{}, ids...), "missing", ids[0]), []string{"memory", "cpu", "absent"}, 0)
})
}
}
// Filtered raw fleets bind both dimensions already. The row boundary should
// transport one series ordinal, while a cache hit with reordered metric names
// must still resolve against this statement's current bindings.
func TestRetainedRawFleetSeriesNumbersBindCurrentMetrics(t *testing.T) {
for _, clustered := range []bool{false, true} {
for _, valuesOnly := range []bool{false, true} {
t.Run(fmt.Sprintf("clustered=%t/values=%t", clustered, valuesOnly), func(t *testing.T) {
db := newPlanTestDB(t)
if clustered {
if _, err := db.Exec(`DROP TABLE metrics; CREATE TABLE metrics (` + metricsTableColumns + `) WITHOUT ROWID; CREATE INDEX idx_metrics_tier_time ON metrics(tier,timestamp)`); err != nil {
t.Fatal(err)
}
}
store := &Store{db: pdb.Wrap(db, "series-number-binding")}
store.clusteredMetrics.Store(clustered)
base := time.Unix(1_800_003_600, 0)
ids := []string{"b':p2", "a:p4"}
names := []string{"memory", "cpu", ":p4"}
for i, id := range ids {
for j, metric := range names {
if _, err := db.Exec(`INSERT INTO metrics(resource_type,resource_id,metric_type,tier,timestamp,value) VALUES ('node',?,?,'minute',?,?)`, id, metric, base.Unix(), float64(i*100+j)); err != nil {
t.Fatal(err)
}
}
}
for _, filters := range [][]string{names, {":p4", "memory", "cpu"}, {"cpu", "memory", ":p4"}} {
query, args := store.retainedQuerySQL("node", ids, filters, base.Add(-3*time.Hour), base.Add(time.Minute), 0, []Tier{TierMinute}, false, valuesOnly)
rows, err := db.Query(query, args...)
if err != nil {
t.Fatal(err)
}
columns, err := rows.Columns()
if err != nil {
t.Fatal(err)
}
rows.Close()
want := []string{"series_number", "timestamp", "value"}
if valuesOnly {
want = []string{"series_sample", "value"}
} else {
want = append(want, "min_value", "max_value")
}
if !reflect.DeepEqual(columns, want) {
t.Fatalf("repeated series identity still crosses row boundary: got %v, want %v", columns, want)
}
full, err := store.QueryMetricTypesBatch("node", ids, filters, base.Add(-3*time.Hour), base.Add(time.Minute), 0)
if err != nil {
t.Fatal(err)
}
values, err := store.QueryMetricValuesBatch("node", ids, filters, base.Add(-3*time.Hour), base.Add(time.Minute), 0)
if err != nil {
t.Fatal(err)
}
assertMetricValueProjection(t, full, values)
for i, id := range ids {
for j, name := range names {
points := full[id][name]
if len(points) != 1 || points[0].Value != float64(i*100+j) || !points[0].Timestamp.Equal(base) {
t.Fatalf("wrong current series for %q/%q: %+v", id, name, points)
}
}
}
}
})
}
}
}
func TestRetainedPackedSeriesKeepsNegativeTimestampsAndOverflowFallback(t *testing.T) {
db := newPlanTestDB(t)
store := &Store{db: pdb.Wrap(db, "packed-series-boundary")}
ids, names := []string{"second", "first"}, []string{"memory", "cpu", "disk"}
const width int64 = 6
const maximum = int64(^uint64(0) >> 1)
const minimum = -maximum - 1
for _, tc := range []struct {
name string
start int64
packed bool
}{
{"negative epoch", -100, true},
{"ordinary epoch", 1_800_000_000, true},
{"near positive bound", (maximum-width+1)/width - 2, true},
{"positive overflow", (maximum - width + 1) / width, false},
{"near negative bound", minimum / width, true},
{"negative overflow", minimum/width - 1, false},
} {
t.Run(tc.name, func(t *testing.T) {
start, end := time.Unix(tc.start, 0), time.Unix(tc.start+2, 0)
if got := retainedQueryUsesPackedSeries(len(ids), len(names), start, end, 0, false, true); got != tc.packed {
t.Fatalf("packed=%t want %t", got, tc.packed)
}
for i, id := range ids {
for j, metric := range names {
for offset := int64(0); offset <= 2; offset++ {
if _, err := db.Exec(`INSERT OR REPLACE INTO metrics(resource_type,resource_id,metric_type,tier,timestamp,value) VALUES ('node',?,?,'raw',?,?)`, id, metric, tc.start+offset, float64(i*100+j*10)+float64(offset)/3); err != nil {
t.Fatal(err)
}
}
}
}
// Reuse one Store and equal parameter counts across packed and
// overflow windows. Cached SQL must still choose the correct shape.
full, err := store.QueryMetricTypesBatch("node", ids, names, start, end, 0)
if err != nil {
t.Fatal(err)
}
values, err := store.QueryMetricValuesBatch("node", ids, names, start, end, 0)
if err != nil {
t.Fatal(err)
}
assertMetricValueProjection(t, full, values)
for _, id := range ids {
for _, name := range names {
points := values[id][name]
if len(points) != 3 {
t.Fatalf("lost observations for %s/%s: %+v", id, name, points)
}
for i, point := range points {
if point.Timestamp.Unix() != tc.start+int64(i) {
t.Fatalf("moved timestamp: %v", point.Timestamp.Unix())
}
}
}
}
})
}
}
// Direct ordinal accumulation must be indistinguishable from the existing
// single-resource assembler, including interleaved rows and empty series. Wide
// sparse filters exercise the bounded fallback without losing statement scope.
func TestRetainedNumberedAssemblyMatchesSingleResourceReads(t *testing.T) {
for _, clustered := range []bool{false, true} {
t.Run(fmt.Sprintf("clustered=%t", clustered), func(t *testing.T) {
db := newPlanTestDB(t)
if clustered {
if _, err := db.Exec(`DROP TABLE metrics; CREATE TABLE metrics (` + metricsTableColumns + `) WITHOUT ROWID; CREATE INDEX idx_metrics_tier_time ON metrics(tier,timestamp)`); err != nil {
t.Fatal(err)
}
}
store := &Store{db: pdb.Wrap(db, "numbered-series-assembly")}
store.clusteredMetrics.Store(clustered)
base := time.Unix(1_800_003_600, 0)
end := base.Add(6 * time.Hour)
ids := []string{"b':p2", "a:p4", "missing"}
names := []string{"memory", "cpu", "netin", "absent"}
tx, err := db.Begin()
if err != nil {
t.Fatal(err)
}
insert, err := tx.Prepare(`INSERT INTO metrics(resource_type,resource_id,metric_type,tier,timestamp,value,min_value,max_value) VALUES ('node',?,?,?,?,?,?,?)`)
if err != nil {
t.Fatal(err)
}
for i, id := range ids[:2] {
for j, name := range names[:3] {
// Minute-tier rows come first; the raw first/tail points must
// still be restored to chronological order, never averaged.
for p := 0; p < 300; p++ {
value := float64(i*1000 + j*300 + p)
if _, err := insert.Exec(id, name, "minute", base.Unix()+int64(p*60+60), value, value-7, value+9); err != nil {
t.Fatal(err)
}
}
for _, p := range []struct{ offset, value int64 }{{1, 12}, {65, 99999}, {18061, 0}} {
if _, err := insert.Exec(id, name, "raw", base.Unix()+p.offset, p.value, nil, nil); err != nil {
t.Fatal(err)
}
}
}
}
insert.Close()
if err := tx.Commit(); err != nil {
t.Fatal(err)
}
for _, shape := range []string{"compact", "rebound", "wide-sparse"} {
t.Run(shape, func(t *testing.T) {
requested := append([]string(nil), ids...)
filters := append([]string(nil), names...)
if shape == "rebound" {
requested[0], requested[1] = requested[1], requested[0]
filters[0], filters[2] = filters[2], filters[0]
}
if shape == "wide-sparse" {
for len(requested) < queryAllBatchChunkSize {
requested = append(requested, fmt.Sprintf("missing-%d", len(requested)))
}
for len(filters) <= maxRetainedNumberedSeries/queryAllBatchChunkSize {
filters = append(filters, fmt.Sprintf("absent-%d", len(filters)))
}
}
full, err := store.QueryMetricTypesBatch("node", requested, filters, base, end, 0)
if err != nil {
t.Fatal(err)
}
if len(full) != 2 {
t.Fatalf("empty resource materialised: %d resources", len(full))
}
for _, id := range ids[:2] {
single, err := store.QueryMetricTypesBatch("node", []string{id}, filters, base, end, 0)
if err != nil {
t.Fatal(err)
}
if !reflect.DeepEqual(full[id], single[id]) {
t.Fatalf("fleet differs from single-resource read for %s", id)
}
for _, points := range full[id] {
if len(points) != 302 || points[0].Value != 12 || points[len(points)-1].Value != 0 || points[len(points)-1].Min != 0 || points[1].Min != points[1].Value-7 {
t.Fatalf("chronology/zero/extrema/overlap changed: %d points", len(points))
}
}
}
values, err := store.QueryMetricValuesBatch("node", requested, filters, base, end, 0)
if err != nil {
t.Fatal(err)
}
assertMetricValueProjection(t, full, values)
full[ids[0]][names[0]][0].Value = -999
if values[ids[0]][names[0]][0].Value != 12 {
t.Fatal("reader outputs alias each other")
}
})
}
})
}
}