Versioned tables

A VERSIONED table keeps one current row per primary key and retains every accepted version beside it. Two optional clauses declare how versions of a key are ordered, so the current row is the same whatever order the writes arrive in.

Choosing the current row

For each key the engine keeps the version that is greatest under this order, compared left to right:

1. the effective order, from the VALID BY (…) columns: where a value takes effect;
2. the revision order, from the REVISION BY (…) columns: which recording wins at the same effective position;
3. the database commit order, which always breaks a remaining tie.

An empty binding falls through to commit order, so a table with neither clause keeps the last committed write. Writes delivered in order, shuffled, or late converge to the same current row and the same retained history.

The columns named by VALID BY and REVISION BY are non-null integers or timestamps, and every write supplies them.

The holding group of balances is ordered by balance_index (where the balance takes effect, for example a ledger position) and balance_version (which recording wins at the same position):

UPSERT INTO balances (
  chain_id, address, token, amount, updated_at, balance_index, balance_version
)
VALUES (1, '0x9c2e4f1a', 'USDC', 100, '2026-10-01 12:00:00.000', 100, 1)
ATTRIBUTE GROUP holding;

Four versions of one key arriving in this order:

VALID BY and REVISION BY also apply on UPSERT tables, where they decide which write is the latest without retaining history. An attribute group declares its own VALID BY and REVISION BY, which order only that group's writes.

What a VERSIONED table retains

UPSERT keeps the latest row per key only. VERSIONED keeps the current row and every accepted version. An index declares which of the two it covers with SCOPE CURRENT, SCOPE HISTORY or SCOPE BOTH; see Indexes.

Reading at a commit: AS OF COMMIT

AS OF COMMIT <n> returns the rows as they stood at commit n. It is written directly after the relation name, or after the WHERE, GROUP BY and ORDER BY clauses, before LIMIT.

SELECT address, token, amount
FROM balances AS OF COMMIT 42
WHERE chain_id = 1;

The read returns each key's winner at that commit, under the current schema; columns added later read at the value they were introduced with. The operand is a commit sequence, a positive integer.

Related pages

• Tables for the three modes.
• CREATE TABLE for the full grammar.
• SELECT for where AS OF COMMIT fits in a query.
• Consistency for what a commit means.