Re: design help for performance - Mailing list pgsql-general

From Alban Hertroys
Subject Re: design help for performance
Date
Msg-id 92EB4439-ABA9-40F5-B58C-C303D68F96BC@gmail.com
Whole thread Raw
In response to design help for performance  (Culley Harrelson <harrelson@gmail.com>)
Responses Re: design help for performance
List pgsql-general
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
applicationhas grown, I am starting to having locking problems on table A.  Any change to table B requires the that
table_b_rowcountbe 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
versionof that row gets created, the references from B to A also need updating to that new version (creating new
versionsof rows in B as well). I think that causes a little bit more locking than originally anticipated - it may even
bethe 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),
mostof those problems go away. It does add an extra foreign key reference to table A though, which means it will weigh
downupdates 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
sureit stays a 1:1 relationship. 

Alban Hertroys

--
Screwing up is an excellent way to attach something to the ceiling.


pgsql-general by date:

Previous
From: Tom Lane
Date:
Subject: Re: out of memory error with loading pg_dumpall
Next
From: Misa Simic
Date:
Subject: Re: design help for performance