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