If you don’t already have XTDB running, follow the brief install steps described in Installation via Docker.
To run your first INSERT transaction, simply enter the following SQL statement into the psql prompt, press return, and you will see:
user=> INSERT INTO people (_id, name) VALUES (6, 'fred');INSERT 0 0Note that XTDB doesn’t currently return information about the number of rows inserted or modified by a statement when using psql.
Things to note:
INSERT, where all columns and types are inferred automatically._id primary key column, but all other columns are optional and dynamically typed by default. This means each row in XTDB can offer the flexibility of a document in a document database._ prefix convention (e.g. _id) is used for reserved columns and tables that XTDB handles automatically. For full details see How XTDB works.Querying this data back again is a simple matter of:
user=> SELECT * FROM people; _id | name-----+------ 6 | fred(1 row)If we now INSERT another row with a slightly different shape, we can see that XTDB automatically handles the implicitly extended schema and allows us to return both rows:
user=> INSERT INTO people (_id, name, likes) VALUES (9, 'bob', ['fishing', 3.14, {nested:'data'}]);INSERT 0 0user=> SELECT * FROM people; _id | likes | name-----+-------------------------------------+------ 6 | | fred 9 | ["fishing",3.14,{"nested":"data"}] | bob(2 rows)XTDB is designed to work with JSON-like nested data as a first-class concept (i.e. not restricted to JSON or JSONB types). This means you can easily handle complex document-like nested data. Here the RECORDS syntax, combined with the ‘upsert’ behavior of the INSERT, avoids the chore of having to be explicit about all the individual columns involved:
user=> INSERT INTO people RECORDS{_id: 6, name: 'fred', info: {contact: [{loc: 'home', tel: '123'}, {loc: 'work', tel: '456', registered: DATE '2024-01-01'}]}};INSERT 0 0You can then query this nested data intuitively:
user=> SELECT (people.info).contact[2].telFROM people WHERE people.name = 'fred'; tel----- 456(1 row)You can also observe the inferred schema using the SQL standard’s INFORMATION_SCHEMA facilities:
user=> SELECT * FROM information_schema.columns; column_name | data_type | table_catalog | table_name | table_schema-------------+---------------------------------------------------------------------------------------------------------------------------------------------+---------------+------------+-------------- system_time | [:timestamp-tz :micro "UTC"] | xtdb | txs | xt committed | :bool | xtdb | txs | xt _id | :i64 | xtdb | txs | xt error | [:union #{:null :transit}] | xtdb | txs | xt _id | :i64 | xtdb | people | public name | :utf8 | xtdb | people | public likes | [:union #{[:list [:union #{:f64 :utf8 [:struct {nested :utf8}]}]] :null}] | xtdb | people | public info | [:union #{[:struct {contact [:union #{:null [:list [:struct {loc :utf8, registered [:union #{[:date :day] :null}], tel :utf8}]]}]}] :null}] | xtdb | people | public(8 rows)Capturing fast-changing data may be powerful, but what if we made a mistake somewhere and wanted to undo a change? The thing that makes XTDB most interesting is the approach to immutability and time-travel…
XTDB provides a Postgres wire-compatible endpoint that enables developers to re-use many existing tools and drivers that have been built for connecting to real Postgres servers.
A key distinction between interacting with Postgres and interacting with XTDB (e.g. using psql or otherwise) is that all clients connected to XTDB operate in a ‘stateless’ manner that:
The complete transaction log history is permanently stored within the system-maintained xt.txs table:
user=> SELECT * FROM xt.txs ORDER BY _id DESC LIMIT 20; _id | committed | error | system_time------+-----------+-------+------------------------------- 2722 | t | | 2024-07-15T12:43:27.345281Z 1341 | t | | 2024-07-15T12:38:12.750543Z 0 | t | | 2024-07-15T12:36:31.310430Z(3 rows)XTDB relies on the immutable, append-only nature of the transaction log and its timestamps to automatically record all changes across the database. This approach means that queries can use the reliable ordering of the log to query previous states of the database using nothing more than a timestamp. More on that next!
Unlike in a typical SQL database, UPDATE and DELETE operations in XTDB are non-destructive, meaning previous versions of records are retained automatically and previous states of the entire database can be readily accessed.
A query like SELECT * FROM people will only show the ‘current’ version of everything by default.
Let’s try deleting fred from the database…
user=> DELETE FROM people WHERE name = 'fred';DELETE 0user=> SELECT * FROM people; _id | info | likes | name-----+------+-------------------------------------+------ 9 | | ["fishing",3.14,{"nested":"data"}] | bob(1 row)Despite having seemingly deleted the latest version of the fred record, the prior two versions are not lost and can be retrieved using a couple of methods.
The simplest way to observe the prior version of the fred record is to re-run the exact same query against an earlier ‘basis’.
user=> SETTING DEFAULT SYSTEM_TIME TO AS OF DATE '2020-01-01'SELECT * FROM PEOPLE; _id | info | likes | name-----+------+-------+------(0 rows)Here we set the basis for the whole query by adjusting DEFAULT SYSTEM_TIME.
This allows us to view exactly what the database looked like at the beginning of 2020.
The mechanics of SYSTEM_TIME are discussed in the next section, but they underpin the notion of basis.
As it happens, the database had no records in 2020 so no results are returned.
Now try setting the basis to a point in time shortly after our initial transaction:
user=> SETTING DEFAULT SYSTEM_TIME TO AS OF TIMESTAMP '2024-07-15T12:37:00'SELECT * FROM PEOPLE; _id | info | likes | name-----+------+-------+------ 6 | | | fred(1 row)We can see that on the 1st (our first INSERT) we have our original fred row, but try changing the date to the 2nd and the bob row appears!
The concept of basis makes querying consistently across a scaled-out cluster of read replicas very simple. Independent applications can use the same basis to observe the same database state, regardless of which XTDB node they are connected to. For more information on the implications of the log-oriented design, see How XTDB works or take a look at this blog post by LinkedIn Engineering.
The mechanism underpinning the basis concept is called ‘System Time’. This is what ensures that changes to data in XTDB are immutable, so you always have access to prior states.
The SQL:2011 model of System Time and ‘temporal tables’ is baked into the core design of XTDB, and has been simplified such that you don’t need to learn new syntax or clutter your SQL to take advantage of the immutability benefits.
This means that all INSERT, UPDATE and DELETEs are automatically versioned - you can write SQL intuitively and never lose data again!
You can avoid ever needing to reach for backups or ETL integrations with data warehousing systems in order to recover or make use of previous data. It also helps avoid complicating application schemas with things like “soft delete” columns, audit tables and append-only tables.
The built-in system-time columns _system_from and _system_to are hidden from view by default but, when specified, can be accessed on every table using regular SQL:
user=> SELECT name, _system_from FROM people; name | _system_from------+------------------------------- fred | "2024-07-18T13:57:09.910730Z" bob | "2024-07-18T13:27:35.329921Z"(2 rows)_system_from can take the place of the modified_at columns found across many application schemas. More details about these columns and how they are maintained can be found in How XTDB Works.
The full system-time history for a set of records in a table can be retrieved by specifying FOR SYSTEM_TIME ALL after the table reference:
user=> SELECT name, likes, _system_from, _system_to FROM people FOR SYSTEM_TIME ALL; name | likes | _system_from | _system_to------+-------------------------------------+-------------------------------+------------------------------- fred | | "2024-07-18T13:57:09.910730Z" | null fred | | "2024-07-18T12:49:27.912683Z" | "2024-07-18T13:57:09.910730Z" bob | ["fishing",3.14,{"nested":"data"}] | "2024-07-18T13:27:35.329921Z" | null(3 rows)You can also run queries against individual tables at specific timestamps using FOR SYSTEM_TIME AS_OF <timestamp>, use temporal period operators (OVERLAPS, PRECEDES etc.) to understanding how data has change over time, and much more - see the SQL reference documentation.
Here are some useful capabilities these temporal eatures enable…
user=> SELECT name, _system_from, _system_to FROM people FOR SYSTEM_TIME BETWEEN DATE '2020-01-01' AND NOW; name | _system_from | _system_to------+-------------------------------+------------------------------- fred | "2024-07-18T13:57:09.910730Z" | null fred | "2024-07-18T12:49:27.912683Z" | "2024-07-18T13:57:09.910730Z" bob | "2024-07-18T13:27:35.329921Z" | null(3 rows)Because XTDB retains history, the regular SQL DELETE statement is essentially performing a ‘soft delete’ (“An operation in which a flag is used to mark data as unusable, without erasing the data itself from the database” …but here it’s first-class and ubiquitous).
Here’s how we can bring fred back to being visible and active in our database:
user=> INSERT INTO people (_id, name, info)SELECT _id, name, info FROM people FOR ALL SYSTEM_TIME WHERE _id = 6 ORDER BY _system_to DESC LIMIT 1;INSERT 0 0user=> SELECT * FROM people; _id | info | likes | name-----+-----------------------------------------------------------------------------------------------+-------------------------------------+------ 6 | {"contact":[{"loc":"home","tel":"123"},{"loc":"work","registered":"2024-01-01","tel":"456"}]} | | fred 9 | | ["fishing",3.14,{"nested":"data"}] | bob(2 rows)Sometimes you do really want to forget the past though, and for circumstances where data does need to be erased (“hard deleted”), an ERASE operation is provided:
user=> ERASE FROM people WHERE _id = 6;ERASE 0user=> SELECT * FROM people; _id | info | likes | name-----+------+-------------------------------------+------ 9 | | ["fishing",3.14,{"nested":"data"}] | bob(1 row)The ERASE is effective as soon as the transaction is committed - no longer accessible to an application - and under the hood the relevant data is guaranteed to be fully erased only once all background index processing has completed and the changes have been written to the remote object storage.
With everything covered so far, you are already well-versed in the main benefits of XTDB.
Really there is only one more topic left to examine before you are familiar with all the novel SQL functionality XTDB has to offer…
Everything demonstrated so far only scratches the surface of what XTDB can do, given that XTDB is a full SQL implementation with all the implications that has, however there is one further aspect where XTDB is very different to most databases: ubiquitous ‘Valid-Time’ versioning.
In addition to system-time versioning, SQL:2011 also defines ‘application-time’ versioning. XTDB applies this versioning to all tables and refers to it as valid-time.
Valid-time is a key tool for developers who need to offer time-travel functionality within their applications. It is a rigourously defined model that can help avoid cluttering schemas and queries with bespoke updated_at, deleted_at and effective_from columns (…and all the various TRIGGERs that typically live alongside those).
Developers who try to build useful functionality on top of system-time directly will likely encounter issues with migrations, backfill, and out-of-order ingestion. Valid-time solves these challenges head-on whilst also enabling other advanced usage scenarios:
Note that valid-time as provided by XTDB is specifically about the validity (or “effective from” time) of a given row in the table, and not necessarily some other domain conception of time (unless you carefully model it 1:1).
Let’s have a glimpse of what can you do with SQL to make use of valid-time…
We can specify the _valid_from column during an INSERT statement to record when the organization (i.e. thinking beyond this particular database!) first became aware of the person carol:
user=> INSERT INTO people (_id, name, favorite_color, _valid_from) VALUES (2, 'carol', 'blue', DATE '2023-01-01');INSERT 0 0user=> SELECT name, _valid_from FROM people; name | _valid_from-------+------------------------------- carol | "2023-01-01T00:00Z" bob | "2024-07-18T13:27:35.329921Z"(2 rows)With backdated information now correctly loaded into XTDB, we can easily verify that we knew carol existed in the company records at a time before our current database was even created:
user=> SELECT * FROM people FOR VALID_TIME AS OF DATE '2023-10-01'; _id | favorite_color | info | likes | name-----+----------------+------+-------+------- 2 | blue | | | carol(1 row)The ‘bitemporal’ combination of valid-time and system-time columns allows us to readily produce an auditable history about what we claimed to have known in the past:
user=> INSERT INTO people (_id, name, favorite_color, _valid_from) VALUES (2, 'carol', 'red', DATE '2023-09-01');INSERT 0 0user=> SELECT name, favorite_color, _valid_from, _valid_to, _system_from, _system_toFROM people FOR VALID_TIME ALL FOR SYSTEM_TIME ALL; name | favorite_color | _valid_from | _valid_to | _system_from | _system_to-------+----------------+-------------------------------+---------------------+-------------------------------+------------------------------- carol | red | "2023-09-01T00:00Z" | null | "2024-07-18T20:09:27.822861Z" | null carol | blue | "2023-01-01T00:00Z" | "2023-09-01T00:00Z" | "2024-07-18T19:43:53.398249Z" | null carol | blue | "2023-09-01T00:00Z" | null | "2024-07-18T19:43:53.398249Z" | "2024-07-18T20:09:27.822861Z" bob | | "2024-07-18T13:27:35.329921Z" | null | "2024-07-18T13:27:35.329921Z" | null(4 rows)Perhaps most importantly for many applications, we can easily produce and later re-produce correct reports against business-relevant timestamps without having to assemble wildly complex queries or maintain unnecessary ETL infrastructure:
user=> SETTING DEFAULT VALID_TIME AS OF DATE '2023-10-01',DEFAULT SYSTEM_TIME AS OF DATE '2030-01-01'; -- (some recent/latest timestamp)SELECT name, favorite_color , _valid_from, _system_from FROM people; name | favorite_color | _valid_from | _system_from-------+----------------+---------------------------+---------------------------------- carol | red | 2023-09-01 00:00:00+00:00 | 2024-07-18 20:09:27.822861+00:00(1 row)XTDB implements a SQL API that closely follows the ISO standard specifications and draws inspiration from Postgres where needed, however unlike most SQL systems XTDB:
CREATE TABLE DDL statement) - tables may be created dynamically via an initial INSERT statement, along with any supplied columns and inferrable type information - schema automatically evolves over time as data changesYou have now learned the essentials of using XTDB!
Looking for more? Please have a browse around, try building something, and feel very welcome to say hello 👋