gabriel / musehub public
0088_intel_page_indexes.py python
68 lines 3.6 KB
Raw
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