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
$$
BEGIN
LOOP
-- 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
RETURN;
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
BEGIN
INSERT INTO fieldhistory(tableid,fieldid,id,value) VALUES (key1, key2, key3, hstore(array[whend||'.'||theuser,data]) );
RETURN;
EXCEPTION WHEN unique_violation THEN
-- Do nothing, and loop to try the UPDATE again.
END;
END LOOP;
END;
$$
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.
