Re: where to divide application and database

On Sat, 21 Feb 2009 15:02:55 -0800
Ron Mayer <rm_pg(at)cheapcomplexdevices(dot)com> wrote:

> Ivan Sergio Borgonovo wrote:

> > ....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 was wondering where and if they could have a performance impact
(positive or negative).
We're talking about PostgreSQL, not an abstract DB or another
implementation. Would you delegate constraint check to *any other
DB*?

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

Me too. Postgresql is a mature application; the chances the most
frequent useful optimisation are not already there are smaller than
the mistakes I could make putting optimisations and constraints
check in my application. Still it is better to know than guess. That
could help in engineering the constraints differently or well to
exploit better their "performance boost".

--
Ivan Sergio Borgonovo
http://www.webthatworks.it