Message-ID: <443D127D.2070601@modgraph-usa.com>
Date: Wed, 12 Apr 2006 07:45:17 -0700
From: "Craig A. James" <cjames@modgraph-usa.com>
User-Agent: Mozilla Thunderbird 1.0.6 (X11/20050716)
MIME-Version: 1.0
To: PFC <lists@peufeu.com>
CC: Michael Glaesemann <grzm@myrealbox.com>,
	Rodrigo Sakai <rodrigo.sakai@zanthus.com.br>,
	pgsql-performance@postgresql.org
Subject: Re: FOREIGN KEYS  vs  PERFORMANCE
References: <001301c65d9c$08884ef0$4700a8c0@TREEZANTHUS>
	<72C39159-1A4B-4595-A978-1DF57AD42527@myrealbox.com>
	<op.s7vv4ex2cigqcu@apollo13>
In-Reply-To: <op.s7vv4ex2cigqcu@apollo13>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

>>>   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