Creating Postgres Indexes Concurrently in Django

A slow-query alert led to a one-line fix: db_index=True on Order.customer_ref. makemigrations produced an AlterField, the deploy ran it, and for the next four minutes every write to the orders table waited, because the migration issued a plain CREATE INDEX on a sixty-million-row table and PostgreSQL blocks writes for the entire build of a non-concurrent index. Django can build indexes online — django.contrib.postgres.operations.AddIndexConcurrently has existed since Django 3.0 — but makemigrations never generates it on its own. This guide shows how to turn index changes into concurrent builds, how to structure the non-atomic migration they require, and how to recover when a concurrent build fails. It is part of Django Migrations Without Downtime.

Symptom / Error Signatures

Two distinct problems bring people here. The first is the blocking build itself: during a migration, pg_stat_activity shows application sessions waiting with wait_event_type = 'Lock' behind a session running CREATE INDEX "orders_order_customer_ref_...", and write latency spikes for the length of the build. The second is what happens when you try to fix it naively:

django.db.utils.NotSupportedError: AddIndexConcurrently cannot be executed inside a transaction
-- or, with RunSQL:
django.db.utils.InternalError: CREATE INDEX CONCURRENTLY cannot run inside a transaction block

Both mean the migration is still atomic. A third symptom follows a failed concurrent build: a later migrate fails with relation "order_customer_ref_idx" already exists, or queries ignore the index because PostgreSQL left it INVALID.

Root Cause Analysis

Django represents indexes in two ways. db_index=True on a field produces an AlterField (or is included in AddField), and the schema editor emits a plain CREATE INDEX. Entries in Meta.indexes produce AddIndex, which also emits a plain CREATE INDEX. Neither knows about concurrency; the PostgreSQL-specific AddIndexConcurrently and RemoveIndexConcurrently operations must be used explicitly.

Concurrent builds cannot run inside a transaction because PostgreSQL commits internally between phases of the build. Django migrations are atomic by default on PostgreSQL, so the migration class must set atomic = False. Without a transaction, a failed build is not rolled back: PostgreSQL leaves an INVALID index that is maintained on writes but ignored by the planner, and Django does not record the migration as applied. Rerunning then fails because the index name is taken. That failure model is the general one described in running concurrent index builds outside migration transactions.

Immediate Mitigation

If a blocking index build is running now, cancelling it restores writes immediately; the transaction rolls back and nothing is left behind.

1. Cancel the blocking build.

-- PostgreSQL · requires pg_signal_backend · safe: a non-concurrent CREATE INDEX rolls back fully
SELECT pid, now() - query_start AS running_for, left(query, 80)
FROM pg_stat_activity WHERE query ILIKE 'CREATE INDEX%' AND state = 'active';
SELECT pg_cancel_backend(<pid>);

2. Rewrite the migration as a concurrent build. Keep it alone in a non-atomic migration:

# Python · orders/migrations/0046_order_customer_ref_idx.py · PostgreSQL only
# WARNING: atomic = False — a failure is not rolled back; keep only this operation in the file.
from django.contrib.postgres.operations import AddIndexConcurrently
from django.db import migrations, models

class Migration(migrations.Migration):
    atomic = False
    dependencies = [("orders", "0045_previous")]
    operations = [
        AddIndexConcurrently(
            "order",
            models.Index(fields=["customer_ref"], name="order_customer_ref_idx"),
        ),
    ]
# ROLLBACK PATH: migrating back to 0045 runs RemoveIndexConcurrently for the same index.

Update the model to match — indexes = [models.Index(fields=["customer_ref"], name="order_customer_ref_idx")] in Meta, with db_index removed from the field — and run makemigrations --check to confirm Django sees no further changes.

3. If a previous concurrent attempt failed, drop the invalid leftover first.

