laguagu/claude-code-nextjs-skills / skills/postgres-semantic-search
postgres-semantic-search
VerifiedBuilds 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.
| File | Kind | Size |
|---|---|---|
| SKILL.md | skill md | 12.1 KB |
| references/evaluation.md | markdown | 4.9 KB |
| references/hybrid-search.md | markdown | 7.7 KB |
| references/keyword-search.md | markdown | 11.0 KB |
| references/pgvector.md | markdown | 6.2 KB |
| references/reranking.md | markdown | 7.3 KB |
| scripts/fuzzy_search.sql | other | 4.5 KB |
| scripts/hybrid_search_bm25.sql | other | 6.0 KB |
| scripts/hybrid_search_fts.sql | other | 4.3 KB |
| scripts/indexes.sql | other | 2.3 KB |
| scripts/prefix_tsquery.sql | other | 5.2 KB |
| scripts/semantic_search.sql | other | 4.7 KB |
| scripts/setup.sql | other | 1.5 KB |
references/hybrid-search.md
View on GitHub# Hybrid search, chunking and query handling
Read when fusing vector and keyword results, deciding whether a keyword arm
earns its latency, chunking documents or transcripts, handling queries in a
language other than the corpus, or expanding queries with synonyms.
## Contents
- [Fusion](#fusion)
- [Hybrid does not automatically beat vector search](#hybrid-does-not-automatically-beat-vector-search)
- [Where each arm fails: long questions and short terms](#where-each-arm-fails-long-questions-and-short-terms)
- [Chunks and context](#chunks-and-context)
- [Time-coded transcripts](#time-coded-transcripts)
- [Queries in another language](#queries-in-another-language)
- [Query expansion: a multi-word synonym is not one term](#query-expansion-a-multi-word-synonym-is-not-one-term)
## Fusion
Reciprocal rank fusion needs no score normalization: each arm adds
`weight / (k + rank)`, with `k = 60` by convention. RRF is a useful baseline when scores are not comparable. A linear score mix
is also possible, but needs calibration/normalization and evaluation. Take each arm's candidates with `ORDER BY ... LIMIT`
and number them afterwards; a window function beside the `LIMIT` has to see
every row first, which rules out a top-N sort when the arm is not served by an
index. [scripts/hybrid_search_fts.sql](../scripts/hybrid_search_fts.sql) shows
the shape.
## Hybrid does not automatically beat vector search
On a ~54,000-chunk single-language corpus with 92 graded questions:
| Strategy | Recall@15 | MRR | Median latency |
| --- | ---: | ---: | ---: |
| Vector only | **75.0 %** | **0.557** | **76 ms** |
| Hybrid, RRF, vector weight 3 | 73.9 % | 0.519 | 681 ms |
| Hybrid, RRF, equal weights | 65.2 % | 0.357 | 702 ms |
| Keyword only (FTS) | 33.7 % | 0.144 | 602 ms |
- **Do not add the keyword arm on faith.** Equal weights were 10 points worse
than vector alone. Compare fusion weights with the appropriate single-arm baseline rather than
assuming that the vector side must always dominate.
- **Do not delete it on these numbers either.** Its job is exact identifiers:
part numbers, section references, names, product codes. A "find the chunk
this question was generated from" eval set rarely contains them. Judge the arm
on queries that need it.
## Where each arm fails: long questions and short terms
A Finnish lecture-transcript search measured live, reranker off, right moment
first:
| Mode | 70 long questions | 15 short terms |
| --- | ---: | ---: |
| Vector only | 52.9 % | 53.3 % |
| Keyword only (prefix FTS) | 24.3 % | 80.0 % |
| Hybrid (weighted RRF) | 52.9 % | **93.3 %** |
- **Keyword alone is weak on long questions**: a question's words spread over
many segments, and the arm ranks by overlap, not meaning.
- **Vector alone is weak on short terms**: it missed the first result for 7
of 15, though 14 were in its top ten. The exact term is what a short query
has, and only the keyword arm rewards it.
- **Hybrid was the only mode strong on both**, so the long-question tie with
vector search is no reason to drop the keyword arm. Evaluate modes on both
sets ([evaluation.md](evaluation.md)).
- Part of the short-term lead came from the app around the arms (title-row
handling, synonyms, a trigram fallback), not from fusion alone.
## Chunks and context
- **Search chunks, return documents**: keep the best chunk per document inside
each arm's candidates, then fuse by document
([scripts/hybrid_search_bm25.sql](../scripts/hybrid_search_bm25.sql)).
- **Contextual prefix.** Prepending the document title and section to each
chunk before embedding resolves "section 28 says…". [Anthropic](https://www.anthropic.com/engineering/contextual-retrieval)
measured relative top-20 retrieval failure reductions of 35 % from contextual
embeddings, 49 % with the context in the BM25 index as well, using LLM-generated,
chunk-specific context informed by the whole document, not title/section prefixes alone.
In a keyword index the repeated title words can also
make every chunk of a document match a title term, so measure both
placements on your own set. Regenerate prefixes when chunking changes.
- **Cap chunks with the embedding model's tokenizer and documented input
limit.** Language/text-specific token density can make an English-derived
character cap overflow a different corpus. Apply the limit on every ingest
path and define oversized-chunk/batch handling.
- **Translated content**: add a `language_code` to the chunk table, include it
in the uniqueness key, and scope ingest writes and deletes to one language.
## Time-coded transcripts
Video and audio search must find the right source and the right moment.
Measured on a Finnish lecture-transcript corpus (70 questions, 15 short terms):
- **Chunk length barely matters for finding the right video.** Windows from 20
to 150 seconds found it about equally often; spend tuning effort elsewhere.
- **Find the moment with a second, fine-grained search.** After segment search
picks a segment, score the subtitle cues inside it (± a few seconds) against
the query and seek to the best one, falling back to the segment start. That
beat shrinking the chunks.
- **Put the video title in the reranker input** with the segment text.
## Queries in another language
When the corpus is one language and a query arrives in another:
- **The lexical arm can contribute little.** A single-language FTS index may
not match another-language query, making fusion effectively vector-only with
extra latency. Names, codes and shared words can still match.
- **An off-language query costs about 12 points** even with a multilingual
embedding model (Recall@15 73.9 % native, 62.0 % untranslated).
- **Translate into a sentence, not a keyword list.** A translated question
reached 66.3 %. A translated list of search terms raised keyword recall from
5.4 % to 20.7 % but embedded so badly that the pipeline fell to 47.8 %, 14
points below not translating at all.
- **Two-pass fusion** recovers domain terms that cross-lingual embeddings blur:
run hybrid search twice with the translated keyword text, once with the
original-language embedding and once with the translated one, and RRF-merge
the two. Cache translations and embeddings, and skip all of this when the
query is already in the corpus language.
## Query expansion: a multi-word synonym is not one term
Adding the other name for a concept is the only way a keyword arm can connect
two names that share no prefix or trigram. The trap: tsquery builders OR-join
terms, so a multi-word expansion enters as one independent arm per word, and
its most common word takes over the ranking. Expanding `laptop` with
`portable computer` hands the keyword arm `computer`; on a ~2,000-segment
corpus the generic half of such a pair matched 47 segments against the
specific half's 1, and one off-topic document took six of the top ten.
A corpus-frequency cut for over-common query words does not catch this: it runs
before expansion, and 2 % of a corpus is under any sane floor. A word the user
typed is part of what they asked; an expansion word must earn its place.
Weigh a phrase's parts against each other, not against the query:
```text
for each multi-word expansion phrase:
parts = tokens with a known document frequency > 0
if len(parts) < 2: keep the phrase unchanged
rarest = min(df of parts)
drop = parts where df >= rarest * 20 # an order of magnitude commoner
remove drop from the keyword text # the rarest part always stays
```
Comparing with the query's rarest term instead drops the wrong words: for
`sneakers` (df 1) expanded with `running shoes` (df 21 and 11), `running` is 21
times commoner than the typed term but the same order as its own partner.
Trim the keyword text only; the embedding pass keeps the whole phrase, where
the head noun places it correctly.
Related skills
| Skill | Stars | Updated | Works with | Status | Install |
|---|---|---|---|---|---|
| deprecation-and-migrationaddyosmani/agent-skillsManages deprecation and migration. Use when removing old systems, APIs, or features. Use when migrating users from one implementation to another. Use when migrating a database schema in production, such as renaming or dropping a column without downtime (expand/contract). Use when deciding whether to maintain or sunset existing code. | Stars: 119k | Updated | Claude CodeCodex | Verified | Install |
| performance-optimizationaddyosmani/agent-skillsOptimizes application performance across frontend, backend, queries, and databases. Use when performance requirements exist, when you suspect performance regressions, when Core Web Vitals or load times need improvement, when N+1 query patterns need fixing, or when profiling reveals bottlenecks. | Stars: 119k | Updated | Claude CodeCodex | Verified | Install |
| agent-cost-reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json. | Stars: 118k | Updated | Claude CodeCodex | Verified | Install |
| agent-cost-reportthedotmack/claude-memBelievable agent cost report for any period, default the last 7 full days PT, not counting today. Measured tokens from Claude Code transcripts priced at OpenRouter list prices (ESTIMATED), measured provider spend when a sanctioned source exists, note-taker cost separate, Timing-style HTML/PDF plus report.json, line-items.csv, evidence.json. | Stars: 118k | Updated | Claude CodeCodex | Verified | Install |