Message-ID: <46687820.9000604@drivefaster.net>
Date: Thu, 07 Jun 2007 15:26:56 -0600
From: Dan Harris <fbsd@drivefaster.net>
User-Agent: Thunderbird 2.0.0.0 (Macintosh/20070326)
MIME-Version: 1.0
To: pgsql-admin@postgresql.org
Subject: Re: reclaiming disk space after major updates
References: <46672F7C.4020400@drivefaster.net>
	<20070607192025.GO14087@phlogiston.dyndns.org>
In-Reply-To: <20070607192025.GO14087@phlogiston.dyndns.org>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Andrew Sullivan wrote:
> On Wed, Jun 06, 2007 at 04:04:44PM -0600, Dan Harris wrote:
>> of these operations or a full dump/reload.  I do run VACUUM regularly, it's 
>> just that sometimes we need to go back and update a huge percentage of rows 
>> in a single batch due to changing customer requirements, leaving us with 
>> significant table bloat.
> 
> Do you need to update those rows in one transaction (i.e. is the
> requirement that they all get updated such that the change only
> becomes visible at once)?  If not, you can do this in batches and
> vacuum in between.  Batch updates are the prime sucky area in
> Postgres.

They don't always have to be in a single transaction, that's a good idea to 
break it up and vacuum in between, I'll consider that.  Thanks

> 
> Another trick, if the table is otherwise mostly static, is to do the
> updating in a copy of the table, and then use the transactional DDL
> features of postgres to change the table names.

I thought of this, but it seems to break other application logic that feeds a 
steady streams of inserts into the tables.

Thanks again for your thoughts.  I guess I'll just have to work around this 
problem in application logic.