Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125]) by mail.postgresql.org (Postfix) with ESMTP id AC3DBCE6EC for ; Fri, 15 Jun 2012 16:03:52 -0300 (ADT) Received: from gwmta.wicourts.gov ([165.219.244.99] helo=gw.wicourts.gov) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1SfboF-0007mu-Qx for pgsql-performance@postgresql.org; Fri, 15 Jun 2012 19:03:52 +0000 Received: from Courts-MTA by gw.wicourts.gov with Novell_GroupWise; Fri, 15 Jun 2012 14:03:39 -0500 Message-Id: <4FDB40B702000025000484EE@gw.wicourts.gov> X-Mailer: Novell GroupWise Internet Agent 8.0.2 Date: Fri, 15 Jun 2012 14:03:35 -0500 From: "Kevin Grittner" To: "Benedict Holland" Cc: Subject: Re: Update blocking a select count(*)? References: <4FDB3C0202000025000484E4@gw.wicourts.gov> In-Reply-To: Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit Content-Disposition: inline X-Pg-Spam-Score: -4.2 (----) X-Archive-Number: 201206/84 X-Sequence-Number: 47110 Benedict Holland wrote: > 10:25:08.329-04 vacuum (analyze, verbose, full) > 2096 rmv 33528 postgres 8/151 > AccessExclusiveLock > Yes 2012-06-15 10:25:08.329-04 vacuum (analyze, verbose, > full) > 2096 rmv 50267 postgres 8/151 > AccessExclusiveLock > Yes 2012-06-15 10:25:08.329-04 vacuum (analyze, verbose, > full) You have three VACUUM FULL commands running? VACUUM FULL is very aggressive maintenance, which is only needed for cases of extreme bloat. It does lock the table against any concurrent access, since it is completely rewriting it. Now, if you are running UPDATE statements which affect all rows in a table, you will *get* extreme bloat. You either need to do such updates as a series of smaller updates with VACUUM commands in between, or schedule your aggressive maintenance for a time when it can have exclusive access to the tables with minimal impact. Reporting the other issues without mentioning the VACUUM FULL processes is a little bit like calling from the Titanic to mention that the ship isn't going as fast as it should and neglecting to mention the iceberg. :-) -Kevin