Relational Data Model

RelationalTyped Columns

Stable On by default · production-ready

What it is

Every table in AetheriusDB has a rigid, declared schema. Each column has a precise type, and that type is fixed for the life of the column. There are no schemaless documents, no implicit JSON-blob columns, and no “store anything” escape hatch. Before you put data into a table, you tell the engine exactly what it is.

If you come from a document store this can feel restrictive, but the payoff is immediate. Aggregate queries (SUM, AVG, COUNT, MIN/MAX), joins, and group-bys run over uniformly typed columns. The engine is never asked “does this field exist?” or “what shape is this object?” — those questions were answered once, when you declared the table.

Why it matters

• Fast aggregates — a SUM over a million DOUBLE values streams through a single typed column with no per-row shape checks.
• Efficient joins on text — text columns are compared as compact integer keys internally, so JOIN ... ON a.name = b.name does not pay a string comparison per row.
• Predictable storage — row sizes are known in advance, so layout, compression, and query plans are all deterministic.

How you use it

You define a table with declared column types — the same syntax you already know, just stricter type checking. AetheriusDB also extends the standard grammar with semantic table kinds so the planner can pre-optimize for append-only metrics versus slowly-changing reference data.

-- A standard fact table with rigid, explicit typing.
CREATE TABLE sales (
  order_id   BIGINT      NOT NULL,
  customer   TEXT        NOT NULL,
  region     TEXT,
  amount     DOUBLE      NOT NULL,
  occurred   TIMESTAMP   NOT NULL
);

-- A measure table — append-only, semi-additive metrics.
CREATE MEASURE TABLE revenue_by_day (
  day        DATE,
  region     TEXT,
  revenue    DOUBLE
);

-- A dimension table — slowly changing, time-versioned automatically.
CREATE DIMENSION TABLE customer_profile (
  customer   TEXT   PRIMARY KEY,
  tier       TEXT,
  signup     DATE
);

-- A query that runs as a tight scan over typed columns.
SELECT region, SUM(amount)
FROM sales
WHERE occurred >= '2026-01-01'
GROUP BY region;

The two specialised kinds — MEASURE TABLE and DIMENSION TABLE — tell the planner about your intent so it can pick the right physical layout, compression, and history-tracking strategy without any extra configuration.

Pulse tables — keep-latest-by-key

A PULSE TABLE holds a live latest state rather than a growing history — ideal for a “current value per sensor / per key” buffer. Its INSERT behaviour is selected by whether you declare a primary key:

-- KEYED pulse table → keep-latest-by-key (upsert).
CREATE PULSE TABLE latest_reading (
  sensor_id  BIGINT,
  reading    DOUBLE,
  PRIMARY KEY (sensor_id)
);

INSERT INTO latest_reading VALUES (1, 10), (2, 20);
INSERT INTO latest_reading VALUES (1, 99);   -- upserts key 1 only
SELECT * FROM latest_reading;                -- (1, 99), (2, 20) — key 2 untouched

• With a PRIMARY KEY — INSERT is a per-key upsert: the newest row for a key replaces the previous one, and keys you don’t touch are left alone. A composite key keeps the latest per column-tuple.
• Without a key — the original whole-table replace semantics: each INSERT swaps the entire contents (the snapshot-buffer mode used by reactive cascades).

Constraints & referential integrity

Declare keys and relationships with standard SQL, and the engine enforces them on writes:

CREATE TABLE customers (
  id     BIGINT PRIMARY KEY,          -- one primary key per table
  email  TEXT UNIQUE
);

CREATE TABLE orders (
  id           BIGINT PRIMARY KEY,
  customer_id  BIGINT REFERENCES customers(id),   -- column-level FK
  total        DECIMAL(12, 2),
  FOREIGN KEY (customer_id) REFERENCES customers(id)  -- or table-level
);

• PRIMARY KEY / UNIQUE are enforced on INSERT and COPY (a duplicate is rejected). Declaring more than one PRIMARY KEY on a table is an error.
• FOREIGN KEY is enforced on INSERT, COPY, UPDATE, and DELETE:
  • inserting or updating a child row whose key has no matching parent is rejected;
  • deleting (or key-changing) a parent row that a child still references is rejected. The behaviour is RESTRICT — there is no ON DELETE CASCADE.
  • A NULL foreign-key column is exempt (SQL MATCH SIMPLE).

Two boundaries to know

• UPDATE does not re-check UNIQUE/PRIMARY KEY. An UPDATE that moves a row onto an existing unique value is not currently rejected (foreign keys are re-checked on UPDATE). Uniqueness is enforced on INSERT and COPY.
• A FOREIGN KEY that names a parent table which doesn’t exist yet is stored but silently not enforced — declare parent tables before the tables that reference them.

Extended column types: UUID and JSONB

Beyond the primitive types, two document/identity types are first-class and survive restart:

CREATE TABLE events (
  id       UUID PRIMARY KEY,     -- 16-byte UUID
  payload  JSONB                 -- binary JSON document
);

INSERT INTO events VALUES
  ('550e8400-e29b-41d4-a716-446655440000', '{"kind":"login","ok":true}');

-- JSONB path / containment operators:
SELECT payload ->> 'kind'          AS kind      -- text at a key
FROM   events
WHERE  payload @> '{"ok": true}';               -- containment

• UUID accepts a string literal (dashed 8-4-4-4-12 or 32-hex) and round-trips in canonical dashed form. There is no server-side generator function — supply the value from your application.
• JSONB (binary) and JSON (exact text) support the operators ->, ->>, #>, #>> (key / path access), @>, <@ (containment), and ?, ?|, ?& (key existence), plus ::jsonb / ::json casts. The jsonb_* builder / mutator functions are not yet available — the operator set above is the supported surface.

Key concepts

Concept	What it means
Typed column	Every column has a fixed, declared primitive type — no untyped fallbacks.
Measure table	Append-only numeric facts (metrics, ticks, events) — aggregated, never updated.
Dimension table	Slowly-changing reference data — history is tracked automatically.

Tip

If you have semi-structured data you really need to keep, store it in a TEXT column and extract the meaningful fields into typed columns at ingest. You lose typed-scan speed over the blob itself, but keep it for everything else.

Heads up

There is no implicit “any” type. Handle schema drift in your source data at the ingest boundary, not by weakening column types — the engine assumes a column’s values always mean exactly one thing.

ANSI SQL Compliance →

The standard SQL surface plus native extensions.

SQL Grammar & Types →

The exact type system, CTEs, window functions, and NULL semantics.

Hardware-Aware Types →

Specialised column types for trends, tags, and geo.