Table Types in AetheriusDB

ReferenceData ModelTables

This page is the consolidated list of table types in AetheriusDB. Each type has its own CREATE statement and its own reason to exist. If you only need ordinary rows and columns, use a standard table — everything else on this page is an optimization for a specific access pattern.

What types of tables does AetheriusDB support?

AetheriusDB supports these table types:

Table type	DDL	Use it for
Standard table	CREATE TABLE	Ordinary relational data — the default
Pulse table	CREATE PULSE TABLE	Latest-state-only data, where each write replaces the last
Cascade table	CREATE CASCADE TABLE	Derived tables that recompute automatically when their source changes
Group table	CREATE GROUP TABLE	One logical table spanning several physical tables
Pivot table	CREATE PIVOT TABLE	Materialized cross-tabulations (rows × columns × measures)
Materialized view	CREATE MATERIALIZED VIEW	A stored snapshot of a query result
Property graph	CREATE PROPERTY GRAPH	Node/edge graph structure over existing tables
Stream source	CREATE STREAM SOURCE	Streaming topics exposed as queryable virtual tables

Two further properties — temporal (system-versioned) and pinned — are not separate table types. They are settings applied to a table you already have.

What is a standard table in AetheriusDB?

A standard table is created with CREATE TABLE and behaves the way a relational table does in any SQL database: typed columns, PRIMARY KEY and UNIQUE constraints, NOT NULL, DEFAULT expressions, and inserts that accumulate rows.

This is the right choice unless you have a specific reason to pick another type. Every other table type on this page exists to serve one access pattern better than a standard table does; none of them replaces it as the general default.

What is a Pulse table and when should I use one?

A Pulse table in AetheriusDB keeps only the latest state. Each insert replaces the previous contents rather than appending to them, which makes the flush an O(1) operation instead of a growing write.

Use a Pulse table for current-value data where history has no value: live device readings, current session state, a most-recent-price table, or a dashboard snapshot that is fully rewritten on each refresh. Do not use one where you need to query past rows — a Pulse table does not retain them.

What is a Cascade table?

A Cascade table is a derived table that updates itself. You define it against one or more source tables, and when any source table is written, AetheriusDB re-runs the transform and refreshes the cascade table automatically.

CREATE CASCADE TABLE daily_totals AS
  SELECT region, SUM(amount) AS total
  FROM orders
  GROUP BY region;

Use a Cascade table for rollups and aggregates you want kept current without an external scheduler or a refresh job. It replaces the common pattern of a cron task that recomputes a summary table on a timer.

What is a Group table?

A Group table presents several physical tables as one logical table. You declare the member tables, and optionally ROUTE rules that decide which member a write lands in.

CREATE GROUP TABLE orders_all FROM orders_us, orders_eu;

Use a Group table when data is physically partitioned — by region, tenant, or time period — but you want to query it as a single table. It gives you one query surface without merging the underlying tables or maintaining a union view.

What is a Pivot table in AetheriusDB?

A Pivot table materializes a cross-tabulation: rows, columns, and measures computed and stored as a physical table. It is created either declaratively (ON <source> ROWS (…) COLUMNS (…) MEASURES (…)) or from a query with CREATE PIVOT TABLE … AS SELECT ….

Use a Pivot table for reporting grids and OLAP-style summaries that are read far more often than the source data changes. Because it is materialized, reads do not recompute the pivot. AetheriusDB also has a separate CREATE PIVOT VIEW for the dynamic, non-materialized equivalent.

What is a materialized view in AetheriusDB?

A materialized view stores the result of a query as a physical table.

CREATE MATERIALIZED VIEW top_customers AS
  SELECT customer_id, SUM(total) AS lifetime_value
  FROM orders GROUP BY customer_id;

A plain CREATE MATERIALIZED VIEW is a snapshot — it holds the result as of creation and does not track later changes to the source. If you want a derived table that refreshes itself when its sources change, use a Cascade table instead. The two look similar and differ exactly on that point.

What is a property graph?

A property graph declares nodes and edges over tables you already have, so relationships can be traversed as a graph without restructuring your schema or copying data into a separate graph database.

Use it for relationship questions — shortest path, neighbourhood expansion, multi-hop traversal — over data that also has to serve ordinary SQL queries. The graph is a view of the relational data, not a second copy of it.

What is a stream source table?

A stream source exposes an external streaming topic as a virtual table:

CREATE STREAM SOURCE live_clicks FROM 'kafka://…';

Once declared, the stream is queryable like any other table and can be joined against stored tables. Use it to enrich or filter live events against reference data without first landing the stream in storage.

What are temporal (system-versioned) tables?

Temporal tables are not a separate table type — they are system versioning turned on for a table. AetheriusDB keeps prior row versions so you can query the table as it existed at an earlier time, using SQL:2011 syntax:

SELECT * FROM accounts FOR SYSTEM_TIME AS OF '2026-01-01 00:00:00';

Use system versioning for audit trails, regulatory history, and “what did this look like last Tuesday” questions — anything where the previous state of a row still matters after it has been overwritten.

What are pinned tables?

Pinned tables are also not a separate type. Pinning is a residency instruction: PIN TABLE <name> keeps a table (or a predicate-scoped subset of it) resident in memory so queries never wait on a slower storage tier. UNPIN TABLE releases it.

Use pinning for small, hot reference data — currency rates, feature flags, lookup dimensions — that is read constantly and cheap to hold in RAM.

Which table type should I choose?

Start with a standard table. Move to another type only when one of these is true:

• Only the newest value matters → Pulse table
• A summary must stay current automatically → Cascade table
• Data is split across physical tables but queried as one → Group table
• A reporting cross-tab is read far more than it changes → Pivot table
• A query result can be stale and just needs to be fast → materialized view
• The question is about relationships, not rows → property graph
• The data arrives continuously from a topic → stream source
• You need to read the past → turn on system versioning
• A small table is read constantly → pin it

Notes & caveats

CREATE MEASURE TABLE and CREATE DIMENSION TABLE are accepted by the AetheriusDB SQL parser, but as of this writing they carry no distinct runtime behaviour — the semantic table kind is recorded and nothing in the storage or query path acts on it yet. Treat them as reserved syntax, not as working features, and use a standard table instead.

Relational Model →

Standard tables, columns, types, and constraints.

Temporal Tables →

System-versioned tables and time-travel queries.

Pinned Tables →

Keeping hot tables resident in memory.

Index Types →

Every index method AetheriusDB supports.

SQL Grammar →

The authoritative DDL syntax.