I have a postgresql database that will hold about 50 tables, each of them having about 15 fields, it would have at least 300.000 rows on each table.

In order to track the changes done on each field I am thinking on create a table defined by:

CREATE TABLE fieldhistory(tableid int, fieldid int, id bigint,value hstore);
CREATE INDEX fieldhistory_index ON fieldhistory(tableid, fieldid, id);

I would expect the table to grow and grow, and retrieve from fieldhistory data only by tableid, fieldid and id.

When the tables are modified I would add a new record with something like:

SELECT upserthistory(1,1,1,'first update','115435','3');
SELECT upserthistory(1,1,1,'second update','115435','3');

where upserthistory is defined by :

CREATE OR REPLACE FUNCTION upserthistory(key1 INT, key2 INT, key3 BIGINT, data TEXT, theuser TEXT, whend TEXT) RETURNS VOID AS
        -- first try to update the key
        UPDATE fieldhistory SET value = value || ((whend||'.'||theuser) => data) WHERE tableid = key1 AND fieldid = key2 AND id = key3;
        IF found THEN
        END IF;
        -- not there, so try to insert the key
        -- if someone else inserts the same key concurrently,
        -- we could get a unique-key failure
            INSERT INTO fieldhistory(tableid,fieldid,id,value) VALUES (key1, key2, key3, hstore(array[whend||'.'||theuser,data]) );
        EXCEPTION WHEN unique_violation THEN
            -- Do nothing, and loop to try the UPDATE again.
LANGUAGE plpgsql;

Are there better approaches to accomplish this? the table would grow max to 225 millions of rows, or would it be better to have 50 tables of 4.5 millions of rows at max each? noting that the actual trace of each field will go to the hstore.


I would personally prefer to have one history table for each "main" table.

There is a ready-made solution for this available for PostgreSQL which implements this history using triggers, see Log Table Changes in PostgreSQL with tablelog by Andreas Scherbaum.

  • I would like to get the history of a single field for a given row in the original tables, I don't find an easy way to accomplish this with the tablelog approach – ikks Jan 5 '12 at 23:26
  • 1
    I had some bad luck with tablelog a few months ago. It doesn't seem to be well-documented or well-maintained. But just today I discovered PG comes with a module called timetravel that does something similar. – Daniel Lyons Mar 6 '12 at 3:57
  • @DanielLyons: thanks for the hint. I wasn't aware of the timetravel module – a_horse_with_no_name Mar 6 '12 at 7:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.