AWS Builder Center

Builder Center

I Broke Production With Vector Search. Here's the Autopsy.

I Broke Production With Vector Search. Here's the Autopsy.

Our RAG app returned confident, fluent, completely wrong answers, and no error ever fired. Here's the full autopsy: what killed it, why, and how to fix it before it happens to you.

Binod Prasad Joshi

AWS Community Builder - Dev Tools | 2× AWS Certified | Devops/Cloud Consultant
It was 2 am. The dashboard was red. And the "AI-powered search" we'd shipped three weeks earlier the one that demoed beautifully, the one leadership loved was returning answers that were confidently, fluently, completely wrong.
Nobody paged us because the database crashed. Nobody paged us because of an error. It just... got worse. Quietly. For days. Until a customer noticed before we did.
This is the autopsy. Every organ that failed, in order, and exactly how to keep it from happening to you.
If you're building RAG (Retrieval-Augmented Generation) on AWS and if you're reading this, you probably are; this is the post I wish someone had handed me before that night.

The Victim: A Perfectly Normal RAG System

Nothing exotic. The architecture every team builds:

Five stages. Everyone obsesses over stage 3 (embeddings) and stage 5 (the LLM prompt), because those are the parts that feel like "AI." Nobody obsesses over stage 4: storage and indexing because it's "just a database."
That's exactly the mistake that killed us. Stage 4 is where the body was found.

First, the Thing Everyone Gets Wrong Without Knowing It

An embedding is just numbers  [0.021, -0.14, 0.88, ...] , a couple hundred to a couple thousand of them plotting a sentence's meaning as a point in space. Close points, similar meaning. Far points, unrelated.
"Close" is measured with a distance function, and here's detail number one that quietly poisons systems:
Distance function
What it measures
Use it when
Cosine distance
Angle between vectors
Default for most text embedding models
L2 (Euclidean)
Straight-line distance
Magnitude carries meaning
Inner product
Dot product
Model was specifically trained for it
Use the wrong one for the embedding model you picked, and nothing crashes. Nothing errors. Your search just gets worse the exact kind of failure that doesn't show up until someone screenshots a bad answer and posts it in Slack.

Cause of Death #1: We Never Measured Recall

Here's the term that should have saved us: recall. Of the results that should have come back as the closest match, what percentage actually did?
Vector indexes are approximate by design; that's the entire trick that makes them fast. You're not asking "find the exact nearest neighbor," you're asking "find something close enough, quickly." The gap between "close enough" and "actually right" is recall, and it is invisible unless you go looking for it.
We never went looking for it.
SQL
-- Ground truth: force a brute-force scan
SET enable_indexscan = off;
SELECT id FROM document_chunks
ORDER BY embedding <=> '[query_vector]'
LIMIT 10;

-- What users actually got: the approximate index
SET enable_indexscan = on;
SELECT id FROM document_chunks
ORDER BY embedding <=> '[query_vector]'
LIMIT 10;

-- Overlap between the two sets = your real recall
Run this against real queries, periodically, forever. It's the only smoke detector for a fire that otherwise burns silently.

Cause of Death #2: The Index We Never Understood

We'd used  IVFFlat  because a tutorial used  IVFFlat . Nobody on the team could have told you why.

IVFFlat: cluster first, search less


IVFFlat buckets your vectors into clusters ahead of time, then only searches the nearest buckets. Fast if the buckets are sized right.
SQL
CREATE INDEX ON document_chunks
USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 100);
Ours weren't.  lists  should track roughly  rows / 1000  under a million rows,  sqrt(rows)  beyond that. We'd set it once, at launch, on a mostly-empty table, which is its own crime, because IVFFlat clusters based on the data at build time. Build it on 500 rows, get clusters shaped for 500 rows. Grow to 2 million rows, and every cluster is now a lie.

HNSW: what we should have used from day one


HNSW builds a layered graph: sparse "highways" up top for covering distance fast, dense local roads at the bottom for precision. A search starts on the highway, exits toward the target, drops a layer, repeats. It's not clustering. It's navigation.
SQL
CREATE INDEX ON document_chunks
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);

