From rodrigo.sakai@zanthus.com.br Tue Apr 11 19:35:47 2006 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 265C211F8B81 for ; Tue, 11 Apr 2006 16:35:47 -0300 (ADT) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Score: 0.001 X-Spam-Level: X-Spam-Status: No, score=0.001 required=5 tests=[HTML_MESSAGE=0.001] Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id yCsSDIwcQ7Vk for ; Tue, 11 Apr 2006 16:33:06 -0300 (ADT) Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id ACF6A11F87D0 for ; Tue, 11 Apr 2006 16:13:30 -0300 (ADT) Received: from mail.zanthus.com.br (mail.zanthus.com.br [200.212.65.85]) by svr4.postgresql.org (Postfix) with ESMTP id 53F2F5AF18F for ; Tue, 11 Apr 2006 19:13:29 +0000 (GMT) Received: from cprsakai ([192.168.0.71]) by mail.zanthus.com.br (8.13.4/8.13.4/Debian-3) with SMTP id k3BJEhB6005390 for ; Tue, 11 Apr 2006 16:14:43 -0300 Message-ID: <001301c65d9c$08884ef0$4700a8c0@TREEZANTHUS> Reply-To: "Rodrigo Sakai" From: "Rodrigo Sakai" To: Subject: FOREIGN KEYS vs PERFORMANCE Date: Tue, 11 Apr 2006 16:13:34 -0300 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0010_01C65D82.E258A630" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1506 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1506 X-Zanthus-MailScanner: Found to be clean X-Zanthus-MailScanner-From: rodrigo.sakai@zanthus.com.br X-Archive-Number: 200604/240 X-Sequence-Number: 18324 This is a multi-part message in MIME format. ------=_NextPart_000_0010_01C65D82.E258A630 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi, I think this is an old question, but I want to know if it really is = well worth to not create some foreign keys an deal with the referential = integrity at application-level????? Specifically, the system we are developing is a server/cliente = architecture that the server is the database and the fat client is an = application developed in DELPHI!!! Thanks in advance!! ------=_NextPart_000_0010_01C65D82.E258A630 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
  Hi,
 
  I think this is an old question, = but I want=20 to know if it really is well worth to not create some = foreign=20 keys an deal with the referential integrity at=20 application-level?????
  Specifically, the system we are = developing=20 is a server/cliente architecture that the server is the database and the = fat=20 client is an application developed in DELPHI!!!
 
  Thanks in=20 advance!!
