pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: postgres@jal.org
To: Christopher Browne <cbbrowne@acm.org>
Cc: pgsql-admin@postgresql.org
Cc: jkanter@virginia.edu
Subject: Re: constraints and performance
Date: Sun, 30 May 2004 18:39:31 -0400
Message-ID: <20040530223931.GF30015@clueinc.net> (raw)
In-Reply-To: <m3smhh7ls9.fsf@wolfe.cbbrowne.com>
References: <402A2C3D.7010009@virginia.edu>
	<m3smhh7ls9.fsf@wolfe.cbbrowne.com>

On Wed, 11 Feb 2004, Christopher Browne wrote:

> A long time ago, in a galaxy far, far away, jkanter@virginia.edu (Jodi Kanter) wrote:
> > Do constraints effect performance significantly?
> 
> They would be expected to provide a significant enhancement to
> performance over:
>  a) Firing triggers,
>  b) Firing rules, and
>  c) Forcing the application to validate the data, and then adding
>     in "data validation" reports to report on the cases where a buggy 
>     application violated the constraints.
> 
> So yes, they should be considerably faster than any of their
> alternatives.

A completely correct answer, but not the one I suspect Jodi wanted,
which was whether there was a "significant" penalty difference between
using constraints on a table and not using constraints on a table.

I'm not sure I have any better answer, because we don't know what
"significant" means, or the nature of the constraints.

I will share my experience, which is that constraints add little
noticable overhead in simple cases. However, when constraining cascading
deletes through many tables, for instance, it is absolutely noticable. 
Between those two extremes, it isn't "too bad", for me and my 
applications, wherein I rely heavily on constraints (and rules, and
server-side triggers).

I don't think it is possible to say "constraints add an n% overhead", due
to the extreme variability of the way they can be used.

Best practice, as Christopher notes, indicates that they should be used. 
It saves a lot of grief (why write the code in the application layer
when you can the DB authors already have?). If you are in a
situation where the difference between using them and not using them
forms a critical boundary, I would suggest you have some other problems,
either in design or specification.

I realize that may not be helpful, given real world constraints - the
consumers of applications may not be realistic in setting requirements.

One thing to think about carefully, if building an application that has
to scale to any real degree, is the tradeoff between client side and
server side processing. While it is best-practice to keep data
validation close to the data, I have been involved in some projects
where scaling the DB server to the task was not economically possible;
the project would not have happened if that were an enforced criteria.
Messy, bad, poor practice? Yes on all counts. One must be very, very
careful if one chooses to ensure data integrity client-side.

Anyway, getting back to the question, the only real answer is "try it
and see". As far as I know, there's no way to quantify the impact of 
constraints on query performance without taking the data model and 
usage patterns of the application into account.

I hope this helps some.

-j


-- 
Jamie Lawrence                                        jal@jal.org
There is nothing more demoralizing than a small but adequate income.
   - Edmund Wilson





view thread (4+ messages)

Message-ID: <20040530223931.GF30015@clueinc.net>
Permalink:  ../20040530223931.GF30015@clueinc.net/
Also on:    postgresql.org/message-id/20040530223931.GF30015@clueinc.net

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: postgres@jal.org, cbbrowne@acm.org, jkanter@virginia.edu
  Subject: Re: constraints and performance
  In-Reply-To: <20040530223931.GF30015@clueinc.net>
  References: <402A2C3D.7010009@virginia.edu> <m3smhh7ls9.fsf@wolfe.cbbrowne.com> <20040530223931.GF30015@clueinc.net>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox