Re: reindex/vacuum locking/performance? - Mailing list pgsql-performance

> vacuum full does require exclusive lock, plain vacuum does not.

I think I need full, because there are updates on the table.  As I
understand it, an update in pg is an insert/delete, so it needs
to be garbage collected.

> It's considerably more likely that the vacuum was waiting for an open
> client transaction (that had a read or write lock on some table) to
> finish than that there was an undetected deadlock.  I suggest looking at
> your client code.  Also, in 7.3 or later you could look at the pg_locks
> view to work out exactly who has the lock that's blocking vacuum.

My client code does a lot.  I look at more often than I'd like to. :-)

I don't understand why the client transaction would block if vacuum
was waiting.  Does vacuum lock the table and then try to get some
other "open transaction" resource?  Free space?  I guess I don't
understand what other resources would be required of vacuum.  The
client transactions are short (< 1s).  They don't deadlock normally,
only with reindex and vacuum did I see this behavior.

> vacuum full is indeed slow.  That's why we do not recommend it as a
> routine maintenance procedure.  The better approach is to do plain
> vacuums often enough that you don't need vacuum full.

The description of vacuum full implies that is required if the db
is updated frequently.   This db gets about 1 txn a second, possibly
more at peak load.

> In pre-7.4
> releases you might need periodic reindexes too, depending on whether
> your usage patterns tickle the index-bloat problem.

7.3, and yes, we have date indexes as well as sequences for primary
keys.

> But it is easily
> demonstrable that reindexing is cheaper than rebuilding the database.

IOW, vacuum+reindex is faster than dump+restore?  I didn't see this,
then again, I had this locking problem, so the stats are distorted.

One other question: The reindex seems to lock the table for the entire
process as opposed to freeing the lock between index rebuilds.  It was
hard to see, but it seemed like the clients were locked for the entire
"reindex table bla" command.

Sorry for lack of detail, but I didn't expect these issues so I wasn't
keeping track of the system state as closely as I should have.  Next
time. :-)

Thanks,
Rob