Tables
Append, keyed, and attribute tables — three engines for arrival order, current state, and temporal reconstruction.
NYXDB offers three table shapes. Each answers a different question about your data, and all three live in one catalog.
Append tables
Append tables keep every row in arrival order. They are the base for history and for feeding streams and transforms.
CREATE TABLE trades (
id UInt64 NOT NULL,
market String NOT NULL,
amount UInt64 NOT NULL,
PRIMARY KEY (id)
);Use append tables when you need the full sequence of events — audit logs, tick history, raw ingestion.
Keyed tables
Keyed tables collapse to the latest value per primary key. "What is true right
now" becomes a point read instead of a scan over history. DELTA (keep = …)
controls the collapse:
keep = 'latest'— pin the latest value per key (current-state table).keep = 'none'— keep no delta history beyond what compaction retains.
CREATE TABLE balances (
address String NOT NULL,
balance UInt256,
PRIMARY KEY (address)
) DELTA (keep = 'latest');Keyed tables give O(1) current state per key and exact counts — no probabilistic sketches on the critical path. Reads reflect the writes that just landed (read-your-writes).
Attribute tables
Attribute tables model each attribute as its own relation, then reconstruct
entity state as of any point in time. They power historical AS OF queries and
wide projections.
CREATE TABLE acct (
id UInt64,
ATTRIBUTE (bal UInt64, tier String)
);Attributes can be added or dropped after creation:
ALTER TABLE acct ADD ATTRIBUTE score UInt64;Reconstruct state at a point in time with a temporal read:
SELECT * FROM token_state FOR SYSTEM_TIME AS OF 42;The settings bag
CREATE TABLE … SETTINGS (…) tunes storage and behavior. Common keys (all
observed in engine tests):
| Setting | Purpose |
|---|---|
storage_policy | Named policy: memory_data, disk_data, default, tiered, … |
shard_by / shards | Sharding column and shard count |
flush_rows / flush_bytes | Delta flush triggers |
index_granularity | Granule width for skip indexes |
state_ttl / ttl | Retention |
durability | Journaling / ack semantics |
layout | Row vs columnar tail layout |
CREATE TABLE a (x Int64) SETTINGS storage_policy = 'memory_data';See Storage model for policies and the delta/parts lifecycle, and the SQL reference for the full grammar.
Choosing an engine
| You need | Use |
|---|---|
| Every event, in order | Append |
| Latest value per key, fast + exact | Keyed |
| State as of a point in time | Attribute |