Re: More Autovacuum questions

Chris Hoover escribió:
> Another question.
>
> When autovacuum sleeps, does it release the lock it has on the table?

What do you mean sleep? There are two sleeps: the one caused by
cost-based vacuum delay (lasts for some milliseconds, happens every
handful of pages vacuumed; no locks released here) and the big sleep
that happens after one worker finishes and the next one starts (all
locks are released).

When one autovac worker is processing a table, no other worker touches
it. So if you have a table that's 64 GB, it won't be analyzed until
the currently running vacuum finishes.

> What we would like to have happen is for vacuum to work for a while, sleep,
> and while it is sleeping run an analyze on the table. We need this due to
> how quickly our data is changing. Currently we are running analyze every 5
> minutes against the table of question and get a very serious performance hit
> when vacuum runs due to the index stats getting out of date. As soon as we
> rerun analyze after the vacuum happiness returns.

This is worst case for Postgres vacuuming, I think.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.