Re: [HACKERS] safety of vacuum verbose analyze on active tables

Jim Mercer <jim@reptiles.org> writes:
> what is the relative safety of doing a vacuum verbose analyze on a 24Gb
> table while there are selects and updates/inserts happening on it?

VACUUM obtains an exclusive lock on the table it's working on, so there
won't *be* any selects or updates happening on that table ;-).  Anyone
else who wants to touch the table will be blocked until VACUUM finishes.

A disadvantage of running VACUUM concurrently with other activity is
that VACUUM cannot remove tuples that are committed dead, but were
deleted by a transaction more recent than the oldest still-active
transaction.  (If that oldest transaction chooses to come look at the
table after VACUUM finishes, it should see those dead tuples as still
live, if it is running fully serializable.  So VACUUM has to leave
the tuples there, just in case.)  So, if you have long-running
transactions happening in parallel with VACUUM, you probably won't get
as much space reclaimed as you'd like.

We have also heard some reports suggesting there are hard-to-duplicate
bugs in the VACUUM logic that tries to deal with these not-quite-dead
tuples.  Tripping over one of those is probably the major risk factor
involved in running VACUUM under full-load conditions.  I do not recall
hearing any reports of actual data corruption from such a bug, but you
might see weird error messages out of VACUUM.  (BTW, if you can get
a reproducible example of a problem like this, we'd definitely like
to hear about it.)

            regards, tom lane