Skip to content
Logo of XTDB

Tuesday, 08 August, 2023

"But bitemporality always introduces complexity!"

James Henderson

Given that we’re writing a bitemporal database, the following might seem like a strange thing to say:

Most of the time, most people don’t need most of the power of bitemporality.

As much as we obviously love bitemporality, pragmatically, it’s true - you could certainly be forgiven for eschewing it in favour of something simpler, especially when you’re just trying to get a new system off the ground.

But, as Kent Beck outlines in his recent blog "Eventual Business Consistency": soon, the following requirements roll in:

  • "Could you just show us the edit history of this company profile?"

  • "I want the user to be able to delete this post on their public profile, but still see its performance on their reporting dashboard."

  • "Oh btw, I moved house last month."

  • "An upstream system was running late, we’re going to need to recalculate the end-of-month reporting"

  • "Could you schedule this marketing promotion for next month?"

  • "On what data did we make that decision?!"

  • "But I didn’t mean to delete that data!"

  • …​ "what do you mean, 'we can’t restore the backup'?!"

Maybe some seem familiar?! 😅

It would be easy to implement all of these as individual stories, but these requirements all have one thing in common: time. Soon, your database schema is littered with time columns and soft-deletes, your queries have to know all about it too, and it’s hard to retro-fit.

No wonder people say "bitemporality introduces complexity"!

That said, bitemporality does provide us with a useful, rigorously defined mental model to help reason about these time-oriented problems, the tools to implement it and, by approaching problems with this in mind, we become aware of edge cases we may have otherwise missed. We believe that, overall, it’s a mistake to cast it aside.

Any sufficiently complicated C or Fortran program database schema contains an ad hoc, informally-specified, bug-ridden, slow implementation of half of Common LISP a bitemporal database.

— Greenspun’s Tenth Rule (although maybe adapted a little, forgive us…​)

Naturally, though, we’d like to have our cake and eat it: we want the ease and performance of a traditional update-in-place database for everyday transactions and queries, but the safety net of a bitemporal database when we need it.

This is the primary success criterion of "XTDB", our bitemporal database supporting SQL:2011 and XTQL.

Note

We’d love your feedback on the 2.x early access - for more details, see the release announcement.

Alternatively, for our production-stable Datalog bitemporal database, available now, see XTDB 1.x.

Atemporality in XTDB

For use cases that don’t yet require the full power of bitemporality - here’s how normal ('atemporal') inserts, updates, queries and deletes work in XTDB 2.x:

INSERT INTO users (xt$id, user_name, ...) VALUES (?, ?, ...);
-- providing `xt$id` and `user_name` as separate parameters, to avoid SQL injection attacks.

UPDATE users SET user_name = ? WHERE xt$id = ?;

SELECT * FROM users WHERE user_name = ?;

DELETE FROM users WHERE xt$id = ?

(in XTQL)

;; no injection attacks here!

[:put :users
 {:xt/id #uuid "26affab8-71b6-49e3-becb-df220561b1c5",
  :user-name "Jmes", ...}]

[:put :users
 {:xt/id #uuid "26affab8-71b6-49e3-becb-df220561b1c5",
  :user-name "James", ...}]

(xt/q '(from :users [* {:user-name $user-name}])
      {:user-name "..."})

[:delete :users #uuid "26affab8-71b6-49e3-becb-df220561b1c5"]

So far, so good. Nothing remotely bitemporal-looking here, just what you’d write in a traditional database - and 90% of the time, this is what XTDB applications look like.

But when you need temporality…​

Here’s where XTDB’s safety net comes in: we can now ask time-oriented questions. These usually take one of the following forms:

  1. What’s the current state of the world?

  2. What’s the history of my database, as we now know it (i.e. taking subsequent corrections into account)?

  3. What’s the history of my database, as we thought it was at the time?

(In our experience, these three categories of questions are in descending order of request frequency, and XTDB optimises accordingly - beneath the surface, we have specific indices to quickly serve current-time queries to get them as close as possible to atemporal performance, and separate indices for historical data.)

To answer these questions, SQL:2011 introduced an array of new bitemporal primitives:

  1. For category 1: we’ve chosen to make this the default behaviour in XTDB - query as you normally would.

  2. For category 2: when selecting from a table, we can specify a valid time period:

    SELECT * FROM users FOR VALID_TIME AS OF DATE '2023-08-01';
SELECT * FROM users FOR VALID_TIME BETWEEN DATE '2023-08-01' AND DATE '2023-09-01';
SELECT * FROM users FOR ALL VALID_TIME;

    (and in XTQL)

    (from :users {:for-valid-time (at #inst "2023-08-01"), :bind [*]})
(from :users {:for-valid-time (in #inst "2023-08-01", #inst "2023-09-01"), :bind [*]})
(from :users {:for-valid-time :all-time, :bind [*]})

  3. For category 3: same, but SYSTEM_TIME:

    SELECT * FROM users FOR SYSTEM_TIME AS OF DATE '2023-08-01';
SELECT * FROM users FOR SYSTEM_TIME BETWEEN DATE '2023-08-01' AND DATE '2023-09-01';
SELECT * FROM users FOR ALL SYSTEM_TIME;

    (and in XTQL)

    (from :users {:for-system-time (at #inst "2023-08-01"), :bind [*]})
(from :users {:for-system-time (in #inst "2023-08-01", #inst "2023-09-01"), :bind [*]})
(from :users {:for-system-time :all-time, :bind [*]})

Inserts, updates and deletes are similar:

  • Inserts behave more like an upsert in XTDB. If you INSERT a row that already exists, no problem - we’ll effectively update any existing rows (so that they remains accessible in historical queries), and your new row becomes the current row.

  • For updates/deletes in the past/future, use the SQL:2011 FOR PORTION OF VALID_TIME syntax

    UPDATE users
FOR PORTION OF VALID_TIME FROM DATE '2023-08-01' TO DATE '2023-09-01'
SET user_name = ?
WHERE xt$id = ?

In summary

Most of the time, most people don’t need most of the power of bitemporality.

So, for most of the time, for most of your requirements, you can use XTDB like a normal database - but while also being safe in the knowledge that, as your requirements grow, you can incrementally pull in the power of bitemporality when you really need it.

XTDB makes this simple everyday behaviour easy and fast, and a wide range of harder bitemporal queries possible.

If this blog piqued your interest, please come say hi, either at https://discuss.xtdb.com, or via email at hello@xtdb.com - we’d love to hear your thoughts and feedback.

Thanks!