Timeseries Engine

Timeseries is a peer engine sharing storage with columnar and spatial. It adds retention policies, continuous aggregation, ILP ingest, and dedicated time-series SQL functions. Data is stored in the same compressed-column memtables with a TIME_KEY column driving partition-by-time and block-level skip.

Selected via WITH (engine='timeseries') on CREATE COLLECTION, or the CREATE TIMESERIES <name> convenience alias.

DDL

CREATE COLLECTION cpu_metrics (
    ts TIMESTAMP TIME_KEY,
    host VARCHAR,
    region VARCHAR,
    cpu_usage FLOAT,
    mem_usage FLOAT
) WITH (engine='timeseries', partition_by='1d', retention='90d');

-- Convenience alias
CREATE TIMESERIES cpu_metrics;

Partitions

Rows land in an in-memory buffer first. A flush of that buffer writes one sealed partition for each partition_by interval its rows fall in. The partitions of one flush appear together: a crash leaves all of them or none.

• ALTER TIMESERIES <name> SET (partition_by = '…') applies to later flushes only. A partition already on disk keeps the interval it was cut at.
• A late row, one whose interval an earlier flush already sealed, forms a new partition in that interval. A sealed partition is never rewritten to take it. The maintenance tick later merges the partitions of that interval (see Merging).

-- List the partitions of a timeseries collection
SHOW PARTITIONS FOR cpu_metrics;

-- Rewrite every sealed partition with the current schema and codecs
REWRITE PARTITIONS FOR cpu_metrics;

SHOW PARTITIONS FOR returns one row per partition:

REWRITE PARTITIONS FOR rewrites each sealed partition and drops every column the collection no longer has. The rows do not change. The statement returns after the last partition is rewritten and reports how many partitions it rewrote. It needs the ALTER privilege on the collection.

Both statements read the collection in the current database. Neither runs inside a transaction. SHOW PARTITIONS ON <stream> is a different statement: it lists a change stream's partitions (see Consumer Groups).

Merging

Late rows leave an interval with several partitions that overlap in time. The maintenance tick merges the partitions of one interval into one Merged partition. A merge moves rows and never changes them.

• A merge runs on the core that owns the collection, and on each replica for its own partitions. Partition names differ between replicas. Rows do not.
• An interval merges once it ended merge_quiet_ms ago, so late rows still arriving for it flush first. COMPACT <name> runs every merge at once, including the merge of an interval that has not ended.
• A merged partition stays within one interval, so retention removes its rows at most one interval later than it would remove the separate partitions. On a bitemporal collection, partitions whose system times lie more than one interval apart do not merge.
• A UNBOUNDED collection has no interval to merge within. Its partitions do not merge.
• A crash at any point leaves every row in exactly one partition. Restart finishes a merge that committed and discards one that did not.
• Encrypted partitions merge with the segment key, and the merged partition is encrypted with it.

A merge keeps every continuous aggregate exact. It waits while an aggregate has folded some partitions of the interval and not others:

• An on_flush or on_seal aggregate folds the missing partitions on the next tick, and the interval merges after that.
• An interval-refreshed or manual aggregate folds them at its next refresh. A manual aggregate that is never refreshed keeps such an interval unmerged.

Queries

-- Time-bucketed aggregation
SELECT time_bucket('5 minutes', ts) AS bucket, host, AVG(cpu_usage) AS avg_cpu
FROM cpu_metrics
WHERE ts > now() - INTERVAL '1 hour'
GROUP BY bucket, host ORDER BY bucket DESC;

-- Approximate aggregation (mergeable across shards)
SELECT approx_count_distinct(host), approx_percentile(cpu_usage, 0.95)
FROM cpu_metrics WHERE ts > now() - INTERVAL '24 hours';

Continuous Aggregation

A continuous aggregate folds new rows into per-bucket state. It never re-scans the source.

CREATE CONTINUOUS AGGREGATE cpu_hourly ON cpu_metrics
  BUCKET '1h'
  AGGREGATE avg(cpu_usage) AS avg_cpu, ts_percentile(cpu_usage, 0.99) AS p99_cpu
  GROUP BY host
  WITH (refresh_interval = '1m', retention = '365d');

REFRESH CONTINUOUS AGGREGATE cpu_hourly;

BUCKET sets the bucket width. Do not use time_bucket(...) in the definition.

Refresh behavior:

• on_flush and on_seal fold each flushed partition once.
• An interval refresh_policy or refresh_interval folds on the maintenance tick at that interval.
• manual folds only on REFRESH CONTINUOUS AGGREGATE. A read serves the last refresh.

Aggregate state survives restart. It keeps its history after raw retention removes the source rows.

SELECT * FROM cpu_hourly returns one row per bucket and group. Columns are bucket, the GROUP BY columns, then the outputs. The aggregate is read-only.

Timeseries SQL Functions

ILP Ingest

Enable with ports.ilp = 8086. Any ILP-compatible client (Telegraf, Vector) pushes metrics directly:

echo "cpu,host=web-01 usage=72.5 1609459200000000000" | nc localhost 8086

Adaptive batching and per-series core routing — self-tuning, no configuration needed.

Grafana / PromQL

NodeDB works as a native Grafana Prometheus data source at http://nodedb:6480/v1/obsv/api. Full PromQL engine (Tier 1+2+3 functions). Also supports Prometheus remote write/read for long-term storage.