Thank you so much everyone! Introducing table C was indeed my next step but I was unsure if I was going to be just moving the locking problems from A to C. Locking on C is preferable to locking on A but it doesn't really solve the problem. It sounds like I should expect less locking on C because it doesn't relate to B. Thanks again, I am going to give it a try.
I am not going to take it to the delta solution for now. On Wed, Dec 21, 2011 at 1:46 AM, Marc Mamin <m.ma...@intershop.de> wrote: > > > -----Original Message----- > > From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- > > ow...@postgresql.org] On Behalf Of Alban Hertroys > > Sent: Mittwoch, 21. Dezember 2011 08:53 > > To: Culley Harrelson > > Cc: pgsql-general@postgresql.org > > Subject: Re: [GENERAL] design help for performance > > > > On 21 Dec 2011, at 24:56, Culley Harrelson wrote: > > > > > Several years ago I added table_b_rowcount to table A in order to > > minimize queries on table B. And now, as the application has grown, I > > am starting to having locking problems on table A. Any change to > table > > B requires the that table_b_rowcount be updated on table A... The > > application has outgrown this solution. > > > > > > When you update rowcount_b in table A, that locks the row in A of > > course, but there's more going on. Because a new version of that row > > gets created, the references from B to A also need updating to that > new > > version (creating new versions of rows in B as well). I think that > > causes a little bit more locking than originally anticipated - it may > > even be the cause of your locking problem. > > > > Instead, if you'd create a new table C that only holds the rowcount_b > > and a reference to A (in a 1:1 relationship), most of those problems > go > > away. It does add an extra foreign key reference to table A though, > > which means it will weigh down updates and deletes there some more. > > > > CREATE TABLE C ( > > table_a_id int PRIMARY KEY > > REFERENCES table_a (id) ON UPDATE CASCADE ON DELETE > > CASCADE, > > table_b_rowcount int NOT NULL DEFAULT 0 > > ); > > > > Yes, those cascades are on purpose - the data in C is useless without > > the accompanying record in A. Also, the PK makes sure it stays a 1:1 > > relationship. > > > > Alban Hertroys > > Hello, > > it may help to combine Alban solution with yours but at the cost of a > higher complexity: > > In table C use instead a column table_b_delta_rowcount (+1 /-1 , > smallint) and only use INSERTs to maintain it, no UPDATEs (hence with a > non unique index on id). > > Then regularily flush table C content to table A, in order to only have > recent changes in C. > Your query should then query both tables: > > SELECT A. table_b_rowcount + coalesce(sum(C.table_b_delta_rowcount)) > FROM A LEFT OUTER JOIN B on (A.id=B.id) > WHERE A.id = xxx > > Marc Mamin >
