Re: Autovacuum / full vacuum

ajs(at)crankycanuck(dot)ca (Andrew Sullivan) writes:
> On Tue, Jan 17, 2006 at 11:18:59AM +0100, Michael Riess wrote:
>> hi,
>>
>> I'm curious as to why autovacuum is not designed to do full vacuum. I
>
> Because nothing that runs automatically should ever take an exclusive
> lock on the entire database, which is what VACUUM FULL does.

That's a bit more than what autovacuum would probably do...
autovacuum does things table by table, so that what would be locked
should just be one table.

Even so, I'd not be keen on having anything that runs automatically
take an exclusive lock on even as much as a table.

>> activity. Increasing the FSM so that even during these bursts most
>> space would be reused would mean to reduce the available memory for
>> all other database tasks.
>
> I don't believe the hit is enough that you should even notice
> it. You'd have to post some pretty incredible use cases to show that
> the tiny loss of memory to FSM is worth (a) an exclusive lock and
> (b) the loss of efficiency you get from having some preallocated
> pages in tables.

There is *a* case for setting up full vacuums of *some* objects. If
you have a table whose tuples all get modified in the course of some
common query, that will lead to a pretty conspicuous bloating of *that
table.*

Even with a big FSM, the pattern of how updates take place will lead
to that table having ~50% of its space being "dead/free," which is way
higher than the desirable "stable proportion" of 10-15%.

For that sort of table, it may be attractive to run VACUUM FULL on a
regular basis. Of course, it may also be attractive to try to come up
with an update process that won't kill the whole table's contents at
once ;-).
--
let name="cbbrowne" and tld="cbbrowne.com" in String.concat "@" [name;tld];;
http://cbbrowne.com/info/x.html
"As long as each individual is facing the TV tube alone, formal
freedom poses no threat to privilege." --Noam Chomsky