PostgreSQL 10 High Performance - Third Edition

High foreign key overhead

When you commit a transaction, if there's a foreign key involved, that commit is fairly expensive. As a percentage of total processing time, this is particularly bad if you're doing updates one at a time. To help improve this situation for batch updates, you can mark foreign key constraints as DEFERRABLE, either during CREATE TABLE or in a later ALTER TABLE to adjust it. The default is NOT DEFERRABLE. Note that this only impacts foreign keys and similar constraints implemented as triggers. It doesn't do anything for CHECK and UNIQUE restrictions, which cannot be deferred and are instead always processed immediately, at least before PostgreSQL 9.0, that is. See the performance notes for 10.0 at the end of this chapter ...