From: Dan Harris <fbsd@drivefaster.net>
To: pgsql-admin@postgresql.org
Subject: Re: reclaiming disk space after major updates
Date: Thu, 07 Jun 2007 15:26:56 -0600
Message-ID: <46687820.9000604@drivefaster.net> (raw)
In-Reply-To: <20070607192025.GO14087@phlogiston.dyndns.org>
References: <46672F7C.4020400@drivefaster.net>
<20070607192025.GO14087@phlogiston.dyndns.org>
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.
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: fbsd@drivefaster.net, pgsql-admin@postgresql.org
Subject: Re: reclaiming disk space after major updates
In-Reply-To: <46687820.9000604@drivefaster.net>
* 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