X-Original-To: pgsql-general-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.130]) by svr1.postgresql.org (Postfix) with ESMTP id AE284D1C515 for ; Wed, 23 Jul 2003 14:49:15 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (neptune.hub.org [64.117.224.130]) (amavisd-new, port 10024) with ESMTP id 34885-01 for ; Wed, 23 Jul 2003 11:48:33 -0300 (ADT) Received: from megazone.bigpanda.com (unknown [66.180.233.26]) by svr1.postgresql.org (Postfix) with ESMTP id 73C26D1C4DA for ; Wed, 23 Jul 2003 11:49:13 -0300 (ADT) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 1E049D67F; Wed, 23 Jul 2003 07:49:13 -0700 (PDT) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 139AE5C12; Wed, 23 Jul 2003 07:49:13 -0700 (PDT) Date: Wed, 23 Jul 2003 07:49:12 -0700 (PDT) From: Stephan Szabo To: Jean-Christian Imbeault Cc: Subject: Re: Performance hit of foreign key constraints? In-Reply-To: <3F1E339C.7010304@mega-bucks.co.jp> Message-ID: <20030723074442.L69170-100000@megazone.bigpanda.com> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200307/1070 X-Sequence-Number: 45923 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.