Re: Performance hit of foreign key constraints?

From: Stephan Szabo <sszabo(at)megazone(dot)bigpanda(dot)com>
To: Jean-Christian Imbeault <jc(at)mega-bucks(dot)co(dot)jp>
Cc: <pgsql-general(at)postgresql(dot)org>
Subject: Re: Performance hit of foreign key constraints?
Date: 2003-07-23 15:02:49
Message-ID: 20030723075933.N69662-100000@megazone.bigpanda.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-performance


On Wed, 23 Jul 2003, Stephan Szabo wrote:

>
> On Wed, 23 Jul 2003, Jean-Christian Imbeault wrote:
>
> > I have a database which is constantly being written to. A web server's
> > log file (and extras) is being written to it. There are no deletions or
> > updates (at least I think so :).
> >
> > As the web traffic increases so will the write intensity.
> >
> > Right now the database tables have no foreign keys defined even though
> > there are foreign keys. The code that inserts into the DB is simple
> > enough (now) that we can make sure that nothing is inserted if the
> > corresponding fk does not exist and that all fk checks pass.
> >
> > I want to add foreign key constraints to the table definitions but I am
> > worried that it might be a big performance hit. Can anyone tell me how
> > much of a performance hit adding one foreign key constraint to one field
> > in a table will roughly be?
>
> Well, generally speaking it'll be (assuming no ref actions - and covering
> actions you aren't doing):
> one select for each insert to the table with the constraint
> one select for each update to the table with the constraint, in current
> releases unpatched
> one select for each update to the table with the constraint if the
> key is changed in patched 7.3 or 7.4beta.
> one select for each delete to the referenced table
> one select for each update to the referenced table if the key is changed

So much for answering questions before I take my shower and wake up.
Make those last two be two selects, and in 7.3 and earlier, one of those
selects on update to referenced happens even if the key isn't changed
(there's a patch that should work to change that on -patches archive).

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Claudio Lapidus 2003-07-23 15:13:19 7.4dev or beta version
Previous Message Stephan Szabo 2003-07-23 14:49:12 Re: Performance hit of foreign key constraints?

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2003-07-23 15:21:06 Re: different query plan for same select
Previous Message Stephan Szabo 2003-07-23 14:49:12 Re: Performance hit of foreign key constraints?