Hacker News Reader

Comment by beders | Hacker News Reader

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

> 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?

Nice work Andy. I'd love to hear about semantic layer developments in this space (e.g. Malloy etc.). Something to consider for the future. Thanks.

> I'd love to hear about semantic layer developments in this space (e.g. Malloy etc.)

We also hosted Llyod to give a talk about Malloy in March 2025:

https://db.cs.cmu.edu/events/sql-death-malloy-a-modern-open-...

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.

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

Are there any new big research/development in immutable database?

I know they are great... but i don't see many news around them

How do you deal with deletion requirements in a immutable database? Like how do you delete personal data when requested?

Crypto shredding?

The fastest approach is to just zero out the data. Alternatively you can rebuild the entire database while preserving only the data accessible in the latest copy of the db (kinda similar to SQLite's VACUUM command).

In the case of Datomic: https://docs.datomic.com/operation/excision.html

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.

Lance graph?

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?

> 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.

FYI I made a comment very similar to yours, before reading yours. I'll put it here for reference. https://news.ycombinator.com/item?id=46503181

Btw Datomic is free now that Nubank supports it (and runs a large bank on it).

There's also a fantastic kind of mini, FOSS, file-based Datomic-style Datalog DB that's not immutable called Datalevin. Uses the hyper-fast LMDB under the hood. It's called Datalevin. https://github.com/juji-io/datalevin

Why fintech specifically?

Because, money.

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.

Not only transactions - but state of the world.

compliance requirements mostly (same for health tech)

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.

Reply on news.ycombinator.com