pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedForeign key - Indexing & Deadlocking.
2+ messages / 2 participants
[nested] [flat]
* Foreign key - Indexing & Deadlocking.
@ 2009-12-29 22:59 Andrew Hall <andrewah@hotmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: Andrew Hall @ 2009-12-29 22:59 UTC (permalink / raw)
To: pgsql-sql
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.
Thanks,
Andrew.
_________________________________________________________________
Use Hotmail to send and receive mail from your different email accounts
http://clk.atdmt.com/UKM/go/186394592/direct/01/=
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Foreign key - Indexing & Deadlocking.
@ 2009-12-30 01:18 Craig Ringer <craig@postnewspapers.com.au>
parent: Andrew Hall <andrewah@hotmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: Craig Ringer @ 2009-12-30 01:18 UTC (permalink / raw)
To: Andrew Hall <andrewah@hotmail.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2009-12-30 01:18 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-12-29 22:59 Foreign key - Indexing & Deadlocking. Andrew Hall <andrewah@hotmail.com>
2009-12-30 01:18 ` Craig Ringer <craig@postnewspapers.com.au>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox