Dawid,

> Ahh, the huge update.  Below are my "hints" I've
> found while trying to optimize such updates.
> Divide the update, if possible.  This way query uses
> less memory and you may call VACUUM inbetween
> updates.  To do this, first SELECT INTO TEMPORARY
> table the list of rows to update (their ids or something),
> and then loop through it to update the values.

There are other ways to deal as well -- one by normalizing the database.   
Often, I find that massive updates like this are caused by a denormalized 
database.

For example, Lyris stores its "mailing numbers" only as repeated numbers in 
the recipients table.   When a mailing is complete, Lyris updates all of the 
recipients .... up to 750,000 rows in the case of my client ... to indicate 
the completion of the mailing (it's actually a little more complicated than 
that, but the essential problem is the example)

It would be far better for Lyris to use a seperate mailings table, with a 
status in that table ... which would then require only *one* update row to 
indicate completion, instead of 750,000.   

I can't tell you how many times I've seen this sort of thing.   And the 
developers always tell me "Well, we denormalized for performance reasons ... 
"

-- 
Josh Berkus
Aglio Database Solutions
San Francisco

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

               http://archives.postgresql.org

Reply via email to