SET hnsw.ef_search = 100; -- turn this up for better recall, down for raw speed
The verdict: IVFFlat is a fallback for when memory is your hard constraint. HNSW is the default everyone should reach for first. We reached for the wrong one and paid for it in silence for months.

IVFFlat
HNSW
Build time
Fast
Slower but modern pgvector supports parallel builds
Memory
Lower
Higher the graph wants to live in RAM
Query speed
Good
Usually faster
Recall
Fragile, tuning-sensitive
Higher, more forgiving
Verdict
Fallback
Default

Cause of Death #3: The Index Build That Took the Whole Weekend

When we finally tried to rebuild as HNSW, on real production volume, the build took hours. Distance calculations between millions of vector pairs, over and over, on an instance we hadn't sized for the job.
SQL
SELECT extversion FROM pg_extension WHERE extname = 'vector'; -- check first

SET max_parallel_maintenance_workers = 7;
SET maintenance_work_mem = '2GB';
Modern pgvector supports parallel HNSW builds — dramatically faster than the naive path. Scale your instance up temporarily for the build, then scale back down. We didn't know this existed. We found out the hard way, watching a progress bar that didn't move for six hours.

Cause of Death #4: Memory We Never Budgeted For

Here's the twist nobody warns you about: HNSW's entire graph wants to sit in memory to stay fast. Not "fits on disk." Not "swaps occasionally." Comfortably, entirely, in RAM.
We sized our instance for the raw data. Nobody sized it for the index. The result: queries that started fast in staging quietly started hitting disk in production as the dataset grew, and "sub-100ms search" became "half-second search" with zero errors and zero alerts, just a slowly rotting user experience.
The fix, in two moves:
  • Budget roughly 2–3x your raw vector data size in RAM for the HNSW graph, not just the data itself.
  • Use scalar or binary quantization — available in current pgvector versions — to compress the index and shrink that footprint, at a small, usually acceptable cost in precision.

Cause of Death #5: The Bug That Looked Like Missing Data

The support team reported it first: "sometimes the search just... returns nothing. Even though we know the document exists."
Here's the trap: when you filter vector search ( WHERE metadata->>'category' = 'billing' ), the index finds nearest neighbors first, then filters. If none of the nearest neighbors happen to match your filter, you get back fewer results than you asked for sometimes zero even though matching rows exist elsewhere in the dataset. Nothing broke. It just quietly stopped looking.
SQL
SET hnsw.iterative_scan = relaxed_order;
This tells the index to keep searching until it actually finds enough matching results, instead of giving up early. One line. Would've saved us three support escalations and a very awkward customer call.

The Resurrection: How We Rebuilt It

Once we understood every organ that failed, the rebuild was almost boring, which is exactly what you want from a database.
Python
import boto3
import psycopg2
import json

bedrock = boto3.client("bedrock-runtime", region_name="us-east-1")

def embed_text(text: str) -> list[float]:
    response = bedrock.invoke_model(
        modelId="amazon.titan-embed-text-v2:0",
        body=json.dumps({"inputText": text, "dimensions": 1024}),
    )
    return json.loads(response["body"].read())["embedding"]

def chunk_text(text: str, chunk_size: int = 500, overlap: int = 50) -> list[str]:
    """Overlap matters -- without it, you WILL slice the one sentence
    your user needed exactly in half, across a chunk boundary."""
    words = text.split()
    chunks, start = [], 0
    while start < len(words):
        chunks.append(" ".join(words[start:start + chunk_size]))
        start += chunk_size - overlap
    return chunks

def ingest_document(conn, document_id: str, full_text: str, metadata: dict):
    with conn.cursor() as cur:
        for i, chunk in enumerate(chunk_text(full_text)):
            embedding = embed_text(chunk)
            cur.execute(
                """INSERT INTO document_chunks
                   (document_id, chunk_text, chunk_index, embedding, metadata)
                   VALUES (%s, %s, %s, %s, %s)""",
                (document_id, chunk, i, embedding, json.dumps(metadata)),
            )
    conn.commit()

