Skip to main content

ADR-018: Semantic Search with pgvector

Status​

Implemented

Date​

2025-01-16 (Retrospective)

Decision Makers​

  • AI/ML Team - Search architecture
  • Backend Team - Implementation approach

Layer​

AI-ML

  • ADR-001: PostgreSQL with pgvector
  • ADR-017: Dual Embedding Strategy
  • ADR-019: RAG Pipeline Design

Supersedes​

None

Depends On​

  • ADR-001: PostgreSQL with pgvector
  • ADR-017: Dual Embedding Strategy

Context​

Traditional keyword search has limitations:

  1. Synonym Blindness: "authentication" doesn't match "login"
  2. Context Loss: Word order and meaning ignored
  3. False Negatives: Relevant results missed
  4. No Semantic Understanding: Can't understand intent

Requirements for semantic search:

  • Find conceptually similar content
  • Support natural language queries
  • Rank by semantic relevance
  • Combine with keyword search
  • Fast response times (<100ms)

Decision​

We implement semantic search using pgvector with cosine similarity:

Key Design Decisions​

  1. pgvector Extension: Native PostgreSQL vector support
  2. Cosine Similarity: Distance metric for comparison
  3. IVFFlat Index: Approximate nearest neighbor for speed
  4. Hybrid Search: Combine vector + keyword results
  5. Configurable Threshold: Adjustable similarity cutoff

Vector Operations​

-- Create embedding column
ALTER TABLE requirements ADD COLUMN embedding vector(1536);

-- Create IVFFlat index
CREATE INDEX ON requirements
USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 100);

-- Semantic search query
SELECT id, title,
       1 - (embedding <=> query_embedding) as similarity
FROM requirements
WHERE embedding IS NOT NULL
  AND 1 - (embedding <=> query_embedding) > 0.3
ORDER BY embedding <=> query_embedding
LIMIT 20;

API Endpoint​

GET /api/v1/requirements/semantic-search?q=user+authentication

Response:
{
  "items": [
    {
      "id": "REQ-000042",
      "title": "Login System Implementation",
      "similarity": 0.87
    },
    {
      "id": "REQ-000015",
      "title": "OAuth2 Integration",
      "similarity": 0.82
    }
  ],
  "total": 2,
  "threshold": 0.3
}

Hybrid Search Strategy​

def hybrid_search(query: str, limit: int = 20):
    # Get semantic results
    semantic_results = vector_search(query, limit * 2)

    # Get keyword results
    keyword_results = fulltext_search(query, limit * 2)

    # Merge and rerank
    combined = merge_results(
        semantic_results,
        keyword_results,
        weights=(0.7, 0.3)  # Favor semantic
    )

    return combined[:limit]

Consequences​

Positive​

  • Understanding Intent: Finds conceptually related content
  • Better Recall: Fewer missed relevant results
  • Natural Language: Users can search conversationally
  • No Separate DB: Uses existing PostgreSQL
  • SQL Integration: Combine with other queries

Negative​

  • Index Memory: Vector indexes use RAM
  • Reindexing: Schema changes need index rebuild
  • Approximate Results: IVFFlat trades accuracy for speed
  • Embedding Cost: Must generate query embedding

Neutral​

  • Query Latency: ~20-50ms with index
  • Storage Growth: ~6KB per 1536-dim vector

Alternatives Considered​

  • Approach: Dedicated search engine with dense vectors
  • Rejected: Additional infrastructure, higher complexity

2. Pinecone​

  • Approach: Managed vector database
  • Rejected: External dependency, network latency

3. FAISS​

  • Approach: Facebook's similarity search library
  • Rejected: In-memory only, no persistence

Implementation Status​

  • Core implementation complete
  • Tests written and passing
  • Documentation updated
  • Migration/upgrade path defined
  • Monitoring/observability in place

Implementation Details​

  • Search Endpoint: backend/api/v1/requirements.py:semantic_search
  • Vector Queries: backend/services/embeddings.py
  • Index Setup: backend/migrations/
  • Threshold Config: App Settings UI
  • Docs: docs/development/embedding-system-guide.md

Compliance/Validation​

  • Automated checks: Search quality tests
  • Manual review: Result relevance reviewed
  • Metrics: Search latency, result click-through

LLM Council Review​

Review Date: 2025-01-16 Confidence Level: High (100%) Verdict: REQUEST CHANGES (Conditional Approval)

Quality Metrics​

  • Consensus Strength Score (CSS): 0.92
  • Deliberation Depth Index (DDI): 0.90

Council Feedback Summary​

pgvector choice is approved, but IVFFlat index and fixed 70/30 weighting are unanimously rejected as presenting unacceptable risks to incident response reliability (MTTR).

Key Concerns Identified:​

  1. IVFFlat is Wrong for SRE: Lower recall and requires periodic retraining; missing relevant runbook during outage is critical failure
  2. Fixed 70/30 Weighting is Brittle: Specific identifier queries (error codes, trace IDs) rely almost entirely on keyword search
  3. Approximate Results Risk: Semantic search always returns "nearest" even if irrelevant - SREs may follow wrong runbook

Required Modifications:​

  1. Switch to HNSW Index:
    • Configuration: m=16, ef_construction=64
    • Query-time: SET hnsw.ef_search = 40 (higher during incidents)
    • Superior 95-99% recall, handles incremental updates without retraining
  2. Replace Fixed Weighting with Reciprocal Rank Fusion (RRF):
    • Ranks results from both searches and fuses by rank position
    • Handles both specific identifiers and fuzzy descriptions gracefully
  3. Two-Stage Retrieval:
    • Use ANN to fetch larger candidate pool (top 100)
    • Apply strict metadata filters and exact re-ranking
  4. Validate Thresholds: 0.3 threshold depends on embedding model; validate against real SRE ticket test set
  5. Incident Mode: Trigger exact database scan if approximate search returns low-confidence results

Modifications Applied​

  1. Updated index recommendation to HNSW
  2. Documented RRF hybrid search pattern
  3. Added two-stage retrieval strategy
  4. Documented threshold validation requirement

Council Ranking​

  • gpt-5.2: Best Response (HNSW analysis)
  • claude-opus-4.5: Strong (RRF recommendation)
  • gemini-3-pro: Good (threshold validation)

References​

ADR-018 | AI-ML Layer | Implemented