Re: reclaiming disk space after major updates

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.