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.kumar@enterprisedb.com
Blog:http://vibhork.blogspot.com