Agent Skills: PostgreSQL Semantic Search

PostgreSQL Semantic Search

Decisions, measured findings and silent failure modes for search built on Postgres, plus tested SQL building blocks in scripts/. Syntax the official docs cover well is left to them; what is here goes wrong without an error.

Build order

1. Look at what users actually type: identifiers and codes, one-to-three-word terms, full questions, which languages. That decides which arms you need.
2. Start with the simplest retrieval suited to those queries: exact/keyword lookup or vector search. For approximate vector search, keep exact vector search as the recall baseline.
3. Build two eval sets, long questions and short terms, before tuning (evaluation.md).
4. Add another arm and fuse with RRF only where it improves the baseline on the queries that need it (hybrid-search.md).
5. Add a reranker last, over the head of a good shortlist (reranking.md).

Change one thing at a time. Predefine the acceptance rule; by default require a gain beyond measured run-to-run spread on the affected query population, without a material loss on the other eval set. Inspect per-query wins/losses and require the agreed latency and cost budget to hold. The numerical findings below are corpus-specific observations, not universal performance guarantees.

Choosing

• Column type by dimensions, not provider: vector(N) indexes up to 2,000; up to 4,000 index a halfvec cast (or use a halfvec column); above that, binary quantization or Matryoshka truncation.
• HNSW by default, IVFFlat when memory or build time rules HNSW out. No row count decides it: measure recall and latency against exact search.
• Models change every few months. Pick embedding and reranker models from the provider's current docs, prefer multilingual models for non-English text, and evaluate on the target language before committing.
• Hybrid when users type both questions and terms. On a Finnish transcript corpus keyword alone was weak on long questions (24 % right first), vector alone on short terms (53 %); hybrid tied vector on long questions and led on short terms (53 % and 93 %; hybrid-search.md).
• BM25 is not available everywhere: managed hosts differ (Neon removed pg_search). Plain FTS with the fixes below is often enough.

Silent failures

These return fewer rows, zero rows or plausible results, never an error.

pgvector (pgvector.md):

