pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedunnecessary updates
3+ messages / 3 participants
[nested] [flat]
* unnecessary updates
@ 2002-10-30 16:42 chester c young <chestercyoung@yahoo.com>
2002-10-30 18:02 ` Re: unnecessary updates Andrew Perrin <clists@perrin.socsci.unc.edu>
0 siblings, 1 reply; 3+ messages in thread
From: chester c young @ 2002-10-30 16:42 UTC (permalink / raw)
To: pgsql-sql
When doing database work over the web, especially when many records are
on one page, *many* updates get posted to pg that do not change the
record. Eg, the page may contain 50 records, the user changes 1, and
submits.
I assume that a no-change update takes the same resources as a "real"
update, ie, a new block is allocated to write the record, the record
written, indicies are rerouted to the new block, and the old block
needs to be vacuumed later. Is this true?
In SQL, the only way I know to prevent this thrashing is to write the
update with an elaborate where clause, eg, "update ... where pk=1 and
(c1!='v1' or c2!='v2' or ... )". This adds cost both to the app server
and to pg - is the cost justified?
Finally, is there anyway to flag pg to ignore no-change updates? This
seems to me to me the most efficient way of handling the needless work.
thanks
chester
__________________________________________________
Do you Yahoo!?
HotJobs - Search new jobs daily now
http://hotjobs.yahoo.com/
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: unnecessary updates
2002-10-30 16:42 unnecessary updates chester c young <chestercyoung@yahoo.com>
@ 2002-10-30 18:02 ` Andrew Perrin <clists@perrin.socsci.unc.edu>
2002-11-01 21:27 ` Re: unnecessary updates Ross J. Reedstrom <reedstrm@rice.edu>
0 siblings, 1 reply; 3+ messages in thread
From: Andrew Perrin @ 2002-10-30 18:02 UTC (permalink / raw)
To: chester c young <chestercyoung@yahoo.com>; +Cc: pgsql-sql
One strategy is to use some sort of middleware that takes care of this. On
a project I did a few years ago, I used a perl module that read the record
from Postgres and made it into a perl object. The object contained a
variable, "changed", that reflected whether anything had actually changed
in the object. Finally, there was an object method put() that took care of
updating the database. put() checked the changed property and simply
silently finished unless changed was true.
ap
----------------------------------------------------------------------
Andrew J Perrin - http://www.unc.edu/~aperrin
Assistant Professor of Sociology, U of North Carolina, Chapel Hill
clists@perrin.socsci.unc.edu * andrew_perrin (at) unc.edu
On Wed, 30 Oct 2002, chester c young wrote:
> When doing database work over the web, especially when many records are
> on one page, *many* updates get posted to pg that do not change the
> record. Eg, the page may contain 50 records, the user changes 1, and
> submits.
>
> I assume that a no-change update takes the same resources as a "real"
> update, ie, a new block is allocated to write the record, the record
> written, indicies are rerouted to the new block, and the old block
> needs to be vacuumed later. Is this true?
>
> In SQL, the only way I know to prevent this thrashing is to write the
> update with an elaborate where clause, eg, "update ... where pk=1 and
> (c1!='v1' or c2!='v2' or ... )". This adds cost both to the app server
> and to pg - is the cost justified?
>
> Finally, is there anyway to flag pg to ignore no-change updates? This
> seems to me to me the most efficient way of handling the needless work.
>
> thanks
> chester
>
> __________________________________________________
> Do you Yahoo!?
> HotJobs - Search new jobs daily now
> http://hotjobs.yahoo.com/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: unnecessary updates
2002-10-30 16:42 unnecessary updates chester c young <chestercyoung@yahoo.com>
2002-10-30 18:02 ` Re: unnecessary updates Andrew Perrin <clists@perrin.socsci.unc.edu>
@ 2002-11-01 21:27 ` Ross J. Reedstrom <reedstrm@rice.edu>
0 siblings, 0 replies; 3+ messages in thread
From: Ross J. Reedstrom @ 2002-11-01 21:27 UTC (permalink / raw)
To: Andrew Perrin <clists@perrin.socsci.unc.edu>; +Cc: chester c young <chestercyoung@yahoo.com>; pgsql-sql
Another way to approach this would be to add a trigger to your table
in the database, that rejects updates that don't change any values.
You'd basically have to hard code that same logic (new.a != old.a or
new.b != old.b ...) and it'd fire on every update, so you're talking
about trading computational cycles for savings in diskspace (and vacuum
time). Like all things, it's a tradoff. Only way to tell for your case is
to try it, I'd guess. It'd be kind of interesting to know if this would
be useful, but you'd need to write a tool to analyze your tables before
vacuum, to determine if the dead tuples differ from the current values
(or from each other).
Ross
On Wed, Oct 30, 2002 at 01:02:26PM -0500, Andrew Perrin wrote:
> One strategy is to use some sort of middleware that takes care of this. On
> a project I did a few years ago, I used a perl module that read the record
> from Postgres and made it into a perl object. The object contained a
> variable, "changed", that reflected whether anything had actually changed
> in the object. Finally, there was an object method put() that took care of
> updating the database. put() checked the changed property and simply
> silently finished unless changed was true.
>
> ap
>
> ----------------------------------------------------------------------
> Andrew J Perrin - http://www.unc.edu/~aperrin
> Assistant Professor of Sociology, U of North Carolina, Chapel Hill
> clists@perrin.socsci.unc.edu * andrew_perrin (at) unc.edu
>
>
> On Wed, 30 Oct 2002, chester c young wrote:
>
> > When doing database work over the web, especially when many records are
> > on one page, *many* updates get posted to pg that do not change the
> > record. Eg, the page may contain 50 records, the user changes 1, and
> > submits.
> >
> > I assume that a no-change update takes the same resources as a "real"
> > update, ie, a new block is allocated to write the record, the record
> > written, indicies are rerouted to the new block, and the old block
> > needs to be vacuumed later. Is this true?
> >
> > In SQL, the only way I know to prevent this thrashing is to write the
> > update with an elaborate where clause, eg, "update ... where pk=1 and
> > (c1!='v1' or c2!='v2' or ... )". This adds cost both to the app server
> > and to pg - is the cost justified?
> >
> > Finally, is there anyway to flag pg to ignore no-change updates? This
> > seems to me to me the most efficient way of handling the needless work.
> >
> > thanks
> > chester
> >
> > __________________________________________________
> > Do you Yahoo!?
> > HotJobs - Search new jobs daily now
> > http://hotjobs.yahoo.com/
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> >
> > http://archives.postgresql.org
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2002-11-01 21:27 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-10-30 16:42 unnecessary updates chester c young <chestercyoung@yahoo.com>
2002-10-30 18:02 ` Andrew Perrin <clists@perrin.socsci.unc.edu>
2002-11-01 21:27 ` Ross J. Reedstrom <reedstrm@rice.edu>
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