pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedDeferred constraints performance impact ?
3+ messages / 3 participants
[nested] [flat]
* Deferred constraints performance impact ?
@ 2012-07-20 02:27 mark <dvlhntr@gmail.com>
2012-08-12 22:42 ` Re: Deferred constraints performance impact ? Jeff Davis <pgsql@j-davis.com>
2012-08-13 08:33 ` Re: Deferred constraints performance impact ? Robert Klemme <shortcutter@googlemail.com>
0 siblings, 2 replies; 3+ messages in thread
From: mark @ 2012-07-20 02:27 UTC (permalink / raw)
To: pgsql-performance
Hi all,
We have put some deferred constraints (some initially immediate, some
initially deferred) into our database for testing with our applications.
I haven't seen any noticeable loss in performance, but I am not sure I can
properly simulate our full production environment load levels in my tests.
I was wondering if I am turning on something that has known significant
negative impacts to performance, just from having them there.
I understand a lot more may have to be tracked through a transaction and
there could be some impact from that. Similar to an after update trigger? Or
are the two not comparable in terms of impact from what is tracked and then
checked.
Anyways, just looking for feedback if anyone has any.
-Mark
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Deferred constraints performance impact ?
2012-07-20 02:27 Deferred constraints performance impact ? mark <dvlhntr@gmail.com>
@ 2012-08-12 22:42 ` Jeff Davis <pgsql@j-davis.com>
1 sibling, 0 replies; 3+ messages in thread
From: Jeff Davis @ 2012-08-12 22:42 UTC (permalink / raw)
To: mark <dvlhntr@gmail.com>; +Cc: pgsql-performance
On Thu, 2012-07-19 at 20:27 -0600, mark wrote:
> I understand a lot more may have to be tracked through a transaction and
> there could be some impact from that. Similar to an after update trigger? Or
> are the two not comparable in terms of impact from what is tracked and then
> checked.
They should be very comparable to AFTER triggers. It's actually a little
better because there are optimizations to avoid queuing constraint
checks if we know it will pass.
I would recommend testing a few degenerate cases to see how big the
impact is, and try to see if that is reasonable for your application.
Regards,
Jeff Davis
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Deferred constraints performance impact ?
2012-07-20 02:27 Deferred constraints performance impact ? mark <dvlhntr@gmail.com>
@ 2012-08-13 08:33 ` Robert Klemme <shortcutter@googlemail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Robert Klemme @ 2012-08-13 08:33 UTC (permalink / raw)
To: mark <dvlhntr@gmail.com>; pgsql-performance
On Fri, Jul 20, 2012 at 4:27 AM, mark <dvlhntr@gmail.com> wrote:
> We have put some deferred constraints (some initially immediate, some
> initially deferred) into our database for testing with our applications.
> I understand a lot more may have to be tracked through a transaction and
> there could be some impact from that. Similar to an after update trigger? Or
> are the two not comparable in terms of impact from what is tracked and then
> checked.
Another factor might be the amount of constraint violations you
expect: if there are many then deferring the check can create much
more work for the DB because you issue more DML as with a non deferred
constraint which could create errors much sooner and hence make you
stop sending DML earlier.
Kind regards
robert
--
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2012-08-13 08:33 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-07-20 02:27 Deferred constraints performance impact ? mark <dvlhntr@gmail.com>
2012-08-12 22:42 ` Jeff Davis <pgsql@j-davis.com>
2012-08-13 08:33 ` Robert Klemme <shortcutter@googlemail.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