Back homeUse case

Time-series & monitoring

Store series compactly, aggregate them over time windows as they arrive, and keep per-series rollups current, all in the same SQL.

The problem

Where the usual stack breaks

01
Time-series work wants two things at once: raw resolution to investigate and coarse aggregates to watch. Meeting both usually means a hot store for recent data, a rollup job that downsamples into a warehouse, and glue between them. Several moving parts around one stream.
02
The rollup job is a stream processor in disguise. It exists only because the store cannot aggregate its own data as it arrives.
03
You want an engine that stores readings compactly, aggregates them over windows as they arrive, and keeps per-series summaries current in the same SQL.

Architecture

How NYXDB does it

Raw readings land in a columnar table with delta-style codecs on the time column; STREAM SELECT over TUMBLE, HOP or SESSION windows emits each aggregate when its window closes; grouped transformations keep per-series totals in a keyed table.

01
Ingest raw
Readings land in a COLUMNAR table; DoubleDelta and ZSTD codecs compress the time and value columns.
02
Window
Aggregate by event time or processing time over HOP or TUMBLE windows in STREAM SELECT, and release each window once with EMIT AFTER WINDOW CLOSE.
03
Summarize
Per-series counts, sums, minimums and maximums stay current in a keyed table, maintained by a grouped transformation.
04
Prune
Scans over history skip what they do not need, thanks to a MINMAX index on the time column.

Examples

In practice

The transfer feed, as an append-only table

CREATE TABLE transfers (
  chain_id UINT16 NOT NULL,
  token STRING NOT NULL,
  sender STRING NOT NULL,
  recipient STRING NOT NULL,
  amount UINT256 NOT NULL,
  transferred_at DATETIME64(3, 'UTC') NOT NULL,
  _index UINT128 NOT NULL,
  INDEX transfers_ts (transferred_at) USING MINMAX SCOPE HISTORY GRANULE 8192
)
MODE APPEND
SHARD BY (token) SHARDS AUTO
PARTITION BY MONTH(transferred_at)
ORDER BY (chain_id, token, _index)
STORAGE PROFILE COLUMNAR;

Transfers per minute, as a windowed query

STREAM SELECT window_start, window_end, token, COUNT() AS transfers
FROM TUMBLE(transfers, transferred_at, INTERVAL 1 MINUTE)
GROUP BY window_start, window_end, token
EMIT AFTER WINDOW CLOSE;

Live volume per token, as a query that stays open

STREAM SELECT chain_id, token, SUM(amount) AS volume, COUNT(*) AS transfers
FROM transfers
GROUP BY chain_id, token;

The examples use the same two tables on every page. See the SQL reference for full syntax.

Capabilities

What you get out of the box

Columnar series

Compact storage with per-column codecs, with STORAGE PROFILE COLUMNAR.

Tumbling, hopping, session windows

By event time or processing time, with TUMBLE, HOP or SESSION in STREAM SELECT.

EMIT control

Each window is released once, when it closes, with EMIT AFTER WINDOW CLOSE.

Per-series rollups

Summaries stay current in a keyed table, maintained by grouped transformations.

MINMAX pruning

Range predicates on time skip the parts that cannot match.

Live aggregates

Incremental changes arrive as commits land, from a grouped STREAM SELECT.

Learn more

Streaming

Windows and EMIT.

Storage

Profiles and codecs.

Functions

Aggregate and date/time functions.

One engine for raw series and their aggregates

Ingest, window and summarize, all in SQL.

Get startedRead the docs