Re: VACUUM ANALYZE block the whole database

On Sun, Jul 29, 2012 at 11:42 PM, Majid Azimi <majid(dot)merkava(at)gmail(dot)com> wrote:

> because the database is growing too much(beyond tra byte in less than 6
> months) we need to delete the oldest data. in each delete we remove about 80
> million records from vbv(there is a trigger to delete the records from
> vbv_denorm and summary). after delete autovacuum starts on vbv. at the same
> time because summary is small (at maximum 2 million records) I ran VACUUM
> ANALYZE on it(it is not VACUUM FULL). but this cause the database to
> completely block. the process inserting to vbv blocks. the process sending
> summary records blocks. Why this is happening? autovacuum and VACUUM ANALYZE
> should not lock tables.

Vacuum by itself does not block read write to tables, however DDL can
block behind it, and then read and writing to tables can block behind
that DDL. When this is happening, do the queries on this page:

http://wiki.postgresql.org/wiki/Lock_Monitoring

tell you anything useful?