Shaun Thomas <sthomas@townnews.com> writes:
>> Have you tried running frequent (more than hourly) non-full vacuums?
> I'd love to. But one of our customers is keeping us from upgrading to
> 7.2. (the bastards.) Full vacuums are my only choice for the time
> being. But knowing how vacuum works, how would this help? It would
> keep our queries nice and speedy, but unless Postgres has a new, magical
> way of knowing outdated versions of a row are actually outdated, the
> overall file bloating will be the same, and we'd still need the full
> vacuums every hour.
It's not any more or less magical than the old vacuum's way of knowing
that it can remove rows. It is a different way of looking at things
though: you try to keep a steady-state percentage of free space in a
table. 7.1 and before could only append new rows to the end of a table,
so you had to full-vacuum down a table to minimum size and then let it
grow again with updates. With the new style vacuum, we don't try to
compress the table, we just record where there's free space due to
removal of dead tuples. Then insertions reuse that space. So if you
run a new-style vacuum after updating say 10% or 25% of the rows, you
can maintain a steady state table size that's say 10% or 25% larger
than the theoretical minimum.
In an installation with a lot of update traffic, you may need to
increase the default size of the free space map to ensure that you
can keep track of all the free space in your heavily-updated tables.
We're still learning about the best way to tune those configuration
parameters.
regards, tom lane