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
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:
- database stats: http://cl.ly/1v1t2L0n2m4220001F2v
- all table stats: http://cl.ly/273B3F242O2y2Z3C3c2L
- app_prices table stats: http://cl.ly/1P3Y142P2d2P3v3s0p11
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?