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?
