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:ring...@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 <ring...@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 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
