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

From Misa Simic
Subject Re: design help for performance
Date
Msg-id 5749858152011795140@iso-8859-1msgid
Whole thread Raw
In response to design help for performance  (Culley Harrelson <harrelson@gmail.com>)
List pgsql-general
Hm...

I think result on the end will be the same... I am not sure realation produce any locks on parent table...

What produces locks is UPDATE, so is it on table A or C should make no difference...

If simple join and count fk is so slow - other option would be materialized view... So it would need to include table C as materialized view but on the way to to don't make expensive calculations in real time during insert in B (and locking)

There is a article about materialized views on postgres wiki...

Sent from my Windows Phone

From: Culley Harrelson
Sent: 21 December 2011 22:07
To: Marc Mamin
Cc: Alban Hertroys; pgsql-general@postgresql.org
Subject: Re: [GENERAL] 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.Mamin@intershop.de> wrote:

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> owner@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

pgsql-general by date:

Previous
From: Misa Simic
Date:
Subject: Re: [partition table] python fetchall or fetchone functioncan not get the returning rows
Next
From: Raghavendra
Date:
Subject: Re: Why vacumming performed on template1 with initdb command ?