From: Craig James <craig_james@emolecules.com>
To: Dave North <DNorth@signiant.com>
To: pgsql-performance@postgresql.org
Subject: Re: Database size Vs performance degradation
Date: Wed, 30 Jul 2008 08:32:24 -0700
Message-ID: <48908988.7020204@emolecules.com> (raw)
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: craig_james@emolecules.com, DNorth@signiant.com
Subject: Re: Database size Vs performance degradation
In-Reply-To: <48908988.7020204@emolecules.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox