Hacker News Reader

Comment by clusterhacks | Hacker News Reader

Several comments mention the data immutability of Datomic as a plus and I just wanted to say you can totally make a plain-old-RDBMS table append-only and get those benefits. I'm sure this is commonly done.

I did it with a timestamp on the tables that was captured at insert time. All reads were against views of the tables that were defined such that the only tuples returned were the "most recent" tuples by appropriate data fields and max(timestamp). "Deleted" records were just indicated by a flag.

This preserved the ability see the full history for a tuple from creation, all mutations, all the way to deletion. This scaled reasonable well up to low millions of tuples on a normal, single database server. But it was for an internal project, so the number of clients hammering at it was quite low.

View on HN

> Several comments mention the data immutability of Datomic as a plus and I just wanted to say you can totally make a plain-old-RDBMS table append-only and get those benefits. I'm sure this is commonly done.

Absolutely, you can also store an append-only log to a txt file on disk and have your API backend recreate the "current" data from that append-only log.

I guess people mentioning as a plus as it's out-of-the-box default behavior for temporal databases like Datomic or XTDB, where they are optimized for these type of queries with years of work done on it.

Just as a fun aside and only slightly related: I discovered the other day that MariaDB supports (bi)temporal tables out-of-the-box too! Probably the only (FOSS) SQL database that does so? https://mariadb.com/kb/en/temporal-tables/

This works to a limited extend and leads to a huge complexity explosion as soon as you go beyond a single table. Going down this route is soon gone eat much of your complexity budget.

I worked on a hospital information system that did this for all forms, and the parts of the table had complex self referential links. Getting the actual history out of that thing was SQL hell.

I agree that this approach would be pretty unwieldy for a large/complex backend schema. It worked quite well for a smaller, in-house application and set of requirements. I think I probably would be perfectly happy using the approach again for our in-house applications if requirements for audit/history preservation needed to be built into a data model. I don't have a good feeling for when I might say "this data model is too large/complex for this approach." I might instead think more about what and how many subsystems are going to have direct access to the RDBMS as a cut-off?

Hospital/medical research information systems (my day job is in the backends of these apps) seem to use backends with many compromises and poor db designs bolted on. I have also dealt with the horror of electronic data capture forms. My most recent headache has been a poor data model that ultimately wraps each form in a very ugly JSON blob. I've never seen anything that so completely lacks any residue of design . . .

Sure, most things are representable in a table. Its very tricky to get these things to perform well. Every query needs to have the additional filters/aggregations and every index needs to be smart and partial. E.g. you need all your indexes to be partial with a CREATE INDEX WHERE deleted_at = null for the deleted case as having a giant boolean filter can quickly include a large percentage of your data making the indexes useless.

I tried this once many years ago and it became quite a headache. Direct SQL access to the database to perform management queries outside your application becomes a headache too.

This description isn't too far from bitemporal tables, which are one of my favorite obscure technologies.

I think though that one distinction is that immutability in this case requires cooperation from the client, a commitment not to modify existing records. As compare to the database enforcing it.

This could be enforced in the schema via triggers and/or security permissions. Cooperation from the client is not required.

EDIT: Oracle has append-only tables, and can also use "blockchain" to verify integrity. See the IMMUTABLE option on CREATE TABLE[0]. PostgreSQL doesn't appear to have append-only tables, so using security and/or triggers seems to be the only option there.

[0] https://docs.oracle.com/en/database/oracle/oracle-database/2...

You're correct, I overlooked triggers. Though that may be a bridge too far for some folks, triggers are only really comfortable for folks who are deep on RDBMSes. For lots of app developers the ORM is the limit of the world.

ORMs could offer a unique advantage by allowing the user to describe an append-only table and generating the required triggers (or the appropriate CREATE TABLE options). They'd also be able to include helpers to make working with the table easier - like defaulting to selecting current rows only, or an easy way to specify that you want rows as-of a certain point in time.

I'm not sure if any ORMs actually support this though.

I suspect there have been a great many examples and attempts at the ORM level - this one with "bi-temporal chaining" springs to mind: https://github.com/goldmansachs/reladomo

And without the ORM layer there's this extension for Postgres: https://github.com/hettie-d/pg_bitemporal

My "ORM" supports event sourcing, or at least easily plugging it in at the model level.

https://github.com/codr7/hostr/blob/532295b40dcad6c54082e1d3...

More to the point Oracle has flashback queries:

    SELECT ... AS OF <timestamp or logical clock time>

So you can query the database as of any point in the past, making manual work to implement the same feature with custom columns redundant.

