Immutable Bi-temporal Databases

Advocacy for XTDB and Datomic for fintech compliance, discussion of audit requirements, time-travel queries, and lack of production-ready options in this category

Immutable and bi-temporal databases are increasingly vital for fintech and health-tech, where compliance mandates non-destructive audit trails and the ability to query the "state of the world" at any point in history. While specialized systems like XTDB and Datomic offer powerful time-travel capabilities, some developers prefer "bolting" these features onto Postgres using range types and audit triggers to avoid the steep learning curves and "exotic" nature of standalone immutable engines. The discussion reveals a perceived "blind spot" in mainstream database discourse, highlighting a demand for more modular, "lego-like" storage engines that could bring these sophisticated temporal features to standard SQL environments. Ultimately, advocates argue that moving away from destructive operations not only ensures regulatory peace of mind but also unlocks technical benefits like seamless concurrency and instant transaction undos.

View on HN · Topics

While the author mentions that he just doesn't have the time to look at all the databases, none of the reviews of the last few years mention immutable and/or bi-temporal databases.

Which looks more like a blind spot to me honestly. This category of databases is just fantastic for industries like fintech.

Two candidates are sticking out.
https://xtdb.com/blog/launching-xtdb-v2 (2025)
https://blog.datomic.com/2023/04/datomic-is-free.html (2023)

View on HN · Topics

> none of the reviews of the last few years mention immutable and/or bi-temporal databases.

We hosted XTDB to give a tech talk five weeks ago:

https://db.cs.cmu.edu/events/futuredata-reconstructing-histo...

> Which looks more like a blind spot to me honestly.

What do you want me to say about them? Just that they exist?

View on HN · Topics

You can get pretty far with just PG using tstzrange and friends: https://www.postgresql.org/docs/current/rangetypes.html

Otherwise there are full bitemporal extensions for PG, like this one: https://github.com/hettie-d/pg_bitemporal

What we do is range types for when a row applies or not, so we get history, and then for 'immutability' we have 2 audit systems, one in-database as row triggers that keeps an on-line copy of what's changed and by who. This also gives us built-in undo for everything. Some mistake happens, we can just undo the change easy peasy. The audit log captures the undo as well of course, so we keep that history as well.

Then we also do an "off-line" copy, via PG logs, that get shipped off the main database into archival storage.

Works really well for us.

View on HN · Topics

People are slow to realize the benefit of immutable databases, but it is happening. It's not just auditability; immutable databases can also allow concurrent reads while writes are happening, fast cloning of data structures, and fast undo of transactions.

The ones you mentioned are large backend databases, but I'm working on an "immutable SQLite"...a single file immutable database that is embedded and works as a library: https://github.com/radarroark/xitdb-java

View on HN · Topics

I see people bolting temporality and immutability onto triple stores, because xtdb and datomic can't keep up with their SPARQL graph traversal. I'm hoping for a triple store with native support for time travel.

View on HN · Topics

Why fintech specifically?

View on HN · Topics

Destructive operations are both tempting to some devs and immensely problematic in that industry for regulatory purposes, so picking a tech that is inherently incapable of destructive operations is alluring, I suppose.

View on HN · Topics

I would assume that it's because in fintech it's more common than in other domains to want to revert a particular thread of transactions without touching others from the same time.

View on HN · Topics

Not only transactions - but state of the world.

View on HN · Topics

compliance requirements mostly (same for health tech)

View on HN · Topics

Because, money.

View on HN · Topics

XTDB addresses a real use-case. I wish we invested more in time series databases actually: there's a ton of potential in a GIS-style database, but 1D and oriented around regions on the timeline, not shapes in space.

That said, it's kind of frustrating that XTDB has to be its own top-level database instead of a storage engine or plugin for another. XTDB's core competence is its approach to temporal row tagging and querying. What part of this core competence requires a new SQL parser?

I get that the XTDB people don't want to expose their feature set as a bunch of awkward table-valued functions or whatever. Ideally, DB plugins for Postgres, SQLite, DuckDB, whatever would be able to extend the SQL grammar itself (which isn't that hard if you structure a PEG parser right) and expose new capabilities in an ergonomic way so we don't end up with a world of custom database-verticals each built around one neat idea and duplicating the rest.

I'd love to see databases built out of reusable lego blocks to a greater extent than today. Why doesn't Calcite get more love? Is it the Java smell?

View on HN · Topics

> it's kind of frustrating that XTDB has to be its own top-level database instead of a storage engine or plugin for another. XTDB's core competence is its approach to temporal row tagging and querying. What part of this core competence requires a new SQL parser?

Many implementation options were considered before we embarked on v2, including building on Calcite. We opted to maximise flexibility over the long term (we have bigger ambitions beyond the bitemporal angle) and to keep non-Clojure/Kotlin dependencies to a minimum.

View on HN · Topics

I think it's time for a big move towards immutable databases that weren't even mentioned in this article. I've already worked with Datomic and immudb: Datomic is very good, but extremely complex and exotic, difficult learning curve to achieve perfect tuning. immudb is definitely not ready for production and starts having problems with mere hundreds of thousands of records. There's nothing too serious yet.

Summarizer