How XTDB Works

XTDB is a database built on top of columnar storage (using Apache Arrow) and is designed to handle evolving, semi-structured data via SQL natively, avoiding the need for workarounds like JSONB columns and audit tables.

In XTDB all data, including nested data, is able to be stored in tables without classic constraints or restrictions in the range of types or shapes, and without any upfront schema definitions. This is more akin to a document database than a classic SQL database.

Additionally XTDB allows for INSERTs into tables that don’t exist until the moment you insert into them.

The only hard requirement in XTDB is that all INSERTs to any table must specify a value for the xt$id column. This is a mandatory, user-provided primary key that is unique per table (i.e. the same xt$id can be safely used in isolation across multiple tables).

For details about the exact range of supported data types, see XTDB Data Types.

Temporal columns

In addition to xt$id, which is the only mandatory column, 2 pairs of system-maintained temporal columns exist which track system time and valid time periods respectively:

SQL Column Name	XTDB Type
xt$system_from	TIMESTAMP WITH TIMEZONE
xt$system_to	TIMESTAMP WITH TIMEZONE
xt$valid_from	TIMESTAMP WITH TIMEZONE
xt$valid_to	TIMESTAMP WITH TIMEZONE

As implied by the nature of these columns, no rows written into XTDB are ever mutated directly, and only new rows can be inserted. The only exceptions to this principle are:

1. the existence of the ERASE operation, which can permanently delete entire rows for explicit data retention policies, and,

2. the need to 'close' the xt$system_to period for the now-superseded row

How temporal columns are maintained

XTDB tracks both the system time when data is inserted (or UPDATE-d) into the database, and also the valid time periods that define exactly when a given row/record/document is considered valid/effective in your application. This combination of system and valid time dimensions is called "bitemporality" and in XTDB all data is bitemporal without having to think about storing or updating additional columns.

The system time columns are useful for audit purposes for providing a stable, immutable 'basis' for running repeatable queries (i.e. queries that return the same answer today as they did last week). These columns cannot be modified. The timestamp used is governed by XTDB’s single-writer Write-Ahead Log component which is used for serial transaction processing.

The valid time columns can be updated and modified at will, but only for new versions of a given record (i.e. a new row sharing the same xt$id in the same table).

Valid time is useful for scenarios where it is crucial to be able offer time-travel queries whilst supporting out of order arrival of information, and including corrections to past data while maintaining a general sense of immutability.

Queries are assumed to query 'now' unless otherwise specified and timestamps are recorded automatically with intuitive values unless otherwise specified.

Non-valid historical data is filtered out during low-level processing at the heart of the internal design.

This allows developers to focus on their essential domain problems without also worrying about their accidental bitemporal problems.

XTDB’s approach to temporality is inspired by SQL:2011, but makes it ubiquitous, practical and transparent during day-to-day development.