I like the "cluster" and "reindex" ideas. The table is not that big and I do have the disk space. This table will also grow 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 be unavailable 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@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Rob Sargent Sent: Friday, March 04, 2011 10:21 AM To: pgsql-general@xxxxxxxxxxxxxx Subject: Re: 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 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. > Elegant, but of course, this doubles the disk space consumed. Not always tenable. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
