Re: updating all records of a table

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 fragmentation when this is finished. Is there some sort of table healing/reorg/rebuild measure I should take if I want the resulting 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.

Thanks & Regards,
Vibhor Kumar
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
vibhor(dot)kumar(at)enterprisedb(dot)com
Blog:http://vibhork.blogspot.com