FKs Lock Contention

["Bruno Almeida do Lago" <teolupus(at)gmail(dot)com>]

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