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 77501D1C990 for ; Wed, 23 Jul 2003 08:47:04 +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 08840-03 for ; Wed, 23 Jul 2003 05:46:24 -0300 (ADT) Received: from ns.mega-bucks.co.jp (ns.mega-bucks.co.jp [219.118.175.241]) by svr1.postgresql.org (Postfix) with ESMTP id BBB8DD1C91A for ; Wed, 23 Jul 2003 04:07:15 -0300 (ADT) Received: from mega-bucks.co.jp (dhcp4.mega-bucks.co.jp [192.168.255.103]) by ns.mega-bucks.co.jp (8.12.9/8.12.5) with ESMTP id h6N6xVdT011102 for ; Wed, 23 Jul 2003 15:59:35 +0900 Message-ID: <3F1E339C.7010304@mega-bucks.co.jp> Date: Wed, 23 Jul 2003 16:05:00 +0900 From: Jean-Christian Imbeault User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.0.2) Gecko/20030208 Netscape/7.02 X-Accept-Language: en-us, en, ko MIME-Version: 1.0 To: pgsql-general@postgresql.org Subject: Performance hit of foreign key constraints? Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200307/1053 X-Sequence-Number: 45906 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