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 jcatalan, 11 years ago

Owner: set to jcatalan
Status: newassigned

comment:2 by Tim Graham, 11 years ago

Triage Stage: UnreviewedAccepted

comment:3 by Andrew Godwin, 10 years ago

Owner: jcatalan removed
Status: assignednew

This won't make it into 1.7, as it's too big of a change.

comment:4 by Alexander, 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 Simon Charette, 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 Erik van Zijst, 8 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 Erik van Zijst, 8 years ago

Cc: erik.van.zijst at gmail added

comment:8 by Shai Berger, 8 years ago

#25833 is more general, but it would allow this to be written easily as user code. I'd even consider closing this ticket as a duplicate of #25833.

comment:9 by Aymeric Augustin, 8 years ago

Resolution: duplicate
Status: newclosed

comment:10 by Tim Graham, 7 years ago

Resolution: duplicate
Status: closednew

With #25833 resolved, it's not clear to me how this is a duplicate of that issue. #28448 is a new ticket requesting support for concurrently indexes. I'm reopening this ticket and closing that new ticket as a duplicate of this one.

comment:11 by Dan Tao, 5 years ago

Now that #25833 is resolved, it seems to me there are (at least) two possible ways to approach this:

  1. Add a new CreateIndexConcurrently operation in the django.contrib.postgres.operations module.
  2. Add a new concurrent argument to the constructor for the base AddIndex operation, and conditionally support this argument via feature detection based on a new attribute (maybe something like supports_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 raise NotSupportedError 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 Simon Charette, 5 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 Dan Tao, 5 years ago

Cc: Dan Tao added
Has patch: set

I've opened a pull request to add the CreateIndexConcurrently operation proposed in my previous comment:

https://github.com/django/django/pull/10901

comment:14 by Tim Graham, 5 years ago

Needs documentation: set

Please add documentation. See the patch review checklist.

comment:15 by Dan Tao, 5 years ago

Owner: set to Dan Tao
Status: newassigned

Per the guidelines on claiming tickets, I'm assigning this to myself.

comment:16 by Dan Tao, 5 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 Asif Saifuddin Auvi, 5 years ago

Triage Stage: AcceptedReady for checkin

comment:18 by Tim Graham, 5 years ago

Patch needs improvement: set
Triage Stage: Ready for checkinAccepted

comment:19 by Mariusz Felisiak, 5 years ago

Patch needs improvement: unset

comment:20 by Mads Jensen, 5 years ago

Patch needs improvement: set

Since there are still things that need work, I'm marking this as WIP.

comment:21 by Mads Jensen, 5 years ago

Patch needs improvement: unset

comment:22 by Simon Charette, 5 years ago

Triage Stage: AcceptedReady for checkin

comment:23 by Mariusz Felisiak, 5 years ago

Patch needs improvement: set
Triage Stage: Ready for checkinAccepted

comment:24 by Mariusz Felisiak, 5 years ago

Component: Migrationscontrib.postgres
Patch needs improvement: unset
Summary: Support Postgres "CREATE INDEX CONCURRENTLY" in migrationsSupport Postgres "CREATE INDEX CONCURRENTLY" in migrations.
Triage Stage: AcceptedReady for checkin

comment:25 by Mariusz Felisiak <felisiak.mariusz@…>, 5 years ago

Resolution: fixed
Status: assignedclosed

In 85ac838d:

Fixed #21039 -- Added AddIndexConcurrently/RemoveIndexConcurrently operations for PostgreSQL.

Thanks to Simon Charettes for review.

Co-Authored-By: Daniel Tao <daniel.tao@…>

Note: See TracTickets for help on using tickets.
Back to Top