Opened 11 years ago
Closed 5 years ago
#21039 closed New feature (fixed)
Support Postgres "CREATE INDEX CONCURRENTLY" in migrations.
Reported by: | FunkyBob | Owned by: | Dan Tao |
---|---|---|---|
Component: | contrib.postgres | Version: | dev |
Severity: | Normal | Keywords: | |
Cc: | erik.van.zijst, at, gmail, Dan Tao | Triage Stage: | Ready for checkin |
Has patch: | yes | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description
When a migration creates a new index, it's sometimes preferable to use a concurrent index creation so it won't interfere with the operation of the DB:
http://www.postgresql.org/docs/9.2/static/sql-createindex.html#SQL-CREATEINDEX-CONCURRENTLY
It would be nice to have an option to enable this in migrations.
Change History (25)
comment:1 by , 11 years ago
Owner: | set to |
---|---|
Status: | new → assigned |
comment:2 by , 11 years ago
Triage Stage: | Unreviewed → Accepted |
---|
comment:3 by , 11 years ago
Owner: | removed |
---|---|
Status: | assigned → new |
comment:4 by , 9 years ago
Why don't we want to always add CONCURRENTLY statement ? It's a bit slower but allows us to execute zero downtime updates without dirty hacks like create "fake" migration which adds indexes and add them in management commands with "create index concurrently".
comment:5 by , 9 years ago
@BlindHunter, concurrent index creation has many documented caveats hence why it's not enabled by default.
I don't think we should expect newcomers to Django/PostgreSQL to make sure all their index have been correctly created even if their migration ran fine. That's the reason we're planing to make it an opt-in feature and not the default behavior, just like it's not the default in PostgreSQL itself.
In the meanwhile I suggest you create your own subclass of Alter(Index|Unique)
operations and override database_forward
to issue the desired SQL.
comment:6 by , 9 years ago
In the meanwhile I suggest you create your own subclass of Alter(Index|Unique) operations and override database_forward to issue the desired SQL.
CREATE INDEX CONCURRENTLY
doesn't run inside a transaction. Can Operation
subclasses execute SQL outside the current transaction?
comment:7 by , 9 years ago
Cc: | added |
---|
comment:8 by , 9 years ago
comment:9 by , 9 years ago
Resolution: | → duplicate |
---|---|
Status: | new → closed |
comment:10 by , 7 years ago
Resolution: | duplicate |
---|---|
Status: | closed → new |
comment:11 by , 6 years ago
Now that #25833 is resolved, it seems to me there are (at least) two possible ways to approach this:
- Add a new
CreateIndexConcurrently
operation in the django.contrib.postgres.operations module. - Add a new
concurrent
argument to the constructor for the baseAddIndex
operation, and conditionally support this argument via feature detection based on a new attribute (maybe something likesupports_concurrent_index_creation
) on the base schema editor class. Database backends that don't support concurrent index creation (i.e. everything but PostgreSQL at present) would presumably raiseNotSupportedError
or something along those lines.
The first option seems simpler to me, at least for now, unless there is compelling evidence that another database backend will support concurrent index creation in the near future.
comment:12 by , 6 years ago
The first option seems simpler to me, at least for now, unless there is compelling evidence that another database backend will support concurrent index creation in the near future.
I agree with your conclusions; there's little benefit to add a concurrent
option to CreateIndex
for now. If any supported database adds support for this option in the future it should be easy enough to deprecate contrib.postgres.CreateIndexConcurrently
.
comment:13 by , 6 years ago
Cc: | added |
---|---|
Has patch: | set |
I've opened a pull request to add the CreateIndexConcurrently
operation proposed in my previous comment:
comment:14 by , 6 years ago
Needs documentation: | set |
---|
Please add documentation. See the patch review checklist.
comment:15 by , 6 years ago
Owner: | set to |
---|---|
Status: | new → assigned |
Per the guidelines on claiming tickets, I'm assigning this to myself.
comment:16 by , 6 years ago
Needs documentation: | unset |
---|
I've added documentation, so I'm unsetting the Needs documentation flag. Of course I am happy to add more docs or update what I've written based on feedback.
comment:17 by , 6 years ago
Triage Stage: | Accepted → Ready for checkin |
---|
comment:18 by , 6 years ago
Patch needs improvement: | set |
---|---|
Triage Stage: | Ready for checkin → Accepted |
comment:20 by , 5 years ago
Patch needs improvement: | set |
---|
Since there are still things that need work, I'm marking this as WIP.
comment:21 by , 5 years ago
Patch needs improvement: | unset |
---|
comment:22 by , 5 years ago
Triage Stage: | Accepted → Ready for checkin |
---|
comment:23 by , 5 years ago
Patch needs improvement: | set |
---|---|
Triage Stage: | Ready for checkin → Accepted |
comment:24 by , 5 years ago
Component: | Migrations → contrib.postgres |
---|---|
Patch needs improvement: | unset |
Summary: | Support Postgres "CREATE INDEX CONCURRENTLY" in migrations → Support Postgres "CREATE INDEX CONCURRENTLY" in migrations. |
Triage Stage: | Accepted → Ready for checkin |
This won't make it into 1.7, as it's too big of a change.