Ways to implement data versioning in PostreSQL

Question

Can you share your thoughts how would you implement data versioning in PostgreSQL. (I've asked similar question regarding Cassandra and MongoDB. If you have any thoughts which db is better for that please share)

Suppose that I need to version records in a simple address book. Address book records are stored in one table without relations for simplicity. I expect that the history:

• will be used infrequently
• will be used all at once to present it in a "time machine" fashion
• there won't be more versions than few hundred to a single record.
• history won't expire.

I'm considering the following approaches:

• Create a new object table to store history of records with a copy of schema of addressbook table and add timestamp and foreign key to address book table.

• Create a kind of schema less table to store changes to address book records. Such table would consist of: AddressBookId, TimeStamp, FieldName, Value. This way I would store only changes to the records and I wouldn't have to keep history table and address book table in sync.

• Create a table to store seralized (JSON) address book records or changes to address book records. Such table would looks as follows: AddressBookId, TimeStamp, Object (varchar). Again this is schema less so I wouldn't have to keep the history table with address book table in sync. (This is modelled after Simple Document Versioning with CouchDB)

Answer 1

I'm versioning glossary data, and my approach was pretty successful for my needs. Basically, for records you need versioning, you divide the fieldset into persistent fields and version-dependent fields, thus creating two tables. Some of the first set should also be the unique key for the first table.

Address
id [pk]
fullname [uk]
birthday [uk]

Version
id [pk]
address_id [uk]
timestamp [uk]
address

In this fashion, you get an address subjects determined by fullname and birthday (should not change by versioning) and a versioned records containing addresses. address_id should be related to Address:id through foreign key. With each entry in Version table you'll get new version for subject Address:id=address_id with a specific timestamp, in which way you can have a history reference.

Answer 2

I do something like your second approach: have the table with the actual working set and a history with changes (timestamp, record_id, property_id, property_value). This includes the creation of records. A third table describes the properties (id, property_name, property_type), which helps in data conversion higher up in the application. So you can also track very easily changes of single properties.

Instead of a timestamp you could also have an int-like, wich you increment for every change per record_id, so you have an actual version.

Answer 3

You could have start_date and end_date.

When end_date is NULL, it`s the actual record.