XTDB Files

Hey folks - XTDB 2.2.0 is here! 🚀

It's been a long road - so if you've been following along, thank you; if you haven't, here's the whole story in one place.

2.2 is about three things:

• External sources - XTDB can now source its data from systems you already run, so you can run XT alongside your existing databases/topics rather than migrating to it.
• Arrow ADBC & Flight SQL - we've always stored everything as Arrow; now you can get it out as Arrow, all the way into your application's memory.
• A single writer per database - each database now elects a leader, which takes load off the rest of the cluster and makes single-put transactions an order of magnitude faster.

There are a handful of breaking changes coming from 2.1, and a rolling upgrade has to go via 2.1.1 - please do have a read of Upgrading from 2.1 before you roll. (For anyone on a 2.2 pre-release: rc2 -> 2.2.0 is a straight upgrade; from anything earlier, see the rc2 upgrade notes.)

External sources - bring your data to XT, without migrating to it

Until now, everything in XTDB arrived through an XTDB node. In 2.2, XTDB can source its transactions from systems you already run.

This means that you don't have to commit (😀) to XTDB as your primary store to get its value. Keep your existing database, run XT alongside it as a secondary, and get bitemporal audit and time-travel reporting over data you're already producing. Or hang XT off your event-sourcing infrastructure as a time-oriented projection of your event streams.

Two sources ship in 2.2:

