Snippet

Non-Blocking Zero-Downtime Database Index Migrations in Django

Standard Django database index migrations lock tables during execution, blocking writes on high-traffic production databases. Implementing a custom non-atomic migration `Operation` allows running `CREATE INDEX CONCURRENTLY` in PostgreSQL without locking write traffic.

snippet.py

python

from django.db.migrations.operations.base import Operation
 
class AddConcurrentIndex(Operation):
    atomic = False
 
    def __init__(self, model_name: str, index_name: str, columns: list[str]):
        self.model_name = model_name
        self.index_name = index_name
        self.columns = columns
 
    def database_forwards(self, app_label, schema_editor, from_state, to_state):
        if schema_editor.connection.vendor == 'postgresql':
            table_name = f"{app_label}_{self.model_name.lower()}"
            cols = ", ".join(self.columns)
            sql = f"CREATE INDEX CONCURRENTLY IF NOT EXISTS {self.index_name} ON {table_name} ({cols});"
            schema_editor.execute(sql)
 
    def database_backwards(self, app_label, schema_editor, from_state, to_state):
        if schema_editor.connection.vendor == 'postgresql':
            sql = f"DROP INDEX CONCURRENTLY IF NOT EXISTS {self.index_name};"
            schema_editor.execute(sql)

django

Breakdown

atomic = False

Informs the migration framework that this migration operation must execute outside a global SQL transaction block.

sql = f"CREATE INDEX CONCURRENTLY IF NOT EXISTS ..."

Executes PostgreSQL concurrent index creation without taking exclusive write locks on the target table.

def database_backwards(self, app_label, schema_editor, from_state, to_state):

Defines the rollback logic for removing the concurrent index when unapplying the migration.

Previous snippet Next snippet

More Expert Python exercises →

Non-Blocking Zero-Downtime Database Index Migrations in Django

Related