Table models

NYXDB has three table models. The choice changes write semantics and read shape; it is not merely a storage optimization.

Append tables

Append tables retain every committed row. Use them for event logs, telemetry, trades, audit history, and transform sources.

CREATE TABLE trades (
  id UInt64 NOT NULL,
  market String NOT NULL,
  amount UInt64 NOT NULL,
  ts DateTime64(3, 'UTC') NOT NULL,
  PRIMARY KEY (id)
) ORDER BY (id)
  SETTINGS mode = 'append', storage_policy = 'default';

A primary or order key controls sorting, pruning, and identity metadata. It does not turn the table into an upsert table.

Keyed tables

Keyed tables retain the latest visible value for each primary key. An UPDATE or repeated INSERT changes current state; DELETE writes a tombstone.

CREATE TABLE balances (
  account String NOT NULL,
  balance Decimal(20, 2) NOT NULL,
  PRIMARY KEY (account)
) SETTINGS mode = 'keyed', storage_policy = 'memory_data';

Keyed reads merge the committed tail and immutable parts by typed primary key and sequence. Counts are exact over the visible latest-key set.

Use a custom policy whose nested delta object sets keep: 'latest' when every live key must remain memory-resident while history is durable:

CREATE STORAGE POLICY pinned_latest (
  serve_pool = 'default',
  durable = {pool: 'default'},
  delta = {keep: 'latest'}
);

CREATE TABLE balances (
  account String NOT NULL,
  balance Decimal(20, 2),
  PRIMARY KEY (account)
) SETTINGS mode = 'keyed', storage_policy = 'pinned_latest';

The keep setting belongs to the storage policy. It is not a table-local DELTA(...) clause.

Attribute tables

Attribute tables store independently versioned attributes for an entity and provide current-state and temporal reconstruction.

CREATE TABLE account_state (
  account_id UInt64 NOT NULL,
  valid_at UInt64 NOT NULL,
  revision UInt64 NOT NULL,
  ATTRIBUTE (
    balance Decimal(20, 2),
    tier String
  )
) SETTINGS
  kind = 'attribute',
  storage_policy = 'memory_data',
  entity = (account_id),
  valid_by = (valid_at),
  revision_by = (revision);

entity and storage_policy are required. valid_by and revision_by name declared, non-null, comparable base columns when used. Attribute names are typed; the maximum supported attribute count is 512.

Default reads return latest state:

SELECT account_id, balance, tier
FROM account_state
WHERE account_id = 42;

Temporal reads reconstruct from attribute history:

SELECT account_id, balance
FROM account_state
FOR SYSTEM_TIME AS OF 1000;

Attributes can be added and dropped with ALTER TABLE ... ADD|DROP ATTRIBUTE. system.attributes includes live definitions and drop tombstones.

Choosing a model

Layout and sharding

layout='columnar' favors scans. layout='row' is currently available for keyed tables and favors point access. A row layout combined with a policy whose nested delta object keeps the latest key in memory is rejected. SHARD BY expression SHARDS n creates process-local shards; it does not distribute the table across nodes.

Always confirm the rendered catalog definition:

SHOW CREATE TABLE balances;
DESCRIBE balances;