• PostgreSQL - XTDB connects to your existing Postgres cluster as a logical-replication client, and mirrors its changes in as bitemporal history (setup guide).
• Kafka Connect - interoperable with your existing Kafka Connect transforms and predicates, so XT sits naturally in a Kafka Connect topology (setup guide).
• (and we did also spike kdb+ for fun one afternoon, but that's very much at proof-of-concept level for now)

Setting one up is a remote in your node config:

:::yaml
remotes:
  pg_remote: !Postgres
    hostname: my.pg.host
    port: 5432
    database: my_upstream_db
    username: !Env PGUSER
    password: !Env PGPASSWORD

and then an ATTACH DATABASE:

:::sql
ATTACH DATABASE pg_test_db WITH $$
  log: !Local
    path: 'pg_test_db/log'
  storage: !Local
    path: 'pg_test_db/storage'

  externalSource: !Postgres
    remote: pg_remote
    publicationName: xtdb
    slotName: xtdb
    indexer: !DirectMirror {}
$$

If you're running one in production, the Postgres source's setup guide and reference cover what you'll want to know about the upstream - PostgreSQL 17 or later, sizing your WAL retention, and surviving a failover.

Under both sources sits the same pluggable source SPI we used to build them (in xtdb.api.tx) - so you can write your own, against whatever upstream you like. It's a supported part of the public API from 2.2.0; if you build one, we'd love to see it - please do get in touch.

And if you'd rather keep ingestion separate from your query nodes, there's a first-class ingest-only node: xtdb.main ingest -f config.yaml (or the Docker ingest command) runs sources, indexers and logs from YAML, independently of your query nodes (docs).

See the external sources overview for the full picture.

If you've got existing Postgres tables or Kafka event streams, this is the bit of 2.2 I'd most like you to kick the tyres on - please do let us know how you get on.

Arrow ADBC & Flight SQL

We've been excitedly following Arrow ADBC and Flight SQL for a while now - in 2.2, XTDB supports both: ADBC in-process, and a Flight SQL server for remote access.

For those who haven't come across it: ADBC ('Arrow Database Connectivity') is a database client API in the spirit of JDBC and ODBC, but built around Apache Arrow's columnar format throughout. Flight SQL is the Arrow project's SQL wire protocol, which ADBC clients use to talk to a remote server.

This is a particularly natural fit for XT, because we already store everything internally as Arrow. Queries over the Postgres wire protocol pay a hidden tax: we translate out of Arrow into pgwire's row-oriented types, only for your client to possibly translate it back into something columnar - a pandas or Polars DataFrame, a DuckDB table, an Arrow Dataset. With ADBC and Flight SQL, that round trip goes away: results stay as Arrow from XTDB's storage all the way into your application's memory.

From Python, over the wire, via the Flight SQL driver - bulk-ingesting an Arrow table in one round trip, and getting the results back as Arrow:

:::python
import adbc_driver_flightsql.dbapi as flight_sql
import pyarrow as pa

with flight_sql.connect("grpc://localhost:9832") as conn:
    with conn.cursor() as cur:
        users = pa.table({
            "_id":  ["alice", "bob", "carol"],
            "name": ["Alice", "Bob", "Carol"],
            "age":  [30,      25,    28],
        })
        cur.adbc_ingest("users", users, mode="create_append")

        cur.execute("SELECT _id, name FROM users WHERE age > ?", parameters=[26])
        print(cur.fetch_arrow_table().to_pydict())

From Kotlin/Java, in-process - node.connect() returns an ADBC connection, so there's no network hop and no copy:

:::kotlin
Xtdb.openNode().use { node ->
    node.connect().use { conn ->
        val stmt = conn.createStatement()
        stmt.setSqlQuery("SELECT _id, name FROM users WHERE age > 26")
        stmt.executeQuery().use { result ->
            // result.reader is an org.apache.arrow.vector.ipc.ArrowReader
        }
    }
}

And from Clojure, xtdb.api now accepts an ADBC connection as well as a JDBC one - in-process, we've seen up to 3x over going through pgwire.

If you've been waiting to point an Arrow-native client at XTDB - PyArrow's Flight SQL client, Python's adbc-driver-flightsql, the JDBC Flight SQL bridge - this is the release where it works end-to-end. The full supported surface is in the ADBC reference.

A shout-out, too, to the folks at Columnar - founded by core Apache Arrow / ADBC developers - whose CLI dbc takes the friction out of installing ADBC drivers. We're aiming to land an XTDB driver in dbc in due course - keep your eyes peeled.

A single writer per database

Since the very first release of XTDB 1, every node has read the whole transaction log and resolved every transaction itself. In 2.2, each database elects a leader instead: the leader resolves each transaction once, and writes the outcome to a 'replica log' that the rest of the cluster follows.

What that means for you:

• Less work for the rest of the cluster - followers apply the leader's results, rather than each re-running every transaction themselves.
• Read-only secondary nodes - nodes that never contend for leadership, they just read the leader's results. This opens the door to XTDB as a federated database: query across other XTDB deployments in your organisation, just by opening read-only access to their log and object store.
• Single-put transactions are an order of magnitude faster. If you're ingesting one transaction per message - off an existing Kafka topic, say - each transaction used to wait out a network round trip; now the leader pipelines them:

1M documents, Kafka replica log	batch size 1	batch size 1000
awaiting each append	246.1 s	2.68 s
pipelined (2.2.0)	22.8 s	2.70 s

A design partner's production deployment, ingesting 1M of its own (larger) documents off Kafka, went from 264 s to 61 s.

• Kafka no longer elects our leaders for us. XT runs its own (Raft-inspired) election in the replica log, so it no longer needs consumer groups or transactional producers - which means Azure Event Hubs' standard tier now works, and it's the first step towards Kafka not being a (pretty much) mandatory dependency for highly-available deployments.

If you're curious how it works - and how we got there (via a couple of pre-releases I wasn't happy with 😅) I've written it up on the blog: Following the leader: leadership election in XTDB 2.2.

Postgres-ecosystem compatibility

One of the main reasons we went with SQL and Postgres wire-compatibility in XT 2.x was to make the vast Postgres ecosystem available to XTDB users - so a fair proportion of 2.2 has gone on supporting the SQL that its tools expect to find.

We've been led largely by what our clients actually use: Metabase and Grafana both now connect to XTDB through their standard Postgres connectors, and on the .NET side, Npgsql and Entity Framework Core (LINQ queries, and dotnet ef dbcontext scaffold) now work too.

The bits that fell out of that, which aren't tool-specific:

