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

On 09/14/2012 11:56 PM, McKinzie, Alan (Alan) wrote:

> My underlying question/concern is "will this change have any adverse
> affects (on performance) during normal operations when the foreign keys
> are set to deferrable initially immediate" .vs. the foreign keys being
> defined as NOT DEFERRABLE.

AFAIK in PostgreSQL DEFERRABLE INITIALLY IMMEDIATE is different to NOT
DEFERRABLE.

DEFERRABLE INITIALLY IMMEDIATE is executed at the end of the statement,
while NOT DEFERRABLE is executed as soon as it arises.

http://www.postgresql.org/docs/current/static/sql-set-constraints.html

http://stackoverflow.com/questions/10032272/constraint-defined-deferrable-initially-immediate-is-still-deferred

Again from memory there's a performance cost to deferring constraint
checks to the end of the statement rather than doing them as soon as
they arise, so NOT DEFERRED can potentially perform better or at least
not hit limits that DEFERRABLE INITIALLY DEFERRED might hit in Pg.

This seems under-documented and I haven't found much good info on it, so
the best thing to do is test it.

--
Craig Ringer

This seems under-documented and I haven't found much good info on it, so the best thing to do is test it.

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.


--
Craig Ringer

Craig Ringer <ringerc@ringerc.id.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.

            regards, tom lane

On 09/16/2012 11:37 PM, Tom Lane wrote:
> Craig Ringer <ringerc@ringerc.id.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

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

Thanks again,
Alan

-----Original Message-----
From: Craig Ringer [mailto:ringerc@ringerc.id.au]
Sent: Sunday, September 16, 2012 9:59 PM
To: Tom Lane
Cc: McKinzie, Alan (Alan); pgsql-performance@postgresql.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@ringerc.id.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