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 (the checkpoint re-records live series and still-open samples in a segment of its own, written and fsynced with the store lock released while appends continue into the next segment; the superseded segments are retired only once it is durable) (WAL size tracks the unflushed window, not history). A checkpoint marker beside the store records the newest checkpoint's segment and flush boundary: at open, only segments older than it skip samples below that boundary (already in blocks); the checkpoint and everything after it replay in full. A mid-window flush (shutdown, memory pressure, the head byte cap) or a repair block reaching into the open window therefore never hides later WAL samples of that window from the next open. The marker carries a CRC-32 of its two fields; one that fails it, does not parse, or names a segment that is not on disk is unusable, and that open replays the whole WAL instead of trusting it (lossless — samples a block already holds dedupe at read and compaction; a log line says so and the marker is rewritten).
• Damage is never silent. A WAL record that fails its CRC (or is cut short) with nothing valid after it is the torn tail of the write a crash interrupted: it is truncated away with a log line naming the segment file, the offset and the bytes dropped (nothing in it was ever acknowledged). A bad record with valid records after it — a flipped byte, a bad length, a zeroed page — is corruption: the open fails with corrupt data: TS WAL <file>: … at offset N …, and the engine QUARANTINES that table alone (everything else serves; restore the table's directory from a backup or peers, or DROP it) rather than replaying up to the damage and losing every record after it. A segment that is MISSING is caught the same way: wal/inventory (one checksummed line, rewritten at every seal and before a checkpoint deletes the segments it retired) names the sealed segments no finished checkpoint covers, and an open that finds one of them gone refuses with WAL file is missing — … held samples no finished checkpoint covers. To accept the loss, create an empty ACCEPT_WAL_LOSS file in the table's directory (timeseries/<db.table>/, next to wal/) and restart; a member then refills from its peers. A store written by an older release adopts the segments present at its first open and is protected from then on. Every open starts a fresh active segment; one an earlier open left EMPTY (nothing was ever acknowledged in it — an idle table's restart, or a 0-byte file a restore left) is removed at the next open, so an idle table keeps one segment, not one per restart. A committed block missing its meta.json or a data file, or with an unparsable meta.json, is corruption naming the block directory and quarantines the table the same way. Damage met while reading — a chunk reference running past the end of chunks.dat, a chunk whose bitstream ends early, an index count the file cannot hold — fails that statement with the block directory, file and offset in the error while the rest of the table keeps answering. Every I/O error from a store names the file it hit. A WAL write or sync that FAILS (an I/O error, a full disk) fails its append and ends its segment: the next append continues in a fresh segment, with every live series recorded in it again, so what is acknowledged after the error replays whatever the failed write left behind — at most a torn record at the old segment's end, truncated as above (a log line names the abandoned segment).
• At rest: with [encryption] at_rest_enabled the table's WAL segments, its blocks (chunks.dat, series.dat, meta.json) and its archived WAL segments are AES-256-GCM ciphertext — a data key per WAL segment and per block, wrapped by the node's KEK (the row engine's envelope; see CLUSTER_SECURITY.md, "At-rest encryption"). A sealed file starts with a magic, so files written before at-rest was enabled keep reading: the WAL is sealed from the next checkpoint on, a plaintext block until compaction folds it. Every WAL record is sealed bound to its offset, and the torn-tail-or-damage rule above holds unchanged with authentication in the CRC's place; block damage fails authentication and reports the block, file and offset. KEK rotation rewraps a block's envelope in place, converges the live WAL by one checkpoint and rewrites archived segments' headers — all counted in skaidb_storage_kek_stale_files. It works through a list of the stale files made when the store opens, a bounded batch every half second: a pass costs its batch whatever the table's size, waits on no lock for the blocks, and the count reaches zero only after the files themselves were listed once more to confirm it.
• A clean shutdown (cluster member or standalone server) flushes every head and feeds its rollups, so the next open rebuilds nothing from the WAL but a rollup source's open bucket; the open's head-replay time and sample count are in the skaidb: engine open took … log line.
• A head cut inside a window — the shutdown flush, the memory-pressure release, the head byte cap — rounds its boundary down to the table's rollup buckets (their least common multiple), so every bucket reaches a rollup in one piece; the open bucket stays in the head until the window flush that closes it. The memory-pressure release feeds the rollups what it flushes. The head byte cap cuts at the newest sample when the whole head is one open bucket, and an out-of-order buffer that fills flushes the whole head; either can split that bucket across two rollup partials.
• 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. A compaction round reads and writes with the store unlocked (only the block list is swapped under the lock), and costs its inputs: the duplicate rule makes it look at other blocks only when one was written between its inputs AND overlaps an older input in time — then at that block's index, and at its chunks only for series whose time ranges meet. That extra work is capped per round (16,384 such blocks; no more of their samples than the round's own inputs hold, 2M at least): a group over the cap is cut down to the inputs that are consecutive in write order, or put off for ten minutes while the planner goes on to other groups. Both are logged (`ts compaction of
  : …`), as is any round that takes over 2 s, with the number of blocks it consulted. - Cardinality cap (default 1M series/node) with per-batch accounting of out-of-order and over-limit rejections; per-table `timeseries..{series,blocks,samples_appended,samples_rejected,disk_bytes}` in `SHOW STATUS`. **SQL surface:** - `CREATE TIMESERIES TABLE (SERIES KEY (...) [, RETENTION ] [, OOO ])` — `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 SET (retention = | ooo = )`** — 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..samples_rejected` in `SHOW STATUS` tracks the cumulative total per node. - **A sample at a timestamp its series already holds replaces it — the last write wins** — and counts in `affected` like any other sample. At the series' newest timestamp that needs no `OOO` window; below it the window decides, as for any late sample (outside it the write is dropped and `affected` says so). The replacement holds in every layer: the head applies it at once, WAL replay re-applies writes in the order they were made, a flush writes the replaced value, and where two blocks hold a sample for the same timestamp the block written later wins — reads, anti-entropy summaries and compaction resolve by that one order (a compaction output keeps the newest write order of its inputs, so a merge never revives a replaced value). Blocks that record no write order — a store that predates the rule holds them — resolve among themselves by their place in the table (first timestamp, then sequence, the later one winning), in reads and in their own compactions, and every block that records one is newer than all of them: such a store answers what it always answered. "Last" is arrival order on each replica. A hinted write and a repair push are REPLAYS — writes made earlier, delivered now: they fill what the replica lacks and outrank what its blocks hold, but where the replica's head (what it received most recently, not yet flushed) holds a different value for the timestamp the greater value stays, so a late replay never puts an older write over a newer one there. Samples carry no write stamp, so two replicas that disagree on a sample — two clients wrote the same `(series, ts)` at once through different coordinators, or a replica missed the second write and its hint — cannot be ordered after the fact: anti-entropy settles the disagreement on the **greater value** (IEEE-754 total order; NaN sorts above every number), the same on every replica, and a witness that is handed different values by two members takes the greater one too. A rollup bucket is write-once: the partial a bucket first received stands. - 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 FROM ` (optionally label-filtered/ordered) answers from the store's series label sets — no sample materialization, regardless of point count; on a cluster each member's label sets are unioned (a full copy answers alone). Label `=` filters narrow through the store's label postings, so a selective filter costs what it returns rather than the table's series count. It is charged **one examined row per series** it lists (every matching series with data still in retention, so a table whose labels churn can hold far more series than `SHOW STATUS`'s `table..series`, which counts the in-memory head only); the budget error under a tight `SET SCAN BUDGET` names that series count, not a sample count. `EXPLAIN` shows the route as `access: time-series series metadata …`. A time (`ts`) constraint forces the sample path (label sets are all-time). - **`SHOW SERIES` — the series catalog**: ```sql SHOW SERIES FROM m_org [WHERE source_type = 'agent' [AND ...]] [LIMIT 100] -- the series of one source that reported in the last 15 minutes: SHOW SERIES FROM m_org WHERE source_type = 'agent' AND source_id = ? AND last_ts >= now() - 15m ``` One row per series: the series-key columns in declared order, then any other label a series carries (a remote_write table's labels), then `first_ts` and `last_ts` — the first and last sample time the store still holds for it, so a source that went quiet shows an old `last_ts`. It reads series metadata only (head chunk ranges and block-index chunk refs, never a sample), so it costs O(series) whatever the point count, and is charged one examined row per series against the scan budget. The filter may name series-key columns and `first_ts` / `last_ts`. A label `=` pushes down to the store's label postings, so a lookup of one source's series costs that source's series, not the table's. An `AND`-combined lower bound on `last_ts` (`>=`, `>`, `=` against a constant) pushes down as well where the copy is whole (standalone, or a cluster member holding the full table): only the head and the blocks reaching the bound decide which series are listed, so series that stopped reporting earlier — a source that minted a new series per container start, say — are not collected; older blocks are consulted only to complete the listed series' `first_ts`. On a sharded cluster the bound applies at the coordinator, after the members' listings merge. `!=`, `IN`, `LIKE`, `OR` and every time predicate re-apply over each series' labels and bounds, and a sample `ts` or value predicate is refused (use `SELECT`). A series-key column named `first_ts` or `last_ts` filters as that label. `?` parameters bind. Rows are ordered by the series-key columns, then cut to `LIMIT`. A table with several value fields stores one label set per field; they fold into one row. A series whose samples have all passed `RETENTION` leaves the catalog when the newest block holding its samples is retired: retention drops whole blocks, at the first flush after a block's newest sample is older than the table's newest sample minus `RETENTION`. So a series that stopped reporting stays listed — with its old `last_ts` — for `RETENTION` plus up to the span of that block (2 h for a fresh block, longer once compaction has merged blocks). It needs `SELECT` on the table (it lists label values), unlike the name-only `SHOW` statements. On a cluster a full copy answers locally (`EXPLAIN` `cluster.fan_out`: "served from this member's series metadata, no peer request") — from the coordinator's own copy, so on a replica that is behind (missed writes not yet repaired) `last_ts` can lag the cluster. Otherwise every member is asked at once and the coordinator merges each series to the earliest `first_ts` and latest `last_ts` among its replicas, under one deadline (10 s, or the statement's if sooner). A member that stays busy through two brief retries, or runs a version without the catalog verb, is asked for its label sets instead: its series are listed with **NULL** `first_ts` / `last_ts` unless another replica reports them. A member with no answer by the deadline is skipped with a log line, as for label-DISTINCT, so on a sharded table **its series are missing**. Neither NULL times nor a missing row mean a series is dead: only a `last_ts` you can read is evidence of when it last reported. - **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=&start&end` in Prometheus's shape. **Native (sparse) histograms** ingest as the classic series a `histogram_quantile` consumes — cumulative `_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, `_sum`, `_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//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](grafana.html). - **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 per-bucket partials, whose state — not the samples they fold — is what the byte budget charges; see below). 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 > 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, and a block outside every queried window is never decoded. A read touching at most 1024 blocks caches the indexes it loads on the blocks (blocks are immutable, so once loaded they stand — a dashboard re-reading a recent window keeps them warm); a wider read loads them for that read only, so an unbounded scan of a block-heavy table adds nothing to the cache itself. Other paths do cache every block's index: anti-entropy summaries and series-label listings (repair, rebalance, the PromQL label endpoints) load and keep the index of every block they visit, so on a cluster member the indexes of a block-heavy table are typically resident whatever the reads do — resident memory that grows with the block count until compaction folds blocks together. 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 >= 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 (`_{count,sum,min,max, first,last}`) maintained automatically at every head flush (cuts inside a window stop at a bucket boundary) 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 (the same windows the evaluator folds — closed `[t−w, t]` by default, see `promql.window` below; 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. - **Aggregates over any range are bounded and cancellable**: partials are folded **streaming** — block by block, one series fragment at a time — holding aggregate state per `(series, bucket)` and never the samples, so `SELECT count(*) FROM ` with no range and no label filter is safe to run on a table of any size (it costs one read of every block, not memory proportional to it). On a **cluster member** (and for the PromQL / HTTP time-series reads on any node) the store is read with the engine lock released (the lock covers only the table lookup) and with the store's own lock held only to snapshot its block list, so concurrent writes, point reads and ingest on the member are unaffected while the aggregate reads. A **standalone** node's SQL `SELECT` (single-node servers, witnesses, the BI node) still runs the whole statement under the server's shared database lock: readers run in parallel with it, but a statement needing the exclusive lock (DDL, a write, catalog replay) waits for the fold to finish. On every node the store's own lock is held only for the snapshot, so ingest is unaffected; compaction, retention or a `DROP` meanwhile is safe (a block taken out of the list stays readable until the last reader lets go; a dropped table's readers stop with an error). The coordinator's own leg runs concurrently with its peers'. Bounds, checked before every block: **KILL QUERY**, a disconnected client and the **statement deadline** stop the fold on the coordinator, and the members serving it stop too — a member notices that its requester dropped the connection (probed every 100 ms), and serves its leg under the **requesting statement's** deadline and byte budget, carried on the request (no tighter, no looser than the coordinator's own leg: an unmetered request — the PromQL HTTP path, repair — is bounded only by the requester probe). The fold's working state (per-series partials, the in-memory head's matching samples it copies, and the samples of any series whose fragments overlap across blocks, merged at once) is charged against the statement's `storage.scan_byte_budget` on every member. Overlap is judged per series from the block indexes, so one uncompacted repair or handoff block of a single series buffers that series alone, not every series of the blocks it spans. A query whose state cannot fit — typically very many series × many buckets — fails with `time-series aggregate exceeded N bytes of working state …: add a ts range or a label filter to bound it, or raise storage.scan_byte_budget`, rather than growing; the coordinator charges the gathered union of the members' answers the same way (on a sharded table it can be several times one member's share). A member that stops its leg at those limits fails the whole statement with its reason, even when the other members would make quorum (its series would otherwise be silently missing), and is not re-asked. A killed, timed-out or over-budget aggregate fails; it does not fall back to the raw-sample path (SQL or the PromQL `*_over_time` fast path). The row budget does not apply to partials (rows examined is not what they cost), and a partials attempt that does fall back to the raw path (an ineligible shape found at run time) does not count its samples against the raw read's row budget. A raw-sample gather is capped the same way on every member, the coordinator's own leg included: past the statement's `scan_byte_budget` worth of samples (16 bytes each) it fails with `time-series read exceeded N bytes of samples on this member`. - **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 reconciles divergent series in both directions via a merge path that accepts samples of any age (fills mid-series gaps a normal append would reject): it pulls the peer's window, merges the samples it lacks and the ones it holds with a lesser value, and pushes the window back, which the peer applies as a replay (newer than its blocks; against its head the greater value stays) — so replicas that held different values for one `(series, ts)` end on the greater one. A merge frame runs on the table's own store lock with no engine lock held, the rollup backfill of a table with rollups included. The summary is folded **one series at a time** on the table's store handle, with no engine lock held (and the store's own lock only for snapshots), so a pass neither costs the whole table in memory nor blocks the member's statements or ingest 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 Range windows and the instant-selector lookback (5 minutes) end at the evaluation time `t`, which is always inside. Their **left edge** is the `promql.window` setting: | `promql.window` | Window | `count_over_time(m[5m])`, 10 s scrape | `rate(m[10s])`, 10 s scrape | Newest sample exactly 5 m old | |---|---|---|---|---| | `"closed"` (default) | `[t − d, t]` | 31 | a value (two samples) | served | | `"left_open"` | `(t − d, t]` | 30 | no value (one sample) | stale | `closed` keeps the sample exactly `d` before the evaluation time (the Prometheus 2 rule); `left_open` drops it (the Prometheus 3 rule — compare against Prometheus 3.0 or later in this mode only). The two differ only when a sample sits exactly on a window's left edge: step-aligned queries over regularly scraped data, and windows equal to the scrape interval, where `rate`, `increase` and `delta` need both edge samples. Subquery steps (`expr[range:step]`), `absent_over_time` and `timestamp()` follow the same edge.

  [promql]
  window = "closed"    # or "left_open"
  

  The key is live-mutable (`SET CONFIG promql.window = 'left_open'`, or `SKAIDB_PROMQL_WINDOW` / `--promql-window` at start) and per node: the node that receives a query decides, and its edge travels with the windowed partials the `*_over_time` fast path asks of the other members, so one query folds one rule on every member whatever their own settings. Set it on every node so every coordinator answers alike. 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.