pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: Jean-Christian Imbeault <jc@mega-bucks.co.jp>
Cc: pgsql-general@postgresql.org
Subject: Re: Performance hit of foreign key constraints?
Date: Wed, 23 Jul 2003 07:49:12 -0700 (PDT)
Message-ID: <20030723074442.L69170-100000@megazone.bigpanda.com> (raw)
In-Reply-To: <3F1E339C.7010304@mega-bucks.co.jp>


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
 plus management of the trigger queue (this can be an issue in long
  transactions since the queue can get big)
 and some misc. work in the triggers.

You really want the foregin key on the table with the constraint to be
indexed and using the index if you expect eitherof the referenced table
conditions to happen.





view thread (4+ messages)  latest in thread

Message-ID: <20030723074442.L69170-100000@megazone.bigpanda.com>
Permalink:  ../20030723074442.L69170-100000@megazone.bigpanda.com/
Also on:    postgresql.org/message-id/20030723074442.L69170-100000@megazone.bigpanda.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: sszabo@megazone.bigpanda.com, jc@mega-bucks.co.jp, pgsql-general@postgresql.org
  Subject: Re: Performance hit of foreign key constraints?
  In-Reply-To: <20030723074442.L69170-100000@megazone.bigpanda.com>
  References: <3F1E339C.7010304@mega-bucks.co.jp> <20030723074442.L69170-100000@megazone.bigpanda.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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