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

From: "McKinzie, Alan (Alan)" <alanmck(at)avaya(dot)com>
To: "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: Are there known performance issues with defining all Foreign Keys as deferrable initially immediate
Date: 2012-09-14 15:56:04
Message-ID: E151F470685C15418AEA609E825A171022EC8EA823@DC-US1MBEX4.global.avaya.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

I am looking at changing all of the foreign key definitions to be deferrable (initially immediate). Then during a few scenarios performed by the application, set all foreign key constraints to be deferred (initially deferred) for that given transaction.

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.

I have read that there can be a difference in behavior/performance when a Primary Key/Unique Key is changed to deferred, due to assumptions the optimizer can or cannot make regarding whether the associated index is unique. But I have not found any negatives in regard to changing foreign key definitions to be deferrable.

Thanks,
Alan

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Sébastien Lorion 2012-09-14 20:11:05 Setting autovacuum_vacuum_scale_factor to 0 a good idea ?
Previous Message Nikolay Ulyanitsky 2012-09-14 15:35:20 Re: 20% performance drop on PostgreSQL 9.2 from kernel 3.5.3 to 3.6-rc5 on AMD chipsets