Re: Autovacuum / full vacuum

On Tue, Jan 17, 2006 at 11:43:14AM -0500, Chris Browne wrote:
> ajs(at)crankycanuck(dot)ca (Andrew Sullivan) writes:
> > Because nothing that runs automatically should ever take an exclusive
> > lock on the entire database,

> That's a bit more than what autovacuum would probably do...

Or even VACUUM FULL, as I tried to make clearer in another message:
the way I phrased it suggests that it's a simultaneous lock on the
entire database (when it is most certainly not). I didn't intend to
mislead; my apologies.

Note, though, that the actual effect for a user might look worse
than a lock on the entire database, though, if you conider
statement_timeout and certain use patterns.

Suppose you want to issue occasional VACCUM FULLs, but your
application is prepared for this, and depends on statement_timeout to
tell it "sorry, too long, try again". Now, if the exclusive lock on
any given table takes less than statement_timeout, so that each
statement is able to continue in its time, the application looks like
it's having an outage _even though_ it is actually blocked on
vacuums. (Yes, it's poor application design. There's plenty of that
in the world, and you can't always fix it.)

> 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.*

Sure. And depending on your use model, that might be good. In many
cases, though, a "rotor table + view + truncate" approach would be
better, and would allow improved uptime. If you don't care about
uptime, and can take long outages every day, then the discussion is
sort of moot anyway. And _all_ of this is moot, as near as I can
tell, given the OP's claim that the hardware is adequate and
immutable, even though the former claim is demonstrably false.

A

--
Andrew Sullivan | ajs(at)crankycanuck(dot)ca
The fact that technology doesn't work is no bar to success in the marketplace.
--Philip Greenspun