Postgres Hybrid Search with pgvector: Resolving Vector Search Limitations Using HNSW and tsvector

When building Retrieval-Augmented Generation (RAG) pipelines, developers frequently encounter a frustrating reality: pure vector search often fails spectacular on real-world user inputs. A simple query like ORDER BY embedding <=> $1 LIMIT 5 demos effortlessly in laboratory settings, but breaks down immediately in production when users input exact identifiers, error codes like ERR_MODULE_NOT_FOUND, part numbers, or specific brand names.

This article shares practical insights gained from upgrading a production retrieval pipeline to hybrid search on PostgreSQL 17 using pgvector 0.8.0. By fusing vector distance with PostgreSQL full-text search using Reciprocal Rank Fusion (RRF), you can achieve high recall and precision—all while keeping your infrastructure simple and hosted inside your existing application database.

Why Pure Vector Search Fails on Exact Matches

Vector embeddings project textual data into dense mathematical vector spaces designed to capture semantic meaning rather than verbatim token sequences. While this works brilliantly for conceptual queries (e.g., matching "how do I cancel my plan" with "Subscription termination"), it exhibits severe blind spots for low-entropy, highly specific keywords.

When a user searches for an exact error string like ERR_MODULE_NOT_FOUND, the embedding model converts this string into an average representation near general programming terms like "import", "module", and "error". Consequently, the vector search step returns five generic troubleshooting paragraphs instead of the single document containing that exact error string verbatim.

The Three Vulnerable Query Shapes

Pure vector retrieval consistently breaks across three main query patterns:

1. Literal Error Codes: System stack traces, exceptions, and unique identifiers.
2. Product SKUs & Numbers: Invoice numbers, tracking codes, and transactional IDs.
3. Rare Proper Nouns: Specialized acronyms, customer organization names, or niche terms.

In all three cases, the critical search signal relies on low-frequency literal tokens. Averaging these rare tokens into a high-dimensional (e.g., 1536-dimension) chunk embedding dilutes the exact token match into low-signal background noise.

Furthermore, large chunk sizes exacerbate the problem. A 1,500-token text chunk produces a single vector that represents the average meaning of the entire passage. A single decisive sentence or SKU code buried inside that passage barely shifts the resulting vector. Reducing chunk sizes to 200–400 tokens with slight overlap dramatically improves retrieval recall regardless of the underlying embedding model.

The Complementary Nature of Full-Text Search

PostgreSQL full-text search (tsvector and tsquery) presents the exact opposite characteristics:

• Vector Search: High semantic comprehension, zero literal keyword guarantee.
• Full-Text Search: High literal keyword precision, zero semantic comprehension (e.g., websearch_to_tsquery('english', 'how do I stop being billed') cannot match a section titled "Subscription termination" unless an explicit synonym or stem exists).

Combining both systems into a hybrid search pipeline cancels out the weaknesses of each approach.

Understanding pgvector 0.8.0 Capabilities

pgvector adds vector storage, specialized distance operators, and Approximate Nearest Neighbor (ANN) indexes to PostgreSQL. As of version 0.8.0, it supports four distinct storage types:

• vector: Standard 4-byte single-precision floats.
• halfvec: 2-byte half-precision floats (added in pgvector 0.7.0).
• bit: Binary quantization.
• sparsevec: Sparse vector storage for high-dimensional keyword representations.

Distance Operators & Index Classes

Selecting the correct distance operator is crucial. Using an incorrect operator will cause PostgreSQL to perform an unindexed sequential table scan without raising any explicit syntax errors:

For standard normalized embeddings, such as those generated by OpenAI text-embedding-3-small or models accessed via unified gateways like n1n.ai, Cosine distance (<=>) paired with vector_cosine_ops is the standard choice.

Schema Design for Hybrid Search in Postgres

To build a hybrid search engine, create a standard PostgreSQL table that stores the document chunk, its vector representation, and a generated tsvector column for full-text search.

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE chunks (
  id          bigserial PRIMARY KEY,
  document_id bigint NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
  body        text NOT NULL,
  embedding   vector(1536) NOT NULL,
  tsv         tsvector GENERATED ALWAYS AS (to_tsvector('english', body)) STORED
);

