RAG on AWS: OpenSearch vs Aurora pgvector for Codebase Chat

“Just use a vector DB” is how codebase chat ends up with 2.5s p99 retrieval, wrong-file citations, and a $400 OpenSearch domain babysitting 2GB of embeddings. For codebase RAG, the decision is less about fancy ANN algorithms and more about metadata filters, hybrid BM25+vector, chunking, and who on-calls the data plane. This post compares Amazon OpenSearch Service k-NN vs Aurora PostgreSQL with pgvector for that workload — with concrete patterns you can ship.

⚡ TL;DR: Use pgvector on Aurora when your corpus is tens–hundreds of thousands of chunks, you already run Postgres, and you need strong SQL filters (repo, path, language, ACL) with simple ops. Use OpenSearch when you need mature hybrid BM25+k-NN, large-scale ANN, or you already operate OpenSearch for logs/search. Either way: chunk by symbol (not naive 512 tokens), embed with a code-aware model on Bedrock, filter before or with ANN, and measure recall@k on a golden question set. Illustrative targets: retrieval p99 < 150–300ms for the vector hop; end-to-end answer p99 dominated by the LLM, not the index.

The codebase RAG shape (same for both stores)

Before picking infra, freeze the retrieval contract:

Query → (optional rewrite) → embed(query)
      → hybrid retrieve top 40
      → rerank / diversity (path + symbol)
      → pack ≤ N tokens into context
      → Bedrock chat with citations

Chunking that works for code (illustrative):

# chunk_python.py — symbol-aware chunks beat sliding windows for “where is X defined?”
import ast
from dataclasses import dataclass

@dataclass
class Chunk:
    repo: str
    path: str
    symbol: str
    start_line: int
    end_line: int
    text: str
    language: str = "python"

def chunk_python_file(repo: str, path: str, source: str) -> list[Chunk]:
    tree = ast.parse(source)
    lines = source.splitlines()
    chunks: list[Chunk] = []
    for node in ast.walk(tree):
        if isinstance(node, (ast.FunctionDef, ast.AsyncFunctionDef, ast.ClassDef)):
            start, end = node.lineno, node.end_lineno or node.lineno
            # ✅ keep signature + body together; ❌ don’t split mid-function at 512 tokens blindly
            text = "\n".join(lines[start - 1 : end])
            if len(text) > 8000:  # illustrative hard cap — split methods of huge classes separately
                continue
            chunks.append(
                Chunk(repo=repo, path=path, symbol=node.name, start_line=start, end_line=end, text=text)
            )
    return chunks

Embed with Bedrock (Titan / Cohere / etc. — pick one and stick to dimensions):

import boto3, json

bedrock = boto3.client("bedrock-runtime")

def embed(texts: list[str]) -> list[list[float]]:
    # Illustrative: Cohere embed v3 via Bedrock — check current model IDs in your region
    body = {
        "texts": texts,
        "input_type": "search_document",
        "embedding_types": ["float"],
    }
    resp = bedrock.invoke_model(
        modelId="cohere.embed-english-v3",
        contentType="application/json",
        accept="application/json",
        body=json.dumps(body),
    )
    payload = json.loads(resp["body"].read())
    return payload["embeddings"]["float"]

Aurora PostgreSQL + pgvector

When it wins: you already have Aurora, need JOINs to permissions tables, want cheap staging DBs, and your chunk count fits comfortably in memory-friendly indexes (think ~10k–few M vectors depending on instance class and lists/m settings — validate with your dimensions).

