> 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 

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?


Reply via email to