Fixing the Filtered-Search Trap in RAG: Hybrid pgvector Search on Aurora with Bedrock
Most pgvector RAG tutorials stop at "embed, insert, query." This one covers what breaks next: metadata-filtered HNSW searches that return too few rows, and exact-term queries that pure vector search misses. Build a hybrid (vector + full-text) retriever with Reciprocal Rank Fusion on Aurora PostgreSQL and Amazon Bedrock.
The problem with "hello world" RAG
Most pgvector tutorials end at the same place: embed some text, store it, and run a nearest-neighbour query with a LIMIT of 5. It works in a demo. Then real requirements arrive, and two things break:
- Filtered searches come back short. Add a WHERE clause such as tenant = 'acme' to an HNSW query and you may get 2 rows back when you asked for 5.
- Exact terms get missed. Semantic search is weak on error codes, SKUs, and acronyms. Ask for ERR_4021 and you get paragraphs that merely feel similar.
This post describes a small RAG retriever on Amazon Aurora PostgreSQL with pgvector and Amazon Bedrock that handles both problems.
Architecture
- Embeddings: Amazon Titan Text Embeddings V2 via Bedrock (1024 dimensions)
- Store and search: Aurora PostgreSQL with pgvector (HNSW index) plus built-in full-text search (tsvector)
- Fusion: Reciprocal Rank Fusion (RRF) in plain SQL
- Generation: any text model enabled in Bedrock, called through the Converse API
No separate search cluster is needed. Everything lives in one database.
Step 1: Schema
Create one table called chunks with these columns:
- id: bigserial primary key
- doc_id: text, the source document
- tenant: text, used for filtering
- content: text, the chunk itself
- embedding: vector(1024), the Titan embedding
- tsv: a generated tsvector column built from content with to_tsvector('english', content)
Then add three indexes:
- An HNSW index on embedding using vector_cosine_ops (m = 16, ef_construction = 64)
- A GIN index on tsv for keyword search
- A regular index on tenant
Because tsv is a generated column, the keyword index stays in sync automatically. There is no second pipeline to maintain.
Step 2: Embed and ingest
For each chunk of text, call the Bedrock model amazon.titan-embed-text-v2:0 with dimensions set to 1024 and normalize set to true. Insert the chunk's text, its tenant, its document id, and the returned embedding into the chunks table.
Keep chunks to a few hundred tokens with a small overlap. Retrieval quality depends on chunking more than on almost anything else.
Step 3: The filtered-search trap
HNSW is an approximate index. With the default hnsw.ef_search of 40, Postgres collects roughly 40 nearest candidates and only then applies your WHERE clause. If most of those candidates belong to other tenants, the filter discards them and you receive fewer rows than your LIMIT. There is no error, and answers quietly get worse.
pgvector 0.8.0 added iterative index scans, which keep scanning until enough rows pass the filter. Run these two settings on every connection that does filtered retrieval:
SET hnsw.iterative_scan = relaxed_order;
SET hnsw.ef_search = 100;
SET hnsw.ef_search = 100;
Confirm that your Aurora engine version ships pgvector 0.8.0 or later. To see the trap for yourself, load a table where one tenant owns 95% of the rows, query as a small tenant, and compare the number of rows returned with and without these settings.
Step 4: Hybrid retrieval with Reciprocal Rank Fusion
Run two searches for the same question and merge them by rank:
- A vector search: order the tenant's chunks by cosine distance to the question's embedding and keep the top 20.
- A keyword search: match the tenant's chunks against websearch_to_tsquery of the question, rank by ts_rank_cd, and keep the top 20.
Then score each chunk with Reciprocal Rank Fusion. A chunk earns 1 / (60 + rank) from each list it appears in, and the two scores are added. Sort by the total and keep the top 5. In SQL, this is two CTEs (one per search) joined back to the chunks table with LEFT JOINs and COALESCE.
RRF needs no score normalization, which makes it easy to use. A chunk that appears in both lists rises to the top. For a query like "how do I fix ERR_4021", the exact-code chunk comes from the keyword search and the conceptual explanation comes from the vector search.
Step 5: Generate the answer
- Embed the user's question with Titan.
- Apply the two hnsw settings from Step 3.
- Run the hybrid query and join the top 5 chunks into one context string.
- Call the Bedrock Converse API with a prompt like: "Answer using only this context. If it is not in the context, say so." followed by the context and the question.
- Return the model's reply.
Where this fits, and where it doesn't
- Choose pgvector on Aurora when your data already lives in Postgres, you need transactional consistency and SQL filters, and you want one system to operate.
- Choose Amazon Bedrock Knowledge Bases when you would rather not own chunking, ingestion, and retrieval code. It can use Aurora PostgreSQL as its vector store.
- Look elsewhere for hundreds of millions of vectors under heavy concurrent load, and benchmark before committing.
Cost notes for experimenters
Aurora Serverless v2 can scale down when idle, which suits side projects. Check current pricing and minimum-capacity behavior for your region, and delete the cluster when you finish. Forgotten databases are the most common source of surprise bills.
Takeaways
- Test filtered vector queries on skewed data. Silent under-retrieval is easy to miss.
- Hybrid search (vector plus full-text) fixes the exact-term weakness for the cost of one extra query step.
- RRF is simple, needs no tuning, and works well as a default.
- One database for vectors, text, and metadata keeps the system easy to reason about.
What retrieval failures have you hit in your own RAG builds? Share them in the comments.
Enjoyed reading this content? Let the author know!
Your likes, comments, shares, and saves help creators reach more builders.
Loading recommendations
Loading article