-- schema.sql
CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE code_chunks (
  id           BIGSERIAL PRIMARY KEY,
  tenant_id    TEXT NOT NULL,
  repo         TEXT NOT NULL,
  path         TEXT NOT NULL,
  symbol       TEXT,
  language     TEXT NOT NULL,
  start_line   INT NOT NULL,
  end_line     INT NOT NULL,
  content      TEXT NOT NULL,
  embedding    vector(1024) NOT NULL,  -- match your model dims
  updated_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- ✅ ACL-friendly filter columns first
CREATE INDEX ON code_chunks (tenant_id, repo);
CREATE INDEX ON code_chunks (tenant_id, language);

-- HNSW (pgvector) — illustrative; tune ef_construction / m for recall vs build time
CREATE INDEX code_chunks_embedding_hnsw
  ON code_chunks
  USING hnsw (embedding vector_cosine_ops)
  WITH (m = 16, ef_construction = 64);

-- hybrid-ish: filter then ANN (critical for multi-tenant codebase chat)
-- SET LOCAL hnsw.ef_search = 64;  -- session GUC for recall
SELECT id, path, symbol, start_line, end_line, content,
       1 - (embedding <=> $1::vector) AS score
FROM code_chunks
WHERE tenant_id = $2
  AND repo = ANY($3::text[])
  AND language = ANY($4::text[])
  AND path NOT LIKE '%/vendor/%'
ORDER BY embedding <=> $1::vector
LIMIT 20;

// lambda retrieve — keep pool outside handler
import { Pool } from "pg";
const pool = new Pool({ connectionString: process.env.DATABASE_URL, max: 1 });

export async function retrieve(opts: {
  tenantId: string;
  repos: string[];
  queryEmbedding: number[];
}) {
  const { rows } = await pool.query(
    `SELECT path, symbol, start_line, end_line, content,
            1 - (embedding <=> $1::vector) AS score
     FROM code_chunks
     WHERE tenant_id = $2 AND repo = ANY($3)
     ORDER BY embedding <=> $1::vector
     LIMIT 20`,
    [`[${opts.queryEmbedding.join(",")}]`, opts.tenantId, opts.repos]
  );
  return rows;
}

Ops notes (illustrative):
– A db.r6g.xlarge Aurora instance might sustain retrieval p99 in the ~20–80ms range for selective filters + HNSW on a mid-size corpus — measure; don’t trust blog folklore.
– Backups, PITR, and IAM auth come “for free” with Aurora discipline you already have.
– True BM25 needs extra work (tsvector + RRF fusion) — doable, more DIY than OpenSearch.

❌ Don’t run unfiltered ORDER BY embedding <=> q LIMIT 20 on a multi-tenant table.
✅ Always constrain tenant_id (and repo ACL) in the same SQL statement as ANN.

OpenSearch Service k-NN

When it wins: hybrid lexical+vector is a first-class need (“find createClient in aws-sdk”), you have millions of chunks, or search is already a product surface (not just RAG context packing).

PUT /code-chunks
{
  "settings": {
    "index": {
      "knn": true,
      "number_of_shards": 3,
      "number_of_replicas": 1
    }
  },
  "mappings": {
    "properties": {
      "tenant_id": { "type": "keyword" },
      "repo": { "type": "keyword" },
      "path": { "type": "keyword" },
      "symbol": { "type": "text" },
      "language": { "type": "keyword" },
      "content": { "type": "text" },
      "embedding": {
        "type": "knn_vector",
        "dimension": 1024,
        "method": {
          "name": "hnsw",
          "space_type": "cosinesimil",
          "engine": "nmslib",
          "parameters": { "ef_construction": 128, "m": 24 }
        }
      }
    }
  }
}

POST /code-chunks/_search
{
  "size": 20,
  "query": {
    "bool": {
      "filter": [
        { "term": { "tenant_id": "acme-42" } },
        { "terms": { "repo": ["payments-api", "payments-lib"] } }
      ],
      "should": [
        {
          "knn": {
            "embedding": {
              "vector": [0.01, 0.02],
              "k": 40
            }
          }
        },
        {
          "multi_match": {
            "query": "createClient timeout retry",
            "fields": ["content", "symbol^3", "path^2"]
          }
        }
      ]
    }
  }
}

Illustrative cost reality check: a small HA OpenSearch domain (e.g. 3× r6g.large.search) often lands in the hundreds of USD/month before query volume — fine when search is core; painful when you only RAG one monorepo for an internal bot. pgvector on an existing Aurora cluster can be near-marginal cost.

Decision matrix (codebase chat)

Evaluation beats vibes

Ship a golden set of 50–100 questions with expected paths/symbols. Track:

# illustrative offline eval harness metrics
# recall@5, recall@20, MRR, citation precision
# retrieval_p50_ms, retrieval_p99_ms
# context_tokens_avg (keep packed context stable — e.g. 2–4k tokens)

def recall_at_k(expected_paths: set[str], hits: list[str], k: int) -> float:
    top = set(hits[:k])
    if not expected_paths:
        return 0.0
    return len(expected_paths & top) / len(expected_paths)

If OpenSearch wins recall@20 by 5% but costs 4× and nobody can tune ef_search, pick pgvector and invest in chunking — chunk quality usually beats store brand.

Closing checklist

✅ Dos
– ✅ Chunk by AST/symbol; store path, repo, language, tenant as filterable fields
– ✅ Filter by tenant/ACL in the same query as ANN
– ✅ Hybrid or keyword boost for exact identifiers (createClient, error codes)
– ✅ Measure retrieval p99 separately from LLM TTFT
– ✅ Version embeddings; re-index on model change (dimensions must match)

❌ Don’ts
– ❌ Don’t naive-split code into overlapping 512-token windows and call it done
– ❌ Don’t retrieve top-200 and dump into the prompt — pack and cite
– ❌ Don’t skip tenant filters because “the prompt says only use their repo”
– ❌ Don’t buy a large OpenSearch domain for a 5k-file monorepo by default
– ❌ Don’t mix embedding models in one index

Related reading

• AWS DynamoDB: Advanced Patterns for Production at Scale — for session/idempotency state beside your RAG store
• Hyper-Scale Serverless API Platform on AWS
• AWS Lambda Best Practices: Write Functions That Scale and Never Time Out
• Amazon Bedrock Agents: Tool Use, Memory, and Production Guardrails (companion post)
• Bedrock Prompt Caching and Batch Inference (companion post) — cache system + retrieved-context prefixes when they repeat

Last updated on September 10, 2026