Re: VACUUM ANALYZE blocking both reads and writes to a table

Alvaro Herrera wrote:
> Peter Schuller wrote:
> > Actually, while on the topic:
> >
> > >     date: 2007-09-10 13:58:50 -0400;  author: alvherre;  state: Exp;  lines: +6 -2;
> > >     Remove the vacuum_delay_point call in count_nondeletable_pages, because we hold
> > >     an exclusive lock on the table at this point, which we want to release as soon
> > >     as possible.  This is called in the phase of lazy vacuum where we truncate the
> > >     empty pages at the end of the table.
> >
> > Even with the fix the lock is held. Is the operation expected to be
> > "fast" (for some definition of "fast") and in-memory, or is this
> > something that causes significant disk I/O and/or scales badly with
> > table size or similar?
>
> It is fast.

To elaborate: it scans the relation backwards and makes note of how many
are unused.  As soon as it finds a non-empty one, it stops scanning.
Typically this should be quick.  It is not impossible that there are a
lot of empty blocks at the end though, but I have never heard a problem
report about this.

It could definitely cause I/O though.

> > I.e., is this enough that, even without the .4 bug, one should not
> > really consider VACUUM ANALYZE non-blocking with respect to other
> > transactions?
>
> You should consider it non-blocking.

The lock in conditionally acquired: as I said earlier, the code would
rather skip this part than block.  So if there's some other operation
going on, there's no lock held at all.  If this grabs the lock, then
other operations are going to block behind it, but the time holding the
lock should be short.  Note, however, that sleeping for 20ms or more
because of vacuum_delay (the bug fixed above) clearly falls out of this
category, and easily explains the behavior you're seeing with 8.2.4.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support