Skip site navigation (1) Skip section navigation (2)

Re: App very unresponsive while performing simple update

From: "Jim C(dot) Nasby" <jnasby(at)pervasive(dot)com>
To: Greg Stark <gsstark(at)mit(dot)edu>
Cc: Brendan Duddridge <brendan(at)clickspace(dot)com>,Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>,PostgreSQL Performance <pgsql-performance(at)postgresql(dot)org>
Subject: Re: App very unresponsive while performing simple update
Date: 2006-05-31 06:23:07
Message-ID: 20060531062307.GG53487@pervasive.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Sun, May 28, 2006 at 07:20:59PM -0400, Greg Stark wrote:
> Brendan Duddridge <brendan(at)clickspace(dot)com> writes:
> 
> > We do have foreign keys on other tables that reference the product  table.
> > Also, there will be updates going on at the same time as this  update. When
> > anyone clicks on a product details link, we issue an  update statement to
> > increment the click_count on the product. e.g.  update product set click_count
> > = click_count + 1;
> 
> You should realize this will produce a lot of garbage records and mean you'll
> have to be running vacuum very frequently. You might consider instead of
> updating the main table inserting into a separate clickstream table. That
> trades off not getting instantaneous live totals with isolating the
> maintenance headache in a single place. That table will grow large but you can
> prune it at your leisure without impacting query performance on your main
> tables.
 
Actually, you can still get instant results, you just have to hit two
tables to do it.

> More likely you were blocking on some lock. Until that other query holding
> that lock tries to commit Postgres won't actually detect a deadlock, it'll
> just sit waiting until the lock becomes available.

Wow, are you sure that's how it works? I would think it would be able to
detect deadlocks as soon as both processes are waiting on each other's
locks.
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby(at)pervasive(dot)com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

In response to

Responses

pgsql-performance by date

Next:From: Jim C. NasbyDate: 2006-05-31 06:29:08
Subject: Re: INSERT OU UPDATE WITHOUT SELECT?
Previous:From: Tom LaneDate: 2006-05-31 03:20:46
Subject: Re: Why the 8.1 plan is worst than 7.4?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group