Changing an immutable database

In part 3, we learned how to insert historical data into XTDB.

In this part, we will understand how to update past data, but we will also learn how to access the raw, un-changed data also.

First, let’s insert three versions of the same product into different points in the past with three different prices:

Run

Open in xt-fiddle

Let’s prove to ourselves that querying at various points in the past, gives us the correct data:

Run

Open in xt-fiddle

Run

Open in xt-fiddle

Run

Open in xt-fiddle

Now let’s say we know that the price for 2023 was INCORRECT. This could have been due to a multitude of reasons, a developer bug, a faulty database update, or an incorrect manual data entry.

Let’s correct the price for 2023:

Run

Open in xt-fiddle

Now when we query in 2023, we get the updated price back:

Run

Open in xt-fiddle

Immutability

BUT - aren’t we mutating an immutable database here?

Aren’t we blasting over the top of original data, and thus losing that original data?

The answer is no. We have been updating the VALID_TIME line. But our XTDB database has another, completely immutable timeline called SYSTEM_TIME.

Using a query against SYSTEM_TIME, we can query the database exactly how it was at a point in database-time.

No updates to this line are possible, we can only add to the end of the timeline. We call this append-only, and it is very useful for auditing all changes made to the database.

For example, querying against SYSTEM_TIME, we should see the original, unmutated data:

Run

Open in xt-fiddle

Conclusion

We have shown in the above, the ability to update the past and see the updated changes, but also how we can query the raw, unedited past.

This is the concept of bitemporality: having two timelines.

One timeline that you can update is: VALID_TIME, and one you can only ever append to: SYSTEM_TIME.