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.

Replay

Category: open source Time: 2006-08-05 Views: 0
Tags:

Related post

  • OSCON 2006: Rock-solid Web Development: Testing Web Apps 2006-07-26

    All this week, Kevin Yank is reporting from OSCON 2006 in Portland, OR. Presenter John Paul Ashenfelter provided the standard sales pitch for testing, but with a web development spin, and demonstrated both Selenium (a rich, in-browser testing framewo

  • OSCON 2006: Cross-site Ajax 2006-07-28

    This week, Kevin Yank is reporting from OSCON 2006 in Portland, OR. Paralyzed by indecision (at any given time at OSCON, there are literally three different sessions I would consider "must-see" going on), I went to Plaxo developer Joseph Smarr's

  • OSCON 2006: Gettting Rich with PHP5 2006-07-28

    This week, Kevin Yank is reporting from OSCON 2006 in Portland, OR. PHP's creator, Rasmus Lerdorf, gave a surprising talk on building PHP rich web applications using PHP5-suprising, because fully half the talk focused on low-level PHP optimization te

  • OSCON 2006: Tim O'Reilly keynote 2006-07-27

    This week, Kevin Yank is reporting from OSCON 2006 in Portland, OR. Kicking off the first day of open sessions at OSCON today, Tim O'Reilly gave his perspectives on the state of open source, and some of the big ideas currently making waves: Architect

  • OSCON 2006: Django: Web Development with Journalists' Deadlines 2006-07-27

    This week, Kevin Yank is reporting from OSCON 2006 in Portland, OR. Lead developer Jacob Kaplan-Moss bills Django as a competitor to Ruby on Rails. Django is a web development framework that was born at a small community newspaper in the city of Lawr

  • OSCON 2006: Ajax Optimization Techniques 2006-07-27

    This week, Kevin Yank is reporting from OSCON 2006 in Portland, OR. Kevin Henrikson of Zimbra gave a brisk presentation covering some of the lessons his organization has learned and the "dirty tricks" it has implemented to improve the performanc

  • OSCON 2006: Understanding ZFramework 2006-07-28

    This week, Kevin Yank is reporting from OSCON 2006 in Portland, OR. John Coggeshall is a respected name in the PHP world, and works as a technical consultant for Zend, and provided a fast-paced introduction to the Zend Framework as it currently exist

  • OSCON 2006: Web Heresies: The Seaside Framework 2006-07-28

    This week, Kevin Yank is reporting from OSCON 2006 in Portland, OR. Avi Bryant has the dubious distinction of being one of the few web geeks with an avid interest in Smalltalk, and has created a web application development framework that suits his pa

  • OSCON 2006: Cosmo and Scooby: Standards-Based Open Source Calendaring 2006-07-29

    This week, Kevin Yank is reporting from OSCON 2006 in Portland, OR. Developed by the Open Source Applications Foundation (OSAF), Cosmo is a service enabling its users to put their personal information online and share it with others via open protocol

  • OSCON 2006: No Flash Required: Interactive Browser Graphics 2006-07-29

    This week, Kevin Yank is reporting from OSCON 2006 in Portland, OR. Gavin Doughtie has done stints in games development for Sony, web development for idealab, and software development for Picasa, but these days he works at DreamWorks Animation (yes,

  • OSCON 2006: Practical OpenID 2006-07-29

    This week, Kevin Yank is reporting from OSCON 2006 in Portland, OR. David Recordon (Six Apart, then Verisign) and Brian Ellin (JanRain) have had their fingers in OpenID since its inception. OpenID is an open user authentication system based on the pr

  • extracting pixel value from a table in postgresql 2012-10-30

    I have a table in postgresql that contains ID, row number, column number, pixel value and centroid of the pixel in WGS84 lat-lon system(4326). How could I extract the pixel value by giving a point in 4326 reference system? --------------Solutions----

  • How to determine the collation of a table in PostgreSQL? 2012-12-06

    I want to script a check of the collations used on my tables in PostgreSQL, but googling for Postgresql detect collation is not working well for me, and the documentation is not making this an easy search. Can anyone tell me how I would check this? -

  • Create table into PostgreSql from Geoserver SQL View query 2015-03-12

    I know how to connect Geoserver to PostgreSql and create SQL View queries with parameters from Geoserver, but ;it is possible to create a new table into PostgreSql from a Geoserver Sql View with parameters like this?: Geoserver: SELECT source FROM pg

  • Replicating table level PostgreSQL 64bit windows 2015-06-18

    I need to replicate some tables on PostgreSQL 64bit for Windows (master -> slaves replication model) and I can't find any tool that can achieve that. Slony-I work very well for PostgreSQL 32bit for Windows, but I can't find Slony-I for PostgreSQL 64b

  • Join 1 Table with 2 different table in Postgresql 2016-01-17

    I have 2 tables in Postgresql. Both these are in different schema. I want the output as in the shared screenshot. Suppose,for field HDRConfig,if the output value is SE then I want the output as Software Engineering instead of SE. How do I show these

  • rake db:migrate doesn't generate table in postgresql 2016-02-01

    I've been having a problem which doesn't seem uncommon (I've read a lot of stack overflow pages in the last 2 days) but every solution I've read hasn't worked for me. I've been following this video tutorial At 6:42 the tutor shows the tables in postg

  • OSCON 2006: Big Bad PostgreSQL 2006-08-05

    Theo Schlossnagle is a principal at OmniTI Computer Consulting, working in the areas of scalable internet architectures, database replication, and e-mail infrastructure. This talk was on converting a really large (over 3 terabytes, largest table is 1

  • GRANT SELECT to all tables in postgresql 2009-08-30

    Is there a one-liner that grants the SELECT permissions to a new user postgresql? Something that would implement the following pseudo-code: GRANT SELECT ON TABLE * TO my_new_user; --------------Solutions------------- I thought it might be helpful to

iOS development

Android development

Python development

JAVA development

Development language

PHP development

Ruby development

search

Front-end development

Database

development tools

Open Platform

Javascript development

.NET development

cloud computing

server

Copyright (C) avrocks.com, All Rights Reserved.

processed in 0.417 (s). 13 q(s)