Re: Performance Problem with Vacuum of bytea table (PG 8.0.13)

Bastian Voigt <post@bastian-voigt.de> writes:
> Now my big big problem is that the database gets really really slow
> during these 20 minutes and after the vacuum process is running for a
> short time, many transactions show state "UPDATE waiting" in the process
> list. In my Java application server I sometimes get tons of deadlock
> Exceptions (waiting on ShareLock blahblah). The web frontend gets nearly
> unusable, logging in takes more than 60 seconds, etc. etc.

Hmm.  That's a bit weird --- what are they waiting on exactly?  Look in
pg_locks to see what the situation is.  A vacuum per se ought not be
blocking any updates.

Aside from the recommendation to make the vacuums happen more frequently
instead of less so, you should experiment with vacuum_cost_delay and
related parameters.  The idea is to reduce vacuum's I/O load so that it
doesn't hurt foreground response time.  This means any individual vacuum
will take longer, but you won't need to care.

            regards, tom lane