Include FK constraints when adding columns in PostgreSQL

[Dan Tao]

On the Bitbucket team, we have been stung my Django's default behavior, when adding a nullable ForeignKey field to a large, high-traffic table, of doing so in two separate statements:

ALTER TABLE [...] ADD COLUMN [...]
ALTER TABLE [...] ADD CONSTRAINT [...] FOREIGN KEY [...] REFERENCES [...]

The reason this is problematic is that, again for a very large table with many millions of rows and significant contention, the statement to add the constraint will acquire a lock and perform a full table scan to validate that there are no invalid foreign keys in the table.

In PostgreSQL, if you execute ADD COLUMN with a REFERENCES clause, it can bypass the table scan since there cannot possibly be any invalid foreign keys in the table.

We have written a custom operation to do just that whenever we encounter this scenario. Without it, we simply would not be able to add columns with foreign key constraints to some of our larger tables, other than by executing the SQL manually and then faking the migration. It would be nice if Django could take the more optimal approach by default, at least for the PostgreSQL database backend. (I'm not familiar enough with the other database backends to know whether this is also a problem for them.)