OSCON 2006: TimeTravel Tables in PostgreSQL

By Matthew Eernisse

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.

  • Pingback: Joseph Scott's Blog()

  • malikyte

    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.

  • elein

    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.


  • malikyte

    Thanks, Elein!

  • s21825

    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!

Get the latest in Front-end, once a week, for free.