robjsargent@xxxxxxxxx (Rob Sargent) writes: > 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. ... But if you needed to do it in one Swell Foop, there really wasn't any other choice. The only way *not* to double (or more) space consumption is to do incremental updates, vacuuming around each increment. -- select 'cbbrowne' || '@' || 'linuxdatabases.info'; http://www3.sympatico.ca/cbbrowne/lisp.html We are MICROS~1. You will be assimilated. Resistance is futile. (Attributed to B.G., Gill Bates) -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
