Query the past

XTDB automatically records all versions and changes to individual rows across your database. This is widely useful for building reliable information systems, and it all starts with a log.

A log of transactions

The xtsql console behaves as an ordinary client of an XTDB server, and it operates by sending stateless JSON payloads over HTTP as the transport layer. HTTP as a transport layer offers many operational advantages, but it also means transactions are inherently non-interactive (i.e. there is no stateful connection/session in place).

Consequently, transactions in XTDB are durable (ACID) requests which are processed serially and asynchronously, i.e. if you wait a few milliseconds for the processing of a newly submitted transaction to finish, you can generally then read the data from that transaction in your next query (a.k.a. "read your writes"). However, xtsql and all XTDB language drivers will, by default, implicitly wait for any recently submitted transactions to be processed before subsequent queries are executed. To achieve this, all transactions are uniquely identified with an xt$id.

For convenience, you can easily see the history of prior transactions within xtsql by using the recent_transactions command:

-> recent_transactions

| xt$id | xt$committed? | xt$error | xt$tx_time                        |
|-------|---------------|----------|-----------------------------------|
| 0     | True          | NULL     | 2024-03-13 18:38:37.398210+00:00  |

You can see this table directly by running

SELECT * FROM xt$txs;

Basis: re-run queries against past states without explicit snapshots

Unlike in 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.

For example, despite having seemingly just updated the original version of the fred record that was inserted initially (SELECT * FROM people will only show the 'current' version of everything by default), the original version was 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'.

A basis is like a pointer to a snapshot of a previous version of the entire database state, except unlike snapshots in other systems in XTDB there is no copying or explicit snapshot creation required.

A basis is stable and allows you to re-run unmodified queries indefinitely. This is useful for debugging, auditing, and exposing application data for processing in downstream systems (generating reports, analytics etc.)

Within xtsql, changing the basis can be accomplished by defining a timestamp using the basis_at_system_time command:

-> basis_at_system_time

9999-12-31T23:59:59.999999Z

Without a parameter supplied, the default value returned implies that the basis is not set. To set the basis, simply use some prefix of a valid ISO8601 timestamp string as a parameter to the basis_at_system_time command:

-> basis_at_system_time 2020

2020-01-01T00:00:00.000000Z

With the basis now set to 2020, try re-running the SELECT * FROM people query and see that no results are returned.

Now try setting the basis to a timestamp shortly after our initial transaction (the timestamp shown here is for illustration only, you should refer to the output of recent_transactions):

-> basis_at_system_time 2024-03-13 18:39
2024-03-13T18:39:00.000000Z

When you re-run the SELECT * FROM people query again, this time you should see the original version of the fred row and also the bob row won’t be visible.

To clear the basis and reset the query behaviour to 'current' you can run the clear_basis command (which resets xtsql back to using the default 9999-12-31 timestamp):

-> clear_basis
basis cleared

Welcome back to the present! You should now be able to see the latest versions of fred and bob by re-running the query once again.

System-Time Columns: automatic time-versioning of rows without audit tables

The mechanism underpinning the basis concept is called 'System Time'.

Normally a SQL database will irreversibly lose access to prior states of data after transactions containing UPDATE or DELETE statements are committed. System-time versioning is a standard defined in SQL:2011, but unlike other implementations, XTDB has it baked into the core of the database engine such that all data is versioned by default and all modification operations are non-destructive.

This minimises the circumstances where developers need to reach for backups or ETL integrations with other warehousing systems in order to recover data. It also helps avoid complicating application schemas with things like "soft delete" columns and audit tables.

The system-time columns xt$system_from and xt$system_to are hidden from view by default but, when specified, can be accessed on every table using regular SQL:

SELECT people.*, people.xt$system_from, people.xt$system_to
  FROM people;

More details about these columns and how they are maintained by the system can be found in the How XTDB Works section.

The full system-time history for a set of records in a table can be retrieved by specifying FOR ALL SYSTEM_TIME after the table reference:

SELECT people.*, people.xt$system_from, people.xt$system_to
  FROM people FOR ALL SYSTEM_TIME;

You can also run queries against individual tables at specific timestamps using FOR SYSTEM_TIME AS_OF <timestamp> and use temporal period operators (OVERLAPS, PRECEDES etc.) to interrogate audit trails - see the SQL reference documentation.

A delta of changes to a table since a given system-time

SELECT people.*, people.xt$system_from, people.xt$system_to
  FROM people FOR SYSTEM_TIME BETWEEN DATE '2020-01-01' AND CURRENT_TIMESTAMP;

Restore a deleted row

Let’s first delete fred:

DELETE FROM people where people.name = 'fred';

Now SELECT * FROM people should only show bob. Here’s how we can get fred back:

INSERT INTO people (xt$id, name, info)
SELECT people.xt$id, people.name, people.info
  FROM people FOR ALL SYSTEM_TIME
  WHERE people.xt$id = 6
  ORDER BY people.xt$system_to DESC
  LIMIT 1;

ERASE as hard-delete

Sometimes you do really want to forget the past though, and where data does need to be erased ("hard deleted"), an ERASE operation is provided:

ERASE FROM people WHERE people.xt$id = 6;

The ERASE is effective as soon as the transaction is committed.

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.

Your basic training is almost complete!

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, read on…​