Time-series

A series is a table that knows two things an ordinary table does not: which instant each record belongs to, and how far back an answer reaches. From those two the store can put a late event in its place, fill a gap you asked it to fill, find the newest reading per sensor with one seek each, pair every event with the reading that was current when it happened, and keep hourly totals in the same transaction as the raw write.

Everything on this page was run against node 0.14.0-beta.

Declaring one

DEFINE SERIES readings RETAIN 30d TIME at;

TIME at names the field that says when the event happened. Each record's identity is made from its own at, so the table is kept in event-time order however the events arrive. A reading written late, from a backfill or a replay, lands where it belongs:

CREATE readings = { sensor: 'boiler', v: 71.5, at: datetime '2026-09-29T10:00:00Z' };
CREATE readings = { sensor: 'boiler', v: 73.0, at: datetime '2026-09-29T12:10:00Z' };
CREATE readings = { sensor: 'intake', v: 18.2, at: datetime '2026-09-29T10:20:00Z' };
CREATE readings = { sensor: 'boiler', v: 70.9, at: datetime '2026-09-29T09:30:00Z' };
SELECT sensor, v, at FROM readings;

The last reading written comes back first, because it happened first:

{ at: datetime '2026-09-29T09:30:00Z', sensor: 'boiler', v: 70.9 }
{ at: datetime '2026-09-29T10:00:00Z', sensor: 'boiler', v: 71.5 }
{ at: datetime '2026-09-29T10:20:00Z', sensor: 'intake', v: 18.2 }
{ at: datetime '2026-09-29T12:10:00Z', sensor: 'boiler', v: 73.0 }

Many events may share one instant; each is its own record. A series holds instants from 1970 on, and an earlier one is refused.

The identity belongs to the store. Naming one by hand is refused, and so is changing a record's at, because the identity would stop describing it. Other fields change as they would on any table:

UPDATE readings:uuid '01a0ec9b-5d00-7000-9891-000000000000' SET at = datetime '2026-09-29T11:00:00Z';

Refused — SeriesTimeFixed:

at fixes where a record of series readings sits, so it cannot change — delete the record and write it again

An event without its time is refused, because the store would have to guess where it goes:

CREATE readings = { sensor: 'boiler', v: 1.0 };

Refused — SeriesTimeMissing:

series readings is ordered by at, and this record's at is absent rather than a datetime

Leave TIME off and the series is ordered by arrival instead: the identity is the moment of the write. That is the right shape when arrival is the event, such as a log of requests. It is the wrong shape when events can arrive late, because the floor would then be about when a record reached the store rather than when it happened.

The retention

RETAIN is required and has no default. A retention the store guessed would drop records at a boundary nobody chose. A retention that could only ever empty the answer is refused where it is written:

DEFINE SERIES empty RETAIN 0s;

a retention of 0s leaves nothing to answer with; a floor is a positive duration

INFO FOR TABLE readings writes the declaration back, with the duration normalised: DEFINE SERIES readings RETAIN 720h TIME at;.

Appending events from a service

Most events come from a program rather than a prompt, so a batch has its own route: POST /series/{namespace}/{database}/{series}. The body is one TessariQL array of objects of literals. The batch is written in one transaction, so every event lands or none does, and the answer says how many landed:

POST /series/plant/metrics/readings
[{ 'sensor': 'boiler', 'v': 71.5, 'at': datetime '2026-09-29T10:00:00Z' },
 { 'sensor': 'intake', 'v': 18.2, 'at': datetime '2026-09-29T10:20:00Z' }]

200 {"appended":2}

Each event goes through the caller's own session, so grants, tenancy, the series' own checks and its rollups apply exactly as they would to a script. A table that is not a series and a table that is not there both answer 404. The full contract is on HTTP.

Every client has a typed method for it. The method renders the events for you and refuses a kind an event cannot carry, such as bytes or a non-finite float, before anything is sent:

let landed = Operations::at("127.0.0.1:8000")
    .series("plant", "metrics", "readings")
    .append(&events)
    .await?;

landed = node.append("plant", "metrics", "readings", events)

const landed = await node.append('plant', 'metrics', 'readings', events);

landed, err := node.Append("plant", "metrics", "readings", events)

val landed = HttpSurface("127.0.0.1:8000").append("plant", "metrics", "readings", events)

A batch is sent once. Appending is not idempotent: sent twice, a batch lands twice. If the connection fails after the request left, only the caller knows whether a second copy would be harmless, so no client retries on its own.

Windows

time::bucket rounds an instant down to the start of the window it falls in, so grouping by it groups by window:

SELECT count(*) AS held, time::bucket(at, 1h) AS window
FROM readings GROUP BY time::bucket(at, 1h);

Windows are anchored at the epoch, not at the first record and not at the time the query ran. Every process that asks for 1h gets the same boundaries, so two services aggregating the same data agree without arranging to.

Two window sizes are refused rather than interpreted: 0s, a window of nothing, and anything below a second, which is below the resolution.

