Message-ID: <48908988.7020204@emolecules.com>
Date: Wed, 30 Jul 2008 08:32:24 -0700
From: Craig James <craig_james@emolecules.com>
User-Agent: Thunderbird 2.0.0.16 (Macintosh/20080707)
MIME-Version: 1.0
To: Dave North <DNorth@signiant.com>, pgsql-performance@postgresql.org
Subject: Re: Database size Vs performance degradation
References: 
 <35FABCF85D99464FB00BC5123DC2A70A051D0FB8@s228130hz1ew09.apptix-01.savvis.net>
	<276541.4189.qm@web25805.mail.ukl.yahoo.com>
	<35FABCF85D99464FB00BC5123DC2A70A051D11E1@s228130hz1ew09.apptix-01.savvis.net>
In-Reply-To: 
 <35FABCF85D99464FB00BC5123DC2A70A051D11E1@s228130hz1ew09.apptix-01.savvis.net>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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.

I assume you mean the "create table as select ..." suggestion (don't forget to include a little quoted material so we'll know what you are replying to :-)

You don't have to change the application.  One of the great advantages of Postgres is that even table creation, dropping and renaming are transactional.  So you can do the select / drop / rename as a transaction by an external app, and your main application will be none the wiser.  In pseudo-SQL:

  begin
  create table new_table as (select * from old_table);
  create index ... on new_table ... (as needed)
  drop table old_table
  alter table new_table rename to old_table
  commit

You should be able to just execute this by hand on a running system, and see if some of your bloat goes away.

Craig