Two years ago I would have reached for a dedicated vector database first. Now I start with the Postgres instance the application already runs on. pgvector hybrid search in PostgreSQL, using the open-source pgvector extension, is enough for most web apps up to a few million chunks, and it removes an entire class of sync bugs that appear the moment a row and its embedding live in different systems.
The failure I see most often is not relevance. It is a deleted document whose embedding survived the cascade, or a product that was renamed while its vector still describes the old text. None of that is possible when the embedding is a plain column on the same row, written in the same transaction as the content.
What follows is the schema I use for chunk-level retrieval, the reciprocal rank fusion query that merges keyword and vector results, the filtered-scan trap that quietly destroys recall on multi-tenant tables, and the measurement plan that tells you whether hybrid search actually beats the keyword search you already had.
For the retrieval side of this system, read the guide to building a RAG system on a custom knowledge base. How you split documents before embedding decides most of your retrieval quality, so pair this page with the breakdown of RAG chunking strategies. If you want the theory behind the distance operators, vector search fundamentals covers embeddings, similarity metrics, and approximate nearest neighbor indexes.
Each retrieval method fails in a predictable way, and the failures barely overlap.
Vector search matches meaning. It finds "car oil change" for a query about "vehicle maintenance" because the embeddings sit near each other. It is unreliable on strings that carry no semantic weight: part numbers like RP-4471-B, error codes like ERR_CONNECTION_TIMED_OUT, invoice references, and three-letter acronyms that mean six different things depending on the tenant.
Lexical search through PostgreSQL's full text search types, tsvector and tsquery, does the opposite. It nails the exact token and ignores a paraphrase. A user who searches "how do I stop my postgres replica from lagging" gets nothing useful from a document titled "Replication Lag Triage" unless the words happen to line up.
Hybrid retrieval runs both branches against the same table and merges the ranked lists. In practice that means a query for RP-4471-B replacement procedure still returns the correct part because the lexical branch locks onto the identifier, while "my order never arrived" finds the refund policy page because the vector branch recognizes the complaint as a refund question.
The business case for doing this inside PostgreSQL rather than in a second system comes down to three properties:
WHERE clauses, not a separate metadata store you must keep in sync with the index.
The mechanism is two ranked lists and a fusion function. Everything else is schema and tuning.
websearch_to_tsquery, match against a generated tsvector column, and rank with ts_rank_cd. Take the top 50.<=> operator, and take the top 50.
Two details matter more than the rest. First, embed chunks, not whole documents, because a 40-page handbook averaged into one vector matches everything weakly and nothing precisely. Second, take more candidates from each branch than you return, since fusion can only reorder what it was given.
Three tables: a document row for the thing users see, a chunk row for what gets embedded, and the query layer that joins them back together. The chunk table carries the tenant and the generated text vector so both indexes stay on one relation.
CREATE EXTENSION IF NOT EXISTS vector;
CREATE TABLE documents (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
tenant_id uuid NOT NULL,
title text NOT NULL,
source_url text,
updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE TABLE chunks (
id bigserial PRIMARY KEY,
document_id uuid NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
tenant_id uuid NOT NULL,
chunk_index int NOT NULL,
content text NOT NULL,
content_tsv tsvector GENERATED ALWAYS AS (to_tsvector('english', content)) STORED,
embedding vector(1536) NOT NULL,
model text NOT NULL DEFAULT 'text-embedding-3-small',
UNIQUE (document_id, chunk_index)
);
CREATE INDEX chunks_tenant_hnsw ON chunks
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);
CREATE INDEX chunks_tsv ON chunks USING GIN (content_tsv);
CREATE INDEX chunks_document ON chunks (document_id);
CREATE INDEX chunks_tenant ON chunks (tenant_id);
The generated column means the text vector can never drift from the content: it recomputes on every write, and it cannot be set by hand. The model column is not decoration. When you change embedding models you will need to know which rows are stale, and a column that records the producing model turns an unbounded reindex into a targeted backfill.
You cannot add a ts_rank_cd score to a cosine distance. They live on different scales, and normalization by min-max over a result set shifts from query to query. Reciprocal rank fusion sidesteps the whole problem by throwing away the raw scores and keeping only the ordering:
score(d) = 1 / (k + rank_lexical(d)) + 1 / (k + rank_semantic(d))
with k typically set to 60, as in the original reciprocal rank fusion paper.
This is the query, with $1 as the raw user text, $2 as the tenant, and $3 as the query embedding:
-- Per-transaction settings. ef_search trades latency for recall;
-- relaxed_order lets filtered scans keep walking the graph.
SET LOCAL hnsw.ef_search = 100;
SET LOCAL hnsw.iterative_scan = relaxed_order;
WITH lexical AS MATERIALIZED (
SELECT c.id, c.document_id,
row_number() OVER (ORDER BY ts_rank_cd(c.content_tsv, q) DESC) AS rank
FROM chunks c, websearch_to_tsquery('english', $1) q
WHERE c.tenant_id = $2
AND c.content_tsv @@ q
ORDER BY ts_rank_cd(c.content_tsv, q) DESC
LIMIT 50
),
semantic AS MATERIALIZED (
SELECT c.id, c.document_id,
row_number() OVER (ORDER BY c.embedding <=> $3) AS rank
FROM chunks c
WHERE c.tenant_id = $2
ORDER BY c.embedding <=> $3
LIMIT 50
),
fused AS (
SELECT coalesce(l.id, s.id) AS chunk_id,
coalesce(l.document_id, s.document_id) AS document_id,
coalesce(1.0 / (60 + l.rank), 0.0)
+ coalesce(1.0 / (60 + s.rank), 0.0) AS score
FROM lexical l
FULL OUTER JOIN semantic s USING (id)
),
best_per_document AS (
SELECT DISTINCT ON (document_id) document_id, chunk_id, score
FROM fused
ORDER BY document_id, score DESC
)
SELECT d.id, d.title, d.source_url, b.score
FROM best_per_document b
JOIN documents d ON d.id = b.document_id
ORDER BY b.score DESC
LIMIT 10;
Four things about that query are easy to get wrong:
DISTINCT ON is load-bearing.MATERIALIZED matters.LIMIT inside it.FULL OUTER JOIN, but every score is now a single term, roughly half of what a two-branch match earns. That is acceptable for ranking inside one result set. It is not comparable across queries, so do not log it as a relevance metric.k is a knob with a narrow useful range.k = 60 the top ranks are compressed. Below about 20, the first result in either branch starts to dominate everything else.
Three failure modes show up after launch, and none of them are visible in a demo.
Duplicate chunks. Overlapping chunk windows produce near-identical rows that both score well. Dedupe at write time with a content hash per document, or you will return the same paragraph twice.
Lexical-only wins. If every query that hybrid search wins was already answered correctly by keyword search, the vector branch is costing you embedding calls and index memory for nothing. That is a measurement question, covered below.
Chunk-level fusion that ignores position. A chunk from the middle of a document has no idea it is supporting material. If the document title or heading matters, include it in the chunk text before embedding, not after retrieval.
This is the part that surprises people, and it has nothing to do with which index you picked.
Approximate indexes are scanned first, then filters are applied to what the scan returned. The pgvector documentation on filtering states the consequence plainly: if a filter matches 10% of rows and hnsw.ef_search is at its default of 40, roughly 4 rows survive. A tenant-scoped query on a shared table frequently has exactly that selectivity, so a naive plan returns four candidate chunks and the fusion step has almost nothing to work with.
There are three fixes, and the right one depends on how many distinct filter values you have:
SET LOCAL hnsw.iterative_scan = relaxed_order keeps walking the graph until enough rows pass the filter or it hits hnsw.max_scan_tuples, which defaults to 20,000. relaxed_order can return slightly out-of-order distances but recovers more results; strict_order preserves ordering at a higher cost. The IVFFlat equivalent is ivfflat.iterative_scan with ivfflat.max_probes.
Raise hnsw.ef_search per transaction with SET LOCAL, never globally. A global setting leaks across your connection pool and quietly raises latency for every query, including the ones that do not need it.
Both are approximate. They fail differently, and the choice is mostly about memory and rebuild cost.
| Property | HNSW | IVFFlat |
|---|---|---|
| Recall at comparable latency | Higher, usually without tuning | Lower unless probes is raised |
| Build time and memory | Higher, uses maintenance_work_mem |
Lower, builds faster |
| Inserts after build | Absorbed into the graph | Decay until a rebuild |
| Query knobs | hnsw.ef_search (default 40) |
ivfflat.probes |
| Sizing guidance | m = 16 ,ef_construction = 64 to start |
lists near rows / 1000 up to 1M rows,sqrt(rows) above;probes nearsqrt(lists) |
| Fits | Frequent writes, latency-sensitive reads | Large, mostly static corpora with tight RAM |
For a web app with active writes, HNSW is the default answer. Its weakness is memory, and the arithmetic is unforgiving. A 1536-dimension float32 vector is 1,536 × 4 = 6,144 bytes before overhead, so one million chunks is about 6.1 GB of raw vectors, plus roughly 128 bytes of graph links per node at m = 16, plus page overhead. That working set needs to be in memory for single-digit millisecond reads.
There are two levers before you buy a bigger server. halfvec stores half-precision vectors at 3,072 bytes for the same dimensionality and supports expression indexes, so you can keep full precision on disk and index the narrowed cast. Binary quantization with binary_quantize gets a 1536-dimension vector down to 192 bytes, which suits a first-pass candidate filter where you rerank the top 200 rows with full precision.
Build the index after the data is loaded, not before. An HNSW index built on an empty table and then fed a million rows produces a worse graph than one built once over the existing rows, and CREATE INDEX CONCURRENTLY means you can do that on a live table without blocking writes. If Postgres logs the notice that the graph no longer fits in maintenance_work_mem, believe it: raise the setting for the build session or accept a much slower index build.
Without a measurement set, hybrid search is a preference. With one, it is a decision with numbers attached.
Build a golden set of 50 to 100 real queries from your own logs, each with the IDs of documents that a human would accept as correct. Then score four configurations against it: lexical only, vector only, hybrid with k = 60, and hybrid with a reranking model in front of it. Report three numbers:
ef_search, on your hardware, with the pool saturated.
Then check the branch attribution. Log which branch produced the clicked result for every query. If hybrid wins are concentrated in queries the lexical branch already handled, you have bought complexity without a user-visible gain. If hybrid wins cluster in natural-language queries and identifier queries where lexical failed, the vector branch is earning its memory.
Two budgets worth writing down before launch: p95 under 150 ms for a user-facing search box, and recall@10 within two points of the best configuration you tested. If you cannot hit both, lower ef_search and see what recall you give up before you add another system to the stack.
Embedding models get replaced, and the replacement is a schema migration whether people call it one or not. A new model means new dimensions, a new distance distribution, and a full re-embed of every chunk. Doing it in place takes search offline.
The sequence that works: add a second column and index, backfill in batches, dual-write both embeddings for new content, then flip a flag at read time and drop the old path once the new one has proven itself on the golden set.
ALTER TABLE chunks ADD COLUMN embedding_v2 halfvec(1536);
CREATE INDEX CONCURRENTLY chunks_hnsw_v2 ON chunks
USING hnsw (embedding_v2 halfvec_cosine_ops) WITH (m = 16, ef_construction = 64);
ALTER TABLE chunks ADD COLUMN model_v2 text;
Backfill in batches small enough that autovacuum keeps up, tracking progress with the model_v2 column so a failed run resumes instead of restarting. Run ANALYZE after the backfill, then rebuild statistics again after the cutover, because the planner's estimates for the new index start from nothing. The general pattern is the same one that applies to any large table change, covered in zero downtime database migrations.
EXPLAIN (ANALYZE, BUFFERS) on the actual hybrid query with a real tenant ID, and confirm the plan uses both indexes rather than falling back to a sequential scan.
Hybrid search inside PostgreSQL is a schema decision, a fusion function, and a measurement habit. The schema keeps content and embeddings in one transaction. The fusion function turns two incompatible scoring scales into one ranking without normalizing anything. The measurement habit is what stops you from shipping a vector branch that costs money and changes nothing.
Get the filtered-scan behaviour wrong and none of it matters, because your candidate pool will be four rows. Get it right and you have one database to back up, one permission model to defend, and a search that handles both RP-4471-B and "my order never arrived" with the same code path.
<span>
Database Engineering & RAG Architecture
</span>
<h3>
Looking to modernize your web search architecture?
</h3>
<p>
Veduis helps engineering teams design high-performance PostgreSQL pgvector pipelines, hybrid search workflows, and scalable RAG backends.
</p>
<a href="https://veduis.com/contact/">
Talk to a Database Specialist
</a>