Add Database Indexes in Django Without Locking Tables

Problem statement

A slow query needs an index, so you add db_index=True or a Meta.indexes entry and deploy. On a table with millions of rows, the migration runs CREATE INDEX, which takes a lock that blocks every INSERT, UPDATE, and DELETE on the table until the index is built, often minutes. Checkouts and signups queue, requests time out, and the deploy looks like an outage. PostgreSQL can build indexes concurrently without blocking writes, and Django exposes that through AddIndexConcurrently, with a few rules to follow.

1. Add the index to the model (Meta.indexes) and generate the migration.
2. Replace AddIndex with AddIndexConcurrently from django.contrib.postgres.operations.
3. Set atomic = False on that migration; concurrent builds cannot run in a transaction.
4. Keep that migration to the index operation only.
5. If the build fails, drop the INVALID index and run it again.
6. Use RemoveIndexConcurrently to drop indexes on large tables.

Step-by-step solution

1. Define the index and generate the migration

class Order(models.Model):
    customer = models.ForeignKey("Customer", on_delete=models.PROTECT)
    status = models.CharField(max_length=20)
    created_at = models.DateTimeField()

    class Meta:
        indexes = [models.Index(fields=["status", "-created_at"], name="order_status_created_idx")]

python manage.py makemigrations orders

2. Make it concurrent

# orders/migrations/0042_order_status_created_idx.py
from django.contrib.postgres.operations import AddIndexConcurrently
from django.db import migrations, models

class Migration(migrations.Migration):
    atomic = False
    dependencies = [("orders", "0041_previous")]
    operations = [
        AddIndexConcurrently(
            "order",
            models.Index(fields=["status", "-created_at"], name="order_status_created_idx"),
        ),
    ]

python manage.py sqlmigrate orders 0042
# CREATE INDEX CONCURRENTLY "order_status_created_idx" ON "orders_order" ("status", "created_at" DESC);

CREATE INDEX CONCURRENTLY scans the table twice and waits for existing transactions, so it takes longer, but normal reads and writes continue throughout.

3. Keep the migration isolated

Because atomic = False, each operation commits on its own. If another operation in the same migration fails after the index is built, you are left half-migrated. Put concurrent index operations in their own migration file.

4. Handle a failed concurrent build

If the build fails (a deadlock, a unique violation for a unique index, a cancelled statement), PostgreSQL leaves an INVALID index that slows writes and is never used. Find and drop it:

SELECT indexrelid::regclass AS index_name
FROM pg_index WHERE NOT indisvalid;

DROP INDEX CONCURRENTLY IF EXISTS order_status_created_idx;

Then rerun the migration (with --fake reversed if Django recorded it, which it does not when the operation fails).

5. Watch for long transactions

CREATE INDEX CONCURRENTLY waits for transactions that started before it. A long-running report or an idle-in-transaction connection makes the build hang. Check pg_stat_activity and set lock_timeout for the migration session if you want it to give up rather than wait.

6. Remove unused indexes too

Indexes cost write performance and disk. Find candidates with low scan counts:

SELECT relname, indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes ORDER BY idx_scan ASC, pg_relation_size(indexrelid) DESC LIMIT 20;

Remove them with RemoveIndexConcurrently in the same non-atomic pattern, after confirming they are not used on replicas or by rare reports.

Verification check: during the migration, application writes keep succeeding (watch error rates); afterwards, \d orders_order lists the index as valid and EXPLAIN ANALYZE on the slow query shows an Index Scan using it with a much lower execution time.

Explanation

A normal index build takes a SHARE lock, which allows reads but blocks writes, so the table is effectively read-only until the build finishes. The concurrent variant takes a weaker lock, builds the index in phases while tracking concurrent changes, and waits for older transactions before marking the index valid. Because it spans several internal transactions, it cannot run inside a transaction block, which is why Django requires a non-atomic migration. The trade-off is longer build time and the possibility of an invalid index if something fails midway.

Edge cases / notes

• Unique indexes built concurrently fail if duplicates exist; clean data first.
• Foreign key fields get indexes automatically from ForeignKey; adding db_index=False and a composite index can replace them.
• MySQL uses online DDL (ALGORITHM=INPLACE, LOCK=NONE) for many index builds; Django does not emit it automatically.
• Replicas replay the build; large index builds can cause replica lag.

Next steps

• Other large-table changes: Django Schema Changes on Large Tables.
• Find which indexes you need with Find Slow Django Queries With pg_stat_statements.

FAQ

How do I add an index in Django without locking the table?

Use AddIndexConcurrently in a migration with atomic = False on PostgreSQL.

Why must the migration be non-atomic?

PostgreSQL cannot run CREATE INDEX CONCURRENTLY inside a transaction block.

What if the concurrent build fails?

Drop the leftover INVALID index with DROP INDEX CONCURRENTLY and run the migration again.

Does db_index=True create indexes concurrently?

No. The generated migration uses a normal CREATE INDEX; switch it to the concurrent operation yourself.