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 0D60E925C70 for ; Thu, 29 May 2003 12:32:45 -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 04409-10 for ; Thu, 29 May 2003 12:32:43 -0400 (EDT) Received: from louise.pinerecords.com (louise.pinerecords.com [213.168.176.16]) by developer.postgresql.org (Postfix) with ESMTP id 3552A925BFF for ; Thu, 29 May 2003 12:32:41 -0400 (EDT) Received: from louise.pinerecords.com (kala@localhost [127.0.0.1]) by louise.pinerecords.com with ESMTP id h4TGWdvI011310 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NO) for ; Thu, 29 May 2003 18:32:39 +0200 Received: (from kala@localhost) by louise.pinerecords.com (submit) id h4TGWdwC011309 for pgsql-bugs@postgresql.org; Thu, 29 May 2003 18:32:39 +0200 Date: Thu, 29 May 2003 18:32:39 +0200 From: Tomas Szepe To: pgsql-bugs@postgresql.org Subject: db growing out of proportion Message-ID: <20030529163239.GA11101@louise.pinerecords.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline User-Agent: Mutt/1.4.1i X-Virus-Scanned: by amavisd-new X-Archive-Number: 200305/109 X-Sequence-Number: 6070 Hello everybody, I'm facing a simple yet gravely problem with postgresql 7.3.2 on x86 Linux. My db is used to store IP accounting statistics for about 30 C's. There are a couple truly trivial tables such as the one below: CREATE TABLE stats_min ( ip inet NOT NULL, start timestamp NOT NULL default CURRENT_TIMESTAMP(0), intlen int4 NOT NULL default 60, d_in int8 NOT NULL, d_out int8 NOT NULL, constraint "stats_min_pkey" PRIMARY KEY ("ip", "start") ); CREATE INDEX stats_min_start ON stats_min (start); A typical transaction committed on these tables looks like this: BEGIN WORK DELETE ... UPDATE/INSERT ... COMMIT WORK 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. It seems that only a full redump involving "pg_dump olddb | \ psql newdb" is capable of restoring the system to its working glory. Please accept my apologies if I've overlooked a relevant piece of information in the docs. I'm in an urgent need of getting this problem resolved. -- Tomas Szepe