0086_hot_read_path_indexes.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 | """Indexes for the hot intel/explore read paths (SQL audit, 2026-11-14) |
| 2 | |
| 3 | * ix_symbol_intel_repo_churn30 / ix_symbol_intel_repo_blast: /explore picks the top symbol per repo (LATERAL .. ORDER BY .. LIMIT 1): one index probe per repo |
| 4 | instead of a window function over every symbol of every listed repo. |
| 5 | * ix_symbol_intel_repo_op: the symbols page's count-by-op becomes an index-only scan. |
| 6 | * ix_intel_type_repo_score_addr: the type page's ORDER BY type_score, address LIMIT N reads N index entries, not the whole repo. |
| 7 | * ix_intel_api_surface_repo_kind: per-kind counts become an index-only scan. |
| 8 | |
| 9 | Revision ID: 0086 |
| 10 | Revises: 0085 |
| 11 | |
| 12 | CONCURRENTLY (no write lock; own autocommit block per statement), IF NOT EXISTS (re-runnable). An INVALID leftover of an interrupted build is dropped and rebuilt, and each |
| 13 | result is checked to be valid (``_build``). Additive: the previous image does not use the indexes, so redeploying it remains a valid rollback. |
| 14 | """ |
| 15 | from __future__ import annotations |
| 16 | |
| 17 | from alembic import op |
| 18 | from sqlalchemy import text |
| 19 | |
| 20 | revision: str = "0086" |
| 21 | down_revision: str = "0085" |
| 22 | branch_labels = None |
| 23 | depends_on = None |
| 24 | |
| 25 | _INDEXES = ( |
| 26 | ("ix_symbol_intel_repo_churn30", "musehub_symbol_intel", "(repo_id, churn_30d DESC NULLS LAST)"), |
| 27 | ("ix_symbol_intel_repo_blast", "musehub_symbol_intel", "(repo_id, blast DESC NULLS LAST)"), |
| 28 | ("ix_symbol_intel_repo_op", "musehub_symbol_intel", "(repo_id, op)"), |
| 29 | ("ix_intel_type_repo_score_addr", "musehub_intel_type", "(repo_id, type_score, address)"), |
| 30 | ("ix_intel_api_surface_repo_kind", "musehub_intel_api_surface", "(repo_id, kind)"), |
| 31 | ) |
| 32 | |
| 33 | |
| 34 | def _build(name: str, ddl: str) -> None: |
| 35 | """Create one index CONCURRENTLY, safely on a small instance and fail-closed. |
| 36 | |
| 37 | * An earlier interrupted CONCURRENTLY build leaves an INVALID index of the same name, which ``IF NOT EXISTS`` would silently accept: drop it first. |
| 38 | * 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. |
| 39 | * Afterwards the index must be valid, or the migration fails (a green migration with an unusable index is the failure this guards against). |
| 40 | """ |
| 41 | bind = op.get_bind() |
| 42 | with op.get_context().autocommit_block(): |
| 43 | invalid = bind.execute(text( |
| 44 | "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() |
| 45 | if invalid: |
| 46 | op.execute(text(f"DROP INDEX CONCURRENTLY IF EXISTS {name}")) |
| 47 | op.execute(text("SET max_parallel_maintenance_workers = 0")) |
| 48 | op.execute(text("SET maintenance_work_mem = '32MB'")) |
| 49 | op.execute(text(ddl)) |
| 50 | valid = bind.execute(text( |
| 51 | "SELECT i.indisvalid FROM pg_index i JOIN pg_class c ON c.oid = i.indexrelid WHERE c.relname = :n"), {"n": name}).scalar() |
| 52 | if valid is not True: |
| 53 | raise RuntimeError(f"index {name} is missing or INVALID after CREATE INDEX CONCURRENTLY") |
| 54 | |
| 55 | |
| 56 | def upgrade() -> None: |
| 57 | for name, table, cols in _INDEXES: |
| 58 | _build(name, f"CREATE INDEX CONCURRENTLY IF NOT EXISTS {name} ON {table} {cols}") |
| 59 | |
| 60 | |
| 61 | def downgrade() -> None: |
| 62 | for name, _table, _cols in reversed(_INDEXES): |
| 63 | with op.get_context().autocommit_block(): |
| 64 | op.execute(text(f"DROP INDEX CONCURRENTLY IF EXISTS {name}")) |
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:93c33b03c434d9887a5d4ee3a9329c9a126343e713a28173ca4c10d85f41eef8
perf(db): SQL audit pass 1 — hot read paths index-served, o…
Opus 5.5
minor
⚠
2 days ago