"""Stage 2 of 2 (see 0002): backfill existing rows and build the GIN index without holding a long lock (R2-T3). Not atomic, on purpose. The backfill runs in id-range batches, each its own short transaction, so concurrent reads and writes keep flowing and a failed deploy can simply be re-run (it only touches rows whose vector is still NULL). CREATE INDEX CONCURRENTLY can't run inside a transaction at all. Rows written during the backfill already get a vector from the trigger installed in 0002. Safe to re-run, and a cheap no-op on an install that already has the index and a full column (e.g. one that applied the earlier single-step version of 0002). No-ops entirely on SQLite. """ import time from django.db import migrations BATCH_SIZE = 5000 INDEX_NAME = "articles_article_search_vector_gin" BACKFILL = """ UPDATE articles_article SET search_vector = setweight(to_tsvector('english', coalesce(title, '')), 'A') || setweight(to_tsvector('english', coalesce(content_html, '')), 'B') WHERE id >= %s AND id < %s AND search_vector IS NULL """ def backfill_and_index(apps, schema_editor): connection = schema_editor.connection if connection.vendor != "postgresql": return with connection.cursor() as cursor: cursor.execute("SELECT min(id), max(id) FROM articles_article") low, high = cursor.fetchone() if low is not None: started = time.monotonic() done = 0 for start in range(low, high + 1, BATCH_SIZE): cursor.execute(BACKFILL, [start, start + BATCH_SIZE]) done += cursor.rowcount print(f"\n backfilled {done} search vector(s) in {time.monotonic() - started:.1f}s") # A failed CONCURRENTLY build leaves an INVALID index behind, which # IF NOT EXISTS would then happily skip — drop it so a re-run # actually rebuilds. cursor.execute( "SELECT 1 FROM pg_index i JOIN pg_class c ON c.oid = i.indexrelid " "WHERE c.relname = %s AND NOT i.indisvalid", [INDEX_NAME], ) if cursor.fetchone(): cursor.execute(f"DROP INDEX CONCURRENTLY IF EXISTS {INDEX_NAME}") cursor.execute( f"CREATE INDEX CONCURRENTLY IF NOT EXISTS {INDEX_NAME} " "ON articles_article USING GIN (search_vector)" ) def drop_index(apps, schema_editor): if schema_editor.connection.vendor != "postgresql": return with schema_editor.connection.cursor() as cursor: cursor.execute(f"DROP INDEX CONCURRENTLY IF EXISTS {INDEX_NAME}") class Migration(migrations.Migration): atomic = False dependencies = [("search", "0002_postgres_search_vector")] operations = [migrations.RunPython(backfill_and_index, drop_index)]