X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id 49954D1B55C for ; Fri, 3 Oct 2003 23:30:45 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 72386-03 for ; Fri, 3 Oct 2003 20:29:59 -0300 (ADT) Received: from wight.ymogen.net (unknown [217.27.240.153]) by svr1.postgresql.org (Postfix) with SMTP id 7BE2DD1B533 for ; Fri, 3 Oct 2003 20:29:55 -0300 (ADT) Received: (qmail 27350 invoked from network); 3 Oct 2003 23:29:58 -0000 Received: from dsl-217-155-239-51.zen.co.uk (HELO finisterre) (217.155.239.51) by wight.ymogen.net with SMTP; 3 Oct 2003 23:29:58 -0000 From: "Matt Clark" To: "Christopher Browne" , Subject: Re: reindex/vacuum locking/performance? Date: Sat, 4 Oct 2003 00:29:55 +0100 Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2910.0) In-Reply-To: <60d6de9fah.fsf@dev6.int.libertyrms.info> X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 Importance: Normal X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/122 X-Sequence-Number: 3870 > > In summary, I suspect that it is better from a UI perspective to > > bring down the app on Sat at 3 a.m and reimport with a fixed time > > period than to live through reindexing/vacuuming which may deadlock. > > Am I missing something? > > Consider running pg_autovacuum, and thereby do a little bit of > vacuuming here and there all the time. It DOESN'T block, so unless > your system is really busy, it shouldn't slow things down to a major > degree. My real world experience on a *very* heavily updated OLTP type DB, following advice from this list (thanks guys!), is that there is essentially zero cost to going ahead and vacuuming as often as you feel like it. Go crazy, and speed up your DB! OK, that's on a quad CPU box with goodish IO, so maybe there are issues on very slow boxen, but in a heavy-update environment the advantages seem to easily wipe out the costs. Matt p.s. Sorry to sound like a "Shake'n'Vac" advert.