pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Performance hit of foreign key constraints?
4+ messages / 3 participants
[nested] [flat]

* Performance hit of foreign key constraints?
@ 2003-07-23 07:05 Jean-Christian Imbeault <jc@mega-bucks.co.jp>
  2003-07-23 09:54 ` Re: Performance hit of foreign key constraints? Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  2003-07-23 14:49 ` Re: Performance hit of foreign key constraints? Stephan Szabo <sszabo@megazone.bigpanda.com>
  0 siblings, 2 replies; 4+ messages in thread

From: Jean-Christian Imbeault @ 2003-07-23 07:05 UTC (permalink / raw)
  To: pgsql-general@postgresql.org

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




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Performance hit of foreign key constraints?
  2003-07-23 07:05 Performance hit of foreign key constraints? Jean-Christian Imbeault <jc@mega-bucks.co.jp>
@ 2003-07-23 09:54 ` Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  1 sibling, 0 replies; 4+ messages in thread

From: Shridhar Daithankar @ 2003-07-23 09:54 UTC (permalink / raw)
  To: pgsql-general@postgresql.org; +Cc: pgsql-performance

On 23 Jul 2003 at 16:05, 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?
> 
> 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).

1. Insert them in batches. Proper size of transactions can speed the write 
performance heavily.
2. What kind of foreign keys you have? It might be possible to reduce FK 
overhead if you are checking against small number of records.
3. Tune your hardware for write performance like getting a good-for-write RAID. 
I forgot which performs which for read and write.
4. Tune WAL and move it to separate drive. That should win you some 
performance.

HTH

Bye
 Shridhar

--
Beauty:	What's in your eye when you have a bee in your hand.




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Performance hit of foreign key constraints?
  2003-07-23 07:05 Performance hit of foreign key constraints? Jean-Christian Imbeault <jc@mega-bucks.co.jp>
@ 2003-07-23 14:49 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
  2003-07-23 15:02   ` Re: Performance hit of foreign key constraints? Stephan Szabo <sszabo@megazone.bigpanda.com>
  1 sibling, 1 reply; 4+ messages in thread

From: Stephan Szabo @ 2003-07-23 14:49 UTC (permalink / raw)
  To: Jean-Christian Imbeault <jc@mega-bucks.co.jp>; +Cc: pgsql-general@postgresql.org


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.





^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Performance hit of foreign key constraints?
  2003-07-23 07:05 Performance hit of foreign key constraints? Jean-Christian Imbeault <jc@mega-bucks.co.jp>
  2003-07-23 14:49 ` Re: Performance hit of foreign key constraints? Stephan Szabo <sszabo@megazone.bigpanda.com>
@ 2003-07-23 15:02   ` Stephan Szabo <sszabo@megazone.bigpanda.com>
  0 siblings, 0 replies; 4+ messages in thread

From: Stephan Szabo @ 2003-07-23 15:02 UTC (permalink / raw)
  To: Jean-Christian Imbeault <jc@mega-bucks.co.jp>; +Cc: pgsql-general@postgresql.org


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).




^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2003-07-23 15:02 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-07-23 07:05 Performance hit of foreign key constraints? Jean-Christian Imbeault <jc@mega-bucks.co.jp>
2003-07-23 09:54 ` Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
2003-07-23 14:49 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-07-23 15:02   ` Stephan Szabo <sszabo@megazone.bigpanda.com>

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