postgres-semantic-search — Claude Code skill: search in PostgreSQL
The postgres-semantic-search Claude Code skill builds and tunes PostgreSQL retrieval. It covers semantic and hybrid search, evaluation, and troubleshooting weak results.
MIT 227 installs ★ 74 updated 2026-11-14
npx skills add laguagu/claude-code-nextjs-skills --skill postgres-semantic-search -a claude-codeWhat postgres-semantic-search does
The skill helps design PostgreSQL search, from exact and full-text lookup to vector retrieval with pgvector. It covers HNSW and IVFFlat indexes, pg_trgm, ParadeDB BM25, result fusion, and reranking. Its evaluation guidance compares short queries with longer questions, and treats measurements as specific to the corpus being tested.
The material explains quiet failure modes: an HNSW query can return fewer rows after filtering, while an ORM migration can leave the keyword search arm empty. It also discusses tokenization, inflection, and chunking for non-English text. Approximate vector search needs an exact-search baseline, since index settings can affect recall.
What it helps with
- choose a retrieval method for user queries
- compare exact search with HNSW or IVFFlat
- combine keyword and vector results
- trace weak results after filtering
- evaluate reranking on short queries
Example prompts for the agent
- Help me choose a pgvector index for this search.
- Find out why HNSW returns fewer results after filtering.
- Compare full-text and vector search for short queries.
How to install postgres-semantic-search
1Skills CLI (Vercel)
npx skills add laguagu/claude-code-nextjs-skills --skill postgres-semantic-search -a claude-code2Manually: put the files in place
~/.claude/skills/postgres-semantic-search/SKILL.mdCopy the whole skill folder; for one project use .claude/skills/postgres-semantic-search/
1Skills CLI (Vercel)
npx skills add laguagu/claude-code-nextjs-skills --skill postgres-semantic-search -a codex2Manually: put the files in place
.agents/skills/postgres-semantic-search/SKILL.mdFor all projects use ~/.agents/skills/
1Skills CLI (Vercel)
npx skills add laguagu/claude-code-nextjs-skills --skill postgres-semantic-search -a cursor2Manually: put the files in place
.cursor/skills/postgres-semantic-search/SKILL.mdCursor also reads .agents/skills/ and .claude/skills/
1GitHub CLI
gh skill install laguagu/claude-code-nextjs-skills skills/postgres-semantic-search2Manually: put the files in place
.github/skills/postgres-semantic-search/SKILL.md1Skills CLI (Vercel)
npx skills add laguagu/claude-code-nextjs-skills --skill postgres-semantic-search -a gemini-cli2Platform CLI
gemini skills install https://github.com/laguagu/claude-code-nextjs-skills --path skills/postgres-semantic-searchManually: put the files in place
.devin/skills/postgres-semantic-search/SKILL.mdThe legacy .windsurf/skills/ path still works
Skills CLI (Vercel)
npx skills add laguagu/claude-code-nextjs-skills --skill postgres-semantic-search -a opencodeClaude.ai
Zip the folder and upload it in Settings → Capabilities → Skills
Step by step, with commands for each platform: How to install skills in Claude Code: five ways
Questions about postgres-semantic-search
How do I install postgres-semantic-search in Claude Code?
Run npx skills add laguagu/claude-code-nextjs-skills --skill postgres-semantic-search -a claude-code. The skill file is at ~/.claude/skills/postgres-semantic-search/SKILL.md.
What does postgres-semantic-search do?
It guides PostgreSQL retrieval work with pgvector, full-text search, and hybrid result fusion. It also covers index choices and search evaluation.
Which search methods does postgres-semantic-search cover?
It covers exact and vector retrieval, PostgreSQL full-text search, pg_trgm, and ParadeDB BM25. It also discusses RRF for combining results and reranking.
Source text
SKILL.md · MIT
---
name: postgres-semantic-search
description: >-
Builds and tunes PostgreSQL retrieval with pgvector, full-text search,
pg_trgm, hybrid fusion, ParadeDB BM25 and reranking. Use for pgvector
columns and indexes, search/RAG implementation, weak results, filtered ANN
scans, non-English corpora or retrieval evaluation. For general schema, RLS
and query tuning unrelated to retrieval, use Supabase's
supabase-postgres-best-practices skill.
---
# PostgreSQL Semantic Search
Decisions, measured findings and silent failure modes for search built on
Postgres, plus tested SQL building blocks in [scripts/](#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](references/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](references/hybrid-search.md)).
5. Add a reranker last, over the head of a good shortlist
([reranking.md](references/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](references/hybrid-search.md#where-each-arm-fails-long-questions-and-short-terms)).
- **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](references/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.
Keyword ([keyword-search.md](references/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](scripts/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](references/keyword-search.md#stemmer-lemmatizer-or-neither)).
- **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](references/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](references/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.
Numbers, model trade-offs and adoption rules: [reranking.md](references/reranking.md).
## 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, to at least the vector arm's candidate count: by default
`hybrid_search_chunks_bm25` and `hybrid_search_fuzzy_semantic` ask for 50,
so the default 40 silently trims them.
| File | Functions (real parameter names) |
| --- | --- |
| [semantic_search.sql](scripts/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](scripts/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](scripts/hybrid_search_bm25.sql) | `hybrid_search_bm25(...)`, `hybrid_search_chunks_bm25(...)`: same parameters without `fts_language`; needs `pg_search` |
| [fuzzy_search.sql](scripts/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](scripts/prefix_tsquery.sql) | `prefix_tsquery(p_config, p_text [, p_join])` |
| [setup.sql](scripts/setup.sql), [indexes.sql](scripts/indexes.sql) | Extensions, example tables, indexes |
Supabase `.rpc()` binds arguments by name, so a misspelled key fails at call
time. node-postgres sends a JS array as an array literal (`{"0.1","0.2"}`) and
Drizzle's `sql` template expands it into `($1, $2, ...)`; both fail with
`invalid input syntax for type vector` or a cast error, so send
`JSON.stringify(embedding)` and cast with `::vector`:
```typescript
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](references/pgvector.md): types and dimension limits, HNSW
tuning, planner choice, filters and thresholds, poolers, builds, operators.
- [keyword-search.md](references/keyword-search.md): tsquery parsing, accents
and language configs, dead keyword arms, prefix matching, trigrams, ParadeDB.
- [hybrid-search.md](references/hybrid-search.md): fusion, hybrid versus vector,
chunking, transcripts, off-language queries, synonym expansion.
- [reranking.md](references/reranking.md): choosing, depth, hardware, the
relevance question, headroom, adoption.
- [evaluation.md](references/evaluation.md): two eval sets, bias, noise, and
four ways a measurement misleads.
## Versions
- **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, 0.8.7 a buffer overflow in IVFFlat builds. Before
upgrading, read the
[CHANGELOG](https://github.com/pgvector/pgvector/blob/master/CHANGELOG.md);
releases ship as git tags only, so the Releases tab is empty.
- **pg_search**: 0.25+ depends on pgvector; install pgvector first. ParadeDB's
API moves quickly; see [keyword-search.md](references/keyword-search.md#paradedb-pg_search).
## Related skills
| Need | Skill |
| --- | --- |
| General Postgres schema, non-search indexes, RLS, pooling | `supabase-postgres-best-practices` (Supabase plugin or [supabase/agent-skills](https://github.com/supabase/agent-skills)) |
| Embedding calls through the AI SDK | `ai-sdk` |
© laguagu · MIT · laguagu/claude-code-nextjs-skills