Back homeUse case

HTAP & operational analytics

Point reads, writes and analytical scans on one engine, with the storage layout chosen per table instead of a second system to ETL into.

The problem

Where the usual stack breaks

01
The HTAP tax is two databases. A row store handles point reads and writes well but scans slowly; a column store scans well but is a poor fit for single-row access. So you run both and ship rows between them with CDC and ETL, paying for the lag, the duplication and the reconciliation jobs that keep them honest.
02
The copy is where correctness goes to die: the analytical side is always behind the operational side, dashboards disagree with the application, and the same question returns a different number depending on which store you ask.
03
You want one engine where each table’s layout matches how you read it, under one catalog and one SQL surface.

Architecture

How NYXDB does it

Every table declares its own storage profile: a keyed ROW table serves point access, a COLUMNAR table with codecs and indexes serves scans, and both live in the same catalog with nothing shuttling rows between them.

01
Choose layout
Point access gets a keyed table with STORAGE PROFILE ROW; scans get an append-only table with STORAGE PROFILE COLUMNAR.
02
Commit
Each statement is its own atomic write, in the write-ahead log before it is acknowledged.
03
Point access
Primary-key reads, plus POSTINGS and BLOOM indexes on non-key columns.
04
Scan analytically
GROUP BY, HAVING and COUNT(DISTINCT) over the columnar table, with MINMAX indexes pruning what is not needed.

Examples

In practice

Balances, as a versioned table with a slice per producer

CREATE TABLE balances (
  chain_id UINT16 NOT NULL,
  address STRING NOT NULL,
  token STRING NOT NULL,
  amount UINT256 NULL,
  updated_at DATETIME64(3, 'UTC') NULL,
  balance_index UINT128 NULL,
  balance_version UINT64 NULL,
  transfer_count UINT64 NULL,
  last_transfer_at DATETIME64(3, 'UTC') NULL,
  activity_index UINT128 NULL,
  PRIMARY KEY (chain_id, address, token),
  ATTRIBUTE GROUP holding (amount, updated_at, balance_index, balance_version)
    VALID BY (balance_index)
    REVISION BY (balance_version),
  ATTRIBUTE GROUP activity (transfer_count, last_transfer_at, activity_index)
    VALID BY (activity_index)
)
MODE VERSIONED
SHARD BY (address) SHARDS AUTO
ORDER BY (chain_id, address, token);

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;

Current balances of one address

SELECT address, token, amount, updated_at
FROM balances
WHERE chain_id = 1 AND address = '0x9c2e4f1a';

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

Per-table layout

ROW, COLUMNAR or HYBRID storage, chosen per table.

Atomic statements

Each statement is its own atomic write, durable before it is acknowledged.

Column reducers

Decide how successive writes combine, with REDUCER SUM, MIN, MAX or REPLACE.

Three index structures

MINMAX for ranges, BLOOM for membership, POSTINGS for exact keys.

One catalog

Operational and analytical tables share one schema and one SQL surface.

No ETL

Analytical tables are derived from operational ones inside the engine, by transformations.

Learn more

Storage

Storage profiles and codecs.

CREATE TABLE

MODE, STORAGE PROFILE, codecs and reducers.

Indexes

MINMAX, BLOOM and POSTINGS.

One engine for writes and analytics

Model an operational table and an analytical table, and query both.

Get startedRead the docs