Filling the gaps

A window with no records has no row unless you ask for one. Ask with FILL and a range. The range is required because only you know where the series is supposed to start and stop:

SELECT time::bucket(at, 1h) AS hour, count(*) AS n, mean(v) AS v
FROM readings WHERE sensor = 'boiler'
GROUP BY time::bucket(at, 1h)
FILL NULL FROM datetime '2026-09-29T09:00:00Z' TO datetime '2026-09-29T13:00:00Z';

{ hour: datetime '2026-09-29T09:00:00Z', n: 1, v: dec 70.9 }
{ hour: datetime '2026-09-29T10:00:00Z', n: 1, v: dec 71.5 }
{ hour: datetime '2026-09-29T11:00:00Z', n: 0, v: NULL }
{ hour: datetime '2026-09-29T12:00:00Z', n: 1, v: dec 73 }

note: 1 window of this answer held no records and was filled as the statement asked; its count is 0

The note is part of the answer. A filled row looks like any other row, so the store says how many it made.

A fill must be able to name its windows, so it needs exactly one time::bucket in the GROUP BY, datetimes at both ends of the range, and at most a million windows:

this FILL would answer 20995200 windows, and a read fills at most 1000000 — narrow the range or widen the window

The newest record per key

DEFINE INDEX by_sensor ON readings FIELDS sensor;
SELECT sensor, v, at FROM readings LATEST BY sensor;

{ at: datetime '2026-09-29T12:10:00Z', sensor: 'boiler', v: 73.0 }
{ at: datetime '2026-09-29T10:20:00Z', sensor: 'intake', v: 18.2 }

With an index on the key, the store takes one seek per key, starting from the newest end, rather than reading the whole series. A condition narrows what counts as newest. For example, the latest reading per sensor before 11:00:

SELECT sensor, v, at FROM readings
WHERE at < datetime '2026-09-29T11:00:00Z' LATEST BY sensor;

LATEST BY is refused on a table that is not a series, because only a series' identity says which record is newest. It is also refused beside a GROUP BY, because the two are different questions and the statement must say one.

Pairing events as of their time

ASOF JOIN pairs each record with the newest record of another series at or before it. A typical use is joining each reading to the setpoint in force when it was taken:

DEFINE SERIES setpoints RETAIN 36500d TIME at;
CREATE setpoints = { sensor: 'boiler', target: 70, at: datetime '2026-09-29T08:00:00Z' };
CREATE setpoints = { sensor: 'boiler', target: 72, at: datetime '2026-09-29T11:00:00Z' };

SELECT * FROM readings ASOF JOIN setpoints ON readings.sensor = setpoints.sensor;

{ readings: { …09:30…, v: 70.9 }, setpoints: { …08:00…, target: 70 } }
{ readings: { …10:00…, v: 71.5 }, setpoints: { …08:00…, target: 70 } }
{ readings: { …10:20…, sensor: 'intake', v: 18.2 } }
{ readings: { …12:10…, v: 73.0 }, setpoints: { …11:00…, target: 72 } }

There is one row per record on the left. A record with nothing at or before it on the right keeps its row with no partner: the intake has no setpoint, so it has no setpoints field, rather than disappearing from the answer. Both sides must be series declared with TIME, because "at or before" needs an event time on each side:

ASOF JOIN pairs records by time, and arrivals is not a series declared with TIME

Counters

A counter only goes up, until the process that keeps it restarts and it starts again from zero. Three aggregates read one correctly. Each takes the value and the instant it was observed at:

SELECT increase(n, at) AS up, delta(n, at) AS change, rate(n, at) AS per_second
FROM hits;

Over samples 10, 15, 3, 8, 20, taken ten seconds apart:

{ change: 10, per_second: dec 0.625, up: 25 }

• increase adds up the rises, and a fall counts as a reset: 10 → 15 is +5, 15 → 3 is a restart that counted 3, then +5 and +12, for 25.
• delta is the last value minus the first, with no reset handling: 10.
• rate is the increase per second between the first and last sample: 25 over 40 seconds.

The samples are ordered by the instant you pass, not by how they were stored, so these work on any table. Group by a window to get a rate per window; a window holding a single sample has no rate, and its row carries none. A float sample turns the answer into a float, and the rises are then summed exactly and rounded once, as sum sums floats (from 0.32.0-beta): rises of 0.1, 0.2 and 0.3 give 0.6, however the samples were split across shards.

Rollups kept by the writes

DEFINE ROLLUP hourly FROM readings WINDOW 1h BY sensor
  COMPUTE count(*) AS n, sum(v) AS total, min(v) AS low, max(v) AS high
  RETAIN 365d;

A rollup is a table of per-window aggregates kept by the transaction that writes the raw record, so the two never disagree. Declaring one fills it from what the series already holds. After that, every write keeps it current:

CREATE readings = { sensor: 'boiler', v: 75.0, at: datetime '2026-09-29T12:40:00Z' };
SELECT window, sensor, n, total, high FROM hourly WHERE sensor = 'boiler';

