Re: FKs Lock Contention

On Tue, Jun 27, 2006 at 00:52:48 -0300,
Bruno Almeida do Lago <teolupus(at)gmail(dot)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), 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?

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.