Re: Foreign key - Indexing & Deadlocking.

On 30/12/2009 6:59 AM, Andrew Hall wrote:
> Hi,
>
> I'm porting some Oracle software to PostgreSQL, & have a question about
> best practices with foreign keys.
>
> In the Oracle environment, you're basically obliged to index all foreign
> keys (more specifically foreign key columns should appear on the leading
> edge of an index) due to the way that it manages locks.
>
> Failure to index foreign keys in this way in Oracle often causes
> deadlock situations.
>
> Although I could no find a mention of anything similar in the postgreSQL
> documentation, I just wanted to check whether or not the same was true
> of PostgreSQL to be on the safe side.

It is not the case. It's quite common in PostgreSQL to leave foreign 
keys without indexes. Doing so does mean that referential integrity 
checks being made on DELETE from the tablew with the referenced primary 
key will require a sequential scan of the referencing table(s) to check 
integrity, though.

PostgreSQL isn't smart enough (yet) to group up such checks into a 
single pass down the target table. So if you delete 100 rows from a 
table in a query, and that table is referenced by another table via a 
foreign key without an index, the referencing table will be sequentially 
scanned 100 times.

Needless to say, you want to add indexes to your foreign keys if you 
expect to delete from the parent, or (for some reason) update the 
primary key value for rows in the parent table.

--
Craig Ringer