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/indexes.sql
View on GitHub-- Indexes for the example schema. Build vector indexes after bulk loads.
-- Parameters are pgvector defaults unless a comment says otherwise; tune
-- against exact search on your own queries (references/pgvector.md).
-- Trigram indexes live in fuzzy_search.sql, ParadeDB indexes in
-- hybrid_search_bm25.sql.
-- Vector: HNSW, cosine. The operator class must match the query operator
-- (<=> cosine_ops, <-> l2_ops, <#> ip_ops) or the index is not used.
CREATE INDEX IF NOT EXISTS documents_embedding_hnsw_idx
ON documents USING hnsw (embedding vector_cosine_ops);
CREATE INDEX IF NOT EXISTS chunks_embedding_hnsw_idx
ON chunks USING hnsw (embedding vector_cosine_ops);
-- 2000 < N <= 4000: keep vector(N) and index a halfvec cast. Queries must use
-- the identical expression: ORDER BY embedding::halfvec(3072) <=> $1::halfvec(3072)
-- CREATE INDEX ON documents USING hnsw ((embedding::halfvec(3072)) halfvec_cosine_ops);
-- IVFFlat: less memory and faster builds than HNSW, lower recall at equal speed.
-- Build only once the table holds representative data. Starting points from
-- pgvector: lists = rows / 1000 up to 1M rows, sqrt(rows) above; probes = sqrt(lists).
-- CREATE INDEX ON documents USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100);
-- A few fixed filter values: one partial index per value beats filtering a
-- shared index. Many values or per-tenant isolation: partition instead.
-- CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops)
-- WHERE (metadata->>'category') = 'news';
-- Full-text search. The expression must match the query's to_tsvector call
-- exactly, config included. A stored tsvector column is simpler to keep in sync:
-- ALTER TABLE documents ADD COLUMN tsv tsvector GENERATED ALWAYS AS
-- (setweight(to_tsvector('simple', coalesce(title, '')), 'A') ||
-- setweight(to_tsvector('simple', content), 'B')) STORED;
-- CREATE INDEX ON documents USING gin (tsv);
CREATE INDEX IF NOT EXISTS documents_content_fts_idx
ON documents USING gin (to_tsvector('simple', content));
CREATE INDEX IF NOT EXISTS chunks_content_fts_idx
ON chunks USING gin (to_tsvector('simple', content));
-- Metadata filters (@> containment). Chunk lookups by document use the
-- UNIQUE (document_id, chunk_index) index from setup.sql.
CREATE INDEX IF NOT EXISTS documents_metadata_gin_idx
ON documents USING gin (metadata);
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 |