BoilerShelf

Part 8 · Building AI products

Hybrid Search in PostgreSQL With pgvector and Full-Text Search

Run pgvector similarity and PostgreSQL full-text search in one query, fuse the results, and test ranking quality on real RAG queries.

B

BoilerShelf Team

November 16, 2026 · 5 min read

Rows of ivory and teal tiles converging into one stack.

Short answer: Use PostgreSQL hybrid search when a RAG query can contain both paraphrases and exact terms. Run pgvector similarity and PostgreSQL full-text search over the same authorized project, take the leading candidates from each search, and combine those ranks with reciprocal rank fusion. The combined score orders candidates. The answer step still needs a check that the documents support a response.

How do you set up pgvector and full-text indexes in one table?

Keep HNSW aligned with the cosine distance operator <=> and use a GIN index for the full-text column. The runnable schema uses three-dimensional vectors for fixtures. Replace vector(3) with your embedding dimension and use the same model and preprocessing for stored and query embeddings. Reject empty or zero vectors before cosine search. Use the same text configuration for indexing and querying.

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE chunks (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    project_id uuid NOT NULL,
    content text NOT NULL,
    embedding vector(3) NOT NULL,
    tsv tsvector GENERATED ALWAYS AS
        (to_tsvector('english'::regconfig, content)) STORED
);

CREATE INDEX chunks_embedding_hnsw
    ON chunks USING hnsw (embedding vector_cosine_ops);
CREATE INDEX chunks_tsv_gin ON chunks USING gin (tsv);
CREATE INDEX chunks_project_btree ON chunks (project_id);

Sources: pgvector documentation · PostgreSQL text-search controls · Full-text index types

How do you combine vector and full-text results with reciprocal rank fusion?

Bind $1 to the authorized project UUID, $2 to the query vector, $3 to the raw text query, and $4 to a calibrated maximum cosine distance or NULL to disable the cutoff. This example uses native positional parameters. Use your driver’s corresponding placeholder syntax and vector binding. The 20 candidates per branch, eight final rows and RRF constant 60 are starting values to tune on labeled queries.

WITH
semantic_candidates AS MATERIALIZED (
    SELECT id, embedding <=> $2::vector AS distance
    FROM chunks
    WHERE project_id = $1::uuid
    ORDER BY embedding <=> $2::vector
    LIMIT 20
),
semantic AS (
    SELECT id, row_number() OVER (ORDER BY distance, id) AS rank
    FROM semantic_candidates
    WHERE $4::float8 IS NULL OR distance <= $4::float8
),
keyword_candidates AS MATERIALIZED (
    SELECT c.id, ts_rank_cd(c.tsv, q.query) AS text_rank
    FROM chunks AS c
    CROSS JOIN (
        SELECT websearch_to_tsquery('english'::regconfig, $3::text) AS query
    ) AS q
    WHERE c.project_id = $1::uuid AND c.tsv @@ q.query
    ORDER BY text_rank DESC, c.id
    LIMIT 20
),
keyword AS (
    SELECT id, row_number() OVER (ORDER BY text_rank DESC, id) AS rank
    FROM keyword_candidates
),
rank_scores AS (
    SELECT id, 1.0 / (60 + rank) AS score FROM semantic
    UNION ALL
    SELECT id, 1.0 / (60 + rank) AS score FROM keyword
),
fused AS (
    SELECT id, sum(score) AS score
    FROM rank_scores
    GROUP BY id
)
SELECT c.id, c.content, f.score
FROM fused AS f
JOIN chunks AS c ON c.id = f.id
WHERE c.project_id = $1::uuid
ORDER BY f.score DESC, c.id
LIMIT 8;

The vector candidate query orders directly by distance in ascending order with a LIMIT, which makes the HNSW path eligible. It stores the candidates before the distance threshold and rank window run. The lexical branch ranks only matched rows with @@, then keeps its top candidates. Each branch contributes one reciprocal-rank score per candidate. UNION ALL and GROUP BY retain candidates from either branch and sum both contributions when they overlap. Tie-breaking by ID gives a stable order for a fixed candidate set.

How do filters, exact identifiers and empty results behave?

Keep the project predicate inside both candidate queries. Derive it from server-side authorization. With approximate pgvector indexes, restrictive filters can yield fewer matches than requested. pgvector 0.8.0 and later support iterative scans. Try SET LOCAL hnsw.iterative_scan = strict_order in a transaction, then measure recall and latency. For a small scoped dataset, exact search over the project subset may be a better plan. For larger deployments, test the real tenant distribution before choosing an indexing strategy.

Full-text search tokenizes input. The simple configuration changes dictionary behavior, while codes and punctuation still pass through the text parser. If a SKU or error code must match exactly, extract it at ingestion, normalize it according to your product’s rules, and query a dedicated indexed field. Keep that exact-match route project-scoped as well.

A vector index can return neighbors even when they are a poor fit. The cutoff in the semantic branch may remove them, while lexical matches remain eligible. A blank or stop-word-only lexical query can still yield vector results. If neither branch provides suitable evidence, return an empty retrieval set or abstain downstream. Treat retrieval failures separately from no-match results.

Sources: PostgreSQL text-search parsers · PostgreSQL EXPLAIN

How do you test whether hybrid search helps?

Compare vector-only, lexical-only and hybrid results over the same labeled queries and project scope. Measure evidence recall@k, ranking quality, p95 latency and the share of queries with no suitable evidence. Include exact identifiers, paraphrases and very selective projects. Use EXPLAIN (ANALYZE, BUFFERS) on representative data to inspect the actual plan. PostgreSQL may choose a sequential scan on small tables or a project-filtered exact path. Check recall against an exact baseline before tuning HNSW.

The SQL ran locally on PostgreSQL 18.6 with pgvector 0.8.7 in PGlite. Fixtures checked overlap at rank one, lexical-only and vector-only hits, project isolation, cutoff behavior, a stop-word-only query and an unset cutoff. Production index choice and latency still need measurement on the deployed database.

Frequently asked questions

Do I need a separate vector database for hybrid search?

Not necessarily. If your data is already in PostgreSQL, pgvector and full-text search can run in one query. A dedicated vector database becomes worth considering when your data or query volume outgrows one PostgreSQL instance, so measure before moving.

Why not add the vector score and the keyword score together?

They are on different scales, and the text rank changes with document length. Combine the positions in each list instead, which is what reciprocal rank fusion does.

What value of k should I use in reciprocal rank fusion?

60 is the commonly used default. Change it only when a test set shows a better value for your data.

Where to go next

BoilerShelf publishes FastAPI and Next.js source-code kits for RAG applications. See the RAG starter kits.

The idea in short

A question produces an embedding and a keyword query. PostgreSQL ranks passages by vector distance and by full-text rank separately, fuses the two rankings with reciprocal rank fusion, and returns the top passages, filtered by project or tenant inside the query.

Patterns covered: Hybrid retrieval · Reciprocal rank fusion · Generated tsvector column · Approximate nearest neighbour index

  • PostgreSQL
  • Pgvector
  • RAG
  • Search
  • Sql

New guides and boilerplates, by email

Leave your email and we will tell you when a new guide or boilerplate is published.

All guides