4) Changing an immutable database

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

In this part, we will see how to update past data while still being able to access the raw, unchanged data.

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

Run

Open in xt-play

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

Run

Open in xt-play

Run

Open in xt-play

Run

Open in xt-play

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-play

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

Run

Open in xt-play

Immutability

But aren’t we mutating an immutable database here?

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

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 as 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-play

Conclusion

We have shown 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 is SYSTEM_TIME.