Re: Database size Vs performance degradation

[Craig James <craig_james(at)emolecules(dot)com>]

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