PostgreSQL database size increasing

Question

I have a strange problem. The size of my postgresql (8.3) is increasing. So I made a dump and then cleaned up the database and then re-imported the dump. The database size was reduced by roughly 50%.

Some infomation: (1) AUTOVACUUM and REINDEX are running regularly in background. (2) Database encoding is ASCII. (3) Database location: /database/pgsql/data (4) System: Suse-Ent. 10.

Any hints are appreciated

Answer 1

Without knowing more specifics about your particular setup, a couple of things come to mind. When AUTOVACUUM runs, is it trying to reclaim disk space, and can you verify that it is through server logs?

Secondly, especially if the previous answer was no, your AUTOVACUUM values may be incorrect. I would highly recommend reading the following on the subject: http://www.postgresql.org/docs/8.3/interactive/routine-vacuuming.html#AUTOVACUUM

Answer 2

running reindex shouldn't be necessary.

run database wide vacuum with verbose, and check the last lines for fsm settings hint - maybe it is what is wrong.

Answer 3

If you haven't already, check your system for long-running idle transactions. They will prevent VACUUM (both manual and auto) from clearing out space.

Answer 4

Did you try a VACUUM FULL, too? (Warning, it locks your database for a long time.) I am not sure that AUTOVACUUM is so eager...

Answer 5

If the dead tuples have stacked up beyond what can be accounted for in max_fsm_pages, a regular VACUUM will not be able to free everything. The end result is that the database will grow larger and larger over time as dead space continues to accumulate. Running a VACUUM FULL should fix this problem. Unfortunately it can take a very long time on a large database.

If you're running into this problem frequently, you either need to vacuum more often (autovacuum can help here) or increase the max_fsm_pages setting. When running VACUUM VERBOSE it will tell you how many pages were freed and give you a warning if max_fsm_pages was exceeded, this can help you determine what this value should be. See the manual for more information. http://www.postgresql.org/docs/8.3/static/runtime-config-resource.html#RUNTIME-CONFIG-RESOURCE-FSM

Fortunately, 8.4's visibility map resolves this issue. Despesz has a great story on the subject as usual: http://www.depesz.com/index.php/2008/12/08/waiting-for-84-visibility-maps/