Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am using postgreSQL 7.4.

I have a large table , call it table_a:

key1 INT NOT NULL, 
key2 INT NOT NULL, 
data INT NOT NULL, 
itstamp INT NOT NULL DEFAULT (date_part('EPOCH'::text, (timeofday())::timestamp without time zone))::INTEGER

and a table that summaries the last update time for key1, call it table_b:

key1        INT NOT NULL,
max_itstamp INT NOT NULL

I created a trigger function in plpgsql to update or insert rows in table_b as necessary:

CREATE OR REPLACE FUNCTION table_b_update() RETURNS TRIGGER AS '
 DECLARE
  l_key1 INT;
  l_itstamp INT;
 BEGIN
  l_key1 := new.key1;
  l_itstamp := new.itstamp;
  PERFORM TRUE FROM table_b WHERE key1=l_key1;
  IF NOT FOUND THEN 
   INSERT INTO table_b(key1, max_itstamp) values (l_key1, l_itstamp);
  ELSE
   UPDATE table_b SET max_itstamp=l_itstamp WHERE key1=l_key1;
  END IF;
  RETURN NULL;
 END'
LANGUAGE plpgsql IMMUTABLE;

and then I attached a trigger to table_a:

CREATE TRIGGER table_a_trigger1 AFTER INSERT OR UPDATE ON table_a FOR EACH ROW
EXECUTE PROCEDURE table_b_upate();

Now, the time to insert new data into table_a grows incrementally. The file footprint of table_b grows steadily.

I have used RAISE NOTICE commands in the function to confirm that the If statement causes an UPDATE and not an INSERT after the first call per key.

Since the table_a insert time grows for each INSERT, I tried a VACUUM FULL on table_b. The table_a insert time was reduced considerably. The file size for table_b was reduced considerably. After the VACUUM FULL the table_a insert time started to grow again. I don't want to do a VACUUM FULL after every INSERT into table_a though.

Is it possible that the UPDATE is actually doing a DELETE and INSERT in table_b?

share|improve this question
    
Not sure what you mean by "resets the INSERT time"? –  Jack Douglas May 17 '11 at 11:24
    
@JackPDouglas: Clarified –  youngthing May 17 '11 at 11:28
    
thanks - I'll edit my answer to include a comment on insert time –  Jack Douglas May 17 '11 at 11:31
    
@JackPDouglas: Further clarification. The 'insert time' relates to inserts on table_a (causing either and insert or an update on table_b - most likely an update). –  youngthing May 17 '11 at 11:40
    
understood - I'm assuming the actual insert to table_a is roughly constant and the triggered insert/update is changing in time - you are not doing a vacuum full on table_a are you? –  Jack Douglas May 17 '11 at 11:48
show 5 more comments

2 Answers

up vote 1 down vote accepted

I don't have 7.4 to test on, but I'm guessing:

  • every time you do a vacuum full the table compacts
  • every time you update, the new version of the row (see MVCC) gets shoved at the end of the heap before the old one is removed by a vacuum

See here for the docs explaining this in more detail, but the simple solution is not to run vacuum full at all - just vacuum. Then your table will probably settle into a steady state where 'holes' in the data are left and can be used by later updates.

As for "insert time", I'm surprised at your results. My expectation would be that insert time would be slower after a vacuum full - but if all the blocks are in the cache, the overhead of finding free space inside the current block might be higher than adding the new row at the end of the heap even if the number of blocks accessed is higher

share|improve this answer
    
The VACUUM FULL was tested on the command line (psql client) as a one-off operation. I have not tested it after every insert into table_a. The majority of inserts into table_a will cause updates to table_b. –  youngthing May 17 '11 at 11:42
add comment

PostgreSQL doesn't really do updates, it does the equivalent to delete and insert in terms of how it does them. It leaves the old version of the row in place and creates a new version that all future transactions will see. Eventually vacuum reclaims the old row space for reuse. A word of advice, update immediately to a supported release. 7.4 is VERY old, is no longer supported, and has known data eating bugs that will never be fixed. I'd recommend going right to 9.0, and researching the issues with removed implicit casts which will be the biggest problem. We had to fix three queries in out app when we went to 8.3 the first version to remove implicit casts.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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