Re: Are there known performance issues with defining all Foreign Keys as deferrable initially immediate

Thanks for the information guys. And Yes, I am only updating the Foreign Key definitions to be deferrable. I am not modifying the Unique/Primary Key definitions.

Thanks again,
Alan

-----Original Message-----
From: Craig Ringer [mailto:ringerc(at)ringerc(dot)id(dot)au]
Sent: Sunday, September 16, 2012 9:59 PM
To: Tom Lane
Cc: McKinzie, Alan (Alan); pgsql-performance(at)postgresql(dot)org
Subject: Re: [PERFORM] Are there known performance issues with defining all Foreign Keys as deferrable initially immediate

On 09/16/2012 11:37 PM, Tom Lane wrote:
> Craig Ringer <ringerc(at)ringerc(dot)id(dot)au> writes:
>> Found it, it's in the NOTES for CREATE TABLE.
>> http://www.postgresql.org/docs/current/static/sql-createtable.html:
>
>> When a UNIQUE or PRIMARY KEY constraint is not deferrable, PostgreSQL
>> checks for uniqueness immediately whenever a row is inserted or
>> modified. The SQL standard says that uniqueness should be enforced only
>> at the end of the statement; this makes a difference when, for example,
>> a single command updates multiple key values. To obtain
>> standard-compliant behavior, declare the constraint as DEFERRABLE but
>> not deferred (i.e., INITIALLY IMMEDIATE). Be aware that this can be
>> significantly slower than immediate uniqueness checking.
>
> Note that that is addressing uniqueness constraints, and *only*
> uniqueness constraints. Foreign key constraints are implemented
> differently. There is no equivalent to an immediate check of a foreign
> key constraint --- it's checked either at end of statement or end of
> transaction, depending on the DEFERRED property. So there's really no
> performance difference for FKs, unless you let a large number of pending
> checks accumulate over multiple commands within a transaction.

Ah, thanks. I missed that detail.

--
Craig Ringer