Hello, we are facing strange situation with exclusively locked table during normal lazy vacuum. There is one big table (66GB) that is heavily inserted and updated in our database. Suddenly (after backup and delete of almost all records) we are not able to run VACUUM over this table because after 50 minutes of work it allocate AccessExclusiveLock on this table and all other connections start to timeout. It's common knowledge that VACUUM doesn't block and it looks like it's not true. I found this little excuse in documentation (http://www.postgresql.org/docs/8.3/interactive/routine-vacuuming.html): "... it does not attempt to reclaim the space used by this dead data unless the space is at the end of the table and an exclusive table lock can be easily obtained. Unused space at the start or middle of the file does not result in the file being shortened and space returned to the operating system." This seems to me that situation can appear that dead tuples are in such position that VACUUM will decide to reclaim free space and block other process! Is it true? I found old thread in archive speaking about similar problem (http://archives.postgresql.org/pgsql-performance/2008-06/msg00235.php) but with a resolution that it should be no problem any more. It looks like it is still a problem. If this is true, is there any solution how to convince vacuum not to reclaim free space in any situation? Regards, Jaromir
<<attachment: smime.p7s>>
