DB
Back homeUse case

Product & event analytics

Ingest clickstream and product events, sessionize and roll them up with continuous SQL, and serve live product metrics from tables that are never more than microseconds behind the source.

The problem

Why this is hard today

Product analytics runs two clocks at once: a firehose of clickstream and event data on the write side, and a wall of funnels, retention curves, and live metric tiles on the read side — each of which a warehouse recomputes on a schedule, so the numbers a team acts on are minutes stale.

The usual stack bolts an event pipeline onto a stream processor for sessionization, a warehouse for funnels, and a cache for the live tiles — three systems that disagree the moment an event lands, and a maintenance burden that grows with every new metric.

What product analytics needs is one engine that absorbs events at line rate, maintains sessionized rollups continuously, and serves the resulting metrics as ordinary keyed reads — fresh, exact, and without a separate serving store.

Architecture

How NYXDB does it

Events append durably at line rate; continuous transforms maintain windowed, sessionized rollups; and PSI routing keeps the serving tables a flat ~2.56µs behind the source (ADR-080), so live product metrics read as ordinary keyed lookups.

  1. 01

    Ingest events

    Clickstream and product events append to a durable, WAL-backed table under the memory governor.

  2. 02

    Sessionize & roll up

    CREATE TRANSFORM maintains windowed rollups (tumble/hop) and per-key session state incrementally (ADR-080).

  3. 03

    Propagate (PSI)

    Only matching changes route to each transform — a flat ~2.56µs event→subscriber path, not a periodic re-scan.

  4. 04

    Serve metrics

    Dashboards read the keyed metric tables as O(1) point/range reads; STREAM SELECT pushes live updates.

Real SQL

In practice

Continuously-materialized product metrics
CREATE TABLE product_metrics (
  project String NOT NULL,
  event   String NOT NULL,
  events  UInt64,
  PRIMARY KEY (project, event)
) SETTINGS mode='keyed', layout='row', storage_policy='memory_data';
CREATE TRANSFORM roll_events
  INTO product_metrics AS
SELECT project, event, count() AS events
FROM events
GROUP BY project, event;
Live metrics for a project — point reads
SELECT event, events FROM product_metrics WHERE project = 'web';
Push metric updates to the client
STREAM SELECT event, events FROM product_metrics WHERE project = 'web';

Every statement follows the engine’s own test SQL shapes. See the SQL reference for full syntax.

Capabilities

What you get

Continuous rollups

CREATE TRANSFORM keeps event metrics fresh (ADR-080).

~2.56µs propagation

Flat event→subscriber latency, empty to a 2M-row tail.

Point-read serving

Metric tiles read pre-aggregated keyed tables in O(1).

Exact counts

Funnel and event counts are exact — never sampled.

Windowed sessions

tumble/hop windows sessionize events continuously.

Live push

STREAM SELECT drives real-time metric tiles without polling.

Proof

Measured on the vetted benchmark lane

~2.56µs

event → serving-table propagation, flat

empty to a 2M-row tail

vetted table

View benchmark268ns

keyed metric read, O(1)

flat 1k–16k keys

vetted table

View benchmark
gap-free

transform recovery from reflected position

ADR-080

Measured on AMD EPYC 9554P 64-Core Processor (64t), 1505GB RAM, 6x NVMe — measured on server-class hardware. Release build, median of 5 (re-captured stragglers: median of 3), commit-pinned (7945ae33, 2026-07-09). Ingest figures are engine-side. See the full benchmark suite.

Learn more

Related documentation

Transforms

Continuous materialized views.

Streaming reads

Push live updates to clients.

Getting started

Run a node in minutes.

Product metrics that are live, not batched

Materialize an event rollup and stream it to the dashboard.

Get started Read the docs