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. > There are 1.2 million rows in this table and my update will affect 200,000 > of them. > > We do have indexes on all foreign keys that reference the product table. Well I suppose you had an update running concurrently against one of CATEGORY, MANUFACTURER, or MEDIA. Do any of those tables have a reference back to the product table? Is it possible to have a record with a reference back to the same record that refers to it? I think you're seeing the problem because these foreign keys are all initially deferred. That means you can update both tables and then can't commit either one because it needs to obtain a shared lock on the other record which is already locked for the update. I'm not certain that making them not deferred would actually eliminate the deadlock. It might just make it less likely. The deferred foreign key checks may also be related to the performance complaints. In my experience they're quite fast but I wonder what happens when you do a large batch update and then need to perform a whole slew of deferred foreign key checks. 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. Also, you have a lot of indexes here. That alone will make updates pretty slow. -- greg
