Development Diary #11

We’ve spent a fair proportion of this month with our heads buried in the profiler!

One area that we’ve particularly focused on is our use of Arrow’s 'union' vectors, which accounted for a not-insignificant proportion of both ingest and query speed. Union vectors are how XT2 gets its polymorphism.

'Normal' columnar use-cases benefit hugely from monomorphic vectors - knowing that you’ve got a whole column of (for example) fixed-width 32-bit integers ('i32s') arranged contiguously in memory gains you a lot of performance. In contrast with row-oriented storage (where you need to first locate the start of each row, then scan through the row’s other values to find the right integer column), in columnar-oriented storage, I know that to get the value of a certain i32 column from the 34th row, I go straight to byte 34 * 4 = 136 and read my four-byte integer. Similarly, if I’m summing the column, I don’t have to skip over any superfluous data for the rest of each row - I know the start byte offset of each column, I simply start summing from there.

In XT2, though, we’ve elected to give users more flexibility in the data they put in their documents. In Arrow, this means using 'union' vectors, which allow values of different types in a given column. (We use 'dense' unions, which we shorten to 'DUVs'; there are also 'sparse' unions, which I won’t cover here.)

Dense unions do this by storing two additional columns, as well as a column for each type. So, for a column that might either contain i32s or strings, say, we’d store four columns:

For example, to store the vector [14, 18, "foo", 16, "bar"], we’d represent this as follows:

One breaking change that we’ve introduced as part of this work is the coalescing of absent and null values. Previously, we endeavoured to distinguish between (e.g.) {a: 1, b: null} and {a: 1}, in order to round-trip documents where possible.

(Notably, neither Arrow nor the SQL standard make this distinction.)

The cost of this decision, though, comes from the fact that Arrow therefore only has one type of 'null' - so we had to create our own Arrow extension type in order to separate the two. We used Arrow’s 'null' for the former case, and added an explicit 'absent' type for the latter, which had to go in a union vector.

This meant (and hands up if you can see where this is going! ✋) that whenever users inserted documents with different sets of keys, we had to fall back to DUVs, completely nullifying (ha!) the above performance improvements.

We’ve now reversed this decision, and represent both absence and null using Arrow’s built-in 'null' flag, which doesn’t carry this tradeoff. We’d recommend users that did previously depend on this distinction (i.e. attached implicit meaning to an explicitly provided 'null' in a map) instead use a separate enum column to represent the underlying reason for the 'null'.

More generally, putting my introspection hat on for a moment, this hopefully gives some insight into our decision making process for XTDB. While in certain areas we’re very keen to innovate (in the areas that make XTDB unique and special), in others, we very much value familiarity, consistency, prior art, and a lack of surprise.

The fun comes when these four sources of prior art disagree with each other, as they often do!

Separately, we’ve been improving our SQL coverage. This month’s focus has mainly been on casting, parsing and rendering operations for various temporal types. Temporal types are without doubt one of the gnarliest areas when it comes to the aforementioned disagreements between Arrow, SQL, Postgres and Java - we even ended up writing a large matrix of how each of them treat instants, dates and timestamps, as well as periods, durations and intervals, and the subtle differences between them.

Keep your eyes peeled for our upcoming documentation improvements on this subject!

On the SQL tooling integration side, we’ve been investigating popular SQL tools (DBeaver, Metabase etc.) to find out how best to get them talking to XT2 - we’d love it if you could Just™ use all of your existing tooling. Our approach here (not dissimilar to CockroachDB or CrateDB, for example) is to implement a Postgres wire protocol server, so that we can capitalise on the existing availability of Postgres support.

We have the initial seeds of a solution here, in our (even more) experimental pgwire-server module - you can already psql -h your-xtdb-server.com, submit transactions and run queries.

Our current iteration is focused on the initial connection queries that these tools send to Postgres (or what they think is Postgres, at least! 🕵). So far we’ve implemented some of information_schema and pg_catalog, so you can query those metadata tables to find out what tables/columns are present in your database.

Those tools also rely on some SQL language constructs that we haven’t yet implemented - for example, some access control function calls, and information about the current user/session/connection. Once we have, this should mean that these tools will then connect to XTDB and query it in the normal way - watch this space!

We have produced a quickstart tutorial that attempts to introduce XT2’s main concepts to an audience who is already familiar with SQL. This tutorial is accompanied by a small, dependency-free Python script - xtsql.py - which works as an interactive SQL console much like psql when pointed at your XT2 server’s HTTP endpoint.

For new users who would prefer to avoid downloading XT2 before they can try using it, we have also created XT Fiddle (in a similar vein to https://www.db-fiddle.com/) - a stateless backend which creates fresh instances of XT2 upon each request. This service is also particularly useful for sharing both working and non-working examples as simple URLs.

Feedback on any of these things would be very welcome over on this forum post.

For anyone curious about some of the motivations for our work on XTQL (and Datalog previously), we spoke with Prof. Viktor Leis for the latest episode of the JUXT Cast, which you can listen to anywhere or watch here:

As always, we’d love to hear from you - we’ve already had many insightful conversations with people who’ve given XT2 a spin which have influenced our roadmap, and it’d be great to get your thoughts too:

Similarly, if you’d like a demo of XT2, or to be kept informed about its progress, come say hello at hello@xtdb.com.

Cheers,

James