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/setup.sql
View on GitHub-- Extensions and the example schema every other script assumes.
-- Rename tables/columns and change vector(1536) to your model's dimension
-- count in all scripts at once; the functions hard-code both.
CREATE EXTENSION IF NOT EXISTS vector; -- pgvector
CREATE EXTENSION IF NOT EXISTS pg_trgm; -- fuzzy_search.sql, trigram indexes
-- CREATE EXTENSION IF NOT EXISTS unaccent; -- only for a custom FTS config, see references/keyword-search.md
-- CREATE EXTENSION IF NOT EXISTS pg_search; -- hybrid_search_bm25.sql (ParadeDB); needs pgvector first
CREATE TABLE IF NOT EXISTS documents (
id SERIAL PRIMARY KEY,
title TEXT,
content TEXT NOT NULL,
metadata JSONB NOT NULL DEFAULT '{}'::JSONB,
-- N <= 2000: vector(N). 2000 < N <= 4000: halfvec(N), or keep vector(N)
-- and index a halfvec cast (references/pgvector.md). NULLs are not indexed.
embedding vector(1536)
);
CREATE TABLE IF NOT EXISTS chunks (
id SERIAL PRIMARY KEY,
document_id INTEGER NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
chunk_index INTEGER NOT NULL,
content TEXT NOT NULL,
embedding vector(1536),
metadata JSONB NOT NULL DEFAULT '{}'::JSONB,
UNIQUE (document_id, chunk_index)
);
-- Bulk loads: insert first, then build the vector index (indexes.sql).
-- An IVFFlat index built on an empty or tiny table picks bad lists and
-- silently returns too few rows.
SELECT extname, extversion
FROM pg_extension
WHERE extname IN ('vector', 'pg_trgm', 'unaccent', 'pg_search');
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 |