pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
constraints and performance
4+ messages / 4 participants
[nested] [flat]

* constraints and performance
@ 2004-02-11 13:21 Jodi Kanter <jkanter@virginia.edu>
  2004-02-11 14:06 ` Re: constraints and performance Christopher Browne <cbbrowne@acm.org>
  2004-02-11 16:01 ` Re: constraints and performance scott.marlowe <scott.marlowe@ihs.com>
  0 siblings, 2 replies; 4+ messages in thread

From: Jodi Kanter @ 2004-02-11 13:21 UTC (permalink / raw)
  To: pgsql-admin

Do constraints effect performance significantly?
Thanks
Jodi
-- 

/_______________________________
//Jodi L Kanter
BioInformatics Database Administrator
University of Virginia
(434) 924-2846
jkanter@virginia.edu <mailto:jkanter@virginia.edu>/

 

/ /

/ /

^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: constraints and performance
  2004-02-11 13:21 constraints and performance Jodi Kanter <jkanter@virginia.edu>
@ 2004-02-11 14:06 ` Christopher Browne <cbbrowne@acm.org>
  2004-05-30 22:39   ` Re: constraints and performance postgres@jal.org
  1 sibling, 1 reply; 4+ messages in thread

From: Christopher Browne @ 2004-02-11 14:06 UTC (permalink / raw)
  To: pgsql-admin

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.
-- 
wm(X,Y):-write(X),write('@'),write(Y). wm('cbbrowne','ntlug.org').
http://cbbrowne.com/info/lsf.html
"The only constructive theory connecting neuroscience and psychology
will arise from the study of software." 
-- Alan Perlis
[To the endless aggravation of both disciplines.  Ed.]



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: constraints and performance
  2004-02-11 13:21 constraints and performance Jodi Kanter <jkanter@virginia.edu>
  2004-02-11 14:06 ` Re: constraints and performance Christopher Browne <cbbrowne@acm.org>
@ 2004-05-30 22:39   ` postgres@jal.org
  0 siblings, 0 replies; 4+ messages in thread

From: postgres@jal.org @ 2004-05-30 22:39 UTC (permalink / raw)
  To: Christopher Browne <cbbrowne@acm.org>; +Cc: pgsql-admin; jkanter@virginia.edu

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





^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: constraints and performance
  2004-02-11 13:21 constraints and performance Jodi Kanter <jkanter@virginia.edu>
@ 2004-02-11 16:01 ` scott.marlowe <scott.marlowe@ihs.com>
  1 sibling, 0 replies; 4+ messages in thread

From: scott.marlowe @ 2004-02-11 16:01 UTC (permalink / raw)
  To: Jodi Kanter <jkanter@virginia.edu>; +Cc: pgsql-admin

On Wed, 11 Feb 2004, Jodi Kanter wrote:

> Do constraints effect performance significantly?

That depends.

Foreign key constraints on int4 columns or int8 columns (matching of 
course) are usually quite fast.

FK constraints on non-int (numeric, text, etc...) tend to be slower.

check constraints can be a killer if they are complex or use functions, 
but simple check constraints are usually alright.

Do you have a specific use case you're looking at?




^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2004-05-30 22:39 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-02-11 13:21 constraints and performance Jodi Kanter <jkanter@virginia.edu>
2004-02-11 14:06 ` Christopher Browne <cbbrowne@acm.org>
2004-05-30 22:39   ` postgres@jal.org
2004-02-11 16:01 ` scott.marlowe <scott.marlowe@ihs.com>

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