-- HNSW Index for Semantic Vector Search
CREATE INDEX chunks_embedding_hnsw
  ON chunks USING hnsw (embedding vector_cosine_ops)
  WITH (m = 16, ef_construction = 64);

-- GIN Index for Lexical Full-Text Search
CREATE INDEX chunks_tsv_gin ON chunks USING gin (tsv);

Using GENERATED ALWAYS AS ... STORED guarantees that the full-text search index remains perfectly in sync with the body text without requiring application-level triggers.

Fusing Results with Reciprocal Rank Fusion (RRF)

Combining raw vector similarity scores with full-text search scores is mathematically problematic. Cosine distance yields values typically bounded between 0 and 2, while full-text ranking algorithms like ts_rank_cd output unbounded floating-point values. Normalizing and weighting these raw scores requires arbitrary magic constants that break under schema changes.

Reciprocal Rank Fusion (RRF) bypasses score normalization by evaluating only the positional rank of each document across the different retrieval arms. The RRF score for a document $d$ across a set of retrieval arms $M$ is defined as:

$RRF\_Score(d) = \sum_\{m \in M\} \frac\{1\}\{k + r_m(d)\}$

Where $r_m(d)$ is the 1-based ordinal rank of document $d$ in arm $m$, and $k$ is a smoothing constant (conventionally set to $60$).

The Single-Query RRF Implementation

We execute semantic and keyword searches inside separate Common Table Expressions (CTEs), rank the top 50 candidates from each arm, and fuse them using a LEFT JOIN pattern:

WITH semantic AS (
  SELECT id, RANK() OVER (ORDER BY embedding <=> $1::vector) AS rank
  FROM chunks
  ORDER BY embedding <=> $1::vector
  LIMIT 50
),
keyword AS (
  SELECT c.id, RANK() OVER (ORDER BY ts_rank_cd(c.tsv, q) DESC) AS rank
  FROM chunks c, websearch_to_tsquery('english', $2) q
  WHERE c.tsv @@ q
  ORDER BY ts_rank_cd(c.tsv, q) DESC
  LIMIT 50
)
SELECT c.id, c.body,
       COALESCE(1.0 / (60 + s.rank), 0.0)
     + COALESCE(1.0 / (60 + k.rank), 0.0) AS score
FROM chunks c
LEFT JOIN semantic s ON s.id = c.id
LEFT JOIN keyword  k ON k.id = c.id
WHERE s.id IS NOT NULL OR k.id IS NOT NULL
ORDER BY score DESC
LIMIT 10;

Critical RRF Implementation Notes

1. LEFT JOIN & COALESCE: Allows a document to win top rank overall even if it appears in only one search arm. An exact SKU match ranking #1 in full-text search will survive and rank near the top despite missing completely from vector results.
2. websearch_to_tsquery: Handles unformatted user inputs, supporting quotes and - exclusions gracefully without throwing syntax errors like raw to_tsquery does.
3. Candidate Pool Sizes: Retrieving 50 candidates per arm prior to filtering down to 10 fused results ensures sufficient overlap for RRF to function effectively.

HNSW vs. IVFFlat: Indexing Strategies in Production

pgvector provides two primary Approximate Nearest Neighbor (ANN) index types:

For production application search, HNSW is the recommended default. IVFFlat should generally be reserved for resource-constrained memory environments where long build times cannot be tolerated.

Tackling the HNSW Filtering Issue with iterative_scan

When applying metadata filters (WHERE clauses) to vector queries, standard HNSW index traversal can lead to zero or incomplete result sets. This occurs because the index collects ef_search nearest neighbors before evaluating the WHERE condition.

If a WHERE condition filters for a specific tenant ID that owns only 0.1% of the database rows, all default candidates selected by the graph traversal might be discarded by the filter step, returning fewer rows than requested by LIMIT.

The Solution in pgvector 0.8.0

