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

Bastian Voigt <post(at)bastian-voigt(dot)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