Re: Vacuum Question

Ed Loehr <eloehr(at)austin(dot)rr(dot)com> writes:
>> The short answer is that a table being vacuumed is locked against any
>> other access, read or write; but only that one table. You might as
>> well leave the rest of the database open for use.

> I take it you don't give much credence to the previous reports that
> concurrently writing during vacuuming caused corruption?

No, not really, for the simple reason that there aren't going to *be*
any concurrent writes (or reads either) during a vacuum ... unless our
table locking system fails, in which case we've got lots worse problems
than vacuum.

There are scenarios involving known (and fixed!) bugs that might be
stressed by vacuum running concurrently with operations on other tables.
I'm thinking particularly of shared cache invalidation: a vacuum on a
system table will spit out a lot of SI messages for the tuples it moves
around, and it used to be that we had bugs that could be triggered by
SI buffer overflow, or more accurately by the cache flush that's used to
recover from SI overflow. (Those bugs seem to be fixed in 7.0. I've
successfully run parallel regression tests with the SI buffer reduced to
a mere dozen messages or so. It spits out an impressive number of "SI
buffer overflow" notices, but keeps going...)

If you are feeling conservative you might want to vacuum just selected
user tables during normal usage, and do a database-wide vacuum only at
low load --- if there are any low-probability bugs still lurking in
there, they're almost surely things that would be triggered by system
table vacuums and not user-table vacuums. That'd probably be a good
idea for performance reasons anyway, since locking a system table is
much more likely to block other backends than locking any one user
table.

regards, tom lane