• Without iterative scanning, HNSW results are limited by hnsw.ef_search (default 40). A larger LIMIT, or a selective WHERE, silently returns fewer. Enable hnsw.iterative_scan (off by default), or use partial indexes or partitions.
• The default ef_search can cost recall with no warning. Raise it until recall against exact search stops moving. If EXPLAIN shows a seq scan, tuning does nothing, and the seq scan may be the faster plan.
• A distance threshold goes outside a MATERIALIZED CTE, other filters inside it.
• SET is per connection. Behind a transaction pooler use SET LOCAL in the same transaction, or a function-level SET.
• Similarity cutoffs are model-specific: a cutoff tuned for one model returns nothing for another. 1 - (a <#> b) is not cosine similarity.
• Clients mangle JS arrays: node-postgres sends {0.1,0.2} and Drizzle's sql template expands an array into ($1, $2, ...); vector input rejects both. Send JSON.stringify(embedding) and cast with ::vector.

Keyword (keyword-search.md):

• plainto_tsquery and unquoted plain input to websearch_to_tsquery AND bare terms, so long questions can match nothing. For plain-input retrieval, consider OR and ts_rank_cd; preserve explicit web-search quotes, OR and exclusions.
• unaccent merges distinct words in Finnish, Swedish, German or Turkish. Fold only decorative accents, inside a text search configuration.
• Unwanted zero-width export artifacts can glue onto tokens and block stemming. Normalize them consistently without deleting meaningful ZWNJ/ZWJ.
• A generated tsvector column can silently become a plain NULL column after an ORM migration. If hybrid and vector-only return identical lists, the keyword arm may be absent or ineffective: check NULL/population counts and keyword-only results before diagnosing it.
• Prefix matching in inflected languages must OR-join terms and expand hyphenated tokens (prefix_tsquery.sql).
• % compares whole strings; use <% for prefixes and short queries against long text.

Non-English and chunking

• Cap chunks with the selected embedding model's tokenizer and input limit. Ratios vary by model and text: with OpenAI's cl100k_base (text-embedding-3), Finnish runs about 2.5 characters per token against 4 or more for English, so an English-tuned character cap is about twice too generous. The endpoint rejects the chunk and often fails the whole batch.
• Off-language queries can make the lexical arm ineffective and cost about 12 points even with a multilingual embedding model. Translate into a sentence, not a keyword list (a keyword list scored 14 points below no translation on one corpus), and consider two-pass fusion.
• A multi-word synonym expansion enters the tsquery as independent words, and its generic word takes over the ranking. Trim parts an order of magnitude commoner than the rest of their own phrase.
• Inflection: on one Finnish corpus embeddings absorbed it; a stemmed arm (Postgres finnish) lifted keyword-only search but not hybrid; a dictionary lemmatizer did worse than the stemmer on spoken and domain text; compound splitting flooded the arm. Re-measure on the target text. Prefix matching never reaches the base form of an inflected query (keyword-search.md).
• Transcripts: chunk length barely changed which video was found. A second, fine-grained search over the subtitle cues inside the matched segment finds the moment.

Details and measurements: hybrid-search.md.

Reranking

• Start by reranking only the head (top 5) of a ~30-candidate shortlist, and deepen only if the short-term set holds. Cross-encoders score "mentions X" rather than "is about X" and promote passing mentions of short terms: on a Finnish transcript corpus, reordering all 30 cut short-term Hit@1 from 0.93 to 0.60–0.80, the top 10 still cost some models a term or two, and the top 5 cost none for six rerankers. Re-measure depth on your own sets.
• The relevance question's wording can matter more than the model (about thirty points on one corpus). Every criterion must be checkable from the text sent, and the source title belongs in the reranker input.
• Choose by where it can run and whether text may leave the network. Measured on a 2-core CPU: a ~120M multilingual cross-encoder added about 0.3 s, while 568M–600M models took 10–25 s. On a data-centre GPU, Qwen3-Reranker-8B was the only model clearly more accurate than that small CPU model (reranking.md). Hosted rerankers need no infrastructure but receive the candidate text. A GPU that comes and goes can serve first, with the CPU model as the timeout fallback. Measure p95 on the target hardware.
• Measure the shortlist ceiling first: a reranker cannot recover what the first stage missed.
• Make the backend/depth configurable when operationally useful; choose the enabled default from the accepted quality and latency results. If callers may choose a backend per request, allow only admin-enabled ones.

Numbers, model trade-offs and adoption rules: reranking.md and jev-rerank-bench.

Scripts

For a new sample database, use setup.sql, then indexes.sql and selected function files. For an existing database, review/adapt them as migrations: they assume documents(id, title, content, metadata, embedding vector(1536)) and chunks(id, document_id, chunk_index, content, embedding): change the names and dimension in every file together. Some function files drop older signatures; review dependent objects before applying them. These are building blocks, not a complete production migration/authorization policy. None sets hnsw.ef_search; the caller does.

| File | Functions (real parameter names) | | --- | --- | | semantic_search.sql | match_documents(query_embedding, match_threshold, match_count), match_documents_filtered(query_embedding, filter_metadata, match_threshold, match_count), match_chunks(query_embedding, match_threshold, match_count); match_threshold NULL = plain top-k | | hybrid_search_fts.sql | hybrid_search_fts(query_embedding, query_text, match_count, rrf_k, fts_language, vector_weight, keyword_weight); either input may be NULL | | hybrid_search_bm25.sql | hybrid_search_bm25(...), hybrid_search_chunks_bm25(...): same parameters without fts_language; needs pg_search | | fuzzy_search.sql | fuzzy_search_trigram(query_text, similarity_threshold, max_results), autocomplete_search(search_prefix, max_results), hybrid_search_fuzzy_semantic(query_text, query_embedding, max_results, rrf_k) | | prefix_tsquery.sql | prefix_tsquery(config, text [, join]) | | setup.sql, indexes.sql | Extensions, example tables, indexes |

Supabase .rpc() binds arguments by name, so a misspelled key fails at call time:

const { data, error } = await supabase.rpc('hybrid_search_fts', {
  query_embedding: embedding, // number[]
  query_text: userQuery,
  match_count: 10,
  fts_language: 'simple',
});

// Drizzle or node-postgres: send the vector as text and cast it
await db.execute(sql`SELECT * FROM match_documents(${JSON.stringify(embedding)}::vector, NULL, 10)`);

References

• pgvector.md: types and dimension limits, HNSW tuning, planner choice, filters and thresholds, poolers, builds, operators.
• keyword-search.md: tsquery parsing, accents and language configs, dead keyword arms, prefix matching, trigrams, ParadeDB.
• hybrid-search.md: fusion, hybrid versus vector, chunking, transcripts, off-language queries, synonym expansion.
• reranking.md: choosing, depth, hardware, the relevance question, headroom, adoption.
• evaluation.md: two eval sets, bias, noise, and four ways a measurement misleads.

Versions (checked 2026-09)

• pgvector: inspect the installed version with SELECT extversion FROM pg_extension WHERE extname = 'vector'. 0.8.0+ for iterative scans. Check current stable releases: 0.8.2 fixed a buffer overflow in parallel HNSW builds, 0.8.3 and 0.8.4 HNSW vacuum corruption and errors. Read the current index-fix and upgrade notes before upgrading. Releases ship as git tags only, so read the CHANGELOG, not the empty Releases tab.
• pg_search: 0.25+ depends on pgvector; install pgvector first. ParadeDB's API moves quickly; see keyword-search.md.

Related skills

| Need | Skill | | --- | --- | | General Postgres schema, indexes, RLS, pooling | supabase-postgres-best-practices | | Chatbot orchestration, sessions, tool calls | nextjs-chatbot | | Embedding calls through the AI SDK | ai-sdk |