def retrieve_context(conn, question: str, top_k: int = 5, category: str = None) -> list[str]:
    query_embedding = embed_text(question)
    with conn.cursor() as cur:
        if category:
            cur.execute(
                """SELECT chunk_text FROM document_chunks
                   WHERE metadata->>'category' = %s
                   ORDER BY embedding <=> %s::vector LIMIT %s""",
                (category, query_embedding, top_k),
            )
        else:
            cur.execute(
                """SELECT chunk_text FROM document_chunks
                   ORDER BY embedding <=> %s::vector LIMIT %s""",
                (query_embedding, top_k),
            )
        return [row[0] for row in cur.fetchall()]

def answer_question(conn, question: str) -> str:
    context = "\n\n".join(retrieve_context(conn, question, top_k=5))
    prompt = f"""Answer using only the context below. If it's not there, say you don't know.

Context:
{context}

Question: {question}"""
    response = bedrock.invoke_model(
        modelId="anthropic.claude-sonnet-5",
        body=json.dumps({
            "anthropic_version": "bedrock-2023-05-31",
            "max_tokens": 1024,
            "messages": [{"role": "user", "content": prompt}],
        }),
    )
    return json.loads(response["body"].read())["content"][0]["text"]

The Upgrade That Actually Fixed the Bad Answers

Vector search alone is bad at exact matches: product codes, IDs, names. "SKU-4471" doesn't have a meaningful "semantic neighbor." So we added keyword search back in, and merged the two:

SQL
ALTER TABLE document_chunks ADD COLUMN chunk_tsv tsvector
    GENERATED ALWAYS AS (to_tsvector('english', chunk_text)) STORED;

CREATE INDEX ON document_chunks USING GIN (chunk_tsv);

WITH vector_results AS (
    SELECT id, chunk_text, ROW_NUMBER() OVER (ORDER BY embedding <=> %s::vector) AS rank
    FROM document_chunks ORDER BY embedding <=> %s::vector LIMIT 20
),
keyword_results AS (
    SELECT id, chunk_text, ROW_NUMBER() OVER (ORDER BY ts_rank(chunk_tsv, query) DESC) AS rank
    FROM document_chunks, plainto_tsquery('english', %s) query
    WHERE chunk_tsv @@ query LIMIT 20
)
SELECT COALESCE(v.id, k.id) AS id, COALESCE(v.chunk_text, k.chunk_text) AS chunk_text,
       (1.0 / (60 + COALESCE(v.rank, 1000))) + (1.0 / (60 + COALESCE(k.rank, 1000))) AS score
FROM vector_results v
FULL OUTER JOIN keyword_results k ON v.id = k.id
ORDER BY score DESC LIMIT 10;
This is Reciprocal Rank Fusion; it blends two very differently scaled ranking systems without needing to normalize them. Single highest-leverage change we made. The "close but not quite right" answers mostly disappeared the day this shipped.

The Autopsy Report: What Killed Us, Summarized

Cause of death
The fix
Never measured recall
Compare ANN results against brute-force scan, on a schedule
Wrong index type (IVFFlat by default)
Use HNSW unless memory-constrained
Index built on near-empty table
Rebuild after real data volume exists
No budget for index memory
Size for ~2-3x data size in RAM, or use quantization
Filtered search silently under-returning
SET hnsw.iterative_scan = relaxed_order
Chunking with no overlap
Always overlap 10% of chunk size is a solid start
Pure vector search for exact terms
Hybrid search with Reciprocal Rank Fusion


What I'd Tell Myself Before That 2 am Page

Vector search isn't a database feature you flip on. It's an approximation algorithm wearing a SQL costume, and every knob you touch  lists ,  m ,  ef_construction ,  ef_search  is the same tradeoff wearing a different name: speed versus accuracy, memory versus recall.
You cannot eliminate that tradeoff. You can only decide, on purpose, how much of it you're willing to pay for instead of finding out from a customer, at 2 am, the way we did.
# rag# vector-search# postgresql# generative-ai# amazon-bedrock
Any opinions in this article are those of the individual author and may not reflect the opinions of AWS.
Enjoyed reading this content? Let the author know!

Your likes, comments, shares, and saves help creators reach more builders.

Loading recommendations

Loading article