X-Original-To: pgsql-bugs@postgresql.org Received: from localhost (developer.postgresql.org [64.117.224.193]) by developer.postgresql.org (Postfix) with ESMTP id 3C619924DF8 for ; Fri, 30 May 2003 03:24:52 -0400 (EDT) Received: from developer.postgresql.org ([64.117.224.193]) by localhost (developer.postgresql.org [64.117.224.193:10024]) (amavisd-new) with ESMTP id 16838-06 for ; Fri, 30 May 2003 03:24:51 -0400 (EDT) Received: from louise.pinerecords.com (louise.pinerecords.com [213.168.176.16]) by developer.postgresql.org (Postfix) with ESMTP id 85C9D924CE8 for ; Fri, 30 May 2003 03:24:50 -0400 (EDT) Received: from louise.pinerecords.com (kala@localhost [127.0.0.1]) by louise.pinerecords.com with ESMTP id h4U7OhvI017941 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NO); Fri, 30 May 2003 09:24:43 +0200 Received: (from kala@localhost) by louise.pinerecords.com (submit) id h4U7Ogit017940; Fri, 30 May 2003 09:24:42 +0200 Date: Fri, 30 May 2003 09:24:42 +0200 From: Tomas Szepe To: Stephan Szabo Cc: pgsql-bugs@postgresql.org Subject: Re: db growing out of proportion Message-ID: <20030530072442.GD14159@louise.pinerecords.com> References: <20030529163239.GA11101@louise.pinerecords.com> <20030529103316.K60582-100000@megazone23.bigpanda.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <20030529103316.K60582-100000@megazone23.bigpanda.com> User-Agent: Mutt/1.4.1i X-Virus-Scanned: by amavisd-new X-Archive-Number: 200305/112 X-Sequence-Number: 6073 > [sszabo@megazone23.bigpanda.com] > > > Trouble is, as the rows in the tables get deleted/inserted/updated > > (the frequency being a couple thousand rows per minute), the database > > is growing out of proportion in size. After about a week, I have > > to redump the db by hand so as to get query times back to sensible > > figures. A transaction that takes ~50 seconds before the redump will > > then complete in under 5 seconds (the corresponding data/base/ dir having > > shrunk from ~2 GB to ~0.6GB). > > > > A nightly VACCUM ANALYZE is no use. > > > > A VACUUM FULL is no use. > > > > A VACUUM FULL followed by REINDEX is no use. > > Is the space being taken up by stats_min, this index, some other object? relname | relkind | relpages | reltuples ---------------------------------+---------+----------+------------- stats_hr | r | 61221 | 3.01881e+06 stats_hr_pkey | i | 26414 | 3.02239e+06 stats_min_pkey | i | 20849 | 953635 stats_hr_start | i | 17218 | 3.02142e+06 stats_min_start | i | 15284 | 949788 stats_min | r | 10885 | 948792 authinfo_pkey | i | 1630 | 1342 authinfo | r | 1004 | 1342 contract_ips | r | 865 | 565 contract_ips_pkey | i | 605 | 565 > What does VACUUM FULL VERBOSE stats_min; give you? Sorry, I can't run a VACUUM FULL at this time. We're in production use. -- Tomas Szepe