Skip site navigation (1) Skip section navigation (2)

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

From: "A(dot) Kretschmer" <andreas(dot)kretschmer(at)schollglas(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: Need to update all my 60 million rows at once without transactional integrity
Date: 2008-04-23 08:22:55
Message-ID: 20080423082255.GE8401@a-kretschmer.de (view raw, whole thread or download thread mbox)
Thread:
Lists: pgsql-general
am  Mon, dem 21.04.2008, um  0:19:34 +0200 mailte christian_behrens(at)gmx(dot)net folgendes:
> Hi!
> 
> How can I make a Update of a column in a very large table for all rows without using the double amount of disc space and without any need for atomic operation?
> 
> I have a very large table with about 60 million rows. I sometimes need to do a simple update to ALL rows that resets a status-flag to zero.
> 
> I don't need to have transactional integrity (but of course if the system crashes, there should be no data corruption. A separate flag in the file system can well save the fact that that bulk update was in progress) for this, I don't care or want a abort or "all or nothing" like SQL mandates. The idea is basically that either this update succeeds or it succeeds or - there is no "not". It must succeed. It must be tried until it works. If the update is halfway finished, that's okay.
> 
> If I just do an 
> UPDATE table SET flag=0;
> then Pg will make a copy of every row which must be cleaned up by vaccuum. I understand - and don't like during this specific problem - that PG is a MVCC database and will keep my old versions of the rows before the update alive. This is very bad.
> 
> 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...


> 
> 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)


Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

In response to

Responses

pgsql-general by date

Next:From: A. KretschmerDate: 2008-04-23 08:38:50
Subject: Re: Updating with a subselect
Previous:From: A. KretschmerDate: 2008-04-23 08:13:55
Subject: Re: help with "plpgsql"

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group