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/evaluation.md
View on GitHub# Evaluating retrieval changes
Read before adopting any retrieval change: embedding model, chunking, fusion
weights, query rewriting, translation or a reranker. Vendor and paper
benchmarks are mostly English and general domain, and do not predict a
multilingual or domain corpus.
## Contents
- [Two eval sets, and they disagree](#two-eval-sets-and-they-disagree)
- [Building the sets](#building-the-sets)
- [Noise and what a delta means](#noise-and-what-a-delta-means)
- [Four ways a measurement misleads](#four-ways-a-measurement-misleads)
- [Gotchas](#gotchas)
## Two eval sets, and they disagree
Real users mostly type one to three words (93 % of searches in one production
log). An LLM-generated set is made of well-formed questions. A change can help
one population and hurt the other: dropping an over-common word from short
queries helped the short set and hurt the long one, and a cross-encoder
reordering 30 candidates did the reverse. Either set alone gave the wrong
answer once. Keep both:
| Set | Shape | Good for |
| --- | --- | --- |
| Long | 70–500 generated natural-language questions | broad regression cover |
| Short | 15–50 hand-graded domain terms of one to three words | the shape users actually type |
## Building the sets
- **Long set**: sample chunks with enough real text (a few hundred
characters), from documents still in use, and have a cheap model write one
natural question each chunk answers. Store question, expected document and
expected chunk. At 50 questions each result moves the score by 2 points;
at a ~50 % hit rate the binomial standard error is about 7 points. Size the
set for the effect you need to detect; a few hundred gave a useful signal
here.
- **Bias**: a question generated from a chunk is unusually close to that chunk
in embedding space. Vector and RRF baselines look better than in production,
and rerankers and query rewriters look worse, because they reshuffle a
ranking the bias already made near-optimal. Mix in 30–50 hand-written,
live-style questions and compare deltas, not absolute scores.
- **Apply production filters** (date, category, access) when sampling, or Hit@K
collapses for reasons unrelated to ranking.
- **Metrics**: Hit@K (K = 1, 5, 10) and MRR on the retrieval side; p50 and p95
latency with cold and warm runs labelled separately. Re-sample when the corpus
drifts.
## Noise and what a delta means
- A short set with no LLM on the request path can be deterministic. A changed
result is real for those fixtures, but a small set still has uncertainty
about the wider query population. A long set that passes through a query rewriter,
intent splitter or translation gate swung ±3 points between runs of identical
code. Measure your run-to-run spread once, write it next to the set, and do
not believe a smaller delta.
- "No change on the long set" is evidence only if some query in it exercises
the changed code. Check; otherwise say it only rules out global damage.
- Set the minimum useful gain and the latency and cost budget before the
experiment. Report sample size, per-query wins and losses, and an interval
(a paired bootstrap is enough).
## Four ways a measurement misleads
1. **An isolated arm is not the pipeline.** Making the keyword arm more precise
on its own (Hit@1 0.357 → 0.371) lost end to end (Hit@5 0.790 → 0.768 over
four runs): that arm was the only lexical bridge for queries whose terms
could not prefix-match their inflected forms, and fusion was using its noisy
tail. Confirm every change on the full pipeline.
2. **Better retrieval can produce worse answers.** Raising top-k from 15 to 30
lifted Recall@30 by 5.4 points and lowered the share of generated claims
supported by a retrieved source from 82.1 % to 78.8 %. Before changing how
much text reaches the model (top-k, chunk size, rerank depth), run a judged
answer-quality pass. For judging whether a passage supports a claim, one
demanding yes/no question that lists what must match (numbers, conditions,
actors, *may* versus *must*) beat a supports/contradicts/unrelated question
(88.1 % against 81.8 % balanced accuracy on subtly wrong Finnish claims).
3. **An offline sweep predicts ordering, not production numbers.** A sweep at
full precision without the index showed one model 2 points ahead of its
quantized form. Through the production pipeline both found the same
documents, and the quantized one ranked them better, ran 40 % faster and
used a quarter of the memory. Use sweeps to rank candidates, then confirm.
4. **The obvious improvement is often noise, a regression, or unmeasured.** Rejected
changes with written reasons are a good outcome.
## Gotchas
- Bigint ids come back as strings from some drivers; compare
`String(a) === String(b)` when computing ranks.
- If production keeps the HNSW index warm, benchmark warm; otherwise you
measure a different workload. Report cold and warm results separately;
discard warm-up runs only when reporting a deliberately warm workload.
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 |