• Functions: LEAD, LAG, PERCENTILE_CONT/PERCENTILE_DISC (medians, for one), MIN/MAX over strings, ILIKE, array_agg(x ORDER BY y), string_to_array, array_length and friends, IS [NOT] DISTINCT FROM, and RANGE alongside GENERATE_SERIES.
• Aggregate FILTER, so you can count two different things over the same groups without reaching for a subquery:

sql SELECT dept, COUNT(*) AS headcount, COUNT(*) FILTER (WHERE status = 'active') AS active FROM employees GROUP BY dept

• Expressions where you'd expect them to work: GROUP BY takes expressions and select-list aliases (GROUP BY DATE_TRUNC('day', created_at)), subqueries can go in ORDER BY, and function calls can go in FROM.
• Postgres's spellings: casts take Postgres's type names (x::int4, float8, bool, int8[]), Postgres's non-reserved keywords (date, year, row, ...) work as table and column names, and WHERE created_at > '2026-01-01' does the obvious thing.
• Session and transaction surface: SET TRANSACTION, COMMIT [SYNC | ASYNC], DISCARD ALL, and CREATE TABLE with columns.
• Catalog introspection: much more of pg_catalog and information_schema is modelled, so the probes these tools fire on connect plan and return sensible answers rather than erroring.
• Identifying XT: version() now returns PostgreSQL 16 (XTDB 2.2.0), and current_setting('xtdb.version') gives you the version on its own - if you've been sniffing for XT by probing incidental behaviour, this is the intended way.

Operations & observability

With XTDB now in production across a number of client sites, we've had a lot of valuable feedback (thank you!) on what actually matters when you're running it:

• Garbage collection: XT's primary index is a log-structured merge tree on object storage, which periodically compacts its files; 2.2 adds a background process that removes the files that compaction has superseded, keeping storage costs in check.
• Blocks are now flushed every 15 minutes on a quiet database, rather than every four hours (breaking, in that it's a new default). That interval turns out to bound four separate things: how much recent data lives only in the log, how long a starting node spends replaying the tail, how stale a read-only secondary can be, and how much WAL an external source's upstream has to retain. Busy databases cut blocks on row count long before the timer fires, so they're unaffected.
• Tracing: OpenTelemetry tracing now covers transactions as well as queries, and you can enable each independently, so you don't pay for the noise of one while you're investigating the other.
• Metrics: plenty more for your Prometheus dashboards - per-database lag, leader/follower status, end-to-end pgwire latency, block-upload times, allocator memory, GC, and the external sources.
• EXPLAIN ANALYZE: scans now report the rows, pages and files they pruned versus used, and every operator reports a self_time alongside its total_time - so you can see at a glance both which work the planner elided and where the time actually went.
• Cleaning up after clients: a pgwire client that disconnects mid-query now cancels it, and a cancel reaches every operator in the plan rather than only being checked at the root.
• Fewer ways to wedge a node: object-store I/O is now bounded, so one stalled request no longer exhausts the shared pool; a replica-log message too large for Kafka goes to object storage rather than stopping the database; and a node whose log is behind its storage refuses to start (pointing you at the runbook) rather than acknowledging transactions it would then discard.

Performance

We've spent a fair proportion of the 2.2 line with our heads in the profiler. As well as single-put transactions (see above):

• WHERE _id IN (...) on non-UUID _ids is ~10-60x faster, depending on the size of the list - the index lookup no longer degenerates into a full table scan.
• The ingest path: a transaction now writes straight into the live index rather than being copied in on commit (15.2k -> 27.8k single-put transactions/s); a repeated DML statement plans once rather than on every execute (861 µs -> 674 µs per single-row INSERT over pgwire); and a transaction's Arrow data is serialised with one copy rather than four or five.
• Sorts with a LIMIT keep only their top k rows, rather than sorting everything and throwing most of it away.
• Join conditions that only read one side are now evaluated in that side's scan, rather than once per candidate pair.
• Correlated EXISTS (SELECT 1 ...) - i.e. how you'd idiomatically write it - now decorrelates into a semi-join.
• Scans no longer copy every freshly-written page on load - on a TPC-H SF1 scan, that copy was 3s of 4.8s.

Upgrading from 2.1

2.2 writes transactions to the log in a new format, which 2.1.0 can't read - so how you upgrade depends on whether you can stop the cluster:

• Stop-start (all nodes down, then all nodes up): directly from 2.1.0.
• Rolling / blue-green: via 2.1.1 - 2.1.0 -> 2.1.1 -> 2.2.0, each step rolling. 2.1.1 is 2.1.0 plus the ability to read 2.2's transactions, so it can share a log with 2.2 nodes while you roll (#6248). Going straight from 2.1.0, the first transaction a 2.2 node accepts stops the remaining 2.1.0 nodes ingesting. Do keep the window where 2.1.1 and 2.2 nodes are running side by side short - they each write blocks, so it's not somewhere to linger.

Before you roll - these will stop a node starting, or change how it runs:

1. Authentication: CREATE USER / ALTER USER and the !UserTable authenticator have gone. Move statically-configured users to !UserList, set XTDB_PASSWORD for the local-dev root user, or - our recommendation for production - use OIDC (docs).
2. Logging: we've moved from Logback to Log4j2, defaulting to structured JSON - if you had a custom logback.xml, you'll need to port it (docs).
3. Docker: the jar is now always xtdb.jar and the default config always config.yaml, so update any custom Dockerfiles, init scripts or volume mounts pointing at the old paths. The images also now ship JDK 25, so any flags you pass through JDK_JAVA_OPTIONS need to be valid on 25.
4. In-process on JDK 25: add -Dio.netty.noUnsafe=false alongside the existing --add-opens - Arrow's allocator fails to initialise without it. (JDK 21 is still the minimum, and nothing changes there.)
5. Kafka: each database now has two topics - its existing source topic, and a replica topic, defaulting to ${topic}-replica. XT will auto-create it if autoCreateTopic is on, but auto-created topics are unreplicated, so for production you'll want to create it yourself; if you're using ACLs, XT needs Describe/Read/Write on both (docs).

SQL and behaviour changes - worth a check against your queries:

• Unknown tables and columns now error, rather than silently returning nothing - catching a typo'd table name is rather more useful than an empty result. CREATE TABLE (with or without columns) declares a table before you've written to it.
• GENERATE_SERIES is end-inclusive, matching Postgres - if you relied on the old exclusive end, switch to RANGE.
• USER is now a reserved word (a synonym for CURRENT_USER, as in the SQL spec) - a table or column called user now needs quoting as "user".
• FROM foo(x) is a function call; a column list on a table needs an alias, as in standard SQL - FROM foo AS f(x).
• Operators bind in Postgres's precedence order - XT had drifted, mostly into obviously-wrong readings (a = b IS TRUE read as a = (b IS TRUE)), but a query that happened to rely on one will now bind the other way.
• A transaction's snapshot and current_timestamp are fixed at its first query, not at BEGIN, as Postgres does - so a write by another connection between your BEGIN and your first query is now visible. If you were relying on BEGIN to pin them, pin them explicitly with SNAPSHOT_TOKEN / CLOCK_TIME.
• SET inside a transaction belongs to it - ROLLBACK discards it, and one that can't be honoured (in a read-write transaction with buffered writes, say) is rejected.
• An explicit SYSTEM_TIME has to be later than the latest transaction's - if you're backfilling, give each transaction its own.
• UPDATE skips writes that would produce an identical document version, so an idempotent updater no longer adds history.
• information_schema.columns.data_type holds Postgres's type names (bigint, timestamp with time zone); XT's own rendering has moved to a new xt_type column.
• JSON output: decimals come back as numbers rather than strings, and error details over pgwire are plain JSON ({"category": ..., "code": ..., "message": ..., "data": ...}) rather than JSON-LD.
• Blocks flush every 15 minutes on a quiet database (see above) - expect more, smaller blocks, and correspondingly more compaction work. An explicit flushDuration is honoured as before.

Deprecated - still working in 2.2, but on their way out:

• Inline storage credentials in ATTACH DATABASE (S3 credentials, Azure storageAccountKey) end up on the source log, and from there in your object store. Move them into node-local !S3 / !AzureBlob remotes and reference those with remote: instead - only the alias is persisted. (Ambient AWS/Azure/GCP credentials are unaffected.)
• logClusters is now remotes - the old name is still honoured, so rename when convenient.

Rolling back

You can roll back to 2.1.1 (not 2.1.0, which can't read 2.2's transactions):

1. Detach any databases 2.1 doesn't have a concept of - read-only secondaries and external-source databases. 2.1 would read them as ordinary read-write databases, and a read-only attachment to another deployment would start writing blocks into its storage.
2. Revert the config changes above - authentication, logging, Docker paths, remotes: -> logClusters: - and remove any 2.2-only config (flightSql, remote: references in storage config).
3. Roll back to 2.1.1.
4. Before rolling forward to 2.2 again, delete each database's replica topic (${topic}-replica, unless you've named it) - otherwise the 2.2 nodes would replay the old replica log on top of the blocks 2.1.1 has since written.

If you're unsure about any of this - particularly if you're running external sources or read-only secondaries - please do get in touch before you roll, rather than after 🙏

Elsewhere

• #5543 - FOR VALID_TIME ONLY FROM x TO y clamps the _valid_from/_valid_to you get back to that window - which also lets the scan skip pages it would otherwise read only to clamp away. UPDATE, DELETE and PATCH always mean 'only', so they get the same speed-up for free.
• #5397, #5402 - xt.source_log_msgs, xt.replica_log_msgs, and block/table-block admin tables (xt.block_files and friends), so you can see what's on the log and in storage from SQL, rather than reaching for the CLI tools.
• #5425 - XTDB_SKIP_DBS starts a node with the named secondary databases left dormant - for when a secondary's topic has gone, and would otherwise stop the whole node starting.
• #5860 - from Clojure, xtdb.api also works over a remote Flight SQL connection (xtdb.flight-sql/open-conn), not just in-process.
• #5821 - query and transaction metrics are recorded for in-process and Flight SQL callers too, not only over pgwire.
• #5909 - /healthz/started no longer waits on a non-critical database catching up.
• #5317 - pgwire runs its connections on virtual threads, which stops glibc's malloc arenas growing with every new connection thread - previously a slow climb towards a container OOM-kill under query load.
• #5218, #5248, #6140 - S3: uploads no longer copy buffers onto the heap (an OOM, then a stalled node), and a block with thousands of tables no longer exhausts the client's connection pool.
• #5778 - Kafka retention expiring past a quiet database's last block no longer stops that node ingesting on restart.
• Ingestion no longer stops on a PATCH with a nested null (#4990), a PATCH over lists of structs containing nulls (#5268), or a column holding only empty lists (#5948).
• Wrong results, now right: more than eight MATERIALIZED CTEs ran in the wrong order (#5109), POSITION over a SUBSTRING ignored the substring (#5168), ordered-set aggregates dropped trailing groups (#5957), ORDER BY ... DESC put NULLS FIRST/LAST on the wrong side (#6172), and concurrent Arrow result streams could corrupt each other (#6231).
• #6150 - the AWS deployment is now Terraform and Helm only; the CloudFormation templates have gone.

As always, the milestone has the full list - all 216 of them.

2.2 has been one of the longest release lines we've run, and we couldn't have got it here without the people running the pre-releases in anger. A big thanks to everyone raising issues, sending reproductions and benchmarks, and kicking the tyres - and especially to our design partners and clients: an awful lot of what's above started life as an issue raised from a production deployment, and it's massively appreciated 🙏

If you've any questions or thoughts, please do get in touch - we'd love to hear from you!

• Discord
• hello@xtdb.com

Cheers!

James & the XT Team