#30108: Include FK constraints when adding columns in PostgreSQL
------------------------------------------------+------------------------
Reporter: Dan Tao | Owner: nobody
Type: Cleanup/optimization | Status: new
Component: Migrations | Version: master
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
------------------------------------------------+------------------------
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.)
--
Ticket URL: <https://code.djangoproject.com/ticket/30108>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/047.248b80673ff203564874bf73587e4ad8%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
