skip to content

In Django on PostgreSQL, how do you add an index to a 50-million-row events table without blocking writes?

level: seniorimportance: should knowfreq 36%

answer

  1. a plain index build blocks writes
  2. a contrib.postgres operation
  3. not inside a transaction
  4. the migration's atomic flag

basics

~10 s

Replace the generated AddIndex with AddIndexConcurrently from django.contrib.postgres.operations and set atomic = False on the migration, because CREATE INDEX CONCURRENTLY cannot run in a transaction. RemoveIndexConcurrently does the same for dropping.

solid answer

~40 s

`makemigrations` writes a plain `AddIndex` for a new entry in `Meta.indexes`, which runs `CREATE INDEX` and blocks writes to the table for the whole build. On PostgreSQL I swap it for `AddIndexConcurrently` from `django.contrib.postgres.operations`, which issues `CREATE INDEX CONCURRENTLY`, and set `atomic = False` on the `Migration` class; otherwise Django raises `NotSupportedError` saying the operation cannot be executed inside a transaction. I keep it alone in its migration, since a non-atomic migration that fails half-way is not rolled back. If a concurrent build fails, PostgreSQL can leave an invalid index under that name, and Django's `CREATE INDEX CONCURRENTLY` has no `IF NOT EXISTS`, so I drop it before retrying. `RemoveIndexConcurrently(model_name, name)` is the counterpart for dropping.

code

python · 15 lines
python
from django.contrib.postgres.operations import AddIndexConcurrently
from django.db import migrations, models


class Migration(migrations.Migration):
    atomic = False

    dependencies = [("analytics", "0031_event_source")]

    operations = [
        AddIndexConcurrently(
            "event",
            models.Index(fields=["source", "created_at"], name="event_source_created_idx"),
        ),
    ]

go deeper

for a junior

Recall that AddIndexConcurrently and RemoveIndexConcurrently live in django.contrib.postgres.operations and need atomic = False on the migration.

for a middle

Explain why CONCURRENTLY cannot run in the migration's transaction and how to turn a generated AddIndex into the concurrent operation.

for a senior

Handle the operational edges: isolate the build in its own migration, recover from an invalid index, and prefer Meta.indexes so the swap is possible.

for a principal

Make concurrent index builds the default for large tables in review, and decide what table size or traffic triggers the rule.

## Why a plain AddIndex hurts When you add `models.Index(...)` to a model's `Meta.indexes`, `makemigrations` generates an `AddIndex` operation, which on PostgreSQL runs a normal `CREATE INDEX`. A normal index build **blocks writes** to the table until it finishes. On a 50-million-row `analytics_event` table that can mean minutes of failed or queued inserts. PostgreSQL's `CONCURRENTLY` option builds the index without locking out writes, at the cost of a slower build. ## The concurrent operations Django exposes that option through two PostgreSQL-only operations in `django.contrib.postgres.operations`: | Operation | SQL on PostgreSQL | Arguments | |---|---|---| | `AddIndex` | `CREATE INDEX ...` | `model_name, index` | | `AddIndexConcurrently` | `CREATE INDEX CONCURRENTLY ...` | `model_name, index` | | `RemoveIndex` | `DROP INDEX IF EXISTS ...` | `model_name, name` | | `RemoveIndexConcurrently` | `DROP INDEX CONCURRENTLY IF EXISTS ...` | `model_name, name` | Both concurrent operations update migration state exactly like their plain counterparts, so the autodetector stays in sync. ## Turning generated output into a concurrent build 1. Declare the index in `Meta.indexes` with an explicit `name`, rather than `db_index=True` on a field. `db_index` creates the index as part of `AddField` or `AlterField`, so there is no `AddIndex` to swap; Django's docs also recommend `Meta.indexes` over `db_index`. 2. Run `makemigrations`; it writes `migrations.AddIndex(...)`. 3. Import `AddIndexConcurrently` and replace `migrations.AddIndex` with it, keeping the same arguments. 4. Set `atomic = False` on the `Migration` class. 5. Keep the index operation alone in that migration, or with other operations that are safe outside a transaction. 6. Check with `sqlmigrate` that the output says `CREATE INDEX CONCURRENTLY`. ## What atomic = False implies - On PostgreSQL a normal migration runs inside one transaction. `CONCURRENTLY` is not allowed inside a transaction, so the operation checks for an open atomic block and raises `NotSupportedError` with the hint to set `atomic = False` on the migration. - The operation itself is marked non-atomic, so Django does not wrap it in a per-operation transaction either. - A non-atomic migration that fails part-way leaves earlier operations applied and the migration **not recorded**. That is why the concurrent build should sit alone. - If the build fails, PostgreSQL can leave an **invalid index** with that name. Django's `CREATE INDEX CONCURRENTLY` statement has no `IF NOT EXISTS`, so rerunning `migrate` fails until you drop the leftover index. ## Related tools for large tables - `AddConstraintNotValid` and `ValidateConstraint`, in the same module, add a check constraint without validating existing rows and validate it later, in separate migrations. - Lock and statement timeouts on the migration connection, which bound how long an `ALTER` may wait, are a runner concern outside this operation. Interviewers look for three things: that a plain index build blocks writes, the exact Django operation and module, and the `atomic = False` requirement with its failure implications.

  • How do you drop an index on the same busy table without blocking writes?
    Remove it from `Meta.indexes`, let `makemigrations` write `RemoveIndex`, then replace it with `RemoveIndexConcurrently(model_name, name)` from `django.contrib.postgres.operations` in a migration with `atomic = False`. It issues `DROP INDEX CONCURRENTLY IF EXISTS`.
  • The concurrent index migration failed half-way; what do you check before rerunning migrate?
    Whether an invalid index with the same name was left behind. Django's concurrent create has no `IF NOT EXISTS`, so a leftover index makes the rerun fail. Drop it, concurrently, then run `migrate` again; the migration was not recorded, so it runs from the start.

saying these in an interview costs you the question

  • AddIndexConcurrently works in a normal atomic migration
  • makemigrations generates AddIndexConcurrently automatically on PostgreSQL
  • A plain AddIndex never blocks writes
  • db_index was removed in Django 6.1
  • A failed non-atomic migration rolls back its completed operations