-- PostgreSQL · migration role · must run outside a transaction
-- WARNING: confirm the index is INVALID before dropping it.
SELECT c.relname, i.indisvalid FROM pg_index i JOIN pg_class c ON c.oid = i.indexrelid
WHERE c.relname = 'order_customer_ref_idx';
DROP INDEX CONCURRENTLY IF EXISTS order_customer_ref_idx;

Permanent Fix / Long-Term Pattern

Adopt three conventions. Declare indexes in Meta.indexes with explicit names rather than db_index=True, so each index is a named object you can create and drop concurrently. Convert every generated AddIndex and RemoveIndex on PostgreSQL to its concurrent counterpart in an atomic = False migration containing only that operation. And enforce both with a CI check that fails on any AddIndex, RemoveIndex or db_index change — a small script over the migration files, or a linter as described in Migration Linting & Static Analysis.

The migration connection still needs a lock_timeout: the concurrent build takes brief locks at its start and end, and it waits for older transactions at several points, as explained in building indexes with CREATE INDEX CONCURRENTLY. Keep statement_timeout high enough for the whole build, since cancelling it partway leaves an invalid index. After deploy, check for invalid indexes as part of the pipeline.

# Python · scripts/check_index_ops.py · CI step over changed migration files
# WARNING: heuristic check; review any exception manually.
import pathlib, re, sys
bad = []
for path in sys.argv[1:]:
    text = pathlib.Path(path).read_text()
    if re.search(r"migrations\.(AddIndex|RemoveIndex)\(", text) or "db_index=True" in text:
        bad.append(path)
if bad:
    print("Use AddIndexConcurrently/RemoveIndexConcurrently in atomic=False migrations:", *bad, sep="\n  ")
    sys.exit(1)

Two special cases deserve a note. Unique indexes built concurrently can fail at the very end if duplicates exist, leaving an invalid unique index that still enforces uniqueness on new writes — so check for duplicates with a GROUP BY ... HAVING count(*) > 1 query before starting, and if you intend to back a unique constraint with the index, attach it afterwards as described in adding unique constraints using an existing index. Partial and expression indexes work with AddIndexConcurrently too — pass condition= or expressions to models.Index — and are often the better fix for a slow query on a large table, because a smaller index builds faster and costs less on every write.

Finally, schedule large builds deliberately. A concurrent build on a table of hundreds of gigabytes can run for an hour and generates a burst of WAL that replicas must replay; start it outside peak traffic, watch replication lag while it runs, and keep the deploy pipeline from timing out on it by giving the migrate job an appropriately long timeout or by running the index migration as its own pipeline step.

Verification Checklist

• sqlmigrate for the migration shows CREATE INDEX CONCURRENTLY and no BEGIN.
• The migration class sets atomic = False and contains only the concurrent index operation.
• makemigrations --check reports no changes after the model’s Meta.indexes is updated.
• After the deploy, SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid returns no rows.
• CI rejects new migrations containing AddIndex, RemoveIndex or db_index=True on PostgreSQL apps.

Frequently Asked Questions

Why doesn’t makemigrations generate AddIndexConcurrently? Because it is PostgreSQL-specific and requires a non-atomic migration, which changes failure behaviour. Django generates the portable, transactional operation and leaves the choice of concurrent building to you.

Can I put two AddIndexConcurrently operations in one migration? Yes, but a failure on the second leaves the first built and the second invalid, and Django will not record the migration as applied. One index per migration keeps recovery simple.

Does AddIndexConcurrently work on MySQL? No; it is in django.contrib.postgres. On MySQL, InnoDB builds secondary indexes online with ALGORITHM=INPLACE, LOCK=NONE by default for most index types, so a standard AddIndex usually does not block writes — but check the generated SQL and table size, and consider an online schema change tool for very large tables.

How do I remove an index without blocking? Use RemoveIndexConcurrently in an atomic = False migration. A plain RemoveIndex issues DROP INDEX, which takes ACCESS EXCLUSIVE briefly and can queue behind long transactions like any exclusive lock.