Aurora pgvector HNSW Tuning: Keep Code Symbol Search Under SLOs

-- Explicit ops class + HNSW params for 1536-d Titan / Cohere embeddings
CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE code_symbols (
 id bigserial PRIMARY KEY,
 repo text NOT NULL,
 path text NOT NULL,
 symbol text NOT NULL,
 language text NOT NULL,
 embedding vector(1536) NOT NULL,
 updated_at timestamptz NOT NULL DEFAULT now()
);

-- Build offline / off-peak; ef_construction hurts build time, not query latency
CREATE INDEX CONCURRENTLY code_symbols_hnsw
 ON code_symbols
 USING hnsw (embedding vector_cosine_ops)
 WITH (m = 24, ef_construction = 200);

CREATE INDEX code_symbols_repo_lang ON code_symbols (repo, language);

# tune_ef_search.py — run against a read replica
import psycopg2, time, json

GOLDEN = json.load(open("golden_symbol_queries.json"))
CANDIDATES = [40, 64, 80, 100, 128, 200]

def recall_at_k(conn, emb, expect_ids, ef, k=10):
 with conn.cursor() as cur:
 cur.execute("SET hnsw.ef_search = %s", (ef,))
 cur.execute(
 """
 SELECT id FROM code_symbols
 ORDER BY embedding <=> %s::vector
 LIMIT %s
 """,
 (emb, k),
 )
 got = {r[0] for r in cur.fetchall()}
 return 1.0 if any(i in got for i in expect_ids) else 0.0

def sweep(dsn: str):
 conn = psycopg2.connect(dsn)
 rows = []
 for ef in CANDIDATES:
 hits, lat = [], []
 for q in GOLDEN:
 t0 = time.perf_counter()
 hits.append(recall_at_k(conn, q["embedding"], q["expect_ids"], ef))
 lat.append((time.perf_counter() - t0) * 1000)
 rows.append({
 "ef_search": ef,
 "hit@10": sum(hits) / len(hits),
 "p95_ms": sorted(lat)[int(0.95 * len(lat))],
 })
 return rows

-- Probe buffer health on a representative query
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT id, path, symbol
FROM code_symbols
WHERE repo = 'payments'
ORDER BY embedding <=> $1
LIMIT 10;

# CloudWatch alarms that catch silent recall death
# - FreeableMemory declining while ReadIOPS climbs → index not warm
# - RDS CPUCreditBalance (T-class) → never put ANN on burstable for prod chat
# - Custom metric: p95_nn_ms from the app, not just DB CPU