DB
Back homeUse case

Real-time dashboards & user-facing analytics

Serve user-facing analytics from continuously-materialized tables. A change propagates to the serving table in microseconds, so dashboards are live — not polled.

The problem

Why this is hard today

User-facing analytics has a latency budget a data warehouse cannot meet: the dashboard has to load in milliseconds, for thousands of concurrent users, over data that changes constantly. Running the aggregation on every page load does not scale; running it on a cron and caching the result serves stale numbers.

The usual fix — a stream processor that maintains rollups into a serving store — adds a second system, a second schema, and a freshness gap between the rollup and the source that users notice the moment they act and refresh.

What user-facing analytics needs is a serving table that stays continuously fresh from the source, updated by event propagation rather than a re-scan timer, and read as an ordinary indexed table.

Architecture

How NYXDB does it

A continuous transform materializes the serving table and keeps it fresh through PSI event routing (ADR-080): a committed change propagates to the subscriber in a flat ~3.2µs regardless of table size, so the serving table is never more than microseconds behind the source.

  1. 01

    Define serving table

    A keyed table holds the pre-aggregated shape the dashboard reads.

  2. 02

    Materialize

    CREATE TRANSFORM … INTO serving AS SELECT … maintains it incrementally.

  3. 03

    Propagate (PSI)

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

  4. 04

    Serve

    Dashboards read the serving table as an ordinary point/range read; STREAM SELECT pushes live updates.

Real SQL

In practice

Continuously-materialized serving table
CREATE TABLE dashboard_tiles (
  tenant String NOT NULL,
  metric String NOT NULL,
  value  Float64,
  PRIMARY KEY (tenant, metric)
) SETTINGS mode='keyed', layout='row', storage_policy='memory_data';
CREATE TRANSFORM roll_tiles
  INTO dashboard_tiles AS
SELECT tenant, metric, sum(amount) AS value
FROM events
GROUP BY tenant, metric;
A tenant's dashboard — point reads
SELECT metric, value FROM dashboard_tiles WHERE tenant = 'acme';
Push live updates to the client
STREAM SELECT metric, value FROM dashboard_tiles WHERE tenant = 'acme';

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

Capabilities

What you get

Continuous materialization

CREATE TRANSFORM keeps serving tables fresh (ADR-080).

~3.2µs propagation

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

Point-read serving

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

Gap-free recovery

Transforms resume from their reflected position after restart.

Exact aggregates

Rollups are exact — no double-counting.

Live push

STREAM SELECT drives real-time UI updates without polling.

Proof

Measured on the vetted benchmark lane

~3.2µs

event → serving-table propagation, flat

empty to a 2M-row tail

vetted table

View benchmark230 ns

keyed serving read, O(1)

flat 1k–16k keys

vetted table

View benchmark
gap-free

transform recovery from reflected position

ADR-080

Measured on Apple M4 Max (dev), macOS — server-class validation pending. Release build, median of 5, commit-pinned (d4a3885b, 2026-07-07). 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.

Dashboards that are live, not polled

Materialize a serving table and stream it to the client.

Get started Read the docs