X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 57C4A32A401 for ; Tue, 19 Oct 2004 19:12:54 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 90732-07 for ; Tue, 19 Oct 2004 18:12:47 +0000 (GMT) Received: from roue.portalpotty.net (roue.portalpotty.net [69.44.62.206]) by svr1.postgresql.org (Postfix) with ESMTP id 107E432A42F for ; Tue, 19 Oct 2004 19:12:47 +0100 (BST) Received: from max by roue.portalpotty.net with local (Exim 4.34) id 1CJyTV-0006EW-Ix; Tue, 19 Oct 2004 11:12:45 -0700 Date: Tue, 19 Oct 2004 14:12:45 -0400 From: Max Baker To: Rod Taylor Cc: Postgresql Performance Subject: Re: Vacuum takes a really long time, vacuum full required Message-ID: <20041019181245.GJ21258@warped.org> Mail-Followup-To: Rod Taylor , Postgresql Performance References: <20041019153821.GA21258@warped.org> <1098200416.750.238.camel@home> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <1098200416.750.238.camel@home> User-Agent: Mutt/1.4.1i X-AntiAbuse: This header was added to track abuse, please include it with any abuse report X-AntiAbuse: Primary Hostname - roue.portalpotty.net X-AntiAbuse: Original Domain - postgresql.org X-AntiAbuse: Originator/Caller UID/GID - [514 32003] / [47 12] X-AntiAbuse: Sender Address Domain - roue.portalpotty.net X-Source: /usr/bin/mutt X-Source-Args: mutt X-Source-Dir: /home/max X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200410/281 X-Sequence-Number: 8757 Hi Rod, On Tue, Oct 19, 2004 at 11:40:17AM -0400, Rod Taylor wrote: > > Whatever the case, the database still slows down to a halt after a month or > > so, and I have to go in and shut everything down and do a VACUUM FULL by > > hand. One index (of many many) takes 2000 seconds to vacuum. The whole > > process takes a few hours. > > Do a REINDEX on that table instead, and regular vacuum more frequently. Great, this is exactly what I think it needs. Meanwhile, I was checking out http://www.postgresql.org/docs/7.3/static/sql-reindex.html Which suggests I might be able to do a drop/add on each index with the database 'live'. However, the DROP INDEX command was taking an awfully long time to complete and it hung my app in the mean time. Does anyone know if the DROP INDEX causes an exclusive lock, or is it just a lengthy process? > > $ pg_config --version > > PostgreSQL 7.3.2 > > 7.4.x deals with index growth a little better 7.3 and older did. Will do. Meanwhile I'm stuck supporting older 7.x versions, so I'm still looking for a solution for them. Thanks! -m