OSCON 2006: TimeTravel Tables in PostgreSQL

By Matthew Eernisse | August 5, 2006 | Open Source

A. Elein Mustain is a veteran developer of Ingres, Illustra, and Informix, and is the author of the weekly PostgreSQL General Bits column.

Elein showed how to use timestamps to keep an audit trail of all changes in your DB. With this technique, you never actually delete records, you just give them an end date. Multiple copies of a record (again, with timestamps for each one) also allow you to track edits. (This is a pretty standard approach, especially in ‘validated’ environments, or in government-regulated industries.)

The value of using Postgres here is in the advanced features like triggers that you can use with your deletes and updates to offload the work needed to maintain this ‘time travel’ system — rather than forcing your app logic to keep up with all of it.

You can also use PostgreSQL’s views to query the database for only the current data, or write procedural functions to query the state of the database at a particular point in time in the past.

Elein has her slides available online here.

Written By:

Matthew Eernisse

Matthew lives in Houston, Texas, USA, and works for the Open Source Applications Foundation building the web UIs for Scooby, a next-generation web-based calendar client, and Cosmo, a calendar server. In his abundant free time, he writes about AJAX, JavaScript, Ruby, and PHP at Fleegix.org, and operates EpiphanyRadio, a webcast radio station. Matthew plays drums and speaks fluent Japanese.

{ 4 comments }

s21825 August 8, 2006 at 11:10 am: I’ve done this type of thing in the past by maintaining a ‘revisions’ table containing a timestamp and a log message. Then each other table in the database contains a ‘revision_id’ column and a ‘deleted’ column. Then you only insert new rows, no actual deletes or updates. You can then join any table to the revisions table to go back in time (based on the timestamp in the revisions table). Interesting to see a different approach to solving the same problem!
malikyte August 7, 2006 at 12:19 pm: Thanks, Elein!
elein August 7, 2006 at 11:45 am: The URL http://www.varlena.com/GeneralBits/Tidbits contains the current presentation *along with* the code used to generate the examples.
The primary key of the timetravel table is the id + starttime. Look at the unique indexes to see how this is formed.
elein@varlena.com
malikyte August 6, 2006 at 4:05 am: I’m not too familiar with the archival use of timestamps for an entry in a database. Is the primary key a composite key of the “id” and “timestamp” fields, or is that done differently?
This interests me as I work on a local government website (library, not too big of a deal, but librarians are really in to historical references, so…) and would like to know where to look for more on this subject.