I like the "cluster" and "reindex" ideas. The table is not that big and I do have the disk space. This table will
alsogrow over time, so if the table ends up taking more space in the end, that's OK, it'll get used. The DB will also
beunavailable to the users while this is happening, so I won't have to be contending with interactive users.
Thanks for all the suggestions!
-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Rob Sargent
Sent: Friday, March 04, 2011 10:21 AM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] updating all records of a table
On 03/04/2011 04:54 AM, Vibhor Kumar wrote:
>
> On Mar 4, 2011, at 5:17 PM, Andrew Sullivan wrote:
>
>> On Thu, Mar 03, 2011 at 08:03:59PM -0700, Gauthier, Dave wrote:
>>> Hi:
>>>
>>> I have to update all the records of a table. I'm worried about what the table will look like in terms of
fragmentationwhen this is finished. Is there some sort of table healing/reorg/rebuild measure I should take if I want
theresulting table to operate at optimal efficiency? What about indexes, should I drop/recreate those?
>>
>> Is it really important that it happen in one transaction?
>>
>> In the past when I've had to do this on large numbers of rows, I
>> always tried to do it in batches. You can run vacuums in between
>> groups, so that the table doesn't get too bloated.
>>
>> Otherwise, yeah, you're better off to do some of the cleanup Joshua
>> suggested.
>>
>> A
> +1
>
> If UPDATE is for all rows, then
> 1. CTAS with change value in SELECT
> 2. Rename the tables. -- This will give zero Bloats.
>
Elegant, but of course, this doubles the disk space consumed. Not
always tenable.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
