Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 3D74CB5DBE9 for ; Fri, 8 Jul 2011 14:12:55 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 14859-07 for ; Fri, 8 Jul 2011 17:12:48 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from moutng.kundenserver.de (moutng.kundenserver.de [212.227.126.187]) by mail.postgresql.org (Postfix) with ESMTP id 5D81CB5DBD2 for ; Fri, 8 Jul 2011 14:12:48 -0300 (ADT) Received: from [192.168.1.7] (mail.highperformancepostgresql.com [71.179.240.8]) by mrelayeu.kundenserver.de (node=mreu3) with ESMTP (Nemesis) id 0M7A1q-1RQAlF11K2-00x3jO; Fri, 08 Jul 2011 19:12:47 +0200 Message-ID: <4E173A8E.80900@2ndQuadrant.com> Date: Fri, 08 Jul 2011 13:12:46 -0400 From: Greg Smith User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US; rv:1.9.1.16) Gecko/20110505 Icedove/3.0.11 MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Re: "VACUUM FULL ANALYZE" vs. Autovacuum Contention References: <4E16258A.4050007@2ndQuadrant.com> In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Provags-ID: V02:K0:ioRf4x3tRf256knWSt/PPm26eD4fpLw3I2lil3YP+fm le7W/urI+jGVhWqxJ2senzQNTf53RaYc3VK8e6dajuR/VaqD+m GZCyX/9RUCrWs81IWDOHduvBsDlUkCYCxdU1wmJX4rz6quxLtu yFi8W6Wfrnu+uU9U5/dida4sHYxNacHAkxHnRpw8nbjZcuqglj rkCPTm6kbR/qOZ80bYzJTl9aFFtMkiMPRl22CNxBB0= X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9, RCVD_IN_DNSWL_NONE=-0.0001 X-Spam-Level: X-Archive-Number: 201107/45 X-Sequence-Number: 44257 On 07/08/2011 12:46 PM, D C wrote: > That said, it sounds like if we switched to daily "trucates" of each > table (they can be purged entirely each day) rather than "delete > froms", then there truly would not be any reason to use "vacuum > full". Does that sound plausible? That's exactly right. If you can re-arrange this data to be truncated instead of deleted, this entire problem should go away. There is also a nice optimization you should know about; if you do this: BEGIN; TRUNCATE t; COPY t FROM ... COMMIT; In single-node systems (no standby slave), this can work much faster than a normal load. It's able to skip the pg_xlog WAL writes in this situation. -- Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD Comprehensive and Customized PostgreSQL Training Classes: http://www.2ndquadrant.us/postgresql-training/