DEV Community

Cover image for PostgreSQL pgvector Hybrid Search for Web Apps
Veduis
Veduis

Posted on Originally published at veduis.com

PostgreSQL pgvector Hybrid Search for Web Apps

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.

Paper model diagram of a data table with two attached index racks, a grid of keyword cards and a column of coiled vector rings, connected to one collection tray

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.

Why Combine Full-Text Search with Vector Embeddings in Web Apps?

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:

  • One transaction. Content and embedding commit together. No dual-write queue, no reconciliation job, no orphan vectors.
  • Relational filters run in the same plan. Tenant isolation, permissions, soft deletes, and publication state are WHERE clauses, not a separate metadata store you must keep in sync with the index.
  • One backup, one replica set, one connection pool. The operational surface stays what it already was.

How Do You Implement Hybrid Search Using pgvector in PostgreSQL?

The mechanism is two ranked lists and a fusion function. Everything else is schema and tuning.

  1. Lexical branch. Convert the user's text with websearch_to_tsquery, match against a generated tsvector column, and rank with ts_rank_cd. Take the top 50.
  2. Vector branch. Embed the same text, order by cosine distance with the <=> operator, and take the top 50.
  3. Fusion. Convert each list's ordering into ranks and combine them with reciprocal rank fusion, then collapse chunk hits to document level and return 10 results.

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.

What Schema Do You Actually Need for Chunk-Level Retrieval?

Paper model diagram of an overlapping document strip sliding into one filing box with a tabbed lid

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);
Enter fullscreen mode Exit fullscreen mode

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.

How Does Reciprocal Rank Fusion Improve Search Ranking Accuracy?

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))
Enter fullscreen mode Exit fullscreen mode

with k typically set to 60, as in the original reciprocal rank fusion paper.

Paper model diagram of two card stacks feeding a central interleaving mechanism that produces one merged sorted stack Each branch contributes a value between 1/61 and 1/110 for its top ranks, so a document that lands near the top of both lists outranks a document that is first in one and absent from the other. The constant compresses the head of each list, which is what makes fusion stable across query types.

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;
Enter fullscreen mode Exit fullscreen mode

Four things about that query are easy to get wrong:

  • DISTINCT ON is load-bearing. Without document-level collapse, ten chunks from the same handbook fill the entire result page. Users search for documents, not passages.
  • MATERIALIZED matters. On Postgres 12 and later, a non-materialized CTE can be inlined, which changes how many rows each branch computes. Force the fence and keep the LIMIT inside it.
  • One empty branch is normal. A query with no lexical matches still returns vector results through the 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. At k = 60 the top ranks are compressed. Below about 20, the first result in either branch starts to dominate everything else.

Where Reciprocal Rank Fusion Fails in Practice

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.

Why Filtered Vector Search Is Where Query Plans Go Wrong

This is the part that surprises people, and it has nothing to do with which index you picked.

Paper model diagram of layered graph contours with a highlighted path threading through a perforated filter plate into an output tray

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:

  • Iterative scans (pgvector 0.8.0 and later). 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.
  • Partial indexes for a handful of values. If you serve five large tenants rather than five thousand small ones, a partial HNSW index per tenant is a legitimate design, at the cost of one index per tenant.
  • Partitioning by tenant. Beyond a few million chunks, partition the table and index each partition. The planner prunes the rest, and vacuum stops fighting one giant graph.

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.

HNSW or IVFFlat: How Do You Choose the Index?

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 near sqrt(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.

How Do You Prove Hybrid Search Beats Keyword Search?

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:

  • Recall@10. The share of queries where at least one acceptable document appears in the first ten results. This is the primary metric, because a ranking that buries the right answer at position 11 is a failure for a search box.
  • MRR@10. Mean reciprocal rank, which rewards putting the right document first rather than tenth.
  • p95 latency. Measured at your production 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.

How Do You Change Embedding Models Without Downtime?

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;
Enter fullscreen mode Exit fullscreen mode

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.

Rollout Plan: From Two Systems to One

  1. Size the working set first. Chunks × bytes per vector, plus graph overhead, must fit in RAM with headroom for Postgres itself. If the number does not fit, this is a partitioning or quantization conversation, not a hardware conversation.
  2. Add the columns and backfill before touching the query path. Ship the embedding column, the generated tsvector, and a nightly backfill. Nothing changes for users yet.
  3. Build indexes concurrently, then tune with real queries. Use 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.
  4. Cut over behind a flag and keep the old path. Run both configurations on the golden set, log branch attribution, and only remove the dedicated search system after a month of comparable recall and better p95.

Conclusion

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 &amp; 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>
Enter fullscreen mode Exit fullscreen mode

Top comments (0)