This gives me hope that maybe we could see something similar with Datomic. Perhaps it is possible to implement the same append-only (this is the best way I understand it), immutable audit trail on top of PostgreSQL and still walk away with SQL (datalog is neat-o but has a learning curve).
Technically PostgreSQL already has this in terms of the WAL log. The problem is that "rolling back" to a given point of time requires recreating the database from the WAL log so it's not useful in the same way.
There are other "remember everything" schemes you can play with clever triggers, but it always comes back to how you end up using the stored data and how easy it is to bring it back to a queryable state.
PostgreSQL also has it in the form of its MVCC table structure. If you never delete 'old' rows, you could query old states of the databases by ignoring rows with newer transaction IDs.
However, nothing about pgSQL is designed for this approach, so i imagine the performance would be terrible.
Our proposed approach is to treat the log as normal
data managed by the DBMS which will simplify the
recovery code and simultaneously provide support for
access to the historical data.
...
3.3. Time Varying Data
POSTQUEL allows users to save and query historical data
and versions [KATZ85, WOOD83]. By default, data in a
relation is never deleted or updated. Conventional
retrievals always access the current tuples in the
relation. Historical data can be accessed by indicating
the desired time when defining a tuple variable.
...
Finally, POSTGRES provides support for versions. A
version can be created from a relation or a snapshot.
Updates to a version do not modify the underlying
relation and updates to the underlying relation will be
visible through the version unless the value has been
modified in the version.
One of the purposes of the much-maligned VACUUM command was to push the historical data to archival (optical) media.
The archival store holds historical records, and the
vacuum demon can ensure that ALL archival records are
valid.
> POSTQUEL allows users to save and query historical data and versions [KATZ85, WOOD83]. By default, data in a relation is never deleted or updated. Conventional retrievals always access the current tuples in the relation. Historical data can be accessed by indicating the desired time when defining a tuple variable.
Or, to put it another way [1]:
"Since one can't change the past, this implies that the database accumulates facts, rather than updates places, and that while the past may be forgotten, it is immutable."
One of Postgres' proudest features actually used to be this -- "time travel", which used MVCC + row time stamps to allow the client to query historical data. This was eventually removed, but the functionality can be emulated using a contrib extension called "timetravel" [1].
Maybe you're looking for event sourcing? It's great in theory but for all the reading I've done I haven't been able to find any case studies on it working well in practice
I think Eventsourcing is mostly used by large enterprises that don't really blog much about those things.
Let me just say: it's a very interesting approach, but it's also very complicated and has a large overhead in development time and infrastructure complexity.
For most problems, it's A LOT easier to do classic CRUD + some distributed task queue.
You can combine those two. It's pretty easy to just emit an additional event for each write into some event store (Apache Kafka, Postgres, whatever), so you can get a 'best of both worlds' state.
An append only audit trail would also have to carry some type information, since tables can change shape. It makes the relationship to ordinary relational querying interesting, to say the least.
Postgres's logical replication slots do provide way to implement your own change streaming.
but I dream the dream...