From: Josh Berkus <josh@agliodbs.com>
To: pgsql-performance@postgresql.org
Subject: Re: Low Performance for big hospital server ..
Date: Thu, 6 Jan 2005 09:06:55 -0800
Message-ID: <200501060906.55832.josh@agliodbs.com> (raw)
In-Reply-To: <758d5e7f050106041510f07dc5@mail.gmail.com>
References: <1104831107.41da6283add0c@webmail.moph.go.th>
<1104939342.41dc094ecb1cf@webmail.moph.go.th>
<758d5e7f050106041510f07dc5@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: josh@agliodbs.com
Subject: Re: Low Performance for big hospital server ..
In-Reply-To: <200501060906.55832.josh@agliodbs.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox