Postgres Semantic Search

PostgreSQL Semantic Search

Quick Start

1. Setup

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    content TEXT NOT NULL,
    embedding vector(1536)  -- 1536-dim embedding
    -- Or: embedding halfvec(3072)  -- 3072-dim embedding (halfvec = 50% memory)
);

2. Basic Semantic Search

SELECT id, content, 1 - (embedding <=> query_vec) AS similarity
FROM documents
ORDER BY embedding <=> query_vec
LIMIT 10;

3. Add Index (> 10k documents)

CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops);

Docker Quick Start

# pgvector with PostgreSQL 17
docker run -d --name pgvector-db \
  -e POSTGRES_PASSWORD=postgres \
  -p 5432:5432 \
  pgvector/pgvector:pg17

# Or PostgreSQL 18 (latest)
docker run -d --name pgvector-db \
  -e POSTGRES_PASSWORD=postgres \
  -p 5432:5432 \
  pgvector/pgvector:pg18

# ParadeDB (includes pgvector + pg_search + BM25)
docker run -d --name paradedb \
  -e POSTGRES_PASSWORD=postgres \
  -p 5432:5432 \
  paradedb/paradedb:latest  # `latest` is convenient for quick-start; pin to e.g. paradedb/paradedb:pg17 for reproducible builds

Connect: psql postgresql://postgres:postgres@localhost:5432/postgres

Cheat Sheet

Distance Operators

embedding <=> query  -- Cosine distance (1 - similarity)
embedding <-> query  -- L2/Euclidean distance
embedding <#> query  -- Negative inner product

Common Queries

-- Top 10 similar (cosine)
SELECT * FROM docs ORDER BY embedding <=> $1 LIMIT 10;

-- With similarity score
SELECT *, 1 - (embedding <=> $1) AS similarity FROM docs ORDER BY embedding <=> $1 LIMIT 10;

-- With threshold (parenthesize the distance — keeps it clear and precedence-safe)
SELECT * FROM docs WHERE (embedding <=> $1) < 0.3 ORDER BY embedding <=> $1 LIMIT 10;

-- Preload index (run on startup)
SELECT 1 FROM docs ORDER BY embedding <=> $1 LIMIT 1;

Index Quick Reference

-- HNSW (recommended)
CREATE INDEX ON docs USING hnsw (embedding vector_cosine_ops);

-- With tuning
CREATE INDEX ON docs USING hnsw (embedding vector_cosine_ops)
WITH (m = 24, ef_construction = 200);

-- Query-time recall
SET hnsw.ef_search = 100;

-- Iterative scan for filtered queries (pgvector 0.8+)
SET hnsw.iterative_scan = relaxed_order;
SET ivfflat.iterative_scan = on;

Decision Trees

Choose Search Method

Query type?
├─ Conceptual/meaning-based → Pure vector search
├─ Exact terms/names → Pure keyword search (FTS)
├─ Fuzzy/typo-tolerant → pg_trgm trigram similarity
├─ Autocomplete/prefix → pg_trgm + prefix index
├─ Substring (LIKE/ILIKE) → pg_trgm GIN index
└─ Mixed/unknown → Hybrid search
    ├─ Simple setup → FTS + RRF (no extra extensions)
    ├─ Better ranking → BM25 + RRF (pg_search extension)
    └─ Full-featured → ParadeDB (Elasticsearch alternative)

Choose Index Type

Document count?
├─ < 10,000 → No index needed
├─ 10k - 1M → HNSW (best recall)
└─ > 1M → IVFFlat (less memory) or HNSW

Choose Vector Type

Choose by dimensions, not by provider — the column type only depends on embedding size and pgvector's HNSW index limits.

Embedding dimensions (N)?
├─ N ≤ 2000  → vector(N)   — HNSW indexable directly
├─ 2000 < N ≤ 4000 → halfvec(N) — vector(N)'s HNSW limit is 2000; halfvec extends to 4000
└─ N > 4000  → vector(N) without HNSW, or quantize via dimensionality reduction

Common embedding dimensions are 1536 and 3072, but sizes vary by provider and model — check the provider's docs for the embedding you're using.

For multilingual / non-English content, prefer multilingual-tuned embedding models (look for "multilingual" in the model name). Models tuned only on English may handle compound words and inflection poorly.

Storage vs. index trick for 2000 < N ≤ 4000: keep the column as vector(N) (full float4, useful for future re-embedding or re-ranking experiments) and only cast at index creation and query time. This preserves precision on disk while staying within HNSW's dimension limit.

CREATE INDEX ON docs USING hnsw ((embedding::halfvec(3072)) halfvec_cosine_ops);
-- Query must cast identically so the planner picks the index:
SELECT * FROM docs ORDER BY embedding::halfvec(3072) <=> $1 LIMIT 10;

If storage is tight or you never plan to re-embed, use halfvec(N) as the column type directly.

Measure before adopting

Every optimization in this skill (hybrid fusion, reranking, query expansion, embedding-model swaps) can regress on a specific corpus. Vendor and paper benchmarks are usually English, general-domain. Real counter-examples observed in production:

• Query expansion (HyDE) regressing Hit@5 by tens of points on a domain corpus.
• A widely recommended reranker regressing Hit@5 double-digits on multilingual text.

Rule: build a domain eval set (evaluation.md), then A/B each change. Adopt with ≥ +3 pp Hit@5 and p95 latency within budget; reject otherwise.

Operators

SQL Functions

Semantic Search

• match_documents(query_vec, threshold, limit) - Basic search
• match_documents_filtered(query_vec, metadata_filter, threshold, limit) - With JSONB filter
• match_chunks(query_vec, threshold, limit) - Search document chunks

Fuzzy Search (pg_trgm)

• fuzzy_search_trigram(query_text, threshold, limit) - Trigram similarity search
• autocomplete_search(prefix, limit) - Prefix + fuzzy autocomplete
• hybrid_search_fuzzy_semantic(query_text, query_vec, limit, rrf_k) - Fuzzy + vector RRF
• weighted_fts_search(query_text, language, limit) - FTS with title/content weighting

Hybrid Search (FTS)

• hybrid_search_fts(query_vec, query_text, limit, rrf_k, language) - FTS + RRF
• hybrid_search_weighted(query_vec, query_text, limit, sem_weight, kw_weight) - Linear combination
• hybrid_search_fallback(query_vec, query_text, limit) - Graceful degradation

Hybrid Search (BM25)

• hybrid_search_bm25(query_vec, query_text, limit, rrf_k) - BM25 + RRF
• hybrid_search_bm25_highlighted(...) - With snippet highlighting
• hybrid_search_chunks_bm25(...) - For RAG with chunks

Re-ranking (Optional)

Two-stage retrieval improves precision: fast recall → precise rerank with a cross-encoder. Use when results need higher precision and you have <50 candidates after initial retrieval.

Key rule: rerankers must be wrapped so a failure (missing key, HTTP error, timeout) returns null and the caller falls back to original retrieval order — never let a reranker outage break search.

For provider comparison, generic Promise<T | null> wrapper, and self-hosted options, see reranking.md.

Multilingual / non-English content tips

When the corpus is non-English (Finnish, German, French, Spanish, etc.):

• FTS language config: pass the matching language to to_tsvector(language, text) to apply the built-in snowball stemmer (e.g., 'finnish' handles opiskelija → opiskelij). For mixed-language corpora, use 'simple' and rely on prefix/trigram fallbacks instead.
• Combine stemmer + unaccent for accent-insensitive matching ("café" matches "cafe"). See hybrid-search.md → Custom FTS configuration for the 3-step DDL pattern.
• Prefix tsquery for languages with rich inflection (no full morphology engine required):

  CREATE OR REPLACE FUNCTION prefix_tsquery(p text)
  RETURNS tsquery LANGUAGE sql IMMUTABLE AS $$
    SELECT to_tsquery('simple',
      string_agg(word || ':*', ' & '))
    FROM regexp_split_to_table(lower(regexp_replace(p, '[^\w\s-]', ' ', 'g')), '\s+') AS word
    WHERE length(word) >= 2
  $$;

Matches kartta, karttaa, karttoja from a single kartta:* token.

• Compound-word fallback: pair semantic search with pg_trgm similarity to catch compound-word misses (e.g., a query for "ammattikorkea" should still find "ammattikorkeakoulu").
• BM25 stemmer in ParadeDB: tokenize with { "type": "default", "stemmer": "<language>" } — a raw tokenizer only matches full fields.
• Multilingual embeddings: prefer models explicitly trained on your target language(s). English-only embeddings often miss inflected forms and compound words. The gap can be large — multilingual-tuned embeddings have been observed to beat general-purpose English-tuned ones by 10+pp Hit@5 on non-English retrieval. Benchmark your specific language + domain before committing.
• Cross-language RRF fusion for monolingual corpora: when the corpus is

one language and queries arrive in many, run two hybrid passes per off-language query (original-language embedding + translated-language embedding, same FTS text) and RRF-merge. Recovers domain terms that cross-lingual embeddings collapse. See hybrid-search.md → Cross-language RRF fusion pattern.

• Per-language indexing for multilingual content: when translated

content exists, add language_code to the chunk table (default to the original language so existing rows backfill), include it in the uniqueness constraint, and scope ingest writes/deletes to one language. Search stays language-agnostic; native-language queries hit native embeddings directly.

  ALTER TABLE chunks ADD COLUMN language_code TEXT NOT NULL DEFAULT 'en';
  ALTER TABLE chunks DROP CONSTRAINT chunks_doc_chunk_unique;
  ALTER TABLE chunks ADD CONSTRAINT chunks_doc_chunk_lang_unique
    UNIQUE (doc_id, chunk_index, language_code);
  CREATE INDEX chunks_doc_lang_idx ON chunks (doc_id, language_code);

References

• fuzzy-search.md - pg_trgm, fuzzy matching, LIKE/ILIKE, autocomplete, advanced FTS
• paradedb.md - ParadeDB full-text search (Elasticsearch alternative)
• vector-types.md - vector vs halfvec, dimensions, storage
• indexing.md - HNSW, IVFFlat, GIN parameters
• hybrid-search.md - FTS, BM25, RRF algorithms
• performance.md - Cold-start, memory, HNSW vs IVFFlat
• evaluation.md - Eval-set construction, Hit@K / MRR, adoption thresholds, reranker/expansion benchmarking
• reranking.md - Two-stage retrieval, graceful fallback, when rerankers regress

Scripts

• setup.sql - Extension and table setup
• semantic_search.sql - Semantic search functions
• hybrid_search_fts.sql - FTS hybrid functions
• hybrid_search_bm25.sql - BM25 hybrid functions
• fuzzy_search.sql - pg_trgm fuzzy search, autocomplete, weighted FTS
• indexes.sql - Index creation scripts
• embeddings.ts - Embedding generation helpers (TypeScript)

Common Patterns

TypeScript Integration (Supabase)

// Semantic search
const { data } = await supabase.rpc('match_documents', {
  query_embedding: embedding,
  match_threshold: 0.7,
  match_count: 10
});

// Hybrid search
const { data } = await supabase.rpc('hybrid_search_fts', {
  query_embedding: embedding,
  query_text: userQuery,
  match_count: 10,
  rrf_k: 60,
  fts_language: 'simple'
});

Drizzle ORM

import { sql } from 'drizzle-orm';

const results = await db.execute(sql`
  SELECT * FROM match_documents(
    ${embedding}::vector(1536),
    0.7,
    10
  )
`);

Troubleshooting

Compatibility

• pgvector: 0.8+ recommended (iterative scans, halfvec). Check pgvector releases.
• pg_search: Check ParadeDB releases for latest.
• PostgreSQL: 17+ recommended. pgvector supports 13-18.

Related Skills

For ParadeDB-specific questions, always apply the Documentation Fetch Policy in references/paradedb.md — live docs at https://docs.paradedb.com/llms-full.txt are the authoritative source.

External Documentation

Core

• pgvector GitHub - Official extension, latest features
• PostgreSQL FTS - Built-in full-text search

Embedding providers

• OpenAI Embeddings - model list + dimensions
• Voyage Embeddings - includes multilingual model
• Cohere Embed - model list
• HuggingFace Hub - open-weight embeddings

Reranker providers

• Cohere Rerank
• Voyage Rerank
• Zerank
• Sentence Transformers - self-hosted cross-encoders

Hosting / extensions

• Supabase Vector Guide - Supabase-specific integration
• ParadeDB pg_search - BM25 extension documentation
• ParadeDB AI Docs - Fetch for latest ParadeDB API (always current)

Evaluation & Benchmarking

Vendor and paper benchmarks are usually English and general-domain. They do not reliably predict performance on multilingual or domain-specific corpora, so every non-trivial change (reranker, embedding swap, fusion weights, query rewriter) needs a domain-owned eval set.

Build an eval set (LLM-generated)

Fastest way to get 200–500 questions without manual labeling:

1. Sample N chunks from the current corpus. Filter to chunks with ≥ ~200 characters of meaningful text, and restrict to documents still in use. 2. For each chunk, prompt a cheap model (GPT-4o-mini, Claude Haiku): "Generate a natural \[language\] question that this text answers." 3. Store as CSV: question, expected_doc_id, expected_chunk_id.

Rule of thumb: < 50 Q is noise; 200–500 Q is a useful production signal;

1000 Q buys little extra discriminative power.

Metrics

• Hit@K for K = 1, 5, 10 — does the expected chunk appear in the top-K?

Hit@5 is the usual headline.

• MRR — mean reciprocal rank of the first correct hit (rewards earlier

ranks more than Hit@10).

• Latency p50 / p95 — measure separately. Warm runs and cold runs

diverge sharply on HNSW; report both or clearly label which you measured.

Bias warning (read this before trusting numbers)

When questions are LLM-generated from the same chunks that count as the correct answer, bi-encoder similarity between query and expected chunk is unnaturally high. This has two consequences:

1. Pure-vector and RRF baselines look better than they will in production. 2. Rerankers and query rewriters look worse than they will in production — they shuffle candidates that the biased similarity already ranked near- optimally.

Mitigations:

• Mix in a smaller set of manually written, live-style questions (≥ 30–50).
• Re-sample chunks periodically — drift in the corpus invalidates the set.
• Compare deltas, not absolute scores, across runs on the same set.

Adoption thresholds

Use these as defaults when deciding whether a change ships:

Same scale works for MRR deltas (use ± 0.01, 0.01–0.03, ≥ 0.03).

Running the eval

Minimal pseudocode:

const results = [];
for (const { question, expected_chunk_id } of evalSet) {
  const t0 = performance.now();
  const hits = await search(question, { limit: 10 });
  const latencyMs = performance.now() - t0;
  const rank = hits.findIndex((h) => h.chunk_id === expected_chunk_id) + 1;
  results.push({ rank, latencyMs });
}

const hitAt = (k: number) =>
  results.filter((r) => r.rank > 0 && r.rank <= k).length / results.length;
const mrr =
  results.reduce((s, r) => s + (r.rank ? 1 / r.rank : 0), 0) / results.length;

Always run the cold query first and discard it (HNSW cold-start dominates). Repeat the set 2–3 times, report the median per-query latency.

Typical gotchas

• Chunk-ID type mismatch: bigint columns come back as string from some

drivers; use String(a) === String(b) when matching ranks.

• Filtered queries: if search applies filters (date, category, access),

apply the same filters when generating the eval set. Otherwise Hit@K collapses for reasons unrelated to ranking quality.

• Warmup cron: if the production service keeps HNSW warm via a cron,

do the same in the benchmark — or you are benchmarking a state users never see.

Fuzzy Search & Text Matching Guide

PostgreSQL native fuzzy search without external extensions (except built-in ones).

pg_trgm (Trigram Similarity)

The most important extension for fuzzy/typo-tolerant search. Built into PostgreSQL.

Setup

CREATE EXTENSION IF NOT EXISTS pg_trgm;

How Trigrams Work

Text is split into 3-character sequences:

"hello" → {"  h", " he", "hel", "ell", "llo", "lo "}

Two strings are compared by the overlap of their trigram sets.

Operators

-- Basic similarity (default threshold 0.3)
SELECT * FROM documents WHERE title % 'PostgreSQL';

-- With explicit threshold
SELECT * FROM documents
WHERE similarity(title, 'PostgreSQL') > 0.4
ORDER BY similarity(title, 'PostgreSQL') DESC;

-- Word similarity (better for partial matches)
SELECT * FROM documents
WHERE 'database' <% title
ORDER BY word_similarity('database', title) DESC;

Threshold Tuning

-- Set global similarity threshold (default 0.3)
SET pg_trgm.similarity_threshold = 0.3;

-- Lower = more results, more typo tolerance
SET pg_trgm.similarity_threshold = 0.1;

-- Higher = fewer results, more precise
SET pg_trgm.similarity_threshold = 0.5;

Recommended thresholds:

Indexes for pg_trgm

-- GIN index (recommended for most cases)
CREATE INDEX ON documents USING gin (title gin_trgm_ops);
CREATE INDEX ON documents USING gin (content gin_trgm_ops);

-- GiST index (supports ORDER BY similarity, KNN)
CREATE INDEX ON documents USING gist (title gist_trgm_ops);

GIN vs GiST for trigrams:

LIKE / ILIKE Optimization

pg_trgm indexes accelerate LIKE and ILIKE queries automatically:

-- These use the GIN trigram index (no seq scan!)
SELECT * FROM documents WHERE title ILIKE '%postgres%';
SELECT * FROM documents WHERE content LIKE '%search%';

-- Prefix search (also uses B-tree with text_pattern_ops)
SELECT * FROM documents WHERE title LIKE 'Post%';

Without pg_trgm: ILIKE '%term%' requires a sequential scan. With pg_trgm GIN index: Uses index scan, dramatically faster on large tables.

fuzzystrmatch Extension

For phonetic and edit-distance matching:

CREATE EXTENSION IF NOT EXISTS fuzzystrmatch;

-- Levenshtein distance (edit distance)
SELECT * FROM documents
WHERE levenshtein(title, 'PostgreSQL') <= 2
ORDER BY levenshtein(title, 'PostgreSQL');

-- Soundex (English phonetic)
SELECT * FROM documents WHERE soundex(title) = soundex('Postgres');

-- Metaphone (better phonetic matching)
SELECT * FROM documents WHERE metaphone(title, 10) = metaphone('Postgres', 10);

When to use:

• levenshtein: Known max edit distance (e.g., 1-2 typos). No index support — slow on large tables.
• soundex/metaphone: English names, "sounds like" queries. Can index the result.
• Prefer pg_trgm for general fuzzy search — it has index support.

unaccent Extension

Removes accents for accent-insensitive search:

CREATE EXTENSION IF NOT EXISTS unaccent;

-- Direct usage
SELECT unaccent('café résumé');  -- 'cafe resume'

-- Combined with FTS
SELECT * FROM documents
WHERE to_tsvector('simple', unaccent(content)) @@ plainto_tsquery('simple', unaccent('café'));

-- Index for accent-insensitive search
CREATE INDEX ON documents USING gin (to_tsvector('simple', unaccent(content)));

Autocomplete Patterns

Prefix + Trigram (Recommended)

-- Fast autocomplete: prefix match first, then fuzzy fallback
CREATE OR REPLACE FUNCTION autocomplete_search(
    search_prefix TEXT,
    max_results INT DEFAULT 10
)
RETURNS TABLE (id INT, title TEXT, score FLOAT)
LANGUAGE sql STABLE AS $$
    -- Exact prefix matches first, then fuzzy matches
    (
        SELECT id, title, 1.0 AS score
        FROM documents
        WHERE title ILIKE search_prefix || '%'
        ORDER BY title
        LIMIT max_results
    )
    UNION ALL
    (
        SELECT id, title, similarity(title, search_prefix) AS score
        FROM documents
        WHERE title % search_prefix
          AND title NOT ILIKE search_prefix || '%'
        ORDER BY similarity(title, search_prefix) DESC
        LIMIT max_results
    )
    LIMIT max_results;
$$;

Indexes for Autocomplete

-- B-tree for prefix matches
CREATE INDEX ON documents (title text_pattern_ops);

-- GIN trigram for fuzzy fallback
CREATE INDEX ON documents USING gin (title gin_trgm_ops);

Advanced FTS Patterns

Prefix Matching for Agglutinative Languages

For Finnish, Turkish, Hungarian, Estonian and other agglutinative languages where 'simple' config doesn't stem, use prefix matching (:* operator) so "xylitol" matches "xylitolin", "xylitolia", etc.

websearch_to_tsquery doesn't support :*, so build the tsquery manually:

-- Convert 'fluoridi ksylitoli' → 'fluoridi:* & ksylitoli:*'
CREATE OR REPLACE FUNCTION prefix_tsquery(p_config regconfig, p_text TEXT)
RETURNS tsquery
LANGUAGE plpgsql IMMUTABLE STRICT PARALLEL SAFE
AS $$
DECLARE
    v_words TEXT[];
    v_parts TEXT[];
    v_word TEXT;
BEGIN
    -- Quoted phrases → fall back to websearch_to_tsquery (no prefix benefit)
    IF p_text LIKE '%"%' THEN
        RETURN websearch_to_tsquery(p_config, p_text);
    END IF;

    -- Split words, strip tsquery-special chars, add :* prefix operator
    v_words := string_to_array(trim(regexp_replace(p_text, '\s+', ' ', 'g')), ' ');
    v_parts := ARRAY[]::TEXT[];

    FOREACH v_word IN ARRAY v_words LOOP
        v_word := regexp_replace(v_word, '[()!&|<>:?\\''"]', '', 'g');
        IF length(v_word) > 0 THEN
            v_parts := array_append(v_parts, v_word || ':*');
        END IF;
    END LOOP;

    IF array_length(v_parts, 1) IS NULL THEN
        RETURN NULL;
    END IF;

    RETURN to_tsquery(p_config, array_to_string(v_parts, ' & '));
END;
$$;

Use with 'simple' config (no stemming, works with any language):

-- In hybrid_search, replace websearch_to_tsquery with prefix_tsquery:
v_tsquery := prefix_tsquery('simple', p_query_text);

-- Direct usage
SELECT * FROM documents
WHERE text_search @@ prefix_tsquery('simple', 'ksylitoli fluori')
ORDER BY ts_rank_cd(text_search, prefix_tsquery('simple', 'ksylitoli fluori')) DESC;

Key points:

• Sanitizes ( ) ? ! & | < > : \ ' to prevent tsquery syntax errors from user input
• Falls back to websearch_to_tsquery for quoted phrases ("exact phrase")
• Works with any 'simple' tsvector column — no schema changes needed
• Combines well with hybrid search (RRF) alongside vector similarity

Weighted Search (Title vs Content)

-- Title matches rank higher than content matches
SELECT id, title,
    ts_rank(
        setweight(to_tsvector('simple', title), 'A') ||
        setweight(to_tsvector('simple', content), 'B'),
        query
    ) AS rank
FROM documents, plainto_tsquery('simple', 'search term') query
WHERE (
    setweight(to_tsvector('simple', title), 'A') ||
    setweight(to_tsvector('simple', content), 'B')
) @@ query
ORDER BY rank DESC;

Google-style Query Parsing

-- websearch_to_tsquery supports AND, OR, NOT, "phrases"
SELECT * FROM documents
WHERE to_tsvector('simple', content)
  @@ websearch_to_tsquery('simple', 'postgres -mysql "full text"');
-- Means: contains "postgres" AND "full text", NOT "mysql"

Phrase Search with Distance

-- Words within 2 positions of each other
SELECT * FROM documents
WHERE to_tsvector('english', content)
  @@ to_tsquery('english', 'full <2> search');

Decision Tree: Choose Text Search Method

What kind of text matching?
├─ Exact substring → LIKE/ILIKE + pg_trgm GIN index
├─ Typo tolerance (fuzzy) → pg_trgm similarity (%)
├─ Autocomplete → Prefix (B-tree) + pg_trgm fallback
├─ Keyword search (stemming) → FTS (tsvector/tsquery)
├─ Ranked keyword search → BM25 (pg_search/ParadeDB)
├─ Meaning-based → Semantic vector search (pgvector)
└─ Combined → Hybrid (vector + keyword + optional fuzzy)

Combining Fuzzy with Semantic Search

For the best user experience, combine pg_trgm with vector search:

-- Stage 1: Quick fuzzy filter for obvious matches
-- Stage 2: Semantic search for meaning-based results
-- Stage 3: RRF to merge results

WITH fuzzy AS (
    SELECT id, similarity(title, $1) AS score,
           ROW_NUMBER() OVER (ORDER BY similarity(title, $1) DESC) AS rank
    FROM documents
    WHERE title % $1
    LIMIT 20
),
semantic AS (
    SELECT id, 1 - (embedding <=> $2) AS score,
           ROW_NUMBER() OVER (ORDER BY embedding <=> $2) AS rank
    FROM documents
    ORDER BY embedding <=> $2
    LIMIT 20
)
SELECT COALESCE(f.id, s.id) AS id,
    COALESCE(1.0/(60 + f.rank), 0) + COALESCE(1.0/(60 + s.rank), 0) AS rrf_score
FROM fuzzy f
FULL OUTER JOIN semantic s ON f.id = s.id
ORDER BY rrf_score DESC
LIMIT 10;

Hybrid Search Guide

Hybrid search combines semantic (vector) search with keyword search for better results.

Why Hybrid Search?

Example: Query "PostgreSQL 17.2 release notes"

• Semantic: Finds "database version updates" (related meaning)
• Keyword: Finds exact "PostgreSQL 17.2" matches
• Hybrid: Finds both, ranks appropriately

Keyword Search Options

Option 1: PostgreSQL FTS (Built-in)

No extra extensions needed.

-- Create index
CREATE INDEX ON documents USING GIN (to_tsvector('simple', content));

-- Search
SELECT * FROM documents
WHERE to_tsvector('simple', content) @@ websearch_to_tsquery('simple', 'search terms')
ORDER BY ts_rank(to_tsvector('simple', content), websearch_to_tsquery('simple', 'search terms')) DESC;

Language options:

• 'simple': No stemming, basic tokenization. Good for mixed languages.
• 'english': English stemming. "running" matches "run".
• 'finnish', 'german', 'french', etc.: Built-in stemmers.

Query parsers: websearch_to_tsquery vs plainto_tsquery

This is the most common FTS pitfall. Pick the right parser for your input:

The trap: plainto_tsquery('how do I reset my password') requires ALL six words to be present in a single document → returns 0 hits for most realistic queries. Use websearch_to_tsquery for any user-typed input.

-- ❌ BAD: long natural-language query → 0 hits
WHERE tsv @@ plainto_tsquery('english', 'how do I reset my password')

-- ✅ GOOD: same query, OR-friendly matching
WHERE tsv @@ websearch_to_tsquery('english', 'how do I reset my password')

Custom FTS configuration (e.g., language + unaccent)

For non-English content, combine a stemmer with unaccent so accented characters match their base forms ("café" matches "cafe", "naïve" matches "naive"). This is essential for Finnish, French, German, Spanish, Portuguese, etc.

-- 1. Enable unaccent extension
CREATE EXTENSION IF NOT EXISTS unaccent;

-- 2. Create custom config: copy the language base, then prepend unaccent mapping
CREATE TEXT SEARCH CONFIGURATION finnish_unaccent (COPY = finnish);
ALTER TEXT SEARCH CONFIGURATION finnish_unaccent
  ALTER MAPPING FOR hword, hword_part, word
  WITH unaccent, finnish_stem;

-- 3. Use in indexes and queries
CREATE INDEX ON documents USING GIN (to_tsvector('finnish_unaccent', content));

SELECT * FROM documents
WHERE to_tsvector('finnish_unaccent', content) @@ websearch_to_tsquery('finnish_unaccent', 'kahvi naiivi');
-- Matches both "kahvi"/"kahvia" and "naïvi"/"naiivit"

The same pattern works for any language: german_unaccent, spanish_unaccent, etc. Always create the custom config once (DDL), then reference it everywhere.

Dual FTS (exact + prefix) for agglutinative languages

websearch_to_tsquery handles OR / quoted phrases well but still misses inflected forms (vuosiloma → vuosilomaa). plainto_tsquery fails differently — ANDs every token, returning 0 hits on 15-word natural questions. For Finnish, Turkish, Hungarian, Estonian and similar, run both queries and take the max rank, damping the fuzzier source so exact matches keep winning ties:

WITH ws_q AS (SELECT websearch_to_tsquery('finnish_unaccent', $1) AS q),
     px_q AS (SELECT prefix_tsquery($1) AS q)
SELECT id,
       GREATEST(
         COALESCE(ts_rank_cd(tsv, (SELECT q FROM ws_q)), 0),
         COALESCE(ts_rank_cd(tsv, (SELECT q FROM px_q)), 0) * 0.7
       ) AS fts_score
FROM documents
WHERE tsv @@ (SELECT q FROM ws_q) OR tsv @@ (SELECT q FROM px_q)
ORDER BY fts_score DESC LIMIT 30;

This boosts recall without over-ranking fuzzy prefix matches against exact phrase hits. See prefix_tsquery definition in the main SKILL.md.

Option 2: pg_search BM25

Better ranking than ts_rank. Requires pg_search extension.

pg_search API note: since pg_search 0.20.0 the v2 operator API is the default (|||, &&&, ###, ===, pdb.score(), pdb.snippet()). The legacy @@@ + paradedb.* functions still work but are slated for removal — the example below uses the v2 syntax. See paradedb.md for the full operator reference.

-- Install
CREATE EXTENSION pg_search;

-- Create BM25 index (CALL paradedb.create_bm25 has been removed from pg_search)
CREATE INDEX documents_bm25_idx ON documents
USING bm25 (id, content)
WITH (key_field = 'id');

-- Search (v2 API)
SELECT id, pdb.score(id) AS score
FROM documents
WHERE content ||| 'search terms'
ORDER BY score DESC;

BM25 vs ts_rank:

• BM25 considers corpus statistics (IDF)
• Better for varying document lengths
• Generally more accurate relevance

Result Fusion Methods

RRF (Reciprocal Rank Fusion)

Combines rankings without needing normalized scores.

RRF_score = 1/(k + rank_semantic) + 1/(k + rank_keyword)

Where k = 60 (constant, default)

WITH semantic AS (
    SELECT id, ROW_NUMBER() OVER (ORDER BY embedding <=> query_vec) AS rank
    FROM documents LIMIT 100
),
keyword AS (
    SELECT id, ROW_NUMBER() OVER (ORDER BY ts_rank(...) DESC) AS rank
    FROM documents WHERE ... LIMIT 100
)
SELECT
    COALESCE(s.id, k.id) AS id,
    (COALESCE(1.0/(60 + s.rank), 0) + COALESCE(1.0/(60 + k.rank), 0)) AS rrf_score
FROM semantic s
FULL OUTER JOIN keyword k ON s.id = k.id
ORDER BY rrf_score DESC;

Pros: No score normalization needed, robust. Cons: Ignores actual score magnitudes.

Linear Weighting

Combines normalized scores with weights.

combined_score = w_semantic * semantic_score + w_keyword * keyword_score

Typical weights:

• Semantic-heavy: 0.7 / 0.3
• Balanced: 0.5 / 0.5
• Keyword-heavy: 0.3 / 0.7

-- Normalize keyword scores to 0-1 range
WITH keyword_normalized AS (
    SELECT id, score / MAX(score) OVER () AS norm_score
    FROM keyword_results
)
SELECT
    s.id,
    0.6 * s.similarity + 0.4 * COALESCE(k.norm_score, 0) AS combined
FROM semantic s
LEFT JOIN keyword_normalized k ON s.id = k.id
ORDER BY combined DESC;

Pros: Tunable per domain. Cons: Requires score normalization.

Ready-to-Use Functions

FTS + RRF (No extra extensions)

See scripts/hybrid_search_fts.sql:

• hybrid_search_fts() - Basic hybrid with RRF
• hybrid_search_weighted() - With tunable weights
• hybrid_search_fallback() - Graceful degradation

BM25 + RRF (With pg_search)

See scripts/hybrid_search_bm25.sql:

• hybrid_search_bm25() - Basic BM25 hybrid
• hybrid_search_bm25_highlighted() - With snippet highlighting
• hybrid_search_chunks_bm25() - For RAG with chunks

Chunk-Based Search (RAG)

For large documents split into chunks:

1. Search chunks, not documents 2. Deduplicate by document (keep best chunk) 3. Return chunk + parent document info

WITH chunk_results AS (
    SELECT
        c.id AS chunk_id,
        c.document_id,
        c.content,
        ROW_NUMBER() OVER (ORDER BY c.embedding <=> query_vec) AS rank,
        ROW_NUMBER() OVER (PARTITION BY c.document_id ORDER BY c.embedding <=> query_vec) AS doc_rank
    FROM chunks c
)
SELECT * FROM chunk_results WHERE doc_rank = 1  -- Best chunk per document
ORDER BY rank LIMIT 10;

Contextual chunk embeddings

Chunk text alone often lacks disambiguating context ("section 28 says…" — of which document?). Before embedding, prepend a short prefix describing the chunk's location in the parent document:

embed_input = `${document_title}, §${section_number} ${section_title}\n\n${chunk_text}`

Generate the prefix once per chunk with a cheap LLM at ingest time (GPT-4o-mini, Claude Haiku). Anthropic reports up to −49% retrieval errors with this pattern; similar gains are observed on domain-specific corpora.

Caveats:

• One-time cost, typically ~$1–5 per 5k chunks with a mini model.
• Re-generate the prefix if chunking strategy changes.
• Do not include the prefix in the FTS tsv column — it inflates false

positives on common document-title keywords. Embed with context, index FTS on raw chunk text.

Best Practices

1. Start with FTS + RRF - No extra dependencies 2. Add BM25 if needed - Better ranking for keyword-heavy queries 3. Use RRF for simplicity - Works well without tuning 4. Tune weights for your domain - If RRF isn't optimal 5. Index both - Vector index + GIN/BM25 index 6. Consider language - Use appropriate FTS language config

Choosing Search Method

Query type?
├─ Conceptual/semantic → Pure vector search
├─ Exact terms/names → Pure keyword search
└─ Mixed/unknown → Hybrid search
    ├─ Simple setup → FTS + RRF (no extra extensions)
    ├─ Better ranking → BM25 + RRF (pg_search extension)
    └─ Full-featured → ParadeDB (Elasticsearch alternative)

ParadeDB (Full-Featured Alternative)

For comprehensive Elasticsearch-like features including BM25 ranking, faceted search, highlighting, fuzzy search, and aggregations, see paradedb.md.

ParadeDB is ideal when you need:

• Production-grade BM25 ranking (better than ts_rank)
• Built-in highlighting with pdb.snippet()
• Faceted queries with pdb.agg()
• Fuzzy search with typo tolerance
• Zero ETL - runs as Postgres extension or logical replica

Cross-language RRF fusion pattern

When the corpus is one language and queries arrive in many, a single hybrid pass underperforms on off-language queries: multilingual embeddings collapse domain-specific terms (jargon, proper nouns, compound words) onto distant points in cross-lingual space. Two-pass RRF recovers them without changing the index.

query_lang != corpus_lang ?
    pass_1 = hybrid_search(translated_text, embedding=embed(query_original))
    pass_2 = hybrid_search(translated_text, embedding=embed(translated_text))
    results = rrf_merge([pass_1, pass_2], k=60)
else:
    results = hybrid_search(query_text, embedding=embed(query_text))

Both passes use the same translated FTS text. Pass 1 leans on the model's cross-lingual map; pass 2 anchors in native-language embedding space and recovers the domain terms pass 1 missed. RRF (k=60) fuses by rank, so the two passes' score scales don't have to align.

Cache the translation and both embeddings — keyed by normalized query + model + target language. Gate fusion behind a language-detection check so already-corpus-language queries take the single-pass path.

Indexing Guide

Index Selection

HNSW (Hierarchical Navigable Small World)

Best for most production workloads.

Parameters

Recommended Settings

Development:

CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);

Production (< 100k vectors):

CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 200);

SET hnsw.ef_search = 100;

Production (100k - 1M vectors):

CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops)
WITH (m = 24, ef_construction = 200);

SET hnsw.ef_search = 100;

Operator Classes

Filtered HNSW queries — iterative_scan (pgvector 0.8+)

When you combine HNSW search with WHERE filters (categories, tenant IDs, date ranges), the index returns its top-N candidates first and the filter is applied after. If filters are selective, you can end up with fewer than LIMIT matching rows.

-- Problematic when filter is selective: returns < 10 rows even if more match
SELECT * FROM documents
WHERE category = 'rare'
ORDER BY embedding <=> query_vec
LIMIT 10;

Fix: enable iterative scan so HNSW keeps fetching candidates until LIMIT is satisfied (or the index is exhausted):

-- Session-level (one-off connections, scripts)
SET hnsw.iterative_scan = relaxed_order;  -- fast, slight reordering near boundary
-- or
SET hnsw.iterative_scan = strict_order;   -- preserves exact distance order, slower

-- Function-level (preferred for stable behavior in stored procedures)
CREATE OR REPLACE FUNCTION search_filtered(...)
  RETURNS TABLE (...)
  LANGUAGE sql STABLE
  SET hnsw.iterative_scan = 'relaxed_order'
AS $$ ... $$;

When to enable:

• WHERE filters typically reduce candidate pool by > 50 %
• Multi-tenant apps (every query filters by tenant_id)
• Queries that combine semantic search with metadata filters

When NOT needed:

• Unfiltered semantic search across all rows
• Filters that match > 80 % of rows (HNSW finds enough naturally)

Same pattern for IVFFlat: SET ivfflat.iterative_scan = on.

halfvec Operator Classes

IVFFlat

Use for very large datasets where memory is critical.

Parameters

-- For 1M rows: lists = 1000
CREATE INDEX ON documents USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 1000);

-- Query time (higher probes = better recall)
SET ivfflat.probes = 32;

Trade-off: Faster build, lower recall than HNSW.

GIN Indexes (Full-Text Search)

PostgreSQL FTS

-- Basic FTS index
CREATE INDEX ON documents USING GIN (to_tsvector('simple', content));

-- Language-specific (with stemming)
CREATE INDEX ON documents USING GIN (to_tsvector('english', content));
CREATE INDEX ON documents USING GIN (to_tsvector('finnish', content));

Weighted tsvector (pre-computed)

-- Add weighted column
ALTER TABLE documents ADD COLUMN tsv tsvector GENERATED ALWAYS AS (
    setweight(to_tsvector('english', coalesce(title,'')), 'A') ||
    setweight(to_tsvector('english', coalesce(content,'')), 'B')
) STORED;

-- Index the weighted column
CREATE INDEX ON documents USING GIN (tsv);

JSONB Metadata

CREATE INDEX ON documents USING GIN (metadata);

-- Query: WHERE metadata @> '{"category": "news"}'

Array Columns

CREATE INDEX ON documents USING GIN (tags);

-- Query: WHERE tags && ARRAY['tag1', 'tag2']

Partial Indexes

Index only rows matching a condition:

-- Index only news category
CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops)
WHERE metadata->>'category' = 'news';

-- Index only non-null embeddings
CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops)
WHERE embedding IS NOT NULL;

Index Maintenance

-- Reindex (if performance degrades)
REINDEX INDEX documents_embedding_hnsw_idx;

-- Update statistics
ANALYZE documents;

-- Check index size
SELECT pg_size_pretty(pg_relation_size('documents_embedding_hnsw_idx'));

-- Check if index is used
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM documents ORDER BY embedding <=> '[...]'::vector LIMIT 10;

Concurrent Index Creation

Create indexes without blocking writes:

CREATE INDEX CONCURRENTLY documents_embedding_idx
ON documents USING hnsw (embedding vector_cosine_ops);

Note: Takes longer but doesn't lock the table.

Build Time Estimates

Build times vary by hardware. HNSW is slower to build but faster to query.

ParadeDB - Elasticsearch Alternative for PostgreSQL

Documentation Fetch Policy

ParadeDB API evolves quickly — the content below is a practical guide but may lag behind. Use the live docs as the authoritative source.

Fetch rules:

• On the first ParadeDB question in a session, fetch https://docs.paradedb.com/llms-full.txt.
• After a successful fetch, treat that content as cached session context and reuse it for later ParadeDB questions in the same session.
• Do not refetch on every turn when the previously fetched docs are still available and relevant.
• Refresh the docs only when one of these is true:
• the user asks for a refresh or re-fetch
• the question depends on very recent/current changes
• the needed content was not included in the earlier fetch
• session context appears lost, truncated, or unavailable
• the earlier fetch failed or looked incomplete

Network failure rules (mandatory): If llms-full.txt cannot be fetched due to DNS/network/access errors:

• State clearly that live docs could not be accessed and include the actual error.
• If cached session docs exist from an earlier successful fetch, continue from that cached copy unless the user wants to stop.
• If no cached session docs exist, ask whether to proceed with the local content below or to retry.
• Do not invent or infer doc URLs, page paths, or feature availability.
• Do not present unverified links as real.
• Label any fallback statements as assumptions and keep them minimal.

Response guidelines:

• Prefer runnable SQL examples over prose-only answers.
• State ParadeDB/Postgres version assumptions when syntax may differ.
• Say when you are relying on cached session docs versus a fresh fetch if that matters to the answer.
• If behavior is uncertain, call it out explicitly instead of guessing.

---

ParadeDB is a YC S23 company with 400,000+ deployments. Used in production by Alibaba Cloud, Bilt Rewards, and others.

Why ParadeDB?

Key benefits:

• Zero ETL - runs as Postgres extension or logical replica
• Full ACID compliance with read-after-write guarantees
• Standard SQL with custom search operators
• Handles updates/deletes well (unlike Elastic)

Installation

Docker (includes Postgres 18 + pgvector + pg_search)

docker run -d --name paradedb \
  -e POSTGRES_USER=myuser \
  -e POSTGRES_PASSWORD=mypassword \
  -e POSTGRES_DB=mydatabase \
  -v paradedb_data:/var/lib/postgresql/ \
  -p 5432:5432 \
  paradedb/paradedb:latest

# Connect
docker exec -it paradedb psql -U myuser -d mydatabase -W

Neon (AWS regions, PostgreSQL 17+)

CREATE EXTENSION pg_search;

Note: pg_search is deprecated for new Neon projects as of 2026-03-19 (existing projects keep working). See https://neon.com/docs/extensions/pg_search.

Self-hosted Postgres

-- Install pg_search extension
CREATE EXTENSION pg_search;

BM25 Index

BM25 index is a covering index - include all columns you'll search, filter, sort, or aggregate.

-- Basic index
CREATE INDEX search_idx ON documents
USING bm25 (id, content, title, category, metadata)
WITH (key_field='id');

-- With tokenizer + stemmer
CREATE INDEX search_idx ON documents
USING bm25 (
    id,
    (content::pdb.unicode_words('stemmer=english')),
    (title::pdb.ngram(3,3)),
    category
)
WITH (key_field='id');

Key field requirements:

• Must have UNIQUE constraint (usually PRIMARY KEY)
• Must be first in column list
• If text, must be untokenized

Available Tokenizers

Tokenizer Parameters

-- Remove emojis from text before indexing
(content::pdb.unicode_words('stemmer=english', 'remove_emojis=true'))

Stemmer Languages

-- English
(content::pdb.unicode_words('stemmer=english'))

-- Finnish
(content::pdb.unicode_words('stemmer=finnish'))

-- Multiple token filters
(content::pdb.simple('stemmer=english', 'ascii_folding=true'))

JSON Field Indexing

JSONB fields are automatically indexed with sub-fields. Target specific sub-fields with tokenizers:

CREATE INDEX ON documents USING bm25 (
    id,
    metadata,  -- Auto-indexes all sub-fields
    ((metadata->>'title')::pdb.unicode_words('stemmer=english')),
    ((metadata->>'tags')::pdb.ngram(2,3))
)
WITH (key_field='id');

Search Operators

Match Disjunction (OR)

-- Find documents containing "semantic" OR "search"
SELECT * FROM documents
WHERE content ||| 'semantic search'
ORDER BY pdb.score(id) DESC;

Match Conjunction (AND)

-- Find documents containing "semantic" AND "search"
SELECT * FROM documents
WHERE content &&& 'semantic search'
ORDER BY pdb.score(id) DESC;

Exact JSON Match

-- Exact match on JSON field
SELECT * FROM documents
WHERE metadata->>'category' === 'technology';

BM25 Scoring

SELECT
    id,
    content,
    pdb.score(id) AS relevance
FROM documents
WHERE content ||| 'search query'
ORDER BY relevance DESC
LIMIT 10;

Highlighting (Snippets)

SELECT
    id,
    pdb.snippet(content) AS highlighted_content,
    pdb.score(id) AS score
FROM documents
WHERE content ||| 'semantic search'
ORDER BY score DESC;

-- Output: "This is about <b>semantic</b> <b>search</b> in databases"

Faceted Queries (Aggregations)

Single query returns both results and aggregates:

SELECT
    content,
    pdb.score(id) AS score,
    pdb.agg('{"value_count": {"field": "id"}}') OVER () AS total_matches
FROM documents
WHERE content ||| 'search'
ORDER BY score DESC
LIMIT 10;

-- Output includes total_matches: {"value": 42.0}

Boolean Queries

Legacy v1 API note: since pg_search 0.20.0 the v2 operator API is the default (|||, &&&, ###, ===, pdb.score(), pdb.snippet()). The legacy @@@ + paradedb.* functions shown below still work but are slated for removal — prefer the v2 syntax in new code.

-- Complex boolean logic
SELECT * FROM documents
WHERE id @@@ paradedb.boolean(
    must => ARRAY[paradedb.match('content', 'postgresql')],
    should => ARRAY[paradedb.match('content', 'vector')],
    must_not => ARRAY[paradedb.match('content', 'deprecated')]
)
ORDER BY pdb.score(id) DESC;

Fuzzy Search

-- Typo-tolerant search (edit distance 2) via tokenizer cast
-- Works with the |||, &&&, and === operators
SELECT * FROM documents
WHERE content ||| 'postgre'::pdb.fuzzy(2)
ORDER BY pdb.score(id) DESC;

Phrase Search

-- Exact phrase matching
SELECT * FROM documents
WHERE id @@@ paradedb.phrase('content', ARRAY['vector', 'database'])
ORDER BY pdb.score(id) DESC;

Hybrid Search (BM25 + pgvector)

Combine BM25 full-text search with vector similarity using RRF:

WITH bm25_results AS (
    SELECT id, ROW_NUMBER() OVER (ORDER BY pdb.score(id) DESC) AS rank
    FROM documents
    WHERE content ||| 'semantic search'
    LIMIT 100
),
vector_results AS (
    SELECT id, ROW_NUMBER() OVER (ORDER BY embedding <=> query_vec) AS rank
    FROM documents
    ORDER BY embedding <=> query_vec
    LIMIT 100
)
SELECT
    COALESCE(b.id, v.id) AS id,
    (COALESCE(1.0/(60 + b.rank), 0) + COALESCE(1.0/(60 + v.rank), 0)) AS rrf_score
FROM bm25_results b
FULL OUTER JOIN vector_results v ON b.id = v.id
ORDER BY rrf_score DESC
LIMIT 10;

Filtering with Search

-- Combine BM25 search with standard SQL filters
SELECT * FROM documents
WHERE content ||| 'search query'
  AND metadata->>'category' === 'tech'
  AND created_at > '2024-01-01'
ORDER BY pdb.score(id) DESC
LIMIT 10;

JOINs

ParadeDB supports all PostgreSQL JOINs:

SELECT d.content, c.name AS category_name, pdb.score(d.id)
FROM documents d
JOIN categories c ON d.category_id = c.id
WHERE d.content ||| 'search query'
ORDER BY pdb.score(d.id) DESC;

-- Combined scores across tables
SELECT d.content, c.name, pdb.score(d.id) + pdb.score(c.id) AS combined_score
FROM documents d
JOIN categories c ON d.category_id = c.id
WHERE d.content ||| 'query' AND c.name ||| 'query'
ORDER BY combined_score DESC;

Important Considerations

Community vs Enterprise

Community is suitable for:

• Development and testing
• Non-critical workloads
• Logical replica setups

Enterprise is required for:

• Production with durability requirements
• High availability setups
• Physical replication

Limitations

• One BM25 index per table - it's a covering index, include all needed columns
• DDL not replicated - if using logical replication, apply schema changes manually
• Key field required - must have unique identifier column

Index Rebuild

Adding/removing columns requires REINDEX:

-- Rebuild index after schema change
REINDEX INDEX search_idx;

External Links

• ParadeDB Documentation
• ParadeDB AI Docs - Full docs for AI agents (always current)
• ParadeDB MCP Endpoint - For MCP-compatible tools
• GitHub Repository
• Install Guide

Performance Optimization

HNSW vs IVFFlat Performance

Based on benchmarks with 1M vectors (1536 dimensions):

Key takeaways:

• HNSW is 15.5x faster for queries (40.5 vs 2.6 QPS)
• IVFFlat builds 6x faster and uses less memory
• For most production workloads, HNSW's query speed advantage outweighs build time

When to choose IVFFlat:

• Memory is severely constrained
• Frequent full index rebuilds needed
• Can accept lower recall (tune probes)
• Dataset changes frequently (faster rebuilds)

When to choose HNSW:

• Query latency is critical
• High query throughput needed
• Can afford one-time longer build
• Need highest recall

Cold-Start Optimization

HNSW indexes load into memory on first query. This can take 30-60+ seconds for large indexes.

Preload Index

-- Force index scan on startup
SELECT COUNT(*) FROM (
    SELECT 1 FROM documents
    ORDER BY embedding <=> '[0,0,...,0]'::vector(1536)
    LIMIT 1
) t;

Add to application startup or cron job.

HNSW Parameter Tuning

For faster cold-start:

-- Higher m = larger index, but better graph quality
CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops)
WITH (m = 24, ef_construction = 100);

-- Higher ef_search = better recall, but slower queries
SET hnsw.ef_search = 80;

Benchmark for your data - optimal values depend on dataset size.

Runtime Embedding Cache

Every hybrid-search request embeds the user query before hitting Postgres — typically a 150–300 ms HTTP round-trip to the embedding provider. On short-lived repeats (same instance, minutes apart), an in-memory LRU removes the call entirely.

import { LRUCache } from 'lru-cache';

const embedCache = new LRUCache<string, number[]>({
  max: 256,
  ttl: 5 * 60 * 1000,   // 5 min — long enough for bursty repeats, short enough to stay tiny
});

async function embedCached(q: string): Promise<number[]> {
  const hit = embedCache.get(q);
  if (hit) return hit;
  const vec = await embed(q);
  embedCache.set(q, vec);
  return vec;
}

Footprint at 256 × 1024-dim float32 ≈ 1 MB. Serverless note: this cache warms per-instance only. Pair it with a cross-instance cache (Redis, managed KV, or a search_cache Postgres table) when cold starts dominate production traffic.

Memory Configuration

PostgreSQL Settings

# postgresql.conf

# Shared memory for caching (25% of RAM)
shared_buffers = 4GB

# Query planner estimate (75% of RAM)
effective_cache_size = 12GB

# Per-query work memory
work_mem = 256MB

# Maintenance operations (index builds)
maintenance_work_mem = 2GB

Session-Level Tuning

-- Increase for complex queries
SET work_mem = '512MB';

-- Higher ef_search for better recall
SET hnsw.ef_search = 200;

-- More IVFFlat probes
SET ivfflat.probes = 20;

Query Optimization

Pre-filtering vs Post-filtering

Pre-filtering (filter first, then vector search):

-- Good when filter is selective (returns few rows)
SELECT * FROM documents
WHERE metadata->>'category' = 'news'  -- Filter first
ORDER BY embedding <=> query_vec
LIMIT 10;

Post-filtering (vector search first, then filter):

-- Good when filter is broad
SELECT * FROM (
    SELECT * FROM documents
    ORDER BY embedding <=> query_vec
    LIMIT 100  -- Get more candidates
) sub
WHERE metadata->>'category' = 'news'  -- Then filter
LIMIT 10;

pgvector 0.8.0+ Iterative Scans

pgvector 0.8.0+ has iterative index scans that automatically handle filtering better:

-- Just write the query naturally
SELECT * FROM documents
WHERE embedding IS NOT NULL
  AND metadata->>'category' = 'news'
ORDER BY embedding <=> query_vec
LIMIT 10;

The planner will choose the best strategy.

Partial Indexes for Filtered Queries

If you frequently filter by the same criteria:

CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops)
WHERE metadata->>'category' = 'news';

-- Query will use this smaller, faster index
SELECT * FROM documents
WHERE metadata->>'category' = 'news'
ORDER BY embedding <=> query_vec
LIMIT 10;

Batch Operations

Batch Embedding Insertion

-- Insert multiple rows at once
INSERT INTO documents (content, embedding)
VALUES
    ('text1', '[...]'::vector),
    ('text2', '[...]'::vector),
    ...
    ('text50', '[...]'::vector);

Batch Queries

-- Multiple queries in one round-trip
SELECT d.*, q.query_id
FROM unnest(ARRAY[
    '[...]'::vector,
    '[...]'::vector
]) WITH ORDINALITY AS q(vec, query_id)
CROSS JOIN LATERAL (
    SELECT * FROM documents
    ORDER BY embedding <=> q.vec
    LIMIT 10
) d;

Connection Pooling

Use connection pooling to avoid connection overhead:

• PgBouncer - External pooler
• Supabase Pooler - Managed pooler
• Application-level - Most ORMs have pooling

# PgBouncer recommended settings
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20

Monitoring

Query Performance

-- Enable timing
\timing on

-- Explain analyze
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM documents
ORDER BY embedding <=> '[...]'::vector
LIMIT 10;

Index Usage

-- Check if index is being used
SELECT
    indexrelname AS index_name,
    idx_scan AS scans,
    idx_tup_read AS tuples_read,
    idx_tup_fetch AS tuples_fetched
FROM pg_stat_user_indexes
WHERE relname = 'documents';

Table Size

SELECT
    pg_size_pretty(pg_total_relation_size('documents')) AS total,
    pg_size_pretty(pg_relation_size('documents')) AS table,
    pg_size_pretty(pg_indexes_size('documents')) AS indexes;

Scaling Strategies

Vertical Scaling

• More RAM = larger indexes in memory
• Faster CPU = faster distance calculations
• NVMe SSD = faster cold-start

Horizontal Scaling (Advanced)

• Read replicas - Distribute read queries
• Citus - Distributed PostgreSQL (pg_search 0.20+ compatible)
• Partitioning - Split by date/category

When to Scale

Benchmarking

Always benchmark with your actual data:

-- Simple benchmark
\timing on
SELECT * FROM documents ORDER BY embedding <=> '[...]'::vector LIMIT 10;
-- Run multiple times, ignore first (cold cache)

-- With EXPLAIN ANALYZE
EXPLAIN (ANALYZE, BUFFERS, TIMING)
SELECT * FROM documents ORDER BY embedding <=> '[...]'::vector LIMIT 10;

Compare:

• Different index types (HNSW vs IVFFlat)
• Different parameters (m, ef_construction, ef_search)
• With and without filters

Re-ranking Guide

Re-ranking is a two-stage retrieval pattern:

1. Stage 1: Fast retrieval (vector/hybrid search) — get 50-100 candidates 2. Stage 2: Precise re-ranking — score candidates with a cross-encoder

Why Re-rank?

Cross-encoders partially rediscover a semantic variant of BM25 (attention ≈ soft TF, embedding matrix ≈ semantic IDF), which is why BM25 / hybrid + cross-encoder works so well together.

Reranker categories

Rerankers fall into two broad families:

• API-based (managed service) — high quality, no infra, pay per query.

Pick when results must be precise and the latency budget allows it.

• Self-hosted (cross-encoder behind your own service) — privacy,

predictable cost at volume, no vendor lock-in. Pick when you have infra and want control.

Ask the user's preference. Check the provider's docs for the current recommended model — never hard-code a model version guessed from training data, because model names rotate every 6-12 months.

Production rules (apply to ANY reranker)

Re-ranking is an enhancement, not a requirement. Implementation must follow these rules — the exact code is straightforward:

1. Return `null` on failure, never throw. Missing API key, HTTP error (including 429), timeout, malformed response → all return null. Caller falls back to the original retrieval order (semantic / hybrid / RRF). 2. Always use a timeout. AbortSignal.timeout(4000) or AbortController. A slow reranker must not hang the search request. 3. Short-circuit empty inputs. Return null (not empty array) if there are no candidates or no API key — the caller shouldn't need to distinguish these cases. 4. Log failures at `warn` level. Include the provider name and failure reason. Never fail the request.

Ask the user's framework/SDK preference before implementing — some stacks (e.g., Vercel AI SDK, LangChain) have first-class reranker adapters that handle retry / timeout / fallback for you.

Calling a reranker

Prefer your framework's reranker adapter — AI SDK, LangChain, LlamaIndex, and similar stacks ship adapters that already handle timeout, retries, and fallback. That keeps the code short and provider-agnostic.

If you must call the HTTP API directly, check the provider's docs for the current request shape (endpoint, body fields, score path). Keep the rules from the previous section: timeout + null-on-failure, never throw.

Self-hosting notes

If self-hosting, two things matter more than the rest:

• Load the model once at startup, not per request — initialization is

slow (typically several seconds) because the cross-encoder weights load into memory.

• Expose it as a small HTTP service (e.g. POST /rerank taking

{ query, documents, top_n }) so the application can call it with the same failure-handling rules as any managed reranker.

Two-stage retrieval pattern

Stage 1: BM25 / hybrid search (fast)
  ├─ Get 50-100 candidates via inverted index or HNSW
  └─ O(log n) per query

Stage 2: Cross-encoder rerank (precise)
  ├─ Score each candidate with full query attention
  └─ O(n) for n candidates

When NOT to re-rank

• Real-time autocomplete (latency critical)
• Very large candidate sets (> 100 docs → too slow, pre-filter first)
• Simple exact-match queries (BM25 alone is already optimal)

Rerankers can regress — benchmark first

Vendor-claimed "+N pp" is usually measured on English, general-domain data. On multilingual or heavily domain-specific corpora the same reranker can hurt retrieval quality — double-digit Hit@K losses have been observed in production.

Likely causes:

• Reranker trained predominantly on English; cross-encoder attention is less

calibrated for the target language's morphology.

• Chunks embedded with a contextual prefix (document title + section) already

saturate relevance — rerank reshuffles near-ties harmfully.

• Eval-set bias: questions LLM-generated from the same chunks as answers favor

bi-encoder similarity, amplifying apparent rerank regression.

Rule: never ship a reranker from a paper or vendor benchmark alone. A/B on your own eval set (see evaluation.md). Require ≥ +3 pp Hit@5 AND p95 latency within budget before adopting.

Provider docs

Check the provider's docs for the current recommended model and request shape (they update these as models rotate). The stable entry points:

• Cohere — <https://docs.cohere.com/docs/rerank>
• Voyage — <https://docs.voyageai.com/reference/reranker-api>
• HuggingFace (open-weight rerankers) — <https://huggingface.co/models?other=reranker>

Vector Types and Dimensions

Vector Types in pgvector

vector (float4)

Standard 32-bit floating-point vector.

-- Column definition
embedding vector(1536)

-- Index
CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops);

Properties:

• 32 bits (4 bytes) per dimension
• Full precision
• Storage: up to 16,000 dims; HNSW/IVFFlat indexing: up to 2,000 dims

Memory per row:

• 1536 dims: ~6 KB
• 3072 dims: ~12 KB

halfvec (float2)

Half-precision 16-bit floating-point vector. Available in pgvector 0.7.0+.

-- Column definition
embedding halfvec(3072)

-- Index (note: different operator class)
CREATE INDEX ON documents USING hnsw (embedding halfvec_cosine_ops);

-- Or cast from vector
CREATE INDEX ON documents USING hnsw ((embedding::halfvec(3072)) halfvec_cosine_ops);

Properties:

• 16 bits (2 bytes) per dimension
• Slightly reduced precision (negligible for embeddings)
• Max dimensions: 4,000 (HNSW)
• 50% memory savings

Memory per row:

• 3072 dims: ~6 KB (same as vector(1536))

bit (binary vectors)

Binary vectors for binary quantization. Pgvector 0.7.0+.

embedding bit(3072)

-- Index
CREATE INDEX ON documents USING hnsw (embedding bit_hamming_ops);

Properties:

• 1 bit per dimension
• Max dimensions: 64,000
• Use for extreme compression

Embedding Model Dimensions

Note: For other embedding models (Cohere, Voyage AI, Mistral, etc.), consult the model provider's documentation for dimension specifications. Use vector(dimensions) for models with ≤ 2000 dimensions, or halfvec(dimensions) for larger models requiring memory optimization.

Dimension Truncation

OpenAI text-embedding-3 models support native dimension reduction:

# Python - truncate at API level
response = openai.embeddings.create(
    model="text-embedding-3-large",
    input=text,
    dimensions=1536  # Truncate from 3072 to 1536
)

-- SQL - truncate stored embedding
SELECT subvector(embedding, 1, 1536) AS truncated
FROM documents;

Trade-offs:

• 3072 dims: Best quality, most memory
• 1536 dims: Good balance
• 768 dims: Fast, lower recall

Choosing Vector Type

Use vector(N) (N ≤ 2000) when:

• Embedding fits in HNSW's native dimension limit
• Storage is not a concern
• Need maximum compatibility

Use halfvec(N) (N ≤ 4000) when:

• Embedding exceeds vector's HNSW limit (2000), or
• You want ~50% memory savings at negligible precision cost
• pgvector 0.7.0+ available

Use bit when:

• Extreme scale (millions of vectors)
• Can accept lower recall
• Binary quantization is acceptable

Conversion Examples

-- vector to halfvec
SELECT embedding::halfvec(1536) FROM documents;

-- halfvec to vector (for functions expecting vector)
SELECT embedding::vector(1536) FROM documents;

-- Truncate dimensions
SELECT subvector(embedding, 1, 768)::vector(768) FROM documents;

Storage Estimation

-- Estimate table size
SELECT
    pg_size_pretty(pg_total_relation_size('documents')) AS total_size,
    pg_size_pretty(pg_relation_size('documents')) AS table_size,
    pg_size_pretty(pg_indexes_size('documents')) AS index_size;

-- Estimate per-row size
-- vector(1536): 4 * 1536 + overhead = ~6.1 KB
-- halfvec(3072): 2 * 3072 + overhead = ~6.1 KB
-- vector(3072): 4 * 3072 + overhead = ~12.3 KB