Re: Keeping historical record changes w/ triggers - best practices?

On Wed, Feb 6, 2013 at 12:41 PM, Wells Oliver <wellsoliver(at)gmail(dot)com> wrote:
> I have a wide-ish table with 60 columns. I want to make a copy of data
> whenever a record is updated or deleted.
>
> Right now I have a table that's almost identical but with a 'created' column
> (timestamp) and an 'action' column (which gets TG_OP for UPDATE or DELETE).
>
> My idea would be to sort on the created column to see the historical record
> by comparing the columns. My other thought is to create two columns for each
> column in the master table (old_column, new_column, etc), storing the old
> values and the new values, and see what's changed that way.
>
> The other idea, probably a terrible idea, was to use hstore to create a list
> of the old values and new values, and have this history table just be the
> timestamp, action, and two hstore columns.
>
> Surely this has been done thousands of times. What are the thoughts
> regarding best practices in PG?

I handle this using middleware outside the db. Past revisions of a
record (from any table I want to track) are serialized into a JSON
format and stored in a single table. Postgres speaks JSON now, so..

--
Greg Donald