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 |
scripts/semantic_search.sql
View on GitHub-- Semantic search functions (pgvector).
--
-- Shape: take LIMIT rows from the index inside a MATERIALIZED CTE and apply
-- the similarity threshold outside it. With the threshold in the same query
-- block the executor keeps scanning the index past it (pgvector's documented
-- form). Put every other filter INSIDE the CTE.
--
-- match_threshold is a cosine-similarity cutoff. Its useful value is
-- model-specific: a cutoff that suits one embedding model returns nothing for
-- another. Leave it NULL for plain top-k, or calibrate it on your eval set.
--
-- None of these set hnsw.ef_search (default 40; without iterative scans,
-- results are limited by that candidate list). The caller owns the trade-off: SET LOCAL it in the same
-- transaction when a transaction pooler sits in front of Postgres.
CREATE OR REPLACE FUNCTION match_documents(
query_embedding vector(1536),
match_threshold FLOAT DEFAULT NULL,
match_count INT DEFAULT 10
)
RETURNS TABLE (id INTEGER, title TEXT, content TEXT, metadata JSONB, similarity FLOAT)
LANGUAGE plpgsql STABLE
AS $$
BEGIN
RETURN QUERY
WITH nearest AS MATERIALIZED (
SELECT d.id, d.title, d.content, d.metadata,
d.embedding <=> query_embedding AS distance
FROM documents d
WHERE d.embedding IS NOT NULL
ORDER BY distance
LIMIT match_count
)
SELECT n.id, n.title, n.content, n.metadata, (1 - n.distance)::FLOAT
FROM nearest n
WHERE match_threshold IS NULL OR (1 - n.distance) > match_threshold
ORDER BY n.distance + 0;
END;
$$;
-- Optional JSONB containment filter, e.g. '{"category": "news"}'.
-- A selective filter needs iterative scan, or the index hands back its first
-- ef_search candidates and the filter leaves fewer than match_count of them.
CREATE OR REPLACE FUNCTION match_documents_filtered(
query_embedding vector(1536),
filter_metadata JSONB DEFAULT NULL,
match_threshold FLOAT DEFAULT NULL,
match_count INT DEFAULT 10
)
RETURNS TABLE (id INTEGER, title TEXT, content TEXT, metadata JSONB, similarity FLOAT)
LANGUAGE plpgsql STABLE
SET hnsw.iterative_scan = 'relaxed_order'
AS $$
BEGIN
RETURN QUERY
WITH nearest AS MATERIALIZED (
SELECT d.id, d.title, d.content, d.metadata,
d.embedding <=> query_embedding AS distance
FROM documents d
WHERE d.embedding IS NOT NULL
AND (filter_metadata IS NULL OR d.metadata @> filter_metadata)
ORDER BY distance
LIMIT match_count
)
SELECT n.id, n.title, n.content, n.metadata, (1 - n.distance)::FLOAT
FROM nearest n
WHERE match_threshold IS NULL OR (1 - n.distance) > match_threshold
ORDER BY n.distance + 0; -- relaxed order: re-sort; "+ 0" is needed on Postgres 17+
END;
$$;
-- Chunks with their parent document. Rank chunks through the index first and
-- join afterwards: a join inside the ordered scan can push the planner off it.
CREATE OR REPLACE FUNCTION match_chunks(
query_embedding vector(1536),
match_threshold FLOAT DEFAULT NULL,
match_count INT DEFAULT 10
)
RETURNS TABLE (
chunk_id INTEGER, document_id INTEGER, document_title TEXT,
chunk_content TEXT, chunk_index INTEGER, similarity FLOAT
)
LANGUAGE plpgsql STABLE
AS $$
BEGIN
RETURN QUERY
WITH nearest AS MATERIALIZED (
SELECT c.id, c.document_id, c.content, c.chunk_index,
c.embedding <=> query_embedding AS distance
FROM chunks c
WHERE c.embedding IS NOT NULL
ORDER BY distance
LIMIT match_count
)
SELECT n.id, n.document_id, d.title, n.content, n.chunk_index,
(1 - n.distance)::FLOAT
FROM nearest n
JOIN documents d ON d.id = n.document_id
WHERE match_threshold IS NULL OR (1 - n.distance) > match_threshold
ORDER BY n.distance + 0;
END;
$$;
-- 2000 < N <= 4000 dimensions, stored at full precision as vector(3072) and
-- indexed as halfvec (see indexes.sql). The query must cast exactly like the
-- index expression, or the planner cannot use the index.
/*
CREATE OR REPLACE FUNCTION match_documents_halfvec(
query_embedding vector(3072),
match_threshold FLOAT DEFAULT NULL,
match_count INT DEFAULT 10
)
RETURNS TABLE (id INTEGER, title TEXT, content TEXT, metadata JSONB, similarity FLOAT)
LANGUAGE plpgsql STABLE
AS $$
BEGIN
RETURN QUERY
WITH nearest AS MATERIALIZED (
SELECT d.id, d.title, d.content, d.metadata,
d.embedding::halfvec(3072) <=> query_embedding::halfvec(3072) AS distance
FROM documents d
WHERE d.embedding IS NOT NULL
ORDER BY distance
LIMIT match_count
)
SELECT n.id, n.title, n.content, n.metadata, (1 - n.distance)::FLOAT
FROM nearest n
WHERE match_threshold IS NULL OR (1 - n.distance) > match_threshold
ORDER BY n.distance + 0;
END;
$$;
*/
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 |