5 ms·

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 th

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.

null_investor 2y ago
That doesn't that well though. Datomic is much more efficient on doing this
parhamn 2y ago
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.
diggan 2y ago
> 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/ https://mariadb.com/kb/en/temporal-tables/
jacques_chester 2y ago
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.
- Mister_Snuggles 2y ago
  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/23/sqlrf/CREATE-TABLE.html https://docs.oracle.com/en/database/oracle/oracle-database/2...
  - jacques_chester 2y ago
    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.
    - Mister_Snuggles 2y ago
      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.
      - refset 2y ago
        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 https://github.com/goldmansachs/reladomo And without the ORM layer there's this extension for Postgres: https://github.com/hettie-d/pg_bitemporal https://github.com/hettie-d/pg_bitemporal
panick21_ 2y ago
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.
- clusterhacks 2y ago
  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 . . .
ilkhan4 2y ago
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.
- stefcoetzee 2y ago
  Think Datomic is unitemporal a.o.t. bitemporal.
- refset 2y ago
  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/1711-time-travel-queries-with-postgres/ 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.
michaelteter 2y ago
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.
- deleted 2y ago
  [deleted]
- mike_hearn 2y ago
  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-enhancements-11gr1#flashback_transaction https://oracle-base.com/articles/11g/flashback-and-logminer-...
  - 15155 2y ago
    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.)
  - refset 2y ago
    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/RichHickey_DeconstructingTheDatabase.pdf 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.
codr7 2y ago
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.
- mananaysiempre 2y ago
  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/ https://samza.apache.org/
  - codr7 2y ago
    No experience. But the thing is, I usually already have a perfectly capable database, any extra infra better pull its weight and then some.
pnathan 2y ago
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.
hcarvalhoalves 2y ago
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.

null_investor 2y ago

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

parhamn 2y ago

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.

diggan 2y ago

> 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/ https://mariadb.com/kb/en/temporal-tables/

jacques_chester 2y ago

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.

Mister_Snuggles 2y ago
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/23/sqlrf/CREATE-TABLE.html https://docs.oracle.com/en/database/oracle/oracle-database/2...
- jacques_chester 2y ago
  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.
  - Mister_Snuggles 2y ago
    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.
    - refset 2y ago
      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 https://github.com/goldmansachs/reladomo And without the ORM layer there's this extension for Postgres: https://github.com/hettie-d/pg_bitemporal https://github.com/hettie-d/pg_bitemporal

panick21_ 2y ago

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.

clusterhacks 2y ago
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 . . .

ilkhan4 2y ago

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.

stefcoetzee 2y ago
Think Datomic is unitemporal a.o.t. bitemporal.
refset 2y ago
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/1711-time-travel-queries-with-postgres/ 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.

michaelteter 2y ago

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.

deleted 2y ago
[deleted]
mike_hearn 2y ago
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-enhancements-11gr1#flashback_transaction https://oracle-base.com/articles/11g/flashback-and-logminer-...
- 15155 2y ago
  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.)
- refset 2y ago
  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/RichHickey_DeconstructingTheDatabase.pdf 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.

codr7 2y ago

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.

mananaysiempre 2y ago
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/ https://samza.apache.org/
- codr7 2y ago
  No experience. But the thing is, I usually already have a perfectly capable database, any extra infra better pull its weight and then some.

pnathan 2y ago

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.

hcarvalhoalves 2y ago

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.