It is also an extremely trivial modification to PostgreSQL to just add that functionality: essentially you just need to disable the vacuum, reify the current transaction identifier, and allow queries to bypass MVCC (which is already storing exactly the kind of information people love to play up about Datomic: it is an append-only system that only is able to delete things due to garbage collection and the vacuum).

I did a proof-of-concept of this years ago in this linked comment (which is the bottom of a long thread which involved Rich Hickey; I remember it being an interesting thread).

https://news.ycombinator.com/item?id=4448767

At the time I did not provide a patch (which is weird for me) to show how "this is totally just leveraging stuff already there", but I just went and found that folder, so here is the patch:

http://test.saurik.com/hackernews/snapshot_txid.diff

Although this is definitely a part of the solution, I still see the coupling Rich mentions in his comment in the thread you linked to as an issue.

To expand: when you have to query a remote database for data, you likely only want to perform one query for performance reasons (and certainly not an unbounded amount of queries - maybe two or three is acceptable but not N+1). This means that information must be passed down the call stack for what information is needed in that single or few queries, creating coupling between unrelated layers of your application.

To make this more concrete: imagine you write a function to find users named Jim. At first this function is for reporting, so you just return a list of user IDs. Later, you decide to build a dashboard. You want to render all users named Jim here, but you need each of their names for display purposes.

Given a remote database, you now need to modify the query function to be able to return the specific attributes you need for this use case. You can imagine if you extend the call stack this passing gets more complicated, requires merging of the queried items, etc.

With datomic, since your data is in memory, your original query can stay the same, since N+1 queries are irrelevant when your data is available in RAM.

I think this point is important.

What's stopping you from storing Postgres database (or particular tables) in memory if it fits? I believe that should be possible.

Also, I may not be getting what Datomic is, but why modify original function, you can just join results with users table.

Also to answer your second question, first off there's no user table in the example so it's a bit confusing. But say we're just comparing a traditional table-per-record-type approach and why this approach still retains the coupling problem.

In that scenario you could join the user table. You'd be overfetching in many scenarios but that's not a huge concern for most people, this is what active record does. However, say you want to get more than just the columns on the user table, then you run into the same issue. Suddenly the query caller needs to inform the query method to include results about some unrelated table. Because datomic is an in memory graph structure, the caller can handle grabbing that extra information without modifying the method or its call signature, obviating the need for this coupling.

The machinenry for keeping peers up to date, for one. Datomic distributes all writes in real time to peers. You could probably recreate this with notify and listen in Postgres although I'm not familiar enough with the details to know if it would fully work.

Please please please try and get this into Postgres!

AIUI, this is more or less how Oracle's flashback queries work:

http://www.dba-oracle.com/t_rman_149_flasbback_query.htm

I'm a n00b and postgres vacuum was news to me. Can I ask, in a postgres cluster with high traffic, how do you manage the nodes? Do you take them offline before starting a vacuum process?

Conceptually that is fine but there are complications (speaking based on recent and ongoing experience on a project I'm working on):

1. When you have multiple entity tables that are inter-related, snapshotting one in isolation is not sufficient.

2. When only certain changes on certain fields should result in a new snapshot.

Granted, both of these are solvable with built-in Postgres mechanisms (and a bit of co-operation from the application server) but simple it is not.

In our case we also move historic records to a second 'history' table.

To add to your list, with 1. referential integrity becomes a problem. Basically you move it up the stack into the application code and lose consistency guarantees RDBMS can provide.

I'm curious wether Datomic supports RE and how.

Alternatively, you can automate this with triggers, where the client only needs to know about the entity table, but all updates to an entity cause the previous value to be inserted into another table entity_log. We do this and it works well for us.

See also the Temporal Tables extension to PostgreSQL https://github.com/arkhipov/temporal_tables

The whole point of storing history is ability for clients to explicitly query it (and sometimes manipulate it), isn't it?

You can query a history table.

Not if "client only needs to know about the entity table"

So If you need to give history access to your client I guess you can put up a "entity_history" function that will generate a snapshot entity table from his request and the log table.

It all depend on your need, RDMS schema are customely build to meet customers needs, I don't believe in proverbial Swiss knife tool anymore.