#30108: Include FK constraints when adding columns in PostgreSQL
-------------------------------------+-------------------------------------
Reporter: Dan Tao | Owner: nobody
Type: | Status: new
Cleanup/optimization |
Component: Migrations | Version: master
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Dan Tao):
* cc: Dan Tao (added)
* has_patch: 0 => 1
Old description:
> 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.)
New description:
On the Bitbucket team, we have been stung by 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#comment:2>
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 django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/062.2a7c9caeb12fa1541896c951270628ec%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
