Aurora pgvector HNSW Tuning: Keep Code Symbol Search Under SLOs

Code-symbol nearest-neighbor search is useless if p95 spikes past your IDE chat budget when the index crosses a few million embeddings. Aurora PostgreSQL with pgvector HNSW is fast when m, ef_construction, and especially ef_search match your recall/latency envelope—not when you copy blog defaults into a 50M-row symbol table.

⚡ TL;DR: Build HNSW with m=16–32 and high ef_construction; set session ef_search from a latency/recall sweep on golden symbol queries; pin Aurora instance class + shared_buffers so the graph stays warm; monitor EXPLAIN (ANALYZE, BUFFERS) and CloudWatch ReadIOPS; never raise ef_search blindly to “fix” bad chunking. Pair with RAG Evaluation on AWS and Embedding Drift Alerts.

Index build that survives production growth

-- 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);

Avoid ivfflat with a tiny lists count on a growing monorepo — recall collapses as partitions fill. Prefer HNSW for interactive symbol search unless you have a hard memory ceiling and a stable corpus.

Session ef_search from a real sweep

ef_search is the knob that trades latency for recall at query time. Sweep it against a frozen golden set of “find this symbol / API” questions and pick the lowest value that clears your hit@10 SLO.

# 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

Keep the graph warm under Aurora

HNSW is memory-sensitive. Undersized instances thrash through the graph and destroy p95 even with perfect ef_search.

-- 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;

• Prefer Aurora memory-optimized classes so the HNSW index + hot pages fit in RAM.
• Use a reader endpoint for search; keep writers for ingest/rebuild only.
• Filter with repo / language after ANN only if selective — better: maintain per-repo indexes or partition when one tenant dominates.

Operational guardrails

# 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

Rebuild with CREATE INDEX CONCURRENTLY after embedding-model swaps; do not REINDEX in place during peak. Track embedding model ID as a column so mixed-generation vectors never share one HNSW graph.

Closing checklist

Dos
– Sweep ef_search on golden symbol queries; pin the winner in app session setup
– Size Aurora so HNSW stays resident; watch EXPLAIN (BUFFERS)
– Combine ANN with metadata filters (repo, language) intentionally
– Rebuild indexes after embedding model changes
– Measure hit@k and p95 together before shipping

Donts
– Do not copy ef_search=100 from a tutorial without a sweep
– Do not run interactive ANN on T-class burstable instances
– Do not mix embedding generations in one index
– Do not “fix” bad chunking by cranking ef_search to 400
– Do not rebuild HNSW on the writer during peak traffic

Related reading

• RAG Evaluation on AWS: Hit Rate, Faithfulness, and Cost Curves
• Embedding Drift Alerts: Catch Provider Vector Changes Before RAG Dies
• Code RAG Rerankers: Cross-Encoders After Cheap Vector Recall
• OpenSearch Neural Pipelines: Embed API Docs on Write, Hybrid Rank

Last updated on September 11, 2026