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.
We actually already have a table for this purpose. product_click_history
Actually, you can still get instant results, you just have to hit two
tables to do it.
Well, not really for our situation. We use the click_count on product
to sort our product listings by popularity. Joining with our
product_click_history to get live counts would be very slow. Some
categories have many tens of thousands of products. Any joins outside
our category_product table tend to be very slow.
We'll probably have to write a process to update the click_count from
querying our product_click_history table.
____________________________________________________________________
Brendan Duddridge | CTO | 403-277-5591 x24 | brendan@xxxxxxxxxxxxxx
ClickSpace Interactive Inc.
Suite L100, 239 - 10th Ave. SE
Calgary, AB T2G 0V9
http://www.clickspace.com
On May 31, 2006, at 12:23 AM, Jim C. Nasby wrote:
On Sun, May 28, 2006 at 07:20:59PM -0400, Greg Stark wrote:
Brendan Duddridge <brendan@xxxxxxxxxxxxxx> 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@xxxxxxxxxxxxx
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
---------------------------(end of
broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
