XTDB for NZ Energy Offers

Overview

This is the 2nd post in a series about databases originating in the Clojure ecosystem. The first article on Datomic is linked here.

In this post, we'll be looking at XTDB version 2. It's a bitemporal SQL database that runs on top of Postgres.

Initially, I was excited about giving XTDB a try as I'm familiar with bitemporal models from using the Australian AEMO MSATS repository. I've also used Postgres in several production contexts.

As a quick reminder, the dataset I'll be using for the investigation is the daily energy offers published by the Electricity Authority in New Zealand. To describe the set in short, it allows sellers of energy and reserves to make offers to supply the NZ wholesale market.

---

Kicking Off

The quickstart guide on the XTDB site was easy to follow. Using the native psql client, everything appeared like standard SQL. For example, from the first example in the tutorial, the inserts were of the form:

INSERT INTO people (_id, name) VALUES (6, 'fred');

I also reviewed the Clojure/JDBC guide and noticed that the syntax differed. It was demonstrated as below using nested JSON. I'll return to this later.

My local setup was straightforward. Here is my local tmux session, with start-xtdb.sh just being a wrapper around docker run:

docker run --name xtdb -p 5432:5432 ghcr.io/xtdb/xtdb

---

Inserting Data - Take 1

Since I already had the parsing logic from the first post using Datomic, I thought this would be a quick exercise, but I was wrong, so very very wrong. I knew that XTDB didn't require a schema upfront, so I figured a straight insert with some bind parameters would be enough.

Given the error, I thought that having a table definition might help with the inserts. I have since found out that XTDB doesn't support user-defined tables, but let's follow through with the errors below anyway.

1. First, I attempted to create a table and received the error message "Error 1" below. line 1:7 missing 'USER' at 'TABLE'. It kind of sounds like XTDB knows about tables from this, but I'd just missed a user prefix.

2. I tried both an xtdb and postgres prefix on the create table statement after which I got the even more helpful message recorded in "Error 2" below. All arguments to `concat` must be of the same type. At this stage, I realised I was in common Clojure territory, where the error messages I was getting had nothing to do with the underlying issue.

---

Inserting Data - Take 2

Now that I was aware that XTDB didn't support user-defined tables (Even though the errors might lead you to believe otherwise), I returned to trying to encode type information into the INSERT statement. My first attempt was to use inline type annotations in the statement itself. I then tried CASTs and, lastly, the "jdbc.next.types" and prepared statement "setParameter" methods.

All ended up with the error: ERROR: Missing types for args - client must specify types for all non-null params in DML statements

---

Give the AI a crack

It was time to give the AI overloads a crack, first I fired up Claude Code on the Opus model. I love Claude Code and use it most days at work but it failed to make any progress. It basically went around in circles using different next.jdbc/bind parameter incantations but never actually solved the problem.

I started an OpenAI session on the 03 model and provided it with the full namespace as input, along with the error. It generated a number of suggestions, but again, none of them worked.

It seemed that the SQL bind parameter approach I know and love was no longer viable. Next, I decided to attempt the nested 'RECORDS' syntax from the Clojure driver documentation. It deviates from standard JDBC, and XTDB is sold as a SQL database, but ah well *shrug*.

---

Nested Record Inserts

Well, now the syntax was going to be much simpler, I started with:

(def offers-insert "INSERT INTO offers RECORDS ?")

And was ready to go with a plain Clojure map, executed it and....

Execution error (PSQLException) at org.postgresql.jdbc.PgPreparedStatement/setMap (PgPreparedStatement.java:547).

No hstore extension installed.

By this stage, I'd run into so many errors that I was determined to get something to work. All that remained was the insertion of a nested JSON doc.

In the end, it was a hack using string concatenation, but I was able to insert the records; this process took over an hour for ~270k records with no batching or overarching transaction.

This allowed me to run some entry-level analytic queries, but even then, I hit parsing problems when trying to issue a query that used the ROUND operation.

---

Summary

Unfortunately, working with XTDB proved to be a frustrating experience. A SQL database should be far easier to populate than this. There are so many strong relational databases including plain Postgres itself that XTDB is a hard sell.

The automatically added bitemporal fields are nice to have, but they're hardly a game changer. At Red Energy, we have been using these bitemporal models for 20 years.

I really need to mention the error messages again. They were misleading and burned developer time trying to interpret them. In the end, it was better to pretend there was no error message and to just reason about what the problem might be.

There are only so many times I can slowly shake my head in frustration when another Clojure-based offering prints some unintelligible gibberish instead of a helpful error message.