Hello,

I need some help to understand better the way PostgreSQL works internally:

Oracle 8.1.7 used to have a severe lock contention when FKs had no index
(causing an sx table lock). AFAIK this was "fixed" on 9i with the addition
of "shared row locking".

Reading the docs I found that PostgreSQL team implemented "shared row
locking" on 8.1 (my personal thanks and admiration to those who did it), so
we now can expect much less contention.

With this new scenario, I wonder which FKs should really get an index and
which not (especially for composed FKs)? How the order of my PKs and FKs
would influence that?

I know this is not a simple question, but hope that someone could show me
the light. :-)


Best Regards,
Bruno Almeida do Lago



---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

[ Post a follow-up to this message ]

On Tue, Jun 27, 2006 at 00:52:48 -0300,
Bruno Almeida do Lago <teolupus@gmail.com> wrote:
>
> Oracle 8.1.7 used to have a severe lock contention when FKs had no index
> (causing an sx table lock). AFAIK this was "fixed" on 9i with the addition
> of "shared row locking".

In Postgres this problem wasn't related to indexes.

> Reading the docs I found that PostgreSQL team implemented "shared row
> locking" on 8.1 (my personal thanks and admiration to those who did it), s
o
> we now can expect much less contention.
>
> With this new scenario, I wonder which FKs should really get an index and
> which not (especially for composed FKs)? How the order of my PKs and FKs
> would influence that?

The referenced key column(s) must be indexed. Postgres qill not give you
an option there.

The referencing columns typically only need indexes if you are deleting or
updating rows in the referenced table. My memory is that updates are only
a problem if one of the referenced columns is updated, but you might want
to double check this.

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

[ Post a follow-up to this message ]

On Tue, Jun 27, 2006 at 00:52:48 -0300,
Bruno Almeida do Lago <teolupus@gmail.com> wrote:
>
> Oracle 8.1.7 used to have a severe lock contention when FKs had no index
> (causing an sx table lock). AFAIK this was "fixed" on 9i with the addition
> of "shared row locking".

In Postgres this problem wasn't related to indexes.

> Reading the docs I found that PostgreSQL team implemented "shared row
> locking" on 8.1 (my personal thanks and admiration to those who did it), s
o
> we now can expect much less contention.
>
> With this new scenario, I wonder which FKs should really get an index and
> which not (especially for composed FKs)? How the order of my PKs and FKs
> would influence that?

The referenced key column(s) must be indexed. Postgres qill not give you
an option there.

The referencing columns typically only need indexes if you are deleting or
updating rows in the referenced table. My memory is that updates are only
a problem if one of the referenced columns is updated, but you might want
to double check this.

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

[ Post a follow-up to this message ]