Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 17981650090 for ; Wed, 30 Jul 2008 12:46:11 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 32233-02 for ; Wed, 30 Jul 2008 12:46:07 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from s402.sureserver.com (s402.sureserver.com [216.35.197.71]) by postgresql.org (Postfix) with ESMTP id 7BA3964FFC0 for ; Wed, 30 Jul 2008 12:46:07 -0300 (ADT) Received: (qmail 15546 invoked by uid 504); 30 Jul 2008 15:46:04 -0000 Received: from unknown (HELO ?97.0.177.44?) (mailing?lists@70.102.219.22) by ns1.s402.sureserver.com with ESMTPA; 30 Jul 2008 15:46:04 -0000 Subject: Re: Database size Vs performance degradation From: Mark Roberts To: Dave North Cc: pgsql-performance@postgresql.org In-Reply-To: <35FABCF85D99464FB00BC5123DC2A70A051D11E1@s228130hz1ew09.apptix-01.savvis.net> References: <35FABCF85D99464FB00BC5123DC2A70A051D0FB8@s228130hz1ew09.apptix-01.savvis.net> <276541.4189.qm@web25805.mail.ukl.yahoo.com> <35FABCF85D99464FB00BC5123DC2A70A051D11E1@s228130hz1ew09.apptix-01.savvis.net> Content-Type: text/plain Date: Wed, 30 Jul 2008 08:46:16 -0700 Message-Id: <1217432776.6288.40.camel@localhost> Mime-Version: 1.0 X-Mailer: Evolution 2.12.1 Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200807/248 X-Sequence-Number: 30848 On Wed, 2008-07-30 at 10:02 -0500, Dave North wrote: > Thank you for the suggestion..much appreciated. Alas, I don't think > this will be possible without a change to the application but it's a > good idea nonetheless. Affirmative, Dave. I read you. If I were in your situation (not having access/desire to change the base application), I'd write a sql script that does something like this: - Create __new_table__ from old_table # Read lock on old table - Rename old_table to __old_table__ # Access Exclusive Lock - Rename __new_table__ to old_table # Access Exclusive Lock - Commit # Now the application can write to the new table - Sync newly written changes to the new table (these would be written between the creation and access exclusive lock). - Drop/Vacuum full/Archive old_table Well, it would at least let you get the benefits of the rename approach without actually altering the application. Additionally, the application's writes would only be blocked for the duration of the rename itself. This makes the assumption that these writes aren't strictly necessary immediately (such as via a find or insert construct). If this assumption is false, you would need to lock the table and block the application from writing while you create the temporary table. This has the advantage of not requiring the final sync step. Sorry if all of this seems redundant, but best of luck! -Mark