{ high: 70.9, n: 1, sensor: 'boiler', total: 70.9, window: datetime '2026-09-29T09:00:00Z' }
{ high: 71.5, n: 1, sensor: 'boiler', total: 71.5, window: datetime '2026-09-29T10:00:00Z' }
{ high: 75.0, n: 2, sensor: 'boiler', total: 148.0, window: datetime '2026-09-29T12:00:00Z' }

A late reading lands in the window it belongs to. An update or a delete recomputes its window from the raw records.

A rollup is itself a series, ordered by window, with its own retention. It can keep hourly totals for a year while the raw readings last a month: the raw series ageing past its floor does not change the rollup. LATEST BY, ASOF JOIN and FILL read it like any other series.

It keeps count, sum, min and max, the folds that merge exactly. A sum over floats is the exact total rounded once — the same bits a GROUP BY over the raw window answers, however the readings arrived. From 0.26.0-beta the row's exact sum is kept beside it for the window its key last wrote, and a reading for another window that already has a row recomputes that row from the raw readings instead; before, a run of inserts kept a running rounded total. A mean is refused, with the way to get one:

a rollup keeps count, sum, min and max, each once and under its own name — mean is not one; for a mean keep sum and count and divide

A rollup's table is written only by its series: a CREATE, UPDATE or DELETE on it is refused with hourly is a rollup, kept by the writes to its series — write the series instead.

DEFINE ROLLUP runs outside BEGIN … COMMIT, because it fills in a commit of its own after the declaration's. A write to the series that was already under way when the rollup was declared is refused with Conflict and succeeds on retry. DROP ROLLUP hourly; removes it.

Rollups that keep estimates

A rollup may keep approx_distinct and approx_quantile too. Folding such a column with its own fold merges the sketches kept beside each row, so the answer is the one the raw series gives; estimates themselves would not add up:

DEFINE SERIES visits RETAIN 30d TIME at;
CREATE visits = { page: '/', visitor: 'a', ms: 12, at: time::now() };
CREATE visits = { page: '/docs', visitor: 'b', ms: 25, at: time::now() };
DEFINE ROLLUP hourly_pages FROM visits WINDOW 1h BY page
  COMPUTE count(*) AS n, approx_distinct(visitor) AS users, approx_quantile(ms, 0.99) AS p99
  RETAIN 365d;
SELECT page, approx_distinct(users) AS users FROM hourly_pages GROUP BY page;

The floor

Past the retention, a read is not answered with the record, whether or not its bytes are still there. For a series declared with TIME the floor is about the event: a reading whose at is already past it is refused rather than written and never read.

CREATE readings = { v: 1, at: datetime '2020-01-01T00:00:00Z' };

Refused — BelowSeriesFloor:

series readings no longer answers for 1577836800.000000000, which is past its retention — the record would be written and never read

The floor is a position in the key, not a predicate over a field, so a read starts after it instead of filtering past it. Naming an aged record directly does not step around it either: a floor that a direct address could bypass would not be a floor.

Removing what aged

The node removes what the floor has hidden on its own housekeeping cadence, and it does so as one range removal per series, whatever the number of records below the floor. A million aged points go in milliseconds. Their index entries go with them. A reader that began before the pass keeps every record its own floor still answers with.

The removal is each node's own storage work, like reclaiming old versions. It is not a write: nothing is added to the log and nothing appears on the change feed. The answer already changed when the floor passed, so there is no change left to announce. A consumer that mirrors a series applies the same retention on its side.

How much a point costs

A series is stored compressed, and the log that is the store's backup is compressed from the moment it is written. On 0.14.0-beta, a million regular readings of a sensor name, a float and an instant take about 28 bytes per point on disk, all copies included, where 0.13.1-beta took about 148.

Dropping one

DROP SERIES readings;

The table goes, and so do the records past the floor that reads had stopped answering with. DROP SERIES refuses a table of another kind rather than dropping it, and refuses a series that still has rollups:

rollups are kept of series readings — drop them first

Reading a stretch of time

A condition on the time field reads a stretch of a series:

DEFINE INDEX by_at ON readings FIELDS at;
SELECT * FROM readings
WHERE at >= datetime '2026-09-29T10:30:00Z' AND at < datetime '2026-09-29T12:00:00Z';

On 0.14.0-beta that condition is served by an index on the field, as above, and without one it reads the whole series. The series' own order is not yet used to start the read at the first instant. Declare the index on any series where range reads must stay cheap as it grows.

Retention over a table that is not a series

A table with no declared floor keeps everything until a statement removes it:

DELETE FROM readings WHERE at < datetime '2026-03-01T01:00:00Z' LIMIT ALL;

It reports how many records it removed, so the pass can be checked rather than assumed. The difference from a floor is who has to remember. A statement is something an operator runs on a schedule. A floor is a property of the table that every read applies, whether anyone remembered or not.