Files
Evection/apps/search/migrations/0003_postgres_search_vector_backfill.py
ksmithandClaude Sonnet 5.5 d6e310b127 Make the Postgres search migration online-safe (R2-T3, #39)
One migration did a full-table UPDATE, a blocking GIN build and the
trigger install together — long locks and a deploy that could outlast
its health-check window on a large archive.

Now two stages. 0002 only adds the nullable column and the trigger
(metadata-only; new and updated rows get a vector from then on). 0003
is non-atomic: it backfills in 5,000-id batches, each its own short
transaction and only touching rows still NULL, then builds the index
with CREATE INDEX CONCURRENTLY (dropping a stale INVALID one from a
failed earlier run first). Re-running is safe, and it's a cheap no-op on
an install that applied the earlier single-step version.

Measured on a real PostgreSQL 16 with 150,000 existing articles while a
second connection kept inserting/updating: the migration took ~20s, the
slowest concurrent write was 37ms (nothing blocked), no NULL vectors
remained, the index was valid, and unapply/re-apply worked. Full suite
passes on Postgres.

Co-Authored-By: Claude Sonnet 5.5 <noreply@anthropic.com>
2026-11-01 12:02:20 -07:00

80 lines
2.7 KiB
Python

"""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)]