X-Original-To: pgsql-admin-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 8BC4D9FB36E for ; Wed, 30 Aug 2006 19:27:55 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 81673-05-5 for ; Wed, 30 Aug 2006 19:27:46 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id ECAC89FB375 for ; Wed, 30 Aug 2006 19:25:13 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.3 Received: from news.hub.org (unknown [200.46.204.72]) by svr4.postgresql.org (Postfix) with ESMTP id C32FE5AF888 for ; Wed, 30 Aug 2006 22:25:12 +0000 (GMT) Received: from news.hub.org ([200.46.204.72]) by news.hub.org (8.13.1/8.13.1) with ESMTP id k7UMNc3u094722 for ; Wed, 30 Aug 2006 19:23:38 -0300 (ADT) (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.13.1/8.13.1/Submit) id k7UM4Unf091071 for pgsql-admin@postgresql.org; Wed, 30 Aug 2006 19:04:30 -0300 (ADT) (envelope-from news) From: Chris Browne X-Newsgroups: pgsql.admin Subject: Re: Database Optimization and Peformance Date: Wed, 30 Aug 2006 17:29:25 -0400 Organization: cbbrowne Computing Inc Lines: 25 Message-ID: <60d5ahzzd6.fsf@dba2.int.libertyrms.com> References: <44F5D91E.8000806@antics.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Complaints-To: usenet@news.hub.org User-Agent: Gnus/5.1007 (Gnus v5.10.7) XEmacs/21.4.19 (linux) Cancel-Lock: sha1:oDz02hoq+efJbUNzYUAGXG6KGQE= To: pgsql-admin@postgresql.org X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.217 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200608/337 X-Sequence-Number: 22880 joe.mcclintock@antics.com (Joe McClintock) writes: > My concern is this, doing a backup and restore does not seem an > appropriate way manage database fragmentation and performance. The > documentation I have read indicates that vacuum, analyze reindex are > the tools to use to de-fragment and optimize the database. In my > case they did not work and reindexing made query performance > slightly worse. Am I missing something? As the database grows, will > I need to essentially rebuild the db on a regular basis? It oughtn't be necessary. It seems quite plausible that there are a few tables that are not being vacuumed nearly often enough. If you have tables where large portions are modified (updated/deleted), then you need to run VACUUM quite frequently, otherwise such tables will be overrun with dead space. We have tables that we run VACUUM on every five minutes because they change really heavily. (200-300 tuples, where we do an update to a tuple every time a customer is billed.) -- "cbbrowne","@","ntlug.org" http://cbbrowne.com/info/finances.html Why does sour cream have an expiration date?