StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POWays to implement data versioning in PostreSQL
text
Body
copied!<p>Can you share your thoughts how would you implement data versioning in PostgreSQL. (I've asked similar question regarding <a href="https://stackoverflow.com/questions/4183945/ways-to-implement-data-versioning-in-cassandra">Cassandra</a> and <a href="https://stackoverflow.com/questions/4185105/ways-to-implement-data-versioning-in-mongodb">MongoDB</a>. If you have any thoughts which db is better for that please share)</p> <p>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:</p> <ul> <li>will be used infrequently</li> <li>will be used all at once to present it in a "time machine" fashion</li> <li>there won't be more versions than few hundred to a single record.</li> <li>history won't expire.</li> </ul> <p>I'm considering the following approaches:</p> <ul> <li><p>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.</p></li> <li><p>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.</p></li> <li><p>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. (<a href="http://blog.couchone.com/post/632718824/simple-document-versioning-with-couchdb" rel="nofollow noreferrer">This is modelled after Simple Document Versioning with CouchDB</a>)</p></li> </ul>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload