Re: table versioning approach (not auditing)

[Jonathan Vanasco <postgres@xxxxxxxx>]

In the past, to accomplish the same thing I've done this:

- store the data in hstore/json.  instead of storing snapshots, I store deltas.  i've been using a second table though, because it's improved performance on reads and writes.

- use a "transaction" log.  every write session gets logged into the transaction table (serial, timestamp, user_id).  all updates to the recorded tables include the transaction's serial.  then there is a "transactions" table, that is just "transaction_serial ,  object_id , object_action".  

whenever I have needs for auditing or versioning, I can just query the transaction table for the records I want... then use that to grab the data out of hstore.

On Sep 28, 2014, at 10:00 PM, Abelard Hoffman wrote:

Hi. I need to maintain a record of all changes to certain tables so assist in viewing history and reverting changes when necessary (customer service makes an incorrect edit, etc.).

I have studied these two audit trigger examples:

https://wiki.postgresql.org/wiki/Audit_trigger

https://wiki.postgresql.org/wiki/Audit_trigger_91plus

I've also read about two other approaches to versioning:

1. maintain all versions in one table, with a flag to indicate which is the current version

2. have a separate versions table for each real table, and insert into the associated version table whenever an update or insert is done.

My current implementation is based on the wiki trigger examples, using a single table, and a json column to record the row changes (rather than hstore). What I like about that, in particular, is I can have a "global," chronological view of all versioned changes very easily.

But there are two types of queries I need to run.

1. Find all changes made by a specific user

2. Find all changes related to a specific record

#1 is simple to do. The versioning table has a user_id column of who made the change, so I can query on that.

#2 is more difficult. I may want to fetch all changes to a group of tables that are all related by foreign keys (e.g., find all changes to "user" record 849, along with any changes to their "articles," "photos," etc.). All of the data is in the json column, of course, but it seems like a pain to try and build a query on the json column that can fetch all those relationships (and if I mess it up, I probably won't generate any errors, since the json is so free-form).

So my question is, do you think using the json approach is wrong for this case? Does it seem better to have separate versioning tables associated with each real table? Or another approach?

Thanks