Performance hit of foreign key constraints?

From: Jean-Christian Imbeault <jc(at)mega-bucks(dot)co(dot)jp>
To: pgsql-general(at)postgresql(dot)org
Subject: Performance hit of foreign key constraints?
Date: 2003-07-23 07:05:00
Message-ID: 3F1E339C.7010304@mega-bucks.co.jp
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general pgsql-performance

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?

Also, for a DB that is write-intensive and rarely read, what are some
things I can do to increase performance? (Keeping in mind that there is
more than on DB on the same pg server).

Thanks,

Jean-Christian Imbeault

Responses

Browse pgsql-general by date

  From Date Subject
Next Message nolan 2003-07-23 07:08:25 Re: birthday calculation
Previous Message Ben 2003-07-23 06:53:54 birthday calculation

Browse pgsql-performance by date

  From Date Subject
Next Message Richard Huxton 2003-07-23 08:49:09 Re: slow table updates
Previous Message Castle, Lindsay 2003-07-23 02:14:04 Re: One table or many tables for data set