DEV Community: Alex Kuznetsov

Taming Time: how to run XTDB in production

Alex Kuznetsov — Wed, 19 Jul 2023 11:13:57 +0000

In the previous articles, we explored the concept of bitemporality and discussed how to get started with XTDB, a bitemporal immutable database. Now, let’s dive into the technical details of deploying XTDB and running it in production. This blog post aims to provide valuable insights and considerations to keep in mind during this process.

XTDB 1

Before we proceed, it’s important to note that the experiences shared in this article are based on working with XTDB version 1.21.
It’s worth mentioning that your experience may vary, especially with the introduction of XTDB 2.0 and subsequent versions.

Deploying XTDB

Deploying XTDB in a production environment offers several options, each with its advantages and considerations:

run as a part of your JVM application
run separately, but on the same server together with your application
run on a standalone node or in a separate container
run a cluster of XTDB nodes

To achieve a resilient and scalable setup, I recommend running a cluster of XTDB nodes, with each node deployed as a separate Docker container managed by Kubernetes. This allows easy orchestration, automatic scaling, and simplified management of the XTDB cluster.

Containerised XTDB

In this section, we will explore how to build and run a Docker container with a custom configured XTDB application (Clojure project) in a Kubernetes cluster. However, in case you don’t need to have a custom build and can simply use a standalone Docker image, you can skip right to the "Authentication" section.

Uberjar

If we want to run the XTDB in Docker the most fitting way to run it in a container would be to compile our preconfigured XT application into a single “uberjar” file.

This can be done by employing Uberdeps in our Clojure project:

    ; deps.edn
    :aliases
     {:uberdeps {:replace-deps {uberdeps/uberdeps {:mvn/version "1.1.0"}}
                 :replace-paths []
                 :main-opts ["-m" "uberdeps.uberjar"]}}

Once you have it installed, you can compile your project into a single Uberjar file:

clj -M:uberdeps

Dockerfile

We want our XTDB Docker image to be as lightweight as possible, so the best approach would be to have a multi-stage build image that builds and runs the Uberjar on the selected JVM image:

    # Build clojure uberjar
    FROM clojure:openjdk-17-tools-deps-alpine AS BUILD

    WORKDIR /xtdb
    COPY . /xtdb
    RUN apk add --no-cache libstdc++

    RUN clojure -M:uberdeps

    # Copy and run uberjar
    FROM openjdk:17-alpine3.14
    WORKDIR /usr/local/lib/xtdb

    RUN apk --no-cache add bash libstdc++

    COPY --from=BUILD /xtdb/resources /usr/local/lib/xtdb/resources
    COPY --from=BUILD /xtdb/target .

    ENV MALLOC_ARENA_MAX=2
    CMD ["java", "-cp", "xtdb.jar", "clojure.main", "-m", "xtdb.core"]

    EXPOSE 3000

Graceful shutdown

Another requirement for running XT in Kubernetes is to gracefully shut down, e.g., on killing containers or Kubernetes deployment restart. To ensure that, we have to change our xtdb.clj file by adding a SIGTERM signal handler:

    ;; Stop the system on SIGTERM
    (with-handler :term
      (log/info "Caught SIGTERM, quitting")
      (.close @xt-node)
      (log/info "All components shut down")
      (System/exit 0))

Authentication

Since we run our XTDB separately from our application containers, we might want to ensure that the requests from services to the database are properly authenticated.

In order to ensure that we need to change the way we start XTDB by providing JWKS (JSON Web Token key set) as an environment variable to the XTDB node:

    ; core.clj
    (def config
      {:xtdb.http-server/server {:port 3000
                                 :jwks (System/getenv "XTDB_JWKS")} ...

The next step is to send the compatible JWT token from your application requests:

    {"Authorization", "Bearer #{your_jwt}"}

Kubernetes

As we’ve decided to go with the running XT nodes as Docker containers in a Kubernetes cluster, we need to prepare Kubernetes manifests for that purpose.

The simplest way to achieve that is to create a Kubernetes stateful set of multiple XT containers:

    ---
    apiVersion: apps/v1
    kind: StatefulSet
    metadata:
      name: xtdb
      labels:
        app.kubernetes.io/name: xtdb
    spec:
      serviceName: xtdb-headless
      replicas: 3
      selector:
        matchLabels:
          app.kubernetes.io/name: xtdb
      template:
        metadata:
          labels:
          app.kubernetes.io/name: xtdb
        spec:
          terminationGracePeriodSeconds: 30
          containers:
            - name: xtdb
              image: $REGISTRY/xtdb:1.21.0
              imagePullPolicy: Always
              ports:
                - containerPort: 3000
              livenessProbe:
                tcpSocket:
                  port: 3000
                periodSeconds: 10
                initialDelaySeconds: 120
                timeoutSeconds: 15
              readinessProbe:
                exec:
                  command:
                    - bash
                    # custom readiness check script
                    - scripts/readiness.sh
                initialDelaySeconds: 30
                periodSeconds: 15
              envFrom:
                - secretRef:
                    name: xtdb-secrets

As for the configuration, you can create a configmap with environment variables required for configuring XTDB and a secrets resource for providing secrets to your XT nodes.

Running XT in production

XTDB is an unbundled database which means that it has a lot of components that can be swapped or changed — and it might work with different technologies and other databases.

In general, it consists of 3 parts:

Transaction log
Document store
Index store

We had an experience using PostgreSQL and JDBC adapter for our XTDB setup, as well as experimenting with Kafka for transaction log — and using RocksDB as an index storage.

However, there are many more other ways and modules to setup the database — you can find them in the documentation.

JDBC

The transaction log and document store are considered to be golden stores in XTDB — which means that they should be reliably persisted, unlike the index storage that can be rebuilt from scratch on node restart.

XT supports JDBC (Java Database Connectivity) which allows us to connect to various SQL databases like PostgreSQL, MySQL, SQLite, and others.
In our example, we were using PostgreSQL + JDBC for both transaction log and document store.

To use PostgreSQL and JDBC together, you have to provide these modules in your deps.edn first:

:deps {org.postgresql/postgresql {:mvn/version "42.2.18"}
        com.xtdb/xtdb-jdbc {:mvn/version "1.21.0"}
       ...

If you want to use environment variables for connecting to PostgreSQL from the XTDB deployment you can also pass them in core.clj file:

    (def db-spec
      {:host (System/getenv "POSTGRES_HOST")
       :port (get (System/getenv) "POSTGRES_PORT" "5432")
       :dbname (System/getenv "POSTGRES_DB")
       :user (System/getenv "POSTGRES_USER")
       :password (System/getenv "POSTGRES_PASSWORD")})

    (def config
      {:xtdb.jdbc/connection-pool {:dialect {:xtdb/module 'xtdb.jdbc.psql/->dialect}
                                   :pool-opts {:maximumPoolSize 10}
                                   :db-spec db-spec}
       :xtdb/tx-log {:xtdb/module 'xtdb.jdbc/->tx-log
                     :connection-pool :xtdb.jdbc/connection-pool}
       :xtdb/document-store {:xtdb/module 'xtdb.jdbc/->document-store
                             :connection-pool :xtdb.jdbc/connection-pool}})

That way we can also re-use the same connection pool for both transaction and document store.

Kafka

However, the recommended option (and also most often used in production) is to leverage Kafka for the transaction store.

During node restarts (e.g. on new deployments) XTDB has to rebuild the transaction log from zero or the latest saved checkpoint, which means reading the whole transaction table in PostgreSQL — and in our experience, this process was slower than expected.

Kafka seems to be a better fit for the very purpose of the transaction log because:

it’s basically a log of events
we only need to use just one partition and one topic
the transactions can be consumed very quickly

Thus, the most optimal and performant setup for us looked like:

using Kafka as a transaction log
using JDBC and relational database as a document store
using RocksDB as an index store

That way, we can ensure that the transactions can be created quickly, the log can be re-consumed fast, and the document storage is performant enough and resilient.

Checkpoints

If we want to rebuild the query indices (e.g. on node restart), XT might need to replay the transaction log — which sometimes might be not so fast especially if you have a long history of changes.

As we run our XTDB in a cluster, it’s vital that the readiness time — when the XT instance is ready to serve DB requests — is as low as possible.
Fortunately, XTDB has a solution for that problem called checkpoints.

Right now, there are three ways to persist the local query indices state:

local files (using Java’s NIO file system)
AWS S3
GPC’s cloud storage

AWS S3

In our case, we’ve decided to go with the AWS setup in order to have a centralised and already configured storage for the checkpoints.
However, it also requires some additional dependencies to be installed:

    ; deps.edn
     :deps {com.xtdb/xtdb-s3 {:mvn/version "1.21.0"}
            software.amazon.awssdk/aws-core {:mvn/version "2.10.91"}

Additional setup in the node configuration is also required.

As we’ve experienced some requests to S3 taking a long time, we’ve also decided to build a custom AWS S3 HTTP client:

    (defn- make-s3-client
      "Increases timeouts for AWS S3 HTTP calls"
      []
      (let [timeout (Duration/ofSeconds 30)
            http-client-builder (->
                                 (NettyNioAsyncHttpClient/builder)
                                 (.connectionAcquisitionTimeout timeout)
                                 (.connectionTimeout timeout))]
        (-> (S3AsyncClient/builder)
            (.httpClientBuilder http-client-builder)
            .build)))

    (def checkpoint-name (get (System/getenv) "CHECKPOINT_NAME" ""))

    (def checkpoint-config
      ; Checkpoints are not enabled on a local machine where we don't have the env
      (if (str/blank? checkpoint-name)
        {}
        {:xtdb/module 'xtdb.checkpoint/->checkpointer
         :store
         {:xtdb/module 'xtdb.s3.checkpoint/->cp-store
          :bucket checkpoint-name
          :configurator (fn [_] (reify S3Configurator (makeClient [_this] (make-s3-client))))}
         :Keep-dir-on-close? false
         :approx-frequency (Duration/ofHours 2)}))

Once configured, XT will persist the current index state to S3 every 2 hours. One might want to adjust S3’s bucket policy so it archives or removes the obsolete checkpoints files.

Caveats

Memory consumption

JVM-based applications tend to consume quite a significant amount of memory budget — in our case, running XT with allowed 4GB of memory wasn’t always enough so we’ve decided to increase the memory limits in Kubernetes up to 8 gigabytes.

Another consideration we’ve observed is that the vertical scaling works better for an XTDB cluster — unlike the horizontal scaling, where we need to wait until the new node restores from the checkpoints or processes the transactions log.

RocksDB tuning

RocksDB is being used by XT as an index store, and as the result, it might consume quite a significant amount of resources.
In order to avoid possible issues with the memory budgeting, it’s recommended to set RocksDB block cache to 1/3 of available memory which can be done in XTDB configuration.

Readiness probes

Depending on your technology stack that you use for XTDB deployment, consuming the transaction log even with the checkpoints feature enabled can take some time — and even though the starting node is able to handle REST API requests, they won’t be processed until the node finishes the consumption.

To avoid that, you might need to check the difference between the last submitted and last completed transactions, e.g. from a bash script:

    #!/bin/bash
    # scripts/readiness.sh: A script that compares the latest submitted and indexed transactions

    set -e
    THRESHOLD=1000

    # Assumes that you have jq and curl installed
    submitted_tx=`curl http://localhost:3000/_xtdb/latest-submitted-tx -H "Accept: application/json" -f | jq .txId`
    completed_tx=`curl http://localhost:3000/_xtdb/latest-completed-tx -H "Accept: application/json" -f | jq .txId`
    diff=$[submitted_tx - completed_tx]

    if ((diff > THRESHOLD)); then
        echo "Node is not ready"
        exit 1
    else
        echo "Node is ready"
    fi

Load balancing and XT cluster

The index storage is not shared between XTDB nodes — so every node might have a slightly different data representation. To ensure integrity, we might need to use await-tx or sync functions whenever we submit a transaction.

However, when we use REST API in a distributed cluster of nodes, it might be that the load balancer that stands in front of the nodes distributes requests to the database randomly — and when we submit a transaction to one node, we can end up reading data from another, which might have not processed that transaction yet.

If we want to prevent such situation, we might need to implement sticky sessions or use HTTP2 connections between your applications and database nodes.

Conclusion

XTDB embraces the bitemporality concept and provides powerful capabilities of handling your data in an immutable way.

However, this also implies that during your journey with XT, you might face some technical challenges caused by its unbundled database concept — and resolve them by reasoning about the selected components, technology stack, and implications.

The new milestone in XTDB’s development — XTDB 2.0 — looks very promising for us as it has a more flexible and scalable architecture as well as the first-class SQL support — and can be used by PostgreSQL clients.

We look forward to trying out the new version and hope that you’ve enjoyed our series of articles about XT.

Happy hacking, and stay tuned!

Thanks Carsten and Adam for the reviews!

Taming Time: how to install & develop with XTDB

Alex Kuznetsov — Wed, 05 Apr 2023 14:43:22 +0000

In the previous article, we discussed the concept of bitemporality and how it can be used to solve complex architectural problems.

At MarleySpoon, we’ve used XTDB (or ’XT’ for short) for our new order management system (OMS), and discovered a lot of interesting insights about the database itself, the concept of bitemporality, and how developing a project using an immutable, bitemporal database could look like.

In this article, we will be focusing primarily on our development experience (installing, testing with XTDB) from an Elixir application, and we cover more details about deploying, running XT in cluster, and tuning in production in the upcoming article.

What is XTDB

XTDB, or Cross-Time Database, is a distributed and transactional database system designed to handle complex and changing data with ease.
It is based on a bitemporal model, which allows for the tracking of both the valid time and transaction time of data, enabling powerful and flexible querying capabilities.
With XTDB, developers can work with immutable data structures, which simplifies development and improves reliability.
Its graph query language, Datalog, provides a powerful and expressive way to navigate relationships within the data.

As we’ve illustrated before, XT has a lot of benefits:

Bitemporal
Supports retroactive corrections
Document and graph-based
Flexible data schema
Unbundled (can be deployed on top of a lot of other DBs and persistence solutions)
Can be used within JVM or through REST API

As one can see, XT is quite different from most of the widely used SQL and NoSQL databases - and while it provides great benefits for dealing with immutable data and retroactive corrections, it also requires an understanding of some of its implementation principles.

How we planned to use XTDB

At MarleySpoon, we ship boxes with recipes and ingredients to our customers. The orders and our subscription model are the backbone of the whole bussiness logic and that is also reflected in how we build our software.

The core of the orders system is the orders state machine, and although the states are essentially simple, there might still be cases where we could have discrepancies - and in such cases, we would like to have more options to debug or restore orders to a previous state, as well as retroactively correct their data and push the change to dependent subsystems.

The shift from the legacy monolith architecture to service-oriented architecture coupled with the introduction of new OMS also required us to be more careful when it comes to eventual consistency - the transaction and valid times can be different in the resulting systems so we can’t just work around it by using the persistence stack we’ve used to (e.g. relational DBs with updates in place).

Initially, we considered adding transaction and valid time columns to PostgreSQL to implement bitemporality, as this seemed like a straightforward solution. However, upon further analysis, we realized that this approach would introduce significant complexity to the system design. In particular, any foreign keys would need to take the bitemporal columns into account, meaning that queries would need to consider both the entity relationship and its temporal context. This would require significant changes to the database schema, query design, and application code, and would likely lead to a higher risk of errors and data inconsistencies.

We also considered event sourcing for our needs, but it would add significant incidental complexity, requiring changes to other services’ architectures and a significant amount of application-level code changes to ensure that all events were captured and persisted correctly.

After considering various approaches to implementing bitemporality in our system, we decided to give XTDB a try due to its native support for bitemporality and graph database capabilities. We designed our data model around XTDB’s capabilities and incorporated the database into our Elixir application.

Installing XTDB

There are multiple ways to install and use XTDB:

Use it as a JVM dependency by simply adding it to your JVM project:

        ; deps.edn
        com.xtdb/xtdb-core {:mvn/version "1.21.0"}

Using a pre-built XTDB JAR on a local machine
Through a Docker image

XTDB is developed in the Clojure programming language and it is very convenient to run it from any Clojure program - so we’ve decided to write a simple app in Clojure that would run XTDB for us and provide all required setup.

Installing dependencies

We’ve installed Clojure using asdf version manager as it’s very convenient to pin JVM and Clojure versions:

    # .tool-versions
    openjdk-18
    clojure 1.11.0.1100

The next step was creating a new Clojure app using deps CLI - all of the necessary dependencies were provided in a single file (deps.edn).
XTDB is very modular so we have to install PostgreSQL support, HTTP client and server, metrics, and other tools as separate packages:

    ;; deps.edn
    {:paths ["src"]
     :deps {org.clojure/clojure {:mvn/version "1.11.0"}
            com.xtdb/xtdb-core {:mvn/version "1.21.0"}
            ;; Persistence
            org.postgresql/postgresql {:mvn/version "42.2.18"}
            com.xtdb/xtdb-jdbc {:mvn/version "1.21.0"}
            com.xtdb/xtdb-rocksdb {:mvn/version "1.21.0"}
            ;; HTTP Client
            com.xtdb/xtdb-http-client {:mvn/version "1.21.0"}
            ;; HTTP Server
            com.xtdb/xtdb-http-server {:mvn/version "1.21.0"}}
    }

Application entry point & configuration

One of the primary reasons we developed a wrapper for XTDB was to enable us to run an XTDB cluster in a Kubernetes environment. We wanted to simplify the setup process by allowing configuration through environment variables, rather than relying on external configuration files. This allowed us to easily manage XTDB’s configuration within Kubernetes and provided us with greater flexibility in managing our XTDB cluster.

We’ve created a xtdb.clj file that is the entry point to the database wrapper and which also has all the required configuration there:

    (def db-spec
      {:host (System/getenv "POSTGRES_HOST")
       :port (System/getenv "POSTGRES_PORT")
       :dbname (System/getenv "POSTGRES_DB")
       :user (System/getenv "POSTGRES_USER")
       :password (System/getenv "POSTGRES_PASSWORD")})

    (def config
      {:xtdb.http-server/server {:port 3000
                                 :jwks (System/getenv "XTDB_JWKS")} ; auth
       :xtdb.rocksdb/block-cache {:xtdb/module 'xtdb.rocksdb/->lru-block-cache
                                  :cache-size (* 1024 1024 1024)} ; RocksDB cache size
       :xtdb/index-store {:kv-store {:xtdb/module 'xtdb.rocksdb/->kv-store
                                     :db-dir "/tmp/xtdb/indexes"
                                     :checkpointer checkpoint-config
                                     :block-cache :xtdb.rocksdb/block-cache
                                     :metrics {:xtdb/module 'xtdb.rocksdb.metrics/->metrics}}}
       :xtdb.jdbc/connection-pool {:dialect {:xtdb/module 'xtdb.jdbc.psql/->dialect}
                                   :pool-opts {:maximumPoolSize 10}
                                   :db-spec db-spec}
       :xtdb/tx-log {:xtdb/module 'xtdb.jdbc/->tx-log
                     :connection-pool :xtdb.jdbc/connection-pool}
       :xtdb/document-store {:xtdb/module 'xtdb.jdbc/->document-store
                             :connection-pool :xtdb.jdbc/connection-pool}})

Once the initial configuration is done we can provide a simple entry point function that will start an XT node:

    ;; XT node - hydrated on start
    (def xt-node (atom nil))

    (defn -main
      "Starts a new XTDB node"
      []
      (let [node (xtdb/start-node config)]
        (log/info "Started a new XT node ...")
        (seed/seed node) ; seed data that we need on start
        (xtdb/sync node)
        (log/info "Loaded data into a new XT node")
        (reset! @xt-node node) ; set xt-node with the started node
        node))

    ;; Can be used to run REPL or local XT instance
    ;;
    ;; As running it from CLI can assume passing some command line arguments
    ;; we should accept a list of optional arguments as a function param
    (defn start
      [&args]
      ;; Runs -main function to start a new XT node
      (-main))

Running XT on a local machine

Once our XT app is installed and configured, we can run clj -X xtdb.core/start in order to start it on a local machine.
This will enable web UI and REST API on http://localhost:3000.

Connecting from REPL

To run XT in REPL we can instead just execute clj in shell, given that we are in the root directory of the Clojure project.

That will start a new Clojure REPL, and if we want to start XT from there, it’s sufficient to use functions we’ve implemented beforehand:

    (in-ns 'xtdb.core) ; switches current namespace to XT wrapper's core namespace
    (-main)

Connecting to a remote node

Another important benefit of XT that we unfortunately didn’t explore enough is that by using the http-client dependency, we’re able to connect to any remote node that is accessible to us by HTTP:

    (def remote-node (xt/new-api-client remote-url))
    (xt/submit-tx remote-node [[::xt/put {:xt/id :foo :bar :bar}]]) ; submits a transaction on the remote node

Thus, we can connect from REPL to any production XT instance and run queries / submit transactions there.

REST API

XT is very convenient to use from any Clojure or JVM-based application, however, for clients implemented in other programming languages or different virtual machines, we should be using XT’s REST API.
XT has a very rich HTTP API that covers most of its functionality (although some of it is only available by Clojure/Java client).

API formats

XT supports multiple formats: application/edn, application/json and application/transit+json.

As XT is written in Clojure and it natively supports Clojure’s data types, we were not satisfied with available JSON types and decided to give EDN a try - that way we would have way more supported types:

symbols, e.g. Elixir atoms
decimals
dates and timestamps

However, we had some issues with encoding Elixir/BEAM VM terms to EDN and, in general, the performance of the format - so the Transit/JSON would be an improvement as apart from being compatible with regular JSON and essentially more performant it also has a more precise types conversion.

Main endpoints

GET /_xtdb/status - can be used as a health check for an XT node
GET /_xtdb/entity - gets a single entity from the database
GET /_xtdb/query - performs a single Datalog query
POST /_xtdb/submit-tx - submits database transactions
GET /_xtdb/await-tx - waits until the transaction was indexed on the node
GET /_xtdb/tx-committed - checks if the transaction was successfully committed

Elixir HTTP client

As at that point in time when we started applying XT at MarleySpoon there were no Elixir libraries fully supporting XT’s REST API and covering our needs so we’ve started writing our own HTTP adapter.

Umbrella application

As OMS was the only project where we were trying XTDB, we’ve decided to move all the Clojure code, as well as the new Elixir HTTP into the same umbrella app:

apps/
  ...
  xtdb/

That way we could also easily start an XTDB node right in our project and use it from the Elixir application, e.g. by starting through docker-compose.

HTTP2

XT’s REST API supports the second revision of HTTP format out of the box - which means that we can have a stable and more performant connection between XT clients (application servers) and the database instances.

When running an XTDB cluster in production, HTTP2 can be particularly useful. By using HTTP2, direct connections can be established between the client application and an XTDB node, rather than relying on load balancing across multiple instances. Since XTDB doesn’t enforce an equal state between nodes, the same request could yield different results on different nodes - but using HTTP2 eliminates the issue and ensures consistent results for each request.

However, not every Elixir’s HTTP client supports sending requests using HTTP2 - so we have to search for another option rather than using HTTPoison that we widely use in other projects.
We’ve decided to go with Finch, as apart from supporting HTTP2 it also focuses on performance and provides telemetry support out of the box - which we’ve found very useful for tracing and debugging purposes.

Using HTTP2 with Finch requires some initial configuration as we have to have just one connection in a connection pool:

    # apps/xtdb/lib/application.ex
    children = [
          {
            Finch,
            name: Xtdb.ConnectionPool,
            pools: %{
              default: [count: 1, protocol: :http2, size: 1]
            }
          }
        ]

    Supervisor.start_link(children, strategy: :one_for_one, name: Xtdb.Supervisor)

Telemetry & OpenTelemetry

As was mentioned in a previous section, using Finch as an HTTP Client library is a great step to have a seamless telemetry integration.

Finch provides the next Telemetry events:

request start/stop
request exception
queue start/stop/exception
connection start/stop/exception

and others, but for our purpose, we will be more interested only in the first two events.

In order to integrate the XTDB client with OpenTelemetry we wrote a simple module that watches Finch’s telemetry events and pushes them to the OpenTelemetry collector:

    # Checking for optional opentelemetry dependency
    case Code.ensure_compiled(OpenTelemetry) do
      {:module, _} ->
        defmodule Xtdb.OpenTelemetry do
          @tracer_id __MODULE__

          @spec setup :: :ok
          def setup do
            attach_http_request_start()
            attach_http_request_stop()

            :ok
          end

          defp attach_http_request_start() do
            :telemetry.attach(
              {__MODULE__, :http_request_start},
              [@http_client, :send, :start],
              &__MODULE__.handle_http_request_start/4,
              %{}
            )
          end

          defp attach_http_request_stop() do
            :telemetry.attach(
              {__MODULE__, :http_request_stop},
              [@http_client, :request, :stop],
              &__MODULE__.handle_http_request_stop/4,
              %{}
            )
          end

          def handle_http_request_start(
                _event,
                _measurements,
                %{request: request},
                _config
              ) do
            ...
            Otel.start_telemetry_span(@tracer_id, "XT #{request_url}", %{}, %{
              kind: :internal,
              attributes: attributes
            })
          end

          def handle_http_request_stop(
                _event,
                _measurements,
                %{request: _request, name: ConnectionPool, result: {_, %{status: status} = result}},
                _config
              ) do
            context = Otel.set_current_telemetry_span(@tracer_id, %{})
            Span.set_attribute(context, :"http.status", status)
            ...
            OpenTelemetry.end_telemetry_span(@tracer_id, %{})
          end
        end

      _ ->
        nil
    end

Encoding EDN

In Elixir are using Eden library in order to decode and encode data from and to EDN format. In most cases it works without any issues, however, for decimals we had to implement protocol support for Elixir Decimal type:

    # Implements Eden.Encode protocol for Decimal structs
    defimpl Eden.Encode, for: Decimal do
      # Adds a decimal digit number so it can be picked by EDN
      @spec encode(Decimal.t()) :: String.t()
      def encode(decimal), do: decimal |> Decimal.to_string() |> Kernel.<>("M")
    end

That way you can also implement support of any other custom type or struct that you might need to persist in XT.

Testing XTDB

Because XTDB is an immutable database, it’s not so simple to delete data from it. This can have implications when it comes to testing, as traditional methods of clearing and resetting a database may not be effective. To integrate XT into a test suite, the most straightforward approach is to run an in-memory node alongside the suite. This allows for more granular control over data, as the in-memory node can be reset or recreated as needed.

We’ve achieved that by using the in-memory XTDB Docker image in our docker-compose setup:

    # Wait for Docker container to be ready
    wait:
      image: dokku/wait

    xtdb_test:
      image: juxt/xtdb-in-memory:1.21.0
      ports:
        - 3000:3000

and also created a simple shell script that we use in order to run the integration test suite:


    docker-compose up --remove-orphans -d xtdb_test
    docker-compose run wait -c xtdb_test:3000
    docker-compose run mix_test
    docker-compose stop xtdb_test mix_test

In the case of unit tests, as any request coming through the XTDB client is basically an HTTP request, we could also use VCR cassettes as mocks in order to avoid sending real requests to the test instances.

What’s next?

In this article, we’ve discussed how XTDB can be used alongside applications written in Elixir and demonstrated how to implement a simple HTTP client for working with the database. We also covered how to develop and test applications using XTDB, and the importance of running an in-memory node for testing purposes.

In the third and final part of this series, we’ll be sharing how we used Docker and docker-compose to set up a local development environment for XTDB, as well as how we deployed and ran it in production. We’ll also be discussing caveats and issues we encountered, and how we addressed them.

We can also recommend some more reading on the topic if you’re interested in developing with XTDB:

Happy Hacking and stay tuned!

Thanks Carsten for the review!
Cover image credit: DALL·E 2.

Bitemporality, or how to change the past

Alex Kuznetsov — Fri, 06 Jan 2023 08:34:00 +0000

We can definitely see the whole history of humanity as a chain of events:
tiny events, big events, huge events, crucial events – some of them were negligible and some, on the contrary, predefined the next link in the chain and changed the way our world looks now.

However, the more details we dig out about our history, the more white spots we erase, the more surprising and even contradictious things we see – things that sometimes can completely refute our views and assumptions. Things that we thought and believed to be facts can be invalidated – and we will need to alter the past.

Every kind of record system is not perfect and can contain errors. But what if we wanted to build a system that is resilient to such errors? What if we wanted to change the past but keep the history as a track of facts?

Updates in place

Throughout almost all the existence of computers and computer science, resources were the biggest limitation and one of the most complex issues.

Our progress allowed us to switch from bytes to kilobytes, from kilobytes to mega and gigabytes. Modern PCs, smartphones, and cloud systems can easily handle virtually unlimited amounts of data.

The storage, which was physically enormous at the beginning of the computer era and started with devices taking over the halls and rooms, moved to CDs, floppy disks and hard drives – so we could even carry movies, tons of photos and music in our pockets. The storage, which always seemed to be the problem, became the smallest of our headaches when it came to building software.

Nonetheless, the habit (the bad one) we inherited from the dawn of the computer era is still there and arguably is one of the biggest developer’s headaches – mutability. Before the progress in the storage capacity, we were very careful with how we consumed the RAM and disk space, and that forced us to mutate variables and data instead of persisting them in the immutable way, i.e. saving a new record instead of updating an existing one.

That habit is still haunting us, even though functional programming languages and the immutability approach are on the rise, we still (mostly) update our records in place – as that is the way most popular databases are built.

An audit system

How can we design a record tracking system using a common mutable DB? Let’s say we use PostgreSQL and define a simple audits table:

Company	Issues found	Auditor	Date
BullSheepInc	180	Joe	15.09.2021
OverHypeD	420	Max	14.02.2022
BullSheepInc	0	Max	15.02.2022

However, one and a half months ago we’ve discovered that Max forgot to write down the records for BullSheepInc — and as the column has default of 0, we were unaware of the change for quite some time. Now, if we want to fix it, we have to overwrite the existing record:

Company	Issues found	Auditor	Date
BullSheepInc	180	Joe	15.09.2021
OverHypeD	420	Max	14.02.2022
BullSheepInc	101	Max	01.04.2022

The drawbacks of such an approach are clearly visible: we’ve lost track of the changes and forgot about the mistake. As our reports were already sent, there won’t be any punishment for BullSheepInc.

Temporality

The next step from mutability to our goal of designing a perfect track record system will be to persist changes as facts, instead of updating data in place.
In order to build a truly immutable system we want to disallow overriding records in our audit system. Instead, we will be storing any kind of data change as a
separate row in the database:

Company	Issues found	Auditor	Date
BullSheepInc	180	Joe	15.09.2021
OverHypeD	420	Max	14.02.2022
BullSheepInc	0	Max	15.02.2022
BullSheepInc	101	Max	01.04.2022

It looks like this option worked out better for us; now we see that the inventory record change was tracked and we’ve actually discovered some issues.
We could stop there and pretend we’ve built the most advanced audit system, but that would be too far away from being the truth, as soon we’ve got yet another request …

Retroactive changes

Once our employees started seeing not just audit records but also data fixes, John has recalled that it was actually him who did the counting, and the amount of found issues was actually 99 instead of 101.
We’ve got a serious problem now as the new record doesn’t fit into our data model:

Company	Issues found	Auditor	Date
BullSheepInc	180	Joe	15.09.2021
OverHypeD	420	Max	14.02.2022
BullSheepInc	0	Max	15.02.2022
BullSheepInc	101	Max	01.04.2022
BullSheepInc	99	John	01.05.2022

Which record is really valid now? Should we trust Max or John? How should we define what was the error and how it was corrected ?
That’s where the concept of bitemporality comes to the rescue.

Bitemporality

In the example above, we have only one time column: the record, or transaction date.
Bitemporality assumes adding another time dimension — the so-called valid time or effective time — along the transaction time for tracking when the change really happened.

Transaction time represents the time when the record was inserted into the data storage. This can be quite useful for audit purposes, tracking changes and event sourcing.
Valid time represents when the change became valid and happened in the real world.

If we follow these definitions we can say that transaction time is the time we thought the data was correct at that point in time — and it was actually correct on the valid time:

On the 15th of February, we’ve thought Max has not found any issues.
On the 1st of April, Max corrected the number of issues to be 101.
On May 1st, we’ve discovered that John actually found 99 issues.
In reality, we want the actual amount of issues recorded to be 99 as of 15th of February.

In a bitemporal system transaction time is immutable and can be only increased while valid time can be any past or future timestamp.
Let’s see how we can redesign the audit system using these two time dimensions:

The perfect audit system™

Now that we know how to utilise transaction and valid dates, we can change our records by writing the record time as transaction date and
time when it became valid as valid time:

Company	Issues found	Auditor	Transaction date	Valid date
BullSheepInc	180	Joe	15.09.2021	15.09.2021
OverHypeD	420	Max	14.02.2022	14.02.2022
BullSheepInc	0	Max	15.02.2022	15.02.2022
BullSheepInc	101	Max	01.04.2022	15.02.2022
BullSheepInc	99	John	01.05.2022	15.02.2022

Let’s execute some queries to our database:

    module Audit
      # @return [Hash] a hash with auditor, issues found and transaction date fields
      def get_record(company, valid_date = nil, transaction_date = nil)
        # calling the DB ...
      end
    end

    > Audit.get_record("BullSheepInc")
    # {auditor: "John", issues_found: 99, transaction_date: "01.05.2022"}
    > Audit.get_record("BullSheepInc", "15.09.2021")
    # {auditor: "Joe", issues_found: 180, transaction_date: "15.09.2021"}
    > Audit.get_record("BullSheepInc", "01.01.2021")
    # nil - we didn't inspect the company as of 01.01.2021
    > Audit.get_record("BullSheepInc", "15.02.2022", "01.04.2022")
    # {auditor: "Max", issues_found: 101, transaction_date: "01.04.2022"}

As you can see, now we have the latest correct value returned by default,
but we can also fetch the record the record as it was on a given valid date in the past.

Use-cases

Bitemporality can be proven useful for any system where you have a track of the data and where it’s possible to have errors and recover them:

payrolls, payment systems
auditing
risk systems
blockchains
insurance
compliance & privacy
temporal data management
event-based systems
distributed transactions

Cross-time Database

Supporting bitemporality in an existing database might be not a trivial task, especially when it comes to the traditional relational database where we all relations between tables should also take into account bitemporal columns.

At the moment, the most prominent open-source solution is XTDB (or cross-time) database developed by JUXT which has a lot of benefits compared to its competitors:

Bitemporal at its core
Supports retroactive corrections
Document&graph based (ultimately a store of versioned documents)
Datalog queries and SQL support
Data eviction (supports eviction of active and historical data to assist with technical compliance for information privacy regulations)
Distributed and scalable
Unbundled database (can be deployed on top of many existing technologies and databases like Kafka, JDBC, AWS S3)
Can be easily integrated into any existing JVM application or connected using its REST API

As we will explore more in the next articles, XTDB can be used as a ready solution for building immutable and bitemporal software.

What’s next?

As one can see, bitemporality can be a perfect match for cases where we build systems that are:

Tracking the history of changes or how data was changing over time
Can potentially have errors, corrections or data adjustments

If we take that concept as the cornerstone of such systems there will be way more chances they will be successful and
we will escape from mutability issues.

We can also recommend some more reading on the topic:

In the upcoming article we will share our experience working with XTDB, connecting to it from an Elixir application and what we learned from it.

Happy Hacking and stay tuned!

Thanks Carsten for the review!