Re: where to divide application and database

Ivan Sergio Borgonovo wrote:
> On Fri, 20 Feb 2009 06:50:22 -0800
> David Fetter <david@fetter.org> wrote:
>>> ... moving some of the checks
>>> into the database and away from the application.
>> Since a useful database has *many* applications instead of "the"
>> application, I think this is an excellent move.
>
> ....I was wondering if "checks" may have an impact
> on performances and if pg does some optimisation over them.

Are you suggesting thee would be a positive or negative impact
on performance.

Moving some checks in the database should *improve* performance
by giving the planner improved information.    For example, unique
constraints indicate when only 0-1 rows may come out of a query;
and range constraints could let a database know when a partition
doesn't even need to be visited.
No doubt other checks (say, spellchecking a column) would have
have performance costs.


I'm with David Fetter's perspective of considering multiple
applications that can run on top of a database.

If a particular check should apply to all conceivable applications
that run on a database (say, foreign key constraints) it seems
to me they belong in a database.

If a particular check should apply to just one application, but
other applications might have reasons not to enforce such a check
(say, one app might do spell checking in english; another in a
different language) - that belongs in the app.