OSCON 2006: TimeTravel Tables in PostgreSQL
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.