gabriel / musehub public
0085_symbol_history_address_pattern_index.py python
55 lines 2.8 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 """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