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.
August 6th, 2006 at 1:54 am
PostgreSQL at OSCONN 2006
SitePoint mentions a couple of PostgreSQL sessions at OSCON 2006. Good to see more PostgreSQL stuff showing up there.
Theo Schlossnagle presented a case study on moving lots of data from Oracle to PostgreSQL. They needed more Oracle servers to accomm…
August 6th, 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.
August 7th, 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
August 7th, 2006 at 12:19 pm
Thanks, Elein!
August 8th, 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!