[PERFORM] database bloat, but vacuums are done, and fsm seems to be setup ok

[hubert depesz lubaczewski]

hi
setup:
postgresql 8.0.3 put on debian on dual xeon, 8GB ram, hardware raid.

database just after recreation from dump takes 15gigabytes.
after some time (up to 3 weeks) it gets really slow and has to be dump'ed and restored.

as for fsm:
end of vacuum info:
INFO:  free space map: 248 relations, 1359140 pages stored; 1361856 total pages needed
DETAIL:  Allocated FSM size: 1000 relations + 10000000 pages = 58659 kB shared memory.

so it looks i have plenty of space in fsm.

vacuums run constantly.
4 different tasks, 3 of them doing:
while true
vacuum table
sleep 15m
done
with different tables (i have chooses the most updated tables in system).

and the fourth vacuum task does the same, but without specifying table - so it vacuumes whole database.

after last dump/restore cycle i noticed that doing reindex on all indices in database made it drop in side from 40G to about 20G - so it might be that i will be using reindex instead of drop/restore.
anyway - i'm not using any special indices - just some (117 to be exact) indices of btree type. we use simple, multi-column, partial and multi-column partial indices. we do not have functional indices.

database has quite huge load of updates, but i thought that vacum will guard me from database bloat, but from what i observed it means that vacuuming of b-tree indices is somewhat faulty.

any suggestions? what else can i supply you with to help you help me?

best regards

depesz