Re: safety of vacuum verbose analyze on active tables

On Tue, Apr 18, 2000 at 12:58:58AM +1000, Andrew Snow wrote:
> > 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?
>
> As far as I know, the table is locked completely during a Vacuum. Any
> transactions attempting to do inserts/updates will be paused safely. So go
> ahead and schedule your vacuums for whenever you need to.

ah. that sounds about right.

however, my insert/update processes are autonomous, and as such, if i had a
3 hour vacuum, i might end up with quite a queue of insert/update processes.

is there a way to determine if there is a lock before i start an insert/update?

also, i have, as a habit, done:

- use pg_dump to get a list of indexes
- nuke indexes
- vacuum table
- recreate indexes

since i have control over the various processes, i enforce a quiet period for
this.

i have noticed that the linear time for:

nuke index ; vacuum ; recreate index

is much less than for a normal "vacuum table";

--
[ Jim Mercer jim(at)reptiles(dot)org +1 416 506-0654 ]
[ Reptilian Research -- Longer Life through Colder Blood ]
[ Don't be fooled by cheap Finnish imitations; BSD is the One True Code. ]