------=_NextPart_000_0010_01C65D82.E258A630-- From smarlowe@g2switchworks.com Tue Apr 11 20:26:12 2006 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 619CA11F6C2F for ; Tue, 11 Apr 2006 17:26:12 -0300 (ADT) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Score: 0.118 X-Spam-Level: X-Spam-Status: No, score=0.118 required=5 tests=[AWL=0.117, UNPARSEABLE_RELAY=0.001] Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 1mPcq8+DLWoE for ; Tue, 11 Apr 2006 17:25:02 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from exchange.g2switchworks.com (mail.g2switchworks.com [63.87.162.25]) by postgresql.org (Postfix) with ESMTP id 69D2B11F6C78 for ; Tue, 11 Apr 2006 16:48:47 -0300 (ADT) Received: from 10.10.1.37 ([10.10.1.37]) by exchange.g2switchworks.com ([10.10.1.2]) with Microsoft Exchange Server HTTP-DAV ; Tue, 11 Apr 2006 19:48:46 +0000 Received: from state.g2switchworks.com by mail.g2switchworks.com; 11 Apr 2006 14:48:46 -0500 Subject: Re: FOREIGN KEYS vs PERFORMANCE From: Scott Marlowe To: Rodrigo Sakai Cc: pgsql-performance@postgresql.org In-Reply-To: <001301c65d9c$08884ef0$4700a8c0@TREEZANTHUS> References: <001301c65d9c$08884ef0$4700a8c0@TREEZANTHUS> Content-Type: text/plain Content-Transfer-Encoding: 7bit Message-Id: <1144784926.32269.88.camel@state.g2switchworks.com> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 (1.4.6-2) Date: Tue, 11 Apr 2006 14:48:46 -0500 X-Archive-Number: 200604/241 X-Sequence-Number: 18325 On Tue, 2006-04-11 at 14:13, Rodrigo Sakai wrote: > Hi, > > I think this is an old question, but I want to know if it really is > well worth to not create some foreign keys an deal with the > referential integrity at application-level????? > Specifically, the system we are developing is a server/cliente > architecture that the server is the database and the fat client is an > application developed in DELPHI!!! > If ref integrity is important, you'll have to do it either in the app or the database. Almost always, it's faster to let the database do it, as there's less traffic across the wire required to maintain ref integrity, plus, the guys who wrote the database have spent years making sure race conditions won't scram your data. For simple, straight forward FK->PK relationships, you will likely NOT be able to beat the database in terms of either reliability or performance with your own code. From grzm@myrealbox.com Tue Apr 11 23:06:29 2006 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id A2BC411F61DE for ; Tue, 11 Apr 2006 20:06:29 -0300 (ADT) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Score: 1.166 X-Spam-Level: * X-Spam-Status: No, score=1.166 required=5 tests=[AWL=-0.166, RCVD_IN_BL_SPAMCOP_NET=1.332] Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id f4GScmnu2Rdc for ; Tue, 11 Apr 2006 20:06:12 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from smtp-send.myrealbox.com (smtp-send.myrealbox.com [151.155.5.143]) by postgresql.org (Postfix) with ESMTP id 208B311F653D for ; Tue, 11 Apr 2006 20:06:20 -0300 (ADT) Received: from [172.16.1.180] grzm [61.197.227.146] by smtp-send.myrealbox.com with NetMail SMTP Agent $Revision: 1.6 $ on Linux; Tue, 11 Apr 2006 17:06:19 -0600 In-Reply-To: <001301c65d9c$08884ef0$4700a8c0@TREEZANTHUS> References: <001301c65d9c$08884ef0$4700a8c0@TREEZANTHUS> Mime-Version: 1.0 (Apple Message framework v749.3) X-Priority: 3 Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed Message-Id: <72C39159-1A4B-4595-A978-1DF57AD42527@myrealbox.com> Cc: Content-Transfer-Encoding: 7bit From: Michael Glaesemann Subject: Re: FOREIGN KEYS vs PERFORMANCE Date: Wed, 12 Apr 2006 08:06:17 +0900 To: "Rodrigo Sakai" X-Mailer: Apple Mail (2.749.3) X-Archive-Number: 200604/253 X-Sequence-Number: 18337 On Apr 12, 2006, at 4:13 , Rodrigo Sakai wrote: > I think this is an old question, but I want to know if it really > is well worth to not create some foreign keys an deal with the > referential integrity at application-level????? If I had to choose between one or the other, I'd leave all referential integrity in the database and deal with the errors thrown when referential integrity is violated in the application. PostgreSQL is designed to handle these kinds of issues. Anything you code in your application is more likely to contain bugs or miss corner cases that would allow referential integrity to be violated. PostgreSQL has been pounded on for years by a great many users and developers, making the likelihood of bugs still remaining much smaller. Of course, you can add some referential integrity checks in your application code, but those should be in addition to your database- level checks. Michael Glaesemann grzm myrealbox com From jnasby@pervasive.com Tue Apr 11 23:38:39 2006 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 78B4511F6611 for ; Tue, 11 Apr 2006 20:38:39 -0300 (ADT) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Score: 0.51 X-Spam-Level: X-Spam-Status: No, score=0.51 required=5 tests=[AWL=0.510] Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 5Hei4ifxS7I6 for ; Tue, 11 Apr 2006 20:38:27 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from noel.decibel.org (noel.decibel.org [67.100.216.10]) by postgresql.org (Postfix) with ESMTP id 5826D11F65B3 for ; Tue, 11 Apr 2006 20:38:36 -0300 (ADT) Received: by noel.decibel.org (Postfix, from userid 1001) id 28B9856427; Tue, 11 Apr 2006 18:38:21 -0500 (CDT) Received: (hashcash-sendmail, from uid 1001); Tue, 11 Apr 2006 18:38:20 -0500 Date: Tue, 11 Apr 2006 18:38:20 -0500 From: "Jim C. Nasby" To: Michael Glaesemann Cc: Rodrigo Sakai , pgsql-performance@postgresql.org Subject: Re: FOREIGN KEYS vs PERFORMANCE Message-ID: <20060411233820.GB49405@pervasive.com> References: <001301c65d9c$08884ef0$4700a8c0@TREEZANTHUS> <72C39159-1A4B-4595-A978-1DF57AD42527@myrealbox.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <72C39159-1A4B-4595-A978-1DF57AD42527@myrealbox.com> X-Operating-System: FreeBSD 6.0-RELEASE-p4 amd64 X-Distributed: Join the Effort! http://www.distributed.net User-Agent: Mutt/1.5.11 X-Hashcash: 1:20:060411:grzm@myrealbox.com::kT7TVqH3MCb6vd9F:0000000000000000000 0000000000000000000000004VoI X-Hashcash: 1:20:060411:rodrigo.sakai@zanthus.com.br::f3yGTUM6VWMmP62H:000000000 000000000000000000000000095p X-Hashcash: 1:20:060411:pgsql-performance@postgresql.org::i1Kji4pFt7Puz1l1:00000 0000000000000000000000004Y6c X-Archive-Number: 200604/254 X-Sequence-Number: 18338 On Wed, Apr 12, 2006 at 08:06:17AM +0900, Michael Glaesemann wrote: > > On Apr 12, 2006, at 4:13 , Rodrigo Sakai wrote: > > > I think this is an old question, but I want to know if it really > >is well worth to not create some foreign keys an deal with the > >referential integrity at application-level????? > > If I had to choose between one or the other, I'd leave all > referential integrity in the database and deal with the errors thrown > when referential integrity is violated in the application. PostgreSQL > is designed to handle these kinds of issues. Anything you code in > your application is more likely to contain bugs or miss corner cases > that would allow referential integrity to be violated. PostgreSQL has > been pounded on for years by a great many users and developers, > making the likelihood of bugs still remaining much smaller. It's also pretty unlikely that you can make RI in the application perform better than in the database. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 From tgl@sss.pgh.pa.us Wed Apr 12 02:56:53 2006 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (mx2.hub.org [200.46.204.254]) by postgresql.org (Postfix) with ESMTP id 50ECD11F6AF1 for ; Tue, 11 Apr 2006 23:56:53 -0300 (ADT) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Score: -1.121 X-Spam-Level: X-Spam-Status: No, score=-1.121 required=5 tests=[AWL=1.480, BAYES_00=-2.599, SPF_HELO_PASS=-0.001, SPF_PASS=-0.001] Received: from postgresql.org ([200.46.204.71]) by localhost (mx2.hub.org [200.46.204.254]) (amavisd-new, port 10024) with ESMTP id wRZ6Y7kDtLnY for ; Tue, 11 Apr 2006 23:56:49 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 9C0D611F6AE3 for ; Tue, 11 Apr 2006 23:56:45 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id k3C2uhHY013088; Tue, 11 Apr 2006 22:56:43 -0400 (EDT) To: "Jim C. Nasby" cc: Michael Glaesemann , Rodrigo Sakai , pgsql-performance@postgresql.org Subject: Re: FOREIGN KEYS vs PERFORMANCE In-reply-to: <20060411233820.GB49405@pervasive.com> References: <001301c65d9c$08884ef0$4700a8c0@TREEZANTHUS> <72C39159-1A4B-4595-A978-1DF57AD42527@myrealbox.com> <20060411233820.GB49405@pervasive.com> Comments: In-reply-to "Jim C. Nasby" message dated "Tue, 11 Apr 2006 18:38:20 -0500" Date: Tue, 11 Apr 2006 22:56:43 -0400 Message-ID: <13087.1144810603@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200604/256 X-Sequence-Number: 18340 "Jim C. Nasby" writes: > On Wed, Apr 12, 2006 at 08:06:17AM +0900, Michael Glaesemann wrote: >> ... Anything you code in >> your application is more likely to contain bugs or miss corner cases >> that would allow referential integrity to be violated. PostgreSQL has >> been pounded on for years by a great many users and developers, >> making the likelihood of bugs still remaining much smaller. > It's also pretty unlikely that you can make RI in the application > perform better than in the database. I think the traditional assumption among the "you should do RI in the application" crowd is that the application has higher-level knowledge that lets it understand when it can skip doing an RI check entirely. Skipping an RI check is always faster than doing it --- so that's right, it's faster. As long as you don't make any mistakes. The question you have to ask yourself is whether you are really that smart ... not just today, but every single time. To quote Clint Eastwood: "Do you feel lucky punk? Well, do you?" regards, tom lane From lists@peufeu.com Wed Apr 12 07:22:09 2006 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (mx2.hub.org [200.46.204.254]) by postgresql.org (Postfix) with ESMTP id 4ECC611F66F6 for ; Wed, 12 Apr 2006 04:22:09 -0300 (ADT) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Score: -2.464 X-Spam-Level: X-Spam-Status: No, score=-2.464 required=5 tests=[BAYES_00=-2.599, FORGED_RCVD_HELO=0.135] Received: from postgresql.org ([200.46.204.71]) by localhost (mx2.hub.org [200.46.204.254]) (amavisd-new, port 10024) with ESMTP id cZUGfAlNt29W for ; Wed, 12 Apr 2006 04:22:02 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from peufeu.com (boutiquenumerique.com [82.67.9.10]) by postgresql.org (Postfix) with ESMTP id 295B811F65F1 for ; Wed, 12 Apr 2006 04:22:00 -0300 (ADT) Received: (qmail 27787 invoked from network); 12 Apr 2006 09:22:54 +0200 Received: from boutiquenumerique.com (HELO apollo13) (82.67.9.10) by boutiquenumerique.com with SMTP; 12 Apr 2006 09:22:54 +0200 To: "Michael Glaesemann" , "Rodrigo Sakai" Cc: pgsql-performance@postgresql.org Subject: Re: FOREIGN KEYS vs PERFORMANCE References: <001301c65d9c$08884ef0$4700a8c0@TREEZANTHUS> <72C39159-1A4B-4595-A978-1DF57AD42527@myrealbox.com> Message-ID: Date: Wed, 12 Apr 2006 09:22:52 +0200 From: PFC Content-Type: text/plain; format=flowed; delsp=yes; charset=utf-8 MIME-Version: 1.0 Content-Transfer-Encoding: 8bit In-Reply-To: <72C39159-1A4B-4595-A978-1DF57AD42527@myrealbox.com> User-Agent: Opera M2/8.51 (Linux, build 1462) X-Archive-Number: 200604/258 X-Sequence-Number: 18342 >> I think this is an old question, but I want to know if it really is >> well worth to not create some foreign keys an deal with the referential >> integrity at application-level????? Trust me : do it in the application and you'll enter a world of hurt. I'm doing it with some mysql apps, and it's a nightmare ; doing cascaded delete's by hand, etc, you always forget something, you have to modify a million places in your code everytime you add a new table, your ORM bloats, you get to write cleanup cron scripts which take forever to run, your website crashes etc. From schabi@logix-tt.com Wed Apr 12 13:18:45 2006 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id D32EB11F6632 for ; Wed, 12 Apr 2006 10:18:45 -0300 (ADT) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Score: 0.12 X-Spam-Level: X-Spam-Status: No, score=0.12 required=5 tests=[AWL=0.120] Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id VYuqahP37JqU for ; Wed, 12 Apr 2006 10:18:39 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from mail.logix-tt.com (unknown [212.211.145.186]) by postgresql.org (Postfix) with ESMTP id 182AB11F6065 for ; Wed, 12 Apr 2006 10:18:43 -0300 (ADT) Received: from kingfisher.intern.logix-tt.com (T9c8e.t.pppool.de [89.55.156.142]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) by mail.logix-tt.com (Postfix) with ESMTP id 51E51657E1; Wed, 12 Apr 2006 15:19:37 +0200 (CEST) Received: from [127.0.0.1] (localhost [127.0.0.1]) by kingfisher.intern.logix-tt.com (Postfix) with ESMTP id 0786C181C1E89; Wed, 12 Apr 2006 15:18:56 +0200 (CEST) Message-ID: <443CFE3F.5090304@logix-tt.com> Date: Wed, 12 Apr 2006 15:18:55 +0200 From: Markus Schaber Organization: Logical Tracking and Tracing International AG, Switzerland User-Agent: Debian Thunderbird 1.0.7 (X11/20051017) X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-performance@postgresql.org Cc: Rodrigo Sakai Subject: Re: FOREIGN KEYS vs PERFORMANCE References: <001301c65d9c$08884ef0$4700a8c0@TREEZANTHUS> <72C39159-1A4B-4595-A978-1DF57AD42527@myrealbox.com> In-Reply-To: <72C39159-1A4B-4595-A978-1DF57AD42527@myrealbox.com> X-Enigmail-Version: 0.93.0.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit X-Archive-Number: 200604/261 X-Sequence-Number: 18345 Hi, Michael, Hi, Rodrigo, Michael Glaesemann wrote: > If I had to choose between one or the other, I'd leave all referential > integrity in the database and deal with the errors thrown when > referential integrity is violated in the application. PostgreSQL is > designed to handle these kinds of issues. Anything you code in your > application is more likely to contain bugs or miss corner cases that > would allow referential integrity to be violated. PostgreSQL has been > pounded on for years by a great many users and developers, making the > likelihood of bugs still remaining much smaller. I strictly agree with Michael here. > Of course, you can add some referential integrity checks in your > application code, but those should be in addition to your database- > level checks. Agree. It does make sense to have reference checks in the UI or application level for the sake of better error handling, but the database should be the mandatory judge. There's another advantage of database based checking: Should there ever be the need of a different application working on the same database (e. G. an "expert level UI", or some connector that connects / synchronizes to another software, or a data import tool), database based constraints cannot be broken opposed to application based ones. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org From cjames@modgraph-usa.com Wed Apr 12 14:50:20 2006 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 967B911F68DF for ; Wed, 12 Apr 2006 11:50:20 -0300 (ADT) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Score: 0 X-Spam-Level: X-Spam-Status: No, score=0 required=5 tests=[none] Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id fc1rrMugyS0R for ; Wed, 12 Apr 2006 11:50:13 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from moonunit2.moonview.localnet (wsip-68-15-5-150.sd.sd.cox.net [68.15.5.150]) by postgresql.org (Postfix) with ESMTP id 69C0311F606B for ; Wed, 12 Apr 2006 11:50:14 -0300 (ADT) Received: from [192.168.0.3] (moonunit3.moonview.localnet [192.168.0.3]) by moonunit2.moonview.localnet (8.13.1/8.13.1) with ESMTP id k3CEsNQt025002; Wed, 12 Apr 2006 07:54:23 -0700 Message-ID: <443D127D.2070601@modgraph-usa.com> Date: Wed, 12 Apr 2006 07:45:17 -0700 From: "Craig A. James" User-Agent: Mozilla Thunderbird 1.0.6 (X11/20050716) X-Accept-Language: en-us, en MIME-Version: 1.0 To: PFC CC: Michael Glaesemann , Rodrigo Sakai , pgsql-performance@postgresql.org Subject: Re: FOREIGN KEYS vs PERFORMANCE References: <001301c65d9c$08884ef0$4700a8c0@TREEZANTHUS> <72C39159-1A4B-4595-A978-1DF57AD42527@myrealbox.com> In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit X-Archive-Number: 200604/262 X-Sequence-Number: 18346 >>> I think this is an old question, but I want to know if it really >>> is well worth to not create some foreign keys an deal with the >>> referential integrity at application-level????? > > > Trust me : do it in the application and you'll enter a world of > hurt. I'm doing it with some mysql apps, and it's a nightmare ; doing > cascaded delete's by hand, etc, you always forget something, you have > to modify a million places in your code everytime you add a new table, > your ORM bloats, you get to write cleanup cron scripts which take > forever to run, your website crashes etc. All good advice, but... there are no absolutes in this world. Application-enforced referential integrity makes sense if (and probably ONLY if): 1. You have only one application that modifies the data. (Otherwise, you have to duplicate the rules across many applications, leading to a code-maintenance nightmare). 2. If your application crashes and leaves a mess, it's not a catastrophe, and you have a good way to clean it up. For example, a bank shouldn't do this, but it might be OK for a computer-aided-design application, or the backend of a news web site. 3. You have application-specific knowledge about when you can skip referential integrity and thereby greatly improve performance. For example, you may have batch operations where large numbers of rows are temporarily inconsistent. If your application doesn't meet ALL of these criteria, you probably should use the database for referential integrity. Craig From rodrigo.sakai@zanthus.com.br Wed Apr 12 14:50:22 2006 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 5F2D511F692E for ; Wed, 12 Apr 2006 11:50:22 -0300 (ADT) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Score: 0.12 X-Spam-Level: X-Spam-Status: No, score=0.12 required=5 tests=[AWL=0.120] Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id eOzoE2yvEbrG for ; Wed, 12 Apr 2006 11:50:14 -0300 (ADT) X-Greylist: delayed 19:36:56.728041 by SQLgrey- Received: from mail.zanthus.com.br (mail.zanthus.com.br [200.212.65.85]) by postgresql.org (Postfix) with ESMTP id 80A2811F65C4 for ; Wed, 12 Apr 2006 11:50:17 -0300 (ADT) Received: from cprsakai ([192.168.0.71]) by mail.zanthus.com.br (8.13.4/8.13.4/Debian-3) with SMTP id k3CEoxMA020904; Wed, 12 Apr 2006 11:50:59 -0300 Message-ID: <000701c65e40$5a1ff700$4700a8c0@TREEZANTHUS> Reply-To: "Rodrigo Sakai" From: "Rodrigo Sakai" To: "Markus Schaber" , References: <001301c65d9c$08884ef0$4700a8c0@TREEZANTHUS> <72C39159-1A4B-4595-A978-1DF57AD42527@myrealbox.com> <443CFE3F.5090304@logix-tt.com> Subject: Re: FOREIGN KEYS vs PERFORMANCE Date: Wed, 12 Apr 2006 11:49:49 -0300 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1506 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1506 X-Zanthus-MailScanner: Found to be clean X-Zanthus-MailScanner-From: rodrigo.sakai@zanthus.com.br X-Archive-Number: 200604/263 X-Sequence-Number: 18347 Thanks for all help!! But my problem is with performance, I agree with all of you, the RI must be maintained by the database, because a bunch of reasons that everyone knows! But, I'm dealing with a very huge database that servers more than 200 clientes at the same time, and because of it, each manipulation (delete, insert, update, select) on the database have a poor performance. So, if we deal with RI in each client station, we take this work off the database! The application is an ERP developed with DELPHI + (postgresql or oracle or sql server)!! Thanks again!! ----- Original Message ----- From: "Markus Schaber" To: Cc: "Rodrigo Sakai" Sent: Wednesday, April 12, 2006 10:18 AM Subject: Re: [PERFORM] FOREIGN KEYS vs PERFORMANCE > Hi, Michael, > Hi, Rodrigo, > > Michael Glaesemann wrote: > > > If I had to choose between one or the other, I'd leave all referential > > integrity in the database and deal with the errors thrown when > > referential integrity is violated in the application. PostgreSQL is > > designed to handle these kinds of issues. Anything you code in your > > application is more likely to contain bugs or miss corner cases that > > would allow referential integrity to be violated. PostgreSQL has been > > pounded on for years by a great many users and developers, making the > > likelihood of bugs still remaining much smaller. > > I strictly agree with Michael here. > > > Of course, you can add some referential integrity checks in your > > application code, but those should be in addition to your database- > > level checks. > > Agree. It does make sense to have reference checks in the UI or > application level for the sake of better error handling, but the > database should be the mandatory judge. > > There's another advantage of database based checking: Should there ever > be the need of a different application working on the same database (e. > G. an "expert level UI", or some connector that connects / synchronizes > to another software, or a data import tool), database based constraints > cannot be broken opposed to application based ones. > > HTH, > Markus > -- > Markus Schaber | Logical Tracking&Tracing International AG > Dipl. Inf. | Software Development GIS > > Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org > From mmoncure@gmail.com Wed Apr 12 15:03:31 2006 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (mx2.hub.org [200.46.204.254]) by postgresql.org (Postfix) with ESMTP id 25B6B11F663F for ; Wed, 12 Apr 2006 12:03:31 -0300 (ADT) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Score: -2.6 X-Spam-Level: X-Spam-Status: No, score=-2.6 required=5 tests=[BAYES_00=-2.599, SPF_PASS=-0.001] Received: from postgresql.org ([200.46.204.71]) by localhost (mx2.hub.org [200.46.204.254]) (amavisd-new, port 10024) with ESMTP id 0FD-9p4I00ov for ; Wed, 12 Apr 2006 12:03:27 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.233]) by postgresql.org (Postfix) with ESMTP id 4CE7B11F68DF for ; Wed, 12 Apr 2006 12:03:26 -0300 (ADT) Received: by wproxy.gmail.com with SMTP id 67so1180457wri for ; Wed, 12 Apr 2006 08:03:26 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=Qxq/zzjepkNTSXVOPXOkt2GEuNRR3hiZCW5X/4qVMogOK9BBMJTUlSrPyFVxTPPoWWNlsOIaNHE7J5siGTDur9oKpQifZYxX//xZPU0EDzuq6zQJgJxkUhFlZXlvF77OEwU6mnS4xlYadX/bPqih19Sxjw9RxAJIZMnM6D9iBXI= Received: by 10.65.250.8 with SMTP id c8mr1117753qbs; Wed, 12 Apr 2006 08:03:26 -0700 (PDT) Received: by 10.65.137.8 with HTTP; Wed, 12 Apr 2006 08:03:26 -0700 (PDT) Message-ID: Date: Wed, 12 Apr 2006 11:03:26 -0400 From: "Merlin Moncure" To: "Rodrigo Sakai" Subject: Re: FOREIGN KEYS vs PERFORMANCE Cc: pgsql-performance@postgresql.org In-Reply-To: <001301c65d9c$08884ef0$4700a8c0@TREEZANTHUS> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline References: <001301c65d9c$08884ef0$4700a8c0@TREEZANTHUS> X-Archive-Number: 200604/264 X-Sequence-Number: 18348 On 4/11/06, Rodrigo Sakai wrote: > > Hi, > > I think this is an old question, but I want to know if it really is wel= l > worth to not create some foreign keys an deal with the referential integr= ity > at application-level????? > Specifically, the system we are developing is a server/cliente > architecture that the server is the database and the fat client is an > application developed in DELPHI!!! > > Thanks in advance!! Delphi IMO is the best RAD win32 IDE ever invented (especially when paired the very excellent Zeos connection objects). However, for purposes of data management ,imperative languages, Delphi included, simply suck. Great form editor though. merlin From grzm@myrealbox.com Wed Apr 12 15:07:04 2006 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (mx2.hub.org [200.46.204.254]) by postgresql.org (Postfix) with ESMTP id AC20811F6653 for ; Wed, 12 Apr 2006 12:07:04 -0300 (ADT) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Score: -2.599 X-Spam-Level: X-Spam-Status: No, score=-2.599 required=5 tests=[BAYES_00=-2.599] Received: from postgresql.org ([200.46.204.71]) by localhost (mx2.hub.org [200.46.204.254]) (amavisd-new, port 10024) with ESMTP id pF5v6Q-40AzH for ; Wed, 12 Apr 2006 12:06:56 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from smtp-send.myrealbox.com (smtp-send.myrealbox.com [151.155.5.143]) by postgresql.org (Postfix) with ESMTP id DDDD311F662E for ; Wed, 12 Apr 2006 12:06:54 -0300 (ADT) Received: from [172.16.1.180] grzm [61.197.227.146] by smtp-send.myrealbox.com with NetMail SMTP Agent $Revision: 1.6 $ on Linux; Wed, 12 Apr 2006 09:06:39 -0600 In-Reply-To: <000701c65e40$5a1ff700$4700a8c0@TREEZANTHUS> References: <001301c65d9c$08884ef0$4700a8c0@TREEZANTHUS> <72C39159-1A4B-4595-A978-1DF57AD42527@myrealbox.com> <443CFE3F.5090304@logix-tt.com> <000701c65e40$5a1ff700$4700a8c0@TREEZANTHUS> Mime-Version: 1.0 (Apple Message framework v749.3) X-Priority: 3 Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed Message-Id: <919DB481-053D-4C06-852C-44F19F381EA8@myrealbox.com> Cc: "Markus Schaber" , Content-Transfer-Encoding: 7bit From: Michael Glaesemann Subject: Re: FOREIGN KEYS vs PERFORMANCE Date: Thu, 13 Apr 2006 00:06:27 +0900 To: "Rodrigo Sakai" X-Mailer: Apple Mail (2.749.3) X-Archive-Number: 200604/265 X-Sequence-Number: 18349 On Apr 12, 2006, at 23:49 , Rodrigo Sakai wrote: > Thanks for all help!! But my problem is with performance, I agree > with all > of you, the RI must be maintained by the database, because a bunch of > reasons that everyone knows! You've gotten a variety of good advice from a number of people. For more specific advice (e.g., for your particular situation), it would be very helpful if you could provide examples of queries that aren't performing well for you (including table schema and explain analyze output). Michael Glaesemann grzm myrealbox com From lists@peufeu.com Wed Apr 12 15:08:57 2006 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (mx2.hub.org [200.46.204.254]) by postgresql.org (Postfix) with ESMTP id A2B3811F696C for ; Wed, 12 Apr 2006 12:08:57 -0300 (ADT) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Score: -2.414 X-Spam-Level: X-Spam-Status: No, score=-2.414 required=5 tests=[AWL=0.050, BAYES_00=-2.599, FORGED_RCVD_HELO=0.135] Received: from postgresql.org ([200.46.204.71]) by localhost (mx2.hub.org [200.46.204.254]) (amavisd-new, port 10024) with ESMTP id 5A5fNm1QGbw2 for ; Wed, 12 Apr 2006 12:08:48 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from peufeu.com (boutiquenumerique.com [82.67.9.10]) by postgresql.org (Postfix) with ESMTP id 7267711F694F for ; Wed, 12 Apr 2006 12:08:46 -0300 (ADT) Received: (qmail 30442 invoked from network); 12 Apr 2006 17:09:41 +0200 Received: from boutiquenumerique.com (HELO apollo13) (82.67.9.10) by boutiquenumerique.com with SMTP; 12 Apr 2006 17:09:41 +0200 Date: Wed, 12 Apr 2006 17:09:37 +0200 To: "Rodrigo Sakai" , pgsql-performance@postgresql.org Subject: Re: FOREIGN KEYS vs PERFORMANCE References: <001301c65d9c$08884ef0$4700a8c0@TREEZANTHUS> <72C39159-1A4B-4595-A978-1DF57AD42527@myrealbox.com> <443CFE3F.5090304@logix-tt.com> <000701c65e40$5a1ff700$4700a8c0@TREEZANTHUS> From: PFC Content-Type: text/plain; format=flowed; delsp=yes; charset=utf-8 MIME-Version: 1.0 Content-Transfer-Encoding: 8bit Message-ID: In-Reply-To: <000701c65e40$5a1ff700$4700a8c0@TREEZANTHUS> User-Agent: Opera M2/8.51 (Linux, build 1462) X-Archive-Number: 200604/266 X-Sequence-Number: 18350 What kinds of operations are made slow by foreign key checks ? Is it : - Simple checks on INSERT ? - Simple checks on UPDATE ? - Cascaded deletes ? - Cascaded updates ? - Locks ? - Something else ? Foreign keys are to ensure that the value in a column is always part of a specific set (the referenced table). If this set changes very rarely, like the list of countries in the world, or the list of states in a country, or the various possible states an order can be in (received, processed, shipped...) then it's alright to skip the check if you're sure your application will insert a valid value. For some other situations, doing the check in the application will need some queries and could be slower (and certainly will be more complicated...) Are you sure you're not missing a few indexes, which would then force fkey checks to use sequential scans ? > Thanks for all help!! But my problem is with performance, I agree with > all > of you, the RI must be maintained by the database, because a bunch of > reasons that everyone knows! > But, I'm dealing with a very huge database that servers more than 200 > clientes at the same time, and because of it, each manipulation (delete, > insert, update, select) on the database have a poor performance. So, if > we > deal with RI in each client station, we take this work off the database! > The application is an ERP developed with DELPHI + (postgresql or > oracle or > sql server)!! From smarlowe@g2switchworks.com Wed Apr 12 15:13:51 2006 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id C353D11F6933 for ; Wed, 12 Apr 2006 12:13:51 -0300 (ADT) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Score: 0.119 X-Spam-Level: X-Spam-Status: No, score=0.119 required=5 tests=[AWL=0.118, UNPARSEABLE_RELAY=0.001] Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id ZNcBQTHvu1TG for ; Wed, 12 Apr 2006 12:13:48 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from exchange.g2switchworks.com (mail.g2switchworks.com [63.87.162.25]) by postgresql.org (Postfix) with ESMTP id 2A8B111F6931 for ; Wed, 12 Apr 2006 12:13:47 -0300 (ADT) Received: from 10.10.1.37 ([10.10.1.37]) by exchange.g2switchworks.com ([10.10.1.2]) with Microsoft Exchange Server HTTP-DAV ; Wed, 12 Apr 2006 15:13:45 +0000 Received: from state.g2switchworks.com by mail.g2switchworks.com; 12 Apr 2006 10:13:45 -0500 Subject: Re: FOREIGN KEYS vs PERFORMANCE From: Scott Marlowe To: Rodrigo Sakai Cc: Markus Schaber , pgsql-performance@postgresql.org In-Reply-To: <000701c65e40$5a1ff700$4700a8c0@TREEZANTHUS> References: <001301c65d9c$08884ef0$4700a8c0@TREEZANTHUS> <72C39159-1A4B-4595-A978-1DF57AD42527@myrealbox.com> <443CFE3F.5090304@logix-tt.com> <000701c65e40$5a1ff700$4700a8c0@TREEZANTHUS> Content-Type: text/plain Content-Transfer-Encoding: 7bit Message-Id: <1144854825.23538.10.camel@state.g2switchworks.com> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 (1.4.6-2) Date: Wed, 12 Apr 2006 10:13:45 -0500 X-Archive-Number: 200604/267 X-Sequence-Number: 18351 On Wed, 2006-04-12 at 09:49, Rodrigo Sakai wrote: > Thanks for all help!! But my problem is with performance, I agree with all > of you, the RI must be maintained by the database, because a bunch of > reasons that everyone knows! > But, I'm dealing with a very huge database that servers more than 200 > clientes at the same time, and because of it, each manipulation (delete, > insert, update, select) on the database have a poor performance. So, if we > deal with RI in each client station, we take this work off the database! > The application is an ERP developed with DELPHI + (postgresql or oracle or > sql server)!! These are separate issues. One is performance of PostgreSQL handling FK->PK relationships. PostgreSQL, in my experience, is quite fast at this. However, there are ways you can set up FK->PK relationships that are non-optimal and will result in poor performance. FK->PK relationships are generally fastest when they are 1-1 and based on integer types. If there's a type mismatch, or you use slower types, like large text fields, or numerics, you may have poor performance. Give us a sample of your schema where you're having problems, let us help you troubleshoot your performance. High parallel load is another issue. No matter where you put your FK->PK relationship handling, having 200+ users connected at the same time and manipulating your database is a heavy load. Handling FK->PK relationships in software often is vulnerable to race conditions. Like so: (T1 and T2 are different "threads) T1: select id from mastertable where id=99; -- check for row T2: delete from mastertable where id=99; -- delete a row T1: insert into slavetable values (....); -- whoops! No master If we change the T1 to select for update, we now have the overhead that most FK->PK relationships have. What version of PostgreSQL are you running. Older versions had much poorer performance than newer versions when updating FK->PK relationships. Don't assume that application level FK->PK relationships will be faster AND as good as the ones at database level. It's quite possible that they're faster for you because you're cutting corners, referentially speaking, and your data will wind up incoherent over time. Also, you may be dealing with a database that is IO bound, and moving the FK checks to software is only a short stop gap, and as the machine hits the IO performance ceiling, you'll have the same problem again, need a bigger machine, and have incoherent data. I.e. the same problem, plus a few more, and have spent a lot of time spinning your wheels going a short distance. From jnasby@pervasive.com Wed Apr 12 15:36:31 2006 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (mx2.hub.org [200.46.204.254]) by postgresql.org (Postfix) with ESMTP id D336911F676B for ; Wed, 12 Apr 2006 12:36:31 -0300 (ADT) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Score: -1.22 X-Spam-Level: X-Spam-Status: No, score=-1.22 required=5 tests=[AWL=1.379, BAYES_00=-2.599] Received: from postgresql.org ([200.46.204.71]) by localhost (mx2.hub.org [200.46.204.254]) (amavisd-new, port 10024) with ESMTP id FOrtFIfLFRhi for ; Wed, 12 Apr 2006 12:36:23 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from noel.decibel.org (noel.decibel.org [67.100.216.10]) by postgresql.org (Postfix) with ESMTP id 7DDA111F6676 for ; Wed, 12 Apr 2006 12:36:23 -0300 (ADT) Received: by noel.decibel.org (Postfix, from userid 1001) id 4696056435; Wed, 12 Apr 2006 10:36:22 -0500 (CDT) Received: (hashcash-sendmail, from uid 1001); Wed, 12 Apr 2006 10:36:20 -0500 Date: Wed, 12 Apr 2006 10:36:20 -0500 From: "Jim C. Nasby" To: PFC Cc: Michael Glaesemann , Rodrigo Sakai , pgsql-performance@postgresql.org Subject: Re: FOREIGN KEYS vs PERFORMANCE Message-ID: <20060412153619.GG49405@pervasive.com> References: <001301c65d9c$08884ef0$4700a8c0@TREEZANTHUS> <72C39159-1A4B-4595-A978-1DF57AD42527@myrealbox.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: X-Operating-System: FreeBSD 6.0-RELEASE-p4 amd64 X-Distributed: Join the Effort! http://www.distributed.net User-Agent: Mutt/1.5.11 X-Hashcash: 1:20:060412:lists@peufeu.com::UfzQdRcOfRR5UwCm:00lpX X-Hashcash: 1:20:060412:grzm@myrealbox.com::bKjnMnlhosp3v+ui:0000000000000000000 0000000000000000000000008iEJ X-Hashcash: 1:20:060412:rodrigo.sakai@zanthus.com.br::JYZ9iVIXU6dh0Y3O:000000000 0000000000000000000000000HDV X-Hashcash: 1:20:060412:pgsql-performance@postgresql.org::kDOK0DhruzYVvIn/:00000 0000000000000000000000008r37 X-Archive-Number: 200604/268 X-Sequence-Number: 18352 On Wed, Apr 12, 2006 at 09:22:52AM +0200, PFC wrote: > > >> I think this is an old question, but I want to know if it really is > >>well worth to not create some foreign keys an deal with the referential > >>integrity at application-level????? > > Trust me : do it in the application and you'll enter a world of > hurt. I'm doing it with some mysql apps, and it's a nightmare ; doing > cascaded delete's by hand, etc, you always forget something, you have to > modify a million places in your code everytime you add a new table, your > ORM bloats, you get to write cleanup cron scripts which take forever to > run, your website crashes etc. Well, yeah, thats typical for MySQL sites, but what's that have to do with RI? Sorry, couldn't resist. :P From jnasby@pervasive.com Wed Apr 12 15:40:00 2006 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 0290E11F690F for ; Wed, 12 Apr 2006 12:40:00 -0300 (ADT) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Score: 0.465 X-Spam-Level: X-Spam-Status: No, score=0.465 required=5 tests=[AWL=0.465] Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 5wXDi0H1ep1s for ; Wed, 12 Apr 2006 12:39:53 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from noel.decibel.org (noel.decibel.org [67.100.216.10]) by postgresql.org (Postfix) with ESMTP id C367211F676B for ; Wed, 12 Apr 2006 12:39:53 -0300 (ADT) Received: by noel.decibel.org (Postfix, from userid 1001) id 2AF9856423; Wed, 12 Apr 2006 10:39:53 -0500 (CDT) Received: (hashcash-sendmail, from uid 1001); Wed, 12 Apr 2006 10:39:51 -0500 Date: Wed, 12 Apr 2006 10:39:51 -0500 From: "Jim C. Nasby" To: "Craig A. James" Cc: PFC , Michael Glaesemann , Rodrigo Sakai , pgsql-performance@postgresql.org Subject: Re: FOREIGN KEYS vs PERFORMANCE Message-ID: <20060412153951.GH49405@pervasive.com> References: <001301c65d9c$08884ef0$4700a8c0@TREEZANTHUS> <72C39159-1A4B-4595-A978-1DF57AD42527@myrealbox.com> <443D127D.2070601@modgraph-usa.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <443D127D.2070601@modgraph-usa.com> X-Operating-System: FreeBSD 6.0-RELEASE-p4 amd64 X-Distributed: Join the Effort! http://www.distributed.net User-Agent: Mutt/1.5.11 X-Hashcash: 1:20:060412:cjames@modgraph-usa.com::FXe4og9mh9SGcaLc:00000000000000 0000000000000000000000003MOA X-Hashcash: 1:20:060412:lists@peufeu.com::Gk+smuipOYLGVDqH:00LSx X-Hashcash: 1:20:060412:grzm@myrealbox.com::7Fe0suc+6Gu0NpiD:0000000000000000000 0000000000000000000000009RN8 X-Hashcash: 1:20:060412:rodrigo.sakai@zanthus.com.br::H5ESVplF9GBZiJrq:000000000 0000000000000000000000000Zb5 X-Hashcash: 1:20:060412:pgsql-performance@postgresql.org::RvW9rDbEiB2ZlTQW:00000 0000000000000000000000001kIq X-Archive-Number: 200604/269 X-Sequence-Number: 18353 On Wed, Apr 12, 2006 at 07:45:17AM -0700, Craig A. James wrote: > All good advice, but... there are no absolutes in this world. > Application-enforced referential integrity makes sense if (and probably > ONLY if): > > 1. You have only one application that modifies the data. (Otherwise, you > have to duplicate the rules across many applications, leading to a > code-maintenance nightmare). You forgot something: 1a: You know that there will never, ever, ever, ever, be any other application that wants to talk to the database. I know tons of people that get burned because they go with something that's "good enough for now", and then regret that decision for years to come. > 2. If your application crashes and leaves a mess, it's not a catastrophe, > and you have a good way to clean it up. For example, a bank shouldn't do > this, but it might be OK for a computer-aided-design application, or the > backend of a news web site. > > 3. You have application-specific knowledge about when you can skip > referential integrity and thereby greatly improve performance. For > example, you may have batch operations where large numbers of rows are > temporarily inconsistent. > > If your application doesn't meet ALL of these criteria, you probably should > use the database for referential integrity. > > Craig > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq > -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 From cjames@modgraph-usa.com Wed Apr 12 17:45:39 2006 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (mx2.hub.org [200.46.204.254]) by postgresql.org (Postfix) with ESMTP id E36F011F6B2E for ; Wed, 12 Apr 2006 14:45:39 -0300 (ADT) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Score: -2.364 X-Spam-Level: X-Spam-Status: No, score=-2.364 required=5 tests=[AWL=0.100, BAYES_00=-2.599, FORGED_RCVD_HELO=0.135] Received: from postgresql.org ([200.46.204.71]) by localhost (mx2.hub.org [200.46.204.254]) (amavisd-new, port 10024) with ESMTP id x20Jh2zgikSP for ; Wed, 12 Apr 2006 14:45:36 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from moonunit2.moonview.localnet (wsip-68-15-5-150.sd.sd.cox.net [68.15.5.150]) by postgresql.org (Postfix) with ESMTP id 9492511F6B06 for ; Wed, 12 Apr 2006 14:45:35 -0300 (ADT) Received: from [192.168.0.3] (moonunit3.moonview.localnet [192.168.0.3]) by moonunit2.moonview.localnet (8.13.1/8.13.1) with ESMTP id k3CHjZO3028658; Wed, 12 Apr 2006 10:45:36 -0700 Message-ID: <443D3A9C.40106@modgraph-usa.com> Date: Wed, 12 Apr 2006 10:36:28 -0700 From: "Craig A. James" User-Agent: Mozilla Thunderbird 1.0.6 (X11/20050716) X-Accept-Language: en-us, en MIME-Version: 1.0 To: "Jim C. Nasby" CC: PFC , Michael Glaesemann , Rodrigo Sakai , pgsql-performance@postgresql.org Subject: Re: FOREIGN KEYS vs PERFORMANCE References: <001301c65d9c$08884ef0$4700a8c0@TREEZANTHUS> <72C39159-1A4B-4595-A978-1DF57AD42527@myrealbox.com> <443D127D.2070601@modgraph-usa.com> <20060412153951.GH49405@pervasive.com> In-Reply-To: <20060412153951.GH49405@pervasive.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Archive-Number: 200604/270 X-Sequence-Number: 18354 Jim C. Nasby wrote: >>1. You have only one application that modifies the data. (Otherwise, you >>have to duplicate the rules across many applications, leading to a >>code-maintenance nightmare). > > You forgot something: > > 1a: You know that there will never, ever, ever, ever, be any other > application that wants to talk to the database. > > I know tons of people that get burned because they go with something > that's "good enough for now", and then regret that decision for years to > come. No, I don't agree with this. Too many people waste time designing for "what if..." scenarios that never happen. You don't want to be dumb and design something that locks out a foreseeable and likely future need, but referential integrity doesn't meet this criterion. There's nothing to keep you from changing from app-managed to database-managed referential integrity if your needs change. Design for your current requirements. Let us be of good cheer, remembering that the misfortunes hardest to bear are those which never happen. - James Russell Lowell (1819-1891) Therefore do not be anxious about tomorrow, for tomorrow will be anxious for itself. Let the day's own trouble be sufficient for the day. - Matthew 6:34 Craig From jnasby@pervasive.com Wed Apr 12 21:41:44 2006 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (mx2.hub.org [200.46.204.254]) by postgresql.org (Postfix) with ESMTP id 4307511F6961 for ; Wed, 12 Apr 2006 18:41:44 -0300 (ADT) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Score: -1.979 X-Spam-Level: X-Spam-Status: No, score=-1.979 required=5 tests=[AWL=0.620, BAYES_00=-2.599] Received: from postgresql.org ([200.46.204.71]) by localhost (mx2.hub.org [200.46.204.254]) (amavisd-new, port 10024) with ESMTP id dg+VSxU9pd1v for ; Wed, 12 Apr 2006 18:41:38 -0300 (ADT) X-Greylist: delayed 00:08:17.643449 by SQLgrey- Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id 4A41411F6CA9 for ; Wed, 12 Apr 2006 18:41:07 -0300 (ADT) Received: from noel.decibel.org (noel.decibel.org [67.100.216.10]) by svr4.postgresql.org (Postfix) with ESMTP id 515EE5AFAE6 for ; Wed, 12 Apr 2006 20:59:47 +0000 (GMT) Received: by noel.decibel.org (Postfix, from userid 1001) id E803456423; Wed, 12 Apr 2006 15:59:15 -0500 (CDT) Received: (hashcash-sendmail, from uid 1001); Wed, 12 Apr 2006 15:59:14 -0500 Date: Wed, 12 Apr 2006 15:59:14 -0500 From: "Jim C. Nasby" To: "Craig A. James" Cc: PFC , Michael Glaesemann , Rodrigo Sakai , pgsql-performance@postgresql.org Subject: Re: FOREIGN KEYS vs PERFORMANCE Message-ID: <20060412205914.GL49405@pervasive.com> References: <001301c65d9c$08884ef0$4700a8c0@TREEZANTHUS> <72C39159-1A4B-4595-A978-1DF57AD42527@myrealbox.com> <443D127D.2070601@modgraph-usa.com> <20060412153951.GH49405@pervasive.com> <443D3A9C.40106@modgraph-usa.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <443D3A9C.40106@modgraph-usa.com> X-Operating-System: FreeBSD 6.0-RELEASE-p4 amd64 X-Distributed: Join the Effort! http://www.distributed.net User-Agent: Mutt/1.5.11 X-Hashcash: 1:20:060412:cjames@modgraph-usa.com::wHRpKp5zY8YN2b1P:00000000000000 0000000000000000000000003e73 X-Hashcash: 1:20:060412:lists@peufeu.com::uiCM1cEdXzNsqRh5:03kzH X-Hashcash: 1:20:060412:grzm@myrealbox.com::KAAmCwKHblTHzLAv:0000000000000000000 0000000000000000000000005WMj X-Hashcash: 1:20:060412:rodrigo.sakai@zanthus.com.br::iLKS8GsqHQiZ7148:000000000 00000000000000000000000009JJ X-Hashcash: 1:20:060412:pgsql-performance@postgresql.org::/o80PIzmCzh+E7/Z:00000 0000000000000000000000003YqF X-Archive-Number: 200604/273 X-Sequence-Number: 18357 On Wed, Apr 12, 2006 at 10:36:28AM -0700, Craig A. James wrote: > Jim C. Nasby wrote: > >>1. You have only one application that modifies the data. (Otherwise, you > >>have to duplicate the rules across many applications, leading to a > >>code-maintenance nightmare). > > > >You forgot something: > > > >1a: You know that there will never, ever, ever, ever, be any other > >application that wants to talk to the database. > > > >I know tons of people that get burned because they go with something > >that's "good enough for now", and then regret that decision for years to > >come. > > No, I don't agree with this. Too many people waste time designing for > "what if..." scenarios that never happen. You don't want to be dumb and > design something that locks out a foreseeable and likely future need, but > referential integrity doesn't meet this criterion. There's nothing to keep > you from changing from app-managed to database-managed referential > integrity if your needs change. In this case your argument makes no sense, because you will spend far more time re-creating RI capability inside an application than if you just use what the database offers natively. It's certainly true that you don't want to over-engineer for no reason, but many times choices are made to save a very small amount of time or hassle up-front, and those choices become extremely painful later. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 From cjames@modgraph-usa.com Thu Apr 13 01:37:31 2006 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 5EFA911F66AC for ; Wed, 12 Apr 2006 22:37:31 -0300 (ADT) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Score: 0 X-Spam-Level: X-Spam-Status: No, score=0 required=5 tests=[none] Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id Swj9UAapqTsU for ; Wed, 12 Apr 2006 22:37:26 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from moonunit2.moonview.localnet (wsip-68-15-5-150.sd.sd.cox.net [68.15.5.150]) by postgresql.org (Postfix) with ESMTP id D211D11F66B8 for ; Wed, 12 Apr 2006 22:37:28 -0300 (ADT) Received: from [192.168.0.3] (moonunit3.moonview.localnet [192.168.0.3]) by moonunit2.moonview.localnet (8.13.1/8.13.1) with ESMTP id k3D1fow0014629; Wed, 12 Apr 2006 18:41:50 -0700 Message-ID: <443DAA37.80902@modgraph-usa.com> Date: Wed, 12 Apr 2006 18:32:39 -0700 From: "Craig A. James" User-Agent: Mozilla Thunderbird 1.0.6 (X11/20050716) X-Accept-Language: en-us, en MIME-Version: 1.0 To: "Jim C. Nasby" , pgsql-performance@postgresql.org Subject: Re: FOREIGN KEYS vs PERFORMANCE References: <001301c65d9c$08884ef0$4700a8c0@TREEZANTHUS> <72C39159-1A4B-4595-A978-1DF57AD42527@myrealbox.com> <443D127D.2070601@modgraph-usa.com> <20060412153951.GH49405@pervasive.com> <443D3A9C.40106@modgraph-usa.com> <20060412205914.GL49405@pervasive.com> In-Reply-To: <20060412205914.GL49405@pervasive.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Archive-Number: 200604/286 X-Sequence-Number: 18370 Jim C. Nasby wrote: >>No, I don't agree with this. Too many people waste time designing for >>"what if..." scenarios that never happen. You don't want to be dumb and >>design something that locks out a foreseeable and likely future need, but >>referential integrity doesn't meet this criterion. There's nothing to keep >>you from changing from app-managed to database-managed referential >>integrity if your needs change. > > In this case your argument makes no sense, because you will spend far > more time re-creating RI capability inside an application than if you > just use what the database offers natively. But one of the specific conditions in my original response was, "You have application-specific knowledge about when you can skip referential integrity and thereby greatly improve performance." If you can't do that, I agree with you. Anyway, this discussion is probably going on too long, and I'm partly to blame. I think we all agree that in almost all situations, using the database to do referential integrity is the right choice, and that you should only violate this rule if you have a really, really good reason, and you've thought out the implications carefully, and you know you may have to pay a steep price later if your requirements change. Craig From rodrigo.sakai@zanthus.com.br Tue Apr 18 17:19:16 2006 X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (mx2.hub.org [200.46.204.254]) by postgresql.org (Postfix) with ESMTP id 250D911F6089 for ; Tue, 18 Apr 2006 14:19:16 -0300 (ADT) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Score: -2.499 X-Spam-Level: X-Spam-Status: No, score=-2.499 required=5 tests=[AWL=0.100, BAYES_00=-2.599] Received: from postgresql.org ([200.46.204.71]) by localhost (mx2.hub.org [200.46.204.254]) (amavisd-new, port 10024) with ESMTP id MFCMz84m7zGF for ; Tue, 18 Apr 2006 14:19:02 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from mail.zanthus.com.br (mail.zanthus.com.br [200.212.65.85]) by postgresql.org (Postfix) with ESMTP id 5F05311F674C for ; Tue, 18 Apr 2006 14:19:00 -0300 (ADT) Received: from cprsakai ([192.168.0.71]) by mail.zanthus.com.br (8.13.4/8.13.4/Debian-3) with SMTP id k3IHKVUc019083 for ; Tue, 18 Apr 2006 14:20:31 -0300 Message-ID: <002601c6630c$403c9f70$4700a8c0@TREEZANTHUS> Reply-To: "Rodrigo Sakai" From: "Rodrigo Sakai" To: References: <001301c65d9c$08884ef0$4700a8c0@TREEZANTHUS> <72C39159-1A4B-4595-A978-1DF57AD42527@myrealbox.com> <443D127D.2070601@modgraph-usa.com> <20060412153951.GH49405@pervasive.com> <443D3A9C.40106@modgraph-usa.com> <20060412205914.GL49405@pervasive.com> Subject: Re: FOREIGN KEYS vs PERFORMANCE Date: Tue, 18 Apr 2006 14:19:29 -0300 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1506 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1506 X-Zanthus-MailScanner: Found to be clean X-Zanthus-MailScanner-From: rodrigo.sakai@zanthus.com.br X-Archive-Number: 200604/389 X-Sequence-Number: 18473 Thanks for all responses! I agree with most of you, and say that the RI is best maintened by Database ! Performance must be improved in other ways (indexes, hardware, etc)! ----- Original Message ----- From: "Jim C. Nasby" To: "Craig A. James" Cc: "PFC" ; "Michael Glaesemann" ; "Rodrigo Sakai" ; Sent: Wednesday, April 12, 2006 5:59 PM Subject: Re: [PERFORM] FOREIGN KEYS vs PERFORMANCE > On Wed, Apr 12, 2006 at 10:36:28AM -0700, Craig A. James wrote: > > Jim C. Nasby wrote: > > >>1. You have only one application that modifies the data. (Otherwise, you > > >>have to duplicate the rules across many applications, leading to a > > >>code-maintenance nightmare). > > > > > >You forgot something: > > > > > >1a: You know that there will never, ever, ever, ever, be any other > > >application that wants to talk to the database. > > > > > >I know tons of people that get burned because they go with something > > >that's "good enough for now", and then regret that decision for years to > > >come. > > > > No, I don't agree with this. Too many people waste time designing for > > "what if..." scenarios that never happen. You don't want to be dumb and > > design something that locks out a foreseeable and likely future need, but > > referential integrity doesn't meet this criterion. There's nothing to keep > > you from changing from app-managed to database-managed referential > > integrity if your needs change. > > In this case your argument makes no sense, because you will spend far > more time re-creating RI capability inside an application than if you > just use what the database offers natively. > > It's certainly true that you don't want to over-engineer for no reason, > but many times choices are made to save a very small amount of time or > hassle up-front, and those choices become extremely painful later. > -- > Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com > Pervasive Software http://pervasive.com work: 512-231-6117 > vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly >