Re: Need to update all my 60 million rows at once without transactional integrity

On Wed, Apr 23, 2008 at 1:52 PM, A. Kretschmer
<andreas(dot)kretschmer(at)schollglas(dot)com> wrote:
> am Mon, dem 21.04.2008, um 0:19:34 +0200 mailte christian_behrens(at)gmx(dot)net 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