Re: Vacuuming on heavily changed databases

On Mon, May 19, 2008 at 8:50 AM, Bohdan Linda <bohdan(dot)linda(at)seznam(dot)cz> wrote:
> Hello,
>
> I would like to ask an opinion on vacuuming general. Imagine situation
> that you have single table with 5 fields (one varchar). This table has
> during the day
>
> - cca 620 000 inserts
> - 0 updates
> - cca 620 000 deletes
>
> The table is vacuumed daily, but somehow after several months I got to
> size of ~50GB

OK. Assuming that the 50G is mostly dead space, there are a few
possibilities that could be biting you here, but the most likely one
is that your Free Space Map settings aren't high enough to include all
the rows that have been deleted since the last vacuum was run. If you
can't take down the server to change those settings, then running
vacuum more often will help.

The autovacuum daemon is your friend. Even with the default non
aggresive settings it comes with, it would have caught this long
before now.

Note that I no longer have 8.0 installed anywhere, only 8.1. But I
know that 8.1 supported vacuum costing features to let you slow down
vacuum so it doesn't stomp on your I/O to the detriment of all the
other pgsql processes. Look for those settings. You can adjust those
and just reload.