On 8th of February, Simon Riggs committed patch:

Extend ALTER TABLE TO allow FOREIGN KEYS TO be added WITHOUT initial validation.
FK constraints that are marked NOT VALID may later be VALIDATED, which uses an
ShareUpdateExclusiveLock ON CONSTRAINT TABLE AND RowShareLock ON referenced
TABLE. Significantly reduces LOCK strength AND duration WHEN adding FKs.
NEW state visible FROM psql.
 
Simon Riggs, WITH reviews FROM Marko Tiikkaja AND Robert Haas

Adding foreign key can be problematic operation – it's locking the table (because it's ALTER TABLE), and it takes long time.

While, adding new foreign key is not usual operation, it is being done (for example) by dump/restore process.

So, if it could be done better/faster – it would mean (at the very least) faster restoration of dumps.

So, let's see how it works.

To test this new way, I will create 2 tables:

• source – just id column, int4 , primary key, 100k rows
• destination – serial based primary key column “id", and source_id column, which will reference source.id column. All not null, 10 million random values (but no rows that would fail foreign key constraint)

I tested it using 4 approaches:

• create tables, load data, add foreign key
• create tables, add foreign key, load data
• create tables, load data, add foreign key as invalid, validate fkey
• create tables, add foreign key as invalid, load data, validate fkey

Data was loaded using “COPY", and each test was repeated 5 times to get some averages.

Results:

• Test #1: average time: 1m 13s
• Test #2: average time: 3m 50s
• Test #3: average time: 1m 12s
• Test #4: average time: 3m 55s

So, as we can see speed difference is negligible. But. There is also difference in locking levels.

The difference in lock levels means that now you actually make two foreign keys at the same time. And this can yield pretty good time difference.