0088_intel_page_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 intel stable/dead pages and the symbols list (SQL audit pass 2, 2026-11-14) |
| 2 | |
| 3 | * ix_intel_stable_repo_days (repo_id, days_stable DESC) INCLUDE (since_start): ORDER BY days_stable DESC LIMIT n reads n entries; the stats are index-only. |
| 4 | * ix_intel_dead_repo_active (repo_id, confidence, address) WHERE dismissed IS false: active candidates per tier in address order, the dashboard preview, the |
| 5 | explore card count. |
| 6 | * ix_intel_dead_repo_cover (repo_id) INCLUDE (confidence, dismissed): the dead page's one-pass stats, index-only. |
| 7 | * ix_symbol_intel_repo_churn_list (repo_id, churn DESC NULLS LAST, address): the symbols list's ORDER BY .. LIMIT (it sorted every symbol of the repository). |
| 8 | * ix_intel_blast_risk_repo_score (repo_id, risk_score DESC): blast-risk page and forecast top-N (it sorted every scored symbol). |
| 9 | * ix_intel_api_surface_repo_kind_addr (repo_id, kind, address): the API-surface list's ORDER BY kind, address LIMIT n. |
| 10 | |
| 11 | Revision ID: 0088 |
| 12 | Revises: 0087 |
| 13 | |
| 14 | Same build discipline as 0085-0087 (CONCURRENTLY, no parallel workers, 32 MB maintenance memory, INVALID leftovers rebuilt, validity checked). Additive: the |
| 15 | previous image does not use these, so redeploying it remains a valid rollback. |
| 16 | """ |
| 17 | from __future__ import annotations |
| 18 | |
| 19 | from alembic import op |
| 20 | from sqlalchemy import text |
| 21 | |
| 22 | revision: str = "0088" |
| 23 | down_revision: str = "0087" |
| 24 | branch_labels = None |
| 25 | depends_on = None |
| 26 | |
| 27 | _INDEXES = ( |
| 28 | ("ix_intel_stable_repo_days", |
| 29 | "CREATE INDEX CONCURRENTLY IF NOT EXISTS ix_intel_stable_repo_days ON musehub_intel_stable (repo_id, days_stable DESC) INCLUDE (since_start)"), |
| 30 | ("ix_intel_dead_repo_active", |
| 31 | "CREATE INDEX CONCURRENTLY IF NOT EXISTS ix_intel_dead_repo_active ON musehub_intel_dead (repo_id, confidence, address) WHERE dismissed IS false"), |
| 32 | ("ix_intel_dead_repo_cover", |
| 33 | "CREATE INDEX CONCURRENTLY IF NOT EXISTS ix_intel_dead_repo_cover ON musehub_intel_dead (repo_id) INCLUDE (confidence, dismissed)"), |
| 34 | ("ix_intel_blast_risk_repo_score", |
| 35 | "CREATE INDEX CONCURRENTLY IF NOT EXISTS ix_intel_blast_risk_repo_score ON musehub_intel_blast_risk (repo_id, risk_score DESC)"), |
| 36 | ("ix_intel_api_surface_repo_kind_addr", |
| 37 | "CREATE INDEX CONCURRENTLY IF NOT EXISTS ix_intel_api_surface_repo_kind_addr ON musehub_intel_api_surface (repo_id, kind, address)"), |
| 38 | ("ix_symbol_intel_repo_churn_list", |
| 39 | "CREATE INDEX CONCURRENTLY IF NOT EXISTS ix_symbol_intel_repo_churn_list ON musehub_symbol_intel (repo_id, churn DESC NULLS LAST, address)"), |
| 40 | ) |
| 41 | |
| 42 | |
| 43 | def _build(name: str, ddl: str) -> None: |
| 44 | """Create one index CONCURRENTLY, safely on a small instance and fail-closed (see 0085).""" |
| 45 | bind = op.get_bind() |
| 46 | with op.get_context().autocommit_block(): |
| 47 | invalid = bind.execute(text( |
| 48 | "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() |
| 49 | if invalid: |
| 50 | op.execute(text(f"DROP INDEX CONCURRENTLY IF EXISTS {name}")) |
| 51 | op.execute(text("SET max_parallel_maintenance_workers = 0")) |
| 52 | op.execute(text("SET maintenance_work_mem = '32MB'")) |
| 53 | op.execute(text(ddl)) |
| 54 | valid = bind.execute(text( |
| 55 | "SELECT i.indisvalid FROM pg_index i JOIN pg_class c ON c.oid = i.indexrelid WHERE c.relname = :n"), {"n": name}).scalar() |
| 56 | if valid is not True: |
| 57 | raise RuntimeError(f"index {name} is missing or INVALID after CREATE INDEX CONCURRENTLY") |
| 58 | |
| 59 | |
| 60 | def upgrade() -> None: |
| 61 | for name, ddl in _INDEXES: |
| 62 | _build(name, ddl) |
| 63 | |
| 64 | |
| 65 | def downgrade() -> None: |
| 66 | for name, _ddl in reversed(_INDEXES): |
| 67 | with op.get_context().autocommit_block(): |
| 68 | op.execute(text(f"DROP INDEX CONCURRENTLY IF EXISTS {name}")) |
File History
1 commit
sha256:30c51e90dfe2486f79752f7305be1faf2f086d5407ee5e1307167b5a8c61549d
perf(db): SQL audit pass 2 + Tier A review fixes; local tim…
Opus 5.5
minor
⚠
2 days ago