gabriel / musehub public
0086_hot_read_path_indexes.py python
64 lines 3.3 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 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