0085_symbol_history_address_pattern_index.py
python
sha256:30c51e90dfe2486f79752f7305be1faf2f086d5407ee5e1307167b5a8c61549d
perf(db): SQL audit pass 2 + Tier A review fixes; local tim…
Opus 5.5
minor
⚠ breaking
2 days ago
| 1 | """Prefix-searchable index on musehub_symbol_history_entries (repo_id, address) |
| 2 | |
| 3 | File- and symbol-history lookups filter ``repo_id = X AND (address = N OR address LIKE 'N::%')``. Under a non-C collation (production and staging |
| 4 | use en_US.UTF-8) a plain btree cannot serve ``LIKE 'prefix%'``, so the planner chose a parallel sequential scan of the whole table (277 MB on staging) |
| 5 | for every page view; on a small instance a crawler made that routinely fatal. ``text_pattern_ops`` makes the prefix (and the equality) index-searchable. |
| 6 | |
| 7 | Revision ID: 0085 |
| 8 | Revises: 0084 |
| 9 | |
| 10 | CONCURRENTLY: no write lock on the table; cannot run inside a transaction, so it uses ``autocommit_block()`` (as 0044 does). ``IF NOT EXISTS`` makes a |
| 11 | re-run safe; an INVALID leftover of an interrupted build is dropped and rebuilt, and the result is checked to be valid (``_build``). Additive: the previous |
| 12 | image does not use or need the index, so redeploying the previous image tag remains a valid rollback. |
| 13 | """ |
| 14 | from __future__ import annotations |
| 15 | |
| 16 | from alembic import op |
| 17 | from sqlalchemy import text |
| 18 | |
| 19 | revision: str = "0085" |
| 20 | down_revision: str = "0084" |
| 21 | branch_labels = None |
| 22 | depends_on = None |
| 23 | |
| 24 | _INDEX = "ix_symbol_history_repo_address_pattern" |
| 25 | |
| 26 | |
| 27 | def _build(name: str, ddl: str) -> None: |
| 28 | """Create one index CONCURRENTLY, safely on a small instance and fail-closed. |
| 29 | |
| 30 | * An earlier interrupted CONCURRENTLY build leaves an INVALID index of the same name, which ``IF NOT EXISTS`` would silently accept: drop it first. |
| 31 | * No parallel maintenance workers and a modest ``maintenance_work_mem`` (each worker would claim its own), so the build fits a micro instance's memory. |
| 32 | * Afterwards the index must be valid, or the migration fails (a green migration with an unusable index is the failure this guards against). |
| 33 | """ |
| 34 | bind = op.get_bind() |
| 35 | with op.get_context().autocommit_block(): |
| 36 | invalid = bind.execute(text( |
| 37 | "SELECT 1 FROM pg_index i JOIN pg_class c ON c.oid = i.indexrelid WHERE c.relname = :n AND NOT i.indisvalid"), {"n": name}).first() |
| 38 | if invalid: |
| 39 | op.execute(text(f"DROP INDEX CONCURRENTLY IF EXISTS {name}")) |
| 40 | op.execute(text("SET max_parallel_maintenance_workers = 0")) |
| 41 | op.execute(text("SET maintenance_work_mem = '32MB'")) |
| 42 | op.execute(text(ddl)) |
| 43 | valid = bind.execute(text( |
| 44 | "SELECT i.indisvalid FROM pg_index i JOIN pg_class c ON c.oid = i.indexrelid WHERE c.relname = :n"), {"n": name}).scalar() |
| 45 | if valid is not True: |
| 46 | raise RuntimeError(f"index {name} is missing or INVALID after CREATE INDEX CONCURRENTLY") |
| 47 | |
| 48 | |
| 49 | def upgrade() -> None: |
| 50 | _build(_INDEX, f"CREATE INDEX CONCURRENTLY IF NOT EXISTS {_INDEX} ON musehub_symbol_history_entries (repo_id, address text_pattern_ops)") |
| 51 | |
| 52 | |
| 53 | def downgrade() -> None: |
| 54 | with op.get_context().autocommit_block(): |
| 55 | op.execute(text(f"DROP INDEX CONCURRENTLY IF EXISTS {_INDEX}")) |
File History
2 commits
sha256:30c51e90dfe2486f79752f7305be1faf2f086d5407ee5e1307167b5a8c61549d
perf(db): SQL audit pass 2 + Tier A review fixes; local tim…
Opus 5.5
minor
⚠
2 days ago
sha256:e48f753d88adb41f4cf695d3653278331a1e64b0a25f9ded82bbd6a11031c736
perf(db): prefix-searchable index for file/symbol history l…
Opus 5.5
minor
⚠
2 days ago