pgvector 0.8.0 introduces hnsw.iterative_scan, which forces the graph traversal to continue scanning until enough candidate rows satisfy the filtering condition:

BEGIN;
-- Enable iterative scanning for filtered vector search
SET LOCAL hnsw.iterative_scan = 'relaxed_order';
SET LOCAL hnsw.max_scan_tuples = 20000;
SET LOCAL hnsw.ef_search = 100;

-- Execute hybrid query here...

COMMIT;

• 'relaxed_order': Prioritizes throughput by returning rows faster while slightly relaxing exact distance ordering.
• 'strict_order': Guarantees exact vector distance ordering during iterative evaluation.
• hnsw.max_scan_tuples: Prevents runaway queries by capping the total number of index nodes scanned.

Production Performance & Operations Engineering

When scaling RAG architectures with large language model aggregators like n1n.ai, tuning the underlying Postgres database is critical for maintaining low end-to-end latency.

1. Managing Dimension Limits & halfvec

Standard pgvector indexed columns have a hard 2,000-dimension cap for classic vector indexes. Embedding models producing 3,072 dimensions (such as text-embedding-3-large) cannot be indexed directly with standard vector types.

To resolve this constraint, cast the embeddings to halfvec(3072) and utilize halfvec_cosine_ops, which supports indexed dimensions up to 4,000 while cutting memory footprint by 50%:

ALTER TABLE chunks ADD COLUMN embedding_half halfvec(3072);

CREATE INDEX chunks_half_hnsw ON chunks 
USING hnsw (embedding_half halfvec_cosine_ops);

2. Speeding Up Index Build Times

Default PostgreSQL settings allocate minimal memory to background maintenance jobs. When building HNSW indexes on hundreds of thousands of rows, adjust session limits to maximize CPU parallelization:

SET maintenance_work_mem = '2GB';
SET max_parallel_maintenance_workers = 4;

3. TOAST Storage Overhead and Row Width

Storing a 1536-dimension float vector adds ~6 KB of data per row. PostgreSQL moves columns exceeding ~2 KB to out-of-line TOAST (The Oversized-Attribute Storage Technique) storage, resulting in additional disk I/O operations for every table access.

Keep your chunk tables slim by storing heavy metadata (document titles, author profiles, permissions) in separate tables and fetching them via primary key joins after the RRF ranking step.

4. Embedding Generation Latency

In production, network latency incurred while generating query embeddings via API roundtrips frequently accounts for the majority of overall retrieval duration. Leveraging optimized API aggregation infrastructure like n1n.ai ensures high reliability and low latency for embedding operations before the database executes SQL queries.

Frequently Asked Questions (FAQ)

Do I need a dedicated vector database instead of PostgreSQL?

Not for typical production applications managing millions of records. Retaining vector data inside PostgreSQL simplifies backup procedures, leverages connection pooling, ensures ACID transactional consistency, and allows developers to run complex SQL joins across user permissions and business logic within a single query.

Which distance operator should I use with OpenAI embeddings?

Use Cosine distance (<=>) along with an HNSW index created using vector_cosine_ops. Ensure that the index operator class matches your ORDER BY query operator; otherwise, PostgreSQL will fall back to an unindexed sequential scan.

Does hybrid search require multiple vector embeddings per text chunk?

No. Hybrid search requires a single vector column for semantic distance calculations and a standard tsvector column for keyword evaluation.

How many candidates should be retrieved before passing to an LLM?

Select approximately 50 candidates from each search arm, fuse them via RRF, and send the top 5 to 10 items to your model prompt. Injecting excessive context windows often decreases model reasoning performance due to attention dilution.

Summary Checklist

1. Combine Keyword & Vector Search: Use RRF to fuse semantic (pgvector) and keyword (tsvector) search scores.
2. Tune Index Parameters: Default to HNSW indexes with vector_cosine_ops for normalized vectors.
3. Prevent Filter Drops: Enable hnsw.iterative_scan = 'relaxed_order' when applying metadata filter conditions.
4. Optimize Overhead: Use high-speed infrastructure such as n1n.ai to minimize external embedding model latency.

Get a free API key at n1n.ai