skaidb v0.263.1

Time-series tables

skaidb can store metrics/telemetry natively: a TIMESERIES table keeps samples in a purpose-built storage engine (Prometheus-style compressed chunks), not the document LSM — with retention, counter-aware SQL aggregates, and time-bucketed queries.

Status: distributed — series place on the consistent-hash ring and replicate at the configured write consistency; queries union-merge across members at the read consistency; joins/decommissions migrate series like any other data.

Usage

CREATE TIMESERIES TABLE cpu (SERIES KEY (host, core), RETENTION 30d);

INSERT INTO cpu (host, core, ts, value)
  VALUES ('web1', '0', 1712000000000, 0.63), ('web1', '1', 1712000000000, 0.41);

-- Time-bucketed aggregation over the last hour:
SELECT time_bucket(1m, ts) AS t, host, avg(value), max(value)
FROM cpu WHERE ts >= now() - 1h AND host = 'web1'
GROUP BY t, host ORDER BY t;

-- Counter rate (reset-aware, per series, summed — sum(rate(...)) semantics):
SELECT time_bucket(5m, ts) AS t, rate(value)
FROM http_requests_total WHERE ts >= now() - 6h GROUP BY t;

-- Raw samples:
SELECT ts, value FROM cpu WHERE host = 'web1' AND ts >= now() - 5m ORDER BY ts;

Column roles: SERIES KEY columns are string labels (required on every insert); ts is the sample timestamp (required, ms); every other inserted column is a numeric field — multiple fields per row are fine, each is stored as its own compressed stream. Full grammar and semantics: QUERY_SYNTAX.md.

From an application

Ingest and query are ordinary SQL. Two things to know before writing code: ts goes in as milliseconds since the epoch (an integer) and comes back as a timestamp value in each driver's native type; and aggregates need an alias if you want to read them by name. Use the driver's batch call for ingest — one round-trip for the whole batch rather than one per sample. Placeholders differ per driver (? everywhere except Node.js and Ruby, which use $1) — see the matrix in HOWDOI.md.

INSERT INTO cpu (host, core, ts, value) VALUES (?, ?, ?, ?);

SELECT time_bucket(1m, ts) AS t, avg(value) AS avg_value
FROM cpu WHERE host = ? AND ts >= ?
GROUP BY t ORDER BY t;

cur = conn.cursor()
cur.executemany("INSERT INTO cpu (host, core, ts, value) VALUES (?, ?, ?, ?)",
                [("web1", "0", 1712000000000, 0.63),
                 ("web1", "1", 1712000000000, 0.41)])
cur.execute("SELECT time_bucket(1m, ts) AS t, avg(value) AS avg_value FROM cpu "
            "WHERE host = ? AND ts >= ? GROUP BY t ORDER BY t",
            ("web1", 1712000000000))
for bucket, avg_value in cur.fetchall():
    print(bucket, avg_value)          # bucket is a datetime

await client.batch('INSERT INTO cpu (host, core, ts, value) VALUES ($1, $2, $3, $4)',
  [['web1', '0', 1712000000000, 0.63], ['web1', '1', 1712000000000, 0.41]]);
const res = await client.query(
  `SELECT time_bucket(1m, ts) AS t, avg(value) AS avg_value FROM cpu
   WHERE host = $1 AND ts >= $2 GROUP BY t ORDER BY t`, ['web1', 1712000000000]);
for (const row of res.rows) console.log(row.t, row.avg_value);

// database/sql has no batch API — Go inserts a row per call.
db.Exec("INSERT INTO cpu (host, core, ts, value) VALUES (?, ?, ?, ?)",
    "web1", "0", int64(1712000000000), 0.63)
rows, err := db.Query(`SELECT time_bucket(1m, ts) AS t, avg(value) AS avg_value FROM cpu
    WHERE host = ? AND ts >= ? GROUP BY t ORDER BY t`, "web1", int64(1712000000000))
defer rows.Close()
for rows.Next() {
    var bucket time.Time
    var avg float64
    rows.Scan(&bucket, &avg)
}

Skaidb.ResultSet rs = conn.prepare(
        "SELECT time_bucket(1m, ts) AS t, avg(value) AS avg_value FROM cpu "
      + "WHERE host = ? AND ts >= ? GROUP BY t ORDER BY t")
    .setString(1, "web1")
    .setLong(2, 1712000000000L)
    .executeQuery();
// Timestamps arrive as Instant — read them with getObject (there is no getInstant).
while (rs.next()) System.out.println(rs.getObject("t") + " " + rs.getDouble("avg_value"));

res = conn.exec_params(<<~SQL, ["web1", 1712000000000])
  SELECT time_bucket(1m, ts) AS t, avg(value) AS avg_value FROM cpu
  WHERE host = $1 AND ts >= $2 GROUP BY t ORDER BY t
SQL
res.each { |row| puts "#{row['t']} #{row['avg_value']}" }

$stmt = $db->prepare('SELECT time_bucket(1m, ts) AS t, avg(value) AS avg_value FROM cpu
    WHERE host = ? AND ts >= ? GROUP BY t ORDER BY t');
$stmt->execute(['web1', 1712000000000]);
// `t` is a DateTimeImmutable — format it; echoing the object directly is a fatal error.
foreach ($stmt->fetchAll() as $row) { echo $row['t']->format('c'), ' ', $row['avg_value'], PHP_EOL; }

using var cmd = conn.CreateCommand();
cmd.CommandText = "SELECT time_bucket(1m, ts) AS t, avg(value) AS avg_value FROM cpu " +
                  "WHERE host = ? AND ts >= ? GROUP BY t ORDER BY t";
cmd.Parameters.Add("web1");
cmd.Parameters.Add(1712000000000L);
using var reader = cmd.ExecuteReader();
while (reader.Read()) Console.WriteLine($"{reader.GetDateTimeOffset(0)} {reader.GetDouble(1)}");

use skaidb_proto::Response;
use skaidb_types::Value;
let mut q = client.prepare(
    "SELECT time_bucket(1m, ts) AS t, avg(value) AS avg_value FROM cpu \
     WHERE host = ? AND ts >= ? GROUP BY t ORDER BY t")?;
let args = [Value::String("web1".into()), Value::Timestamp(1712000000000)];
if let Response::Rows { rows, .. } = client.execute_prepared(&mut q, &args)? {
    for row in rows { println!("{} {}", row[0], row[1]); }
}

now() - 1h and the other relative forms are server-side, so a dashboard query needs no clock on the client: WHERE ts >= now() - 1h takes no parameter at all.

What's implemented

Storage (skaidb-tsdb, measured on workstation NVMe):

  • Gorilla compression (delta-of-delta timestamps + XOR floats): 1.0–1.5 bytes/sample on typical fleet patterns (counters, mostly-idle gauges); ~6.7 worst-case on full-entropy random walks.
  • Ingest ≥2M samples/s single node with a WAL fsync per batch.
  • Crash recovery: CRC-framed WAL, torn-tail tolerant, checkpointed on flush (WAL size tracks the unflushed window, not history).
  • Head flushes (and the bounded compaction that follows one) run on the server's maintenance thread, never inside the statement that completed a window: an append only marks the flush due, and reads merge the head with the blocks either way. The block itself is written with the store unlocked (the head keeps every sample until the block is installed), so appends and reads never wait on a flush. The embedded engine flushes inline.
  • Immutable 2 h blocks, 4× tiered compaction, RETENTION as O(1) whole-block drops.
  • Cardinality cap (default 1M series/node) with per-batch accounting of out-of-order and over-limit rejections; per-table timeseries.<name>.{series,blocks,samples_appended,samples_rejected,disk_bytes} in SHOW STATUS.

SQL surface:

  • CREATE TIMESERIES TABLE (SERIES KEY (...) [, RETENTION <dur>] [, OOO <dur>]) — OOO sets a bounded out-of-order ingest window (buffered per series, merged in time order; the remote_write metrics table auto-creates with OOO 1h for HA Prometheus pairs); plain DROP TABLE; listed by SHOW TABLES with the implicit (series key, ts) key; survives restart (catalog + WAL replay).
  • ALTER TABLE <ts> SET (retention = <dur> | ooo = <dur>) — both are live-tunable, no create-new/backfill/swap: retention changes apply at the next flush (widening cannot resurrect already-dropped blocks; 0 clears retention), the OOO window applies to subsequent inserts. Temporarily widening ooo is the supported way to backfill history into a table that already takes live writes.
  • INSERT reports dropped points. Samples older than a series' OOO window are discarded per sample, and the INSERT's affected count reflects only what landed — {"affected": 0} when every point was late (one count per numeric field when rows carry several; full-success inserts keep reporting the row count). Compare affected with what you sent to detect loss; timeseries.<t>.samples_rejected in SHOW STATUS tracks the cumulative total per node.
  • Duration literals (250ms, 15s, 5m, 2h, 30d, 1w), time_bucket(step, ts), now() (one instant per statement).
  • Time-series aggregates: rate(f) / increase(f) (counter-reset-aware, per series then summed), delta(f), first(f) / last(f) — alongside the ordinary COUNT/SUM/AVG/MIN/MAX.
  • Storage pushdown of AND-combined ts ranges and label = / != predicates; everything else applies afterward with full SQL semantics.
  • Label-DISTINCT serves from series metadata: SELECT DISTINCT <series-key columns> FROM <ts> (optionally label-filtered/ordered) answers from the store's series label sets — no sample materialization, no scan-budget exposure, regardless of point count. A time (ts) constraint forces the sample path (label sets are all-time).
  • Unbounded min(ts)/max(ts) serve from store metadata: an ungrouped, filter-free extrema query over ts answers from block/head time bounds — no sample gather, regardless of point count. On a cluster the answer is the union across every member (the freshest committed frontier); if any member is unreachable the exact sample gather runs instead. Any filter, grouping, or other aggregate keeps the sample path.
  • Prometheus remote_write: POST /api/v1/write on the REST listener (HTTP Basic auth like /query) accepts snappy-compressed protobuf WriteRequests from any Prometheus / Grafana Agent / OTel collector. Samples land in a metrics TS table (auto-created on first write, SERIES KEY (name)); __name__ maps to the name label, other labels pass through — a series' OWN name label renames to exported_name (the Prometheus collision convention) so it cannot clobber the metric name — and any label equality in SQL pushes down to the store, so WHERE name = '...' AND instance = '...' is efficient without declaring every label. In a cluster, ingested samples replicate through the same series-placement path as SQL INSERTs. Exemplars in the request land in a metrics_exemplars row table ((metric_series, ts) primary key, the metric's canonical k=v,… series string, the metric name, the exemplar's value and its own labels document — trace ids, typically; retained 7 days), SQL-queryable and served by /api/v1/query_exemplars?query=<selector>&start&end in Prometheus's shape. Native (sparse) histograms ingest as the classic series a histogram_quantile consumes — cumulative <name>_bucket{le} at the sparse schema's bounds (2^(index·2^-schema); the zero bucket and any negative buckets count under every bound), a +Inf bucket, <name>_sum, <name>_count — so a Grafana panel written for classic histograms works unchanged; custom-bucket schemas are skipped.
  • MQTT sink ([[mqtt.sink]] with mode = "timeseries"): the broker captures matching publishes and flattens numeric JSON leaves into the same fast path — each leaf becomes a series named by its dotted path, topic wildcard captures and string fields become labels, and the TS table auto-creates on first write. Device telemetry (zigbee2mqtt, Tasmota sensors) lands PromQL-queryable with zero glue services; see MQTT.md.
  • Prometheus query API / Grafana: point Grafana's built-in Prometheus datasource at skaidb's REST listener. /api/v1/query and /api/v1/query_range evaluate a PromQL subset — instant selectors with =/!= matchers, rate/increase/delta and avg/min/max/sum/count/last_over_time over range selectors (counter-reset-aware where applicable, matching the SQL aggregates), and sum/avg/min/max/count [by|without (...)] — plus regex matchers (=~/!~, anchored like Prometheus), offset, vector arithmetic (+ - * /; scalar∘vector and one-to-one vector∘vector on identical label sets), and histogram_quantile — over the remote_write metrics table. /api/v1/labels, /api/v1/label/<n>/values, /api/v1/series, /api/v1/query_exemplars, buildinfo and metadata stubs power Grafana's autocomplete; the metadata endpoints honor start/end as Prometheus does — only series with a sample in the window contribute, answered from chunk-range metadata without decoding any sample. On a cluster, regex matchers travel to the peers, which prune with their own regex postings before answering; the coordinator re-applies them on the merged result, so a peer that predates the wire field (mid-roll) merely over-returns. A fresh datasource with no ingest sees empty results, not errors. Datasource setup recipes: GRAFANA.md.
  • Raw dumps are scan-metered at the source: a raw SELECT over a time-series table charges each gathered sample against the statement's scan budget, exactly like row-table gathers — an unbounded dump over a huge range fails with the budget error instead of materializing until the coordinator OOMs. The charge lands inside the store walk (and per peer response on a cluster), so an over-budget gather aborts as it reads instead of after the whole result sits resident at the coordinator. Narrow the time range or aggregate (aggregations push down as bounded per-bucket partials and are unaffected). Note ts bounds are epoch milliseconds — a bound accidentally supplied in epoch seconds reads as ~1970, unbounding the walk (the classic symptom: a narrow-window query dying on the scan budget).
  • COUNT(*) over an empty selection returns 0: the partials plan folds COUNT into a SUM of per-bucket counts, and SQL SUM over zero rows is NULL — the fold coalesces to 0, so an empty time window or non-matching filter counts as 0 like every SQL COUNT.
  • LIMIT'd raw reads page efficiently: WHERE ts > <cursor> ORDER BY ts LIMIT n (or DESC with an upper bound) walks the range in time slices and stops as soon as n rows survive the filter — each page costs ~its own rows, so exporting a table of any size is a linear keyset-pagination loop instead of a quadratic re-scan (and pages never trip the budget on their own). COUNT(*) on single-field tables (all remote_write tables) serves from per-bucket partials the same way aggregates do.
  • Boot reads one small meta.json per sealed block and nothing else: a block's series index and label postings load lazily on the first read that needs them (blocks are immutable, so once loaded they stand), and a block outside every queried window is never decoded. A store of hundreds of thousands of blocks opens in the time it takes to list them; a torn block index fails its first read loudly rather than boot.
  • Unbounded raw reads stream end to end: over a binary-driver query_stream or REST /query, an eligible raw SELECT on a time-series table (bare columns or *, no aggregation/DISTINCT, no LIMIT/OFFSET, unordered or ORDER BY ts) is served server-side as time slices — pages of ts >= <cursor> ORDER BY ts LIMIT 1024 whose trailing rows sharing the page's last timestamp are held back for the next page, so several series at one timestamp cross a page boundary whole and every (series, ts) arrives exactly once, in non-decreasing ts order. The node holds one page, never the dump; a table of millions of samples streams in bounded memory (the >250k-sample single-response shape that used to die on the scan budget). The first page starts at the filter's lower ts bound, else the table's oldest sample. A SELECT * fixes its columns from the first page (a value field first appearing later reads NULL — the same trade as row-table wildcards). Counted as path="ts" in skaidb_query_stream_total.
  • Rollups / downsampling: CREATE ROLLUP r30m ON cpu BUCKET 30m RETENTION 90d — per-bucket partials (<field>_{count,sum,min,max, first,last}) maintained automatically at window flush and queryable as a normal TS table with the same labels. Each replica maintains its rollups locally: a rollup series has the same labels as its source, so it places on the same replica set by construction. Long retention on the rollup + short on the source = classic tiered downsampling.
  • Rollup query rewrite: aggregate queries on the source table keep answering after raw samples age out — buckets older than the source's RETENTION horizon are served from the coarsest rollup whose bucket divides the group's time_bucket step, stitched seamlessly with exact source partials for the within-retention part of the window. Covers count/sum/avg/min/max/first/last; rate-family aggregates need raw samples and never read rollups. Within retention (single-node): group buckets wholly below the head's oldest sample also serve from the rollup — the backfill above keeps them exact, so this is the same numbers with less raw IO; the bucket straddling the head boundary stays on the source. On a cluster the boundary is the minimum over every member's head boundary (one metadata round per statement — a peer's head may lag its rollup); an unreachable or busy member makes it unprovable and that statement keeps retention-only routing.
  • Partial-aggregate pushdown: an aggregation whose WHERE is fully served by the pushdown (a ts range plus label =/!=), grouping by labels and/or one time_bucket, ships per-series per-bucket partials (count/sum/min/max/first/last/increase) from each member instead of raw samples, and answers each (series, bucket) from the responder that saw the most samples for it. All the supported aggregates — count/sum/avg/min/max/first/last/rate/increase/delta, HAVING, ORDER BY, LIMIT included — fold from the partials with identical semantics (equivalence-tested against the raw path). Cuts coordinator transfer ~RF× and more for wide Grafana-style aggregations; anything ineligible (residual predicates, COUNT(*), computed aggregate arguments) transparently uses the raw union-merge path. The PromQL query_range endpoint has its own partials fast path for the shape Grafana panels emit: a bare avg/min/max/sum/count/last/present_over_time(m[w]) is served from windowed partials computed on each member over the query's own step grid (closed windows [t−w, t], any window/step relationship — the windows are anchored to the step grid, not epoch-aligned buckets, which is what makes the values float-for-float identical to the raw evaluation). One bounded row per (series, step) crosses the wire instead of every raw sample. offset, @, subqueries, surrounding expressions and the whole rate/increase/delta family (extrapolation needs the window's raw boundary samples) keep the raw route.
  • Hinted handoff: a replica unreachable during an append gets its batch buffered on the coordinator (bounded per peer) and replayed via the gap-filling merge as soon as it's reachable — brief outages recover in seconds; anti-entropy repair remains the durable backstop for anything the bounded buffer dropped.
  • Anti-entropy: repair() (the periodic pass and cluster repair) converges TS replicas — per-series (count, checksum) summaries are compared per peer, and the series' elected sender pushes divergent series via a merge path that accepts samples of any age (fills mid-series gaps a normal append would reject). The summary is folded one series at a time, releasing the engine read guard between series, so a pass neither costs the whole table in memory nor pins the lock for the length of a decode; the checksum is an order-independent XOR, so no ordering or materialization is needed to compute it. The divergent-series push walks fixed time windows rather than reading a whole history at once. A pass is also skipped entirely while the node is shedding — repair is background work, and one deferred interval costs nothing next to pushing a node under memory pressure into an OOM. Duplicate chunks the merge creates fold away at the next compaction. A long-down replica converges durably, not just at read time. Merge ingest folds its own backlog: each merge call cuts a level-0 block, and once a store holds more than a small backlog of blocks a bounded compaction round runs inline (capped group size, wall-clock budget), so hint-replay storms can't grow the block count without bound. Hinted TS writes coalesce per table into one merge per drain pass. Retention/compaction are best-effort maintenance: a maintenance failure surfaces in stats (maintenance_errors) and retries next flush — it never fails the client append that triggered it. Block sequence numbers skip any directory already on disk, so residue from an interrupted block write can't wedge later flushes.
  • Cluster distribution: TS DDL broadcasts like other DDL; a series (its labels) is the placement unit on the ring, replicated to RF nodes — all of a series' field streams co-locate. Appends group per replica set (one internode batch per replica, per-sample idempotent on replay) and ack at the write consistency. Queries broadcast matchers + range to all members and union-merge per series (samples are immutable facts keyed by timestamp, so any responder holding a sample covers a replica that missed it), requiring the read-consistency responder count.
  • The whole ordinary SELECT surface on top: GROUP BY (including by output alias, GROUP BY t), HAVING, ORDER BY, DISTINCT, LIMIT/OFFSET, multi-field queries, SELECT *.
  • Append-only semantics enforced: UPDATE/DELETE/transactions rejected with clear errors; reserved __-prefixed names blocked.

Compatibility note

The shipped PromQL subset covers selectors, rate/increase/delta, and sum/avg/min/max/count by/without; label matchers push down =/!= (regex matchers re-check against the full population). Selection uses label postings.