Re: DEFERRABLE NOT NULL constraint

On 7 February 2013 08:50, Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com> wrote:
> That's actually a sensible default, because there are consequences to
> making a constraint deferrable --- it can hurt performance if a large
> number of rows need to be queued up for later checking...

Just to clarify --- PostgreSQL goes to some effort to avoid queuing up
re-checks of deferred constraints if they are unnecessary. So, for
example, in the case of primary key/unique constraints, the
performance in the deferrable and non-deferrable cases are about the
same provided that none of the inserted/updated rows violate the
uniqueness check at insert/update time. The real performance hit comes
in if the constraint is deferrable, and a large number of new rows
violate the constraint temporarily, and so need to be re-checked
later.

Regards,
Dean