From: | Craig James <craig_james(at)emolecules(dot)com> |
---|---|
To: | Dave North <DNorth(at)signiant(dot)com>, pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Database size Vs performance degradation |
Date: | 2008-07-30 15:32:24 |
Message-ID: | 48908988.7020204@emolecules.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Mark Roberts | 2008-07-30 15:46:16 | Re: Database size Vs performance degradation |
Previous Message | Tom Lane | 2008-07-30 15:05:16 | Re: Database size Vs performance degradation |