From: | "Merlin Moncure" <mmoncure(at)gmail(dot)com> |
---|---|
To: | "Alvaro Herrera" <alvherre(at)commandprompt(dot)com> |
Cc: | christian_behrens(at)gmx(dot)net, 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 15:27:14 |
Message-ID: | b42b73150804230827w3a2fb591p9c2839f255903f54@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Wed, Apr 23, 2008 at 9:04 AM, Alvaro Herrera
<alvherre(at)commandprompt(dot)com> wrote:
> christian_behrens(at)gmx(dot)net wrote:
>
>
> > 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.
>
> Perhaps you should rethink your data model.
for example:
*) change status-flag to a timestamp
*) make a new table (status_reset or something) with one column, one row...and
*) make a view (perhaps swapping names with your original table) that
joins the two tables and preserves the 1/0 status flag column
appearance so you don't have to change the app.
Consider the fundamental tenet: 'If PostgreSQL can't do it well, you
are probably doing it the wrong way' :-)
merlin
From | Date | Subject | |
---|---|---|---|
Next Message | John Gardner | 2008-04-23 15:27:33 | Vacuuming Questions |
Previous Message | Greg Smith | 2008-04-23 15:25:25 | Re: Debian etch, backport postgresql 8.3 experiences? |