4 ms·

You can achieve similar goals to Datomic using Postgres: create table snapshot ( id uuid primary key default uuid_generate_v4(), ts timesta

You can achieve similar goals to Datomic using Postgres: create table snapshot ( id uuid primary key default uuid_generate_v4(), ts timestamp without time zone default now(), entity_id uuid references entity(id), value jsonb ); create table entity ( id uuid primary key default uuid_generate_v4(), snapshot_id uuid references snapshot(id) ); Rather than mutating the value of an entity, you simply append a new record to the snapshot table and update the entity's reference to it. This gives you a full audit trail of the entity.

saurik 9y ago
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 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 http://test.saurik.com/hackernews/snapshot_txid.diff
- willvarfar 9y ago
  Please please please try and get this into Postgres!
- jtmarmon 9y ago
  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.
  - elmigranto 9y ago
    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.
    - jtmarmon 9y ago
      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.
    - jtmarmon 9y ago
      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.
- twic 9y ago
  AIUI, this is more or less how Oracle's flashback queries work: http://www.dba-oracle.com/t_rman_149_flasbback_query.htm http://www.dba-oracle.com/t_rman_149_flasbback_query.htm
- marcuslager 9y ago
  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?
iaabtpbtpnn 9y ago
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.
- ComodoHacker 9y ago
  The whole point of storing history is ability for clients to explicitly query it (and sometimes manipulate it), isn't it?
  - renesd 9y ago
    You can query a history table.
    - ComodoHacker 9y ago
      Not if "client only needs to know about the entity table"
      - Twisell 9y ago
        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.
- fanf2 9y ago
  See also the Temporal Tables extension to PostgreSQL https://github.com/arkhipov/temporal_tables https://github.com/arkhipov/temporal_tables
jarym 9y ago
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.
- ComodoHacker 9y ago
  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.

saurik 9y ago

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 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 http://test.saurik.com/hackernews/snapshot_txid.diff

willvarfar 9y ago
Please please please try and get this into Postgres!
jtmarmon 9y ago
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.
- elmigranto 9y ago
  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.
  - jtmarmon 9y ago
    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.
  - jtmarmon 9y ago
    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.
twic 9y ago
AIUI, this is more or less how Oracle's flashback queries work: http://www.dba-oracle.com/t_rman_149_flasbback_query.htm http://www.dba-oracle.com/t_rman_149_flasbback_query.htm
marcuslager 9y ago
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?

iaabtpbtpnn 9y ago

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.

ComodoHacker 9y ago
The whole point of storing history is ability for clients to explicitly query it (and sometimes manipulate it), isn't it?
- renesd 9y ago
  You can query a history table.
  - ComodoHacker 9y ago
    Not if "client only needs to know about the entity table"
    - Twisell 9y ago
      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.
fanf2 9y ago
See also the Temporal Tables extension to PostgreSQL https://github.com/arkhipov/temporal_tables https://github.com/arkhipov/temporal_tables

jarym 9y ago

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.

ComodoHacker 9y ago
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.