Re: design help for performance

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(dot)Mamin(at)intershop(dot)de> wrote:

>
> > -----Original Message-----
> > From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-
> > owner(at)postgresql(dot)org] On Behalf Of Alban Hertroys
> > Sent: Mittwoch, 21. Dezember 2011 08:53
> > To: Culley Harrelson
> > Cc: pgsql-general(at)postgresql(dot)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
>