On Wed, Apr 23, 2008 at 1:52 PM, A. Kretschmer <andreas.kretschmer@xxxxxxxxxxxxxx> wrote: > am Mon, dem 21.04.2008, um 0:19:34 +0200 mailte christian_behrens@xxxxxxx folgendes: > > > > > If I do a batched loop like this: > > UPDATE table SET flag=0 where id>=0 and id <200; > > UPDATE table SET flag=0 where id>=200 and id <400; > > UPDATE table SET flag=0 where id>=400 and id <600; > > > Don't forget to VACUUM after every Update... > VACUUMing a large table so often could a problem. But if disk space is the only limitation and you don't care much about IO and CPU usage, its not a bad idea. > > > > > Is there any other way to go? > > Update to 8.3 and profit from the new HOT feature (wild guess: there is > no index on this flag-column) > HOT may not help a lot in this case. HOT needs free space in the same block to put the new version. It can recycle the previously updated rows and thus free up space, but only if the rows were updated in an older (now committed) transaction. Now, if you are doing batch updates, then there is a chance that HOT may be able recycle rows updated in one of the previous batches. But if the updates are happening sequential, then the blocks which were updated previously would never be touched again and hence no space will be freed. If you are updating one row at a time (in a separate transaction) or if the batch updates are kind of scattered, then HOT can reuse the dead tuples and limit the bloat. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com
