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 7D483D1C932 for ; Wed, 23 Jul 2003 09:52:23 +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 14819-08 for ; Wed, 23 Jul 2003 06:51:41 -0300 (ADT) Received: from smtp.pspl.co.in (unknown [202.54.11.65]) by svr1.postgresql.org (Postfix) with ESMTP id 39CE6D1C949 for ; Wed, 23 Jul 2003 06:52:17 -0300 (ADT) Received: (from root@localhost) by smtp.pspl.co.in (8.12.9/8.12.9) id h6N9vwRj021329 for ; Wed, 23 Jul 2003 15:27:58 +0530 Received: from daithan (daithan.intranet.pspl.co.in [192.168.7.161]) (authenticated bits=0) by persistent.co.in (8.12.9/8.12.9) with ESMTP id h6N9vvsW021316; Wed, 23 Jul 2003 15:27:58 +0530 From: "Shridhar Daithankar" To: pgsql-general@postgresql.org Date: Wed, 23 Jul 2003 15:24:01 +0530 MIME-Version: 1.0 Subject: Re: Performance hit of foreign key constraints? Reply-To: shridhar_daithankar@persistent.co.in Cc: pgsql-performance@postgresql.org Message-ID: <3F1EA891.16257.43E80B6@localhost> In-reply-to: <3F1E339C.7010304@mega-bucks.co.jp> X-mailer: Pegasus Mail for Windows (v4.12a) Content-type: text/plain; charset=US-ASCII Content-transfer-encoding: 7BIT Content-description: Mail message body X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200307/1059 X-Sequence-Number: 45912 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.