Re: FOREIGN KEYS vs PERFORMANCE

  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" <schabi@logix-tt.com>
To: <pgsql-performance@postgresql.org>
Cc: "Rodrigo Sakai" <rodrigo.sakai@zanthus.com.br>
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
>