Oracle can also show you a record of transactions made and what SQL to run to undo them, including dependency tracking between transactions.

This sounds like proprietary syntax for SQL 2011 temporal queries. Apparently supported by eg MS SQL server and MariaDB.

I didn't mention it, but in this particular service, no client actually had SQL access to the database. There were create/read/update/delete service functions that clients used instead.

The timestamp for a tuple was returned by reads, so when a client wanted to update/delete a tuple, the service functions all required the client to provide the timestamp from the client read. If that timestamp argument wasn't the most recent, the client had to deal with that. But the actual database insert would fail.

Importantly, this particular service had only a few internal clients who almost 100% "owned" the tuples the client worked with. So there wasn't a lot of contention for any specific tuple by multiple clients.

> There were create/read/update/delete service functions that clients used instead.

Oh! I haven't seen this pattern in a long time - not since I worked on Oracle Application Express apps. In a cousin comment I noted that the bulk of app developers don't think about how to use the database to their advantage.

I would recommend going full event sourcing if you're going to put in that kind of effort into preserving history.

Assuming you don't need real time access to data from several points in time, that is.

Just store the events with timestamps and json in a separate table, I usually build a hierarchy with parent event refs to simplify auditing.

As an added bonus, the app is now completely command driven.

Getting historic data for a specific point in time means starting with an empty database and replaying earlier events, alternatively storing undo information in events.

What about Apache Samza[1], which is specifically built to maintain database-shaped views on top of event logs—do people run it? Or is the infra overhead from it requiring Kafka and a Java runtime and so on too much?

[1] https://samza.apache.org/

No experience.

But the thing is, I usually already have a perfectly capable database, any extra infra better pull its weight and then some.

The timestamps are just the tip of the iceberg. The value, as I see from the YT talk, is that additions (and atomic groups of additions) can be recognized as one event, complete with who did it and additional metadata.

Isn't that just logging transactions? The talk says Datomic is fundamentally different, but I don't see anything on his list of features at the end that's not been available for years in commercial databases.

https://oracle-base.com/articles/11g/flashback-and-logminer-...

Yes, Oracle has had proper/comparable time travel for years.

Datomic is interesting with the Datalog interface combined with first-class temporality. One can likely achieve everything similarly in Oracle (but your pocketbook won't like it.)

Datomic is built around a simple log of fully serial transactions. It doesn't have MVCC or other such magic to coordinate concurrent writers, and this means (1) read-only queries don't require open transactions and (2) the whole stack can have mechanically sympathy with this accrete-only design, affording massive efficiency and scaling potential. Capabilities may look similar on the surface but that's where the similarity ends. Take a look at slides 13 & 14: https://qconsf.com/sf2012/dl/qcon-sanfran-2012/slides/RichHi...

Datomic is much more similar to Delta Lake or Apache Iceberg than to a classic RDBMS like Oracle in that respect - but then that analogy severely undervalues it's information modelling qualities.

if the log is as efficient and scalable why is MVCC even a thing? TBH the description on wikipedia looks identical to Datomic to me, minus the persistent data structures. Does it just come down to rectangular fixed width structs of RDBMS give better query performance?

https://en.m.wikipedia.org/wiki/Multiversion_concurrency_con...

Yeah, and this is probably sufficient for most common cases if we're being honest.

Immutability and bitemporal querying are nice features in Datomic, but the trade offs for most teams are an unfamiliar query language, unfamiliar runtime/hosting requirements, unknown performance footguns, little if any integration with 3rd party tools, and (until recently) licensing costs. If it were me, I'd probably deal with the headache or complexity of adding triggers/permissions and audit tables to Postgres to get that functionality if all of those other things are solved instead.

Think Datomic is unitemporal a.o.t. bitemporal.

As it happens there's a talk happening next week at PGConf NYC on time travel and system-time versioning in Postgres by the DBOS team: https://postgresql.us/events/pgconfnyc2024/schedule/session/...

But system-time is only half the story. There has been chatter over the years on the PG mailing lists, but there doesn't seem to be much momentum currently towards adding full SQL:2011 bitemporal support to Postgres. Adding support in a way that feels as natural as using Datomic seems unlikely.

You definitely can, but the ergonomics of doing that on top of tables vs. on top of a triple-store data model are very different.

I've done that; I set it up so that the table was the record stream, then a materialized view with the current state would be refreshed asynchronously when an insert happened. Fast as lightning. Wouldn't do it by default for everything tho.

That doesn't that well though. Datomic is much more efficient on doing this

Reply on news.ycombinator.com