> -----Original Message----- > From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general- > owner@xxxxxxxxxxxxxx] On Behalf Of Alban Hertroys > Sent: Mittwoch, 21. Dezember 2011 08:53 > To: Culley Harrelson > Cc: pgsql-general@xxxxxxxxxxxxxx > Subject: Re: 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
