Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

I have a Postgres database that I'm importing/updating about 4 GB of data into daily. (Usually about 15-20 million rows.) I'm importing from a flat file dump of data using bulk inserts. I also have an "Insert On duplicate key, update" style trigger that will simply update a row if the id already exists. The import data itself doesn't grow much (as far as filesize), it's mostly just that the records are being updated with a small number of new records being inserted. Also, after each import I'm running VACUUM ANALYZE.

Today I noticed that the database size swelled to about 15GB. After doing some googling, I decided to try a VACUUM FULL and a REINDEX. The size of the database dropped significantly (about 50%). I read that this might be related to the max_fsm_pages setting but it looks like that was removed as of Postgres 8.4 so I'm not sure what's going on here. There is nothing in the postgres logs of particular interest that I could see.

Here's a transcript of the process: http://pastie.org/private/jt7mxajyyxuzvodch9goq

All the tables shrank considerably, but I thought it was interesting that before the vacuum and reindex the largest table was an index that was 3.7GB (bigger than even the largest content table which was 2.8GB) and shrank to 629 MB.

Is this normal or is there something wrong here? I'm trying to keep the on-disk file size down since this is running on a fairly small Linode VM.


UPDATE: Using pgAdmin, here are a few screenshots of the statistics tab:

So, from the last screenshot it definitely looks like it's index bloat that's causing the problem. The table size is 3.0GB while the index size is 4.4GB. The app_prices table, for example is the table with the most rows (about 35 million), has only 5 columns, and 3 indices. Does this still seem normal?

share|improve this question
1  
Check if the indexes are taking the space. pgAdminIII statistics tabs show index and data sizes. –  jkj Jun 9 '11 at 12:08
    
Thanks for the tip about pgAdmin. I've updated my question with some info from the statistics tab. –  mirthlab Jun 9 '11 at 20:41

1 Answer 1

Because of the way that Postgresql implements MVCC, this is probably normal.

Edit: http://www.postgresql.org/docs/9.0/static/routine-vacuuming.html has a pretty good explanation of what's happening and how to deal with it.

share|improve this answer
    
Yeah, I had come across that document earlier, but I'm inclined to think something other than the normal MVCC operations is happening here. If my data is only 4-5GB and, let's say every row is hit on an update, then I can see the db doubling its size, but tripling the data (15GB!) seems like a bit much no? –  mirthlab Jun 9 '11 at 6:32
    
Not necessarily. Using the app_prices table as an example-- The total size increase isn't from the dead tuples as much as from index bloat. Since this appears to be a "skinny" table (possibly only 2 columns) with at least three indices, I'm not suprised to see the > 2x difference. If this is typical for many of the tables being updated then it isn't suprising at all. –  gsiems Jun 9 '11 at 12:36
    
Hmm, interesting. The app_prices table is the table with the most rows (just over 35 million). It has 5 columns and (you're right) three indices. I guess I'll look into index bloat and see if anything can be done there. Thanks! –  mirthlab Jun 9 '11 at 20:15

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.