Mmcp.market

postgres-semantic-search skill

by laguagu·laguagu/claude-code-nextjs-skills·67 stars·MIT

Builds and tunes search in PostgreSQL: pgvector semantic search, full-text and pg_trgm keyword search, hybrid RRF fusion, ParadeDB BM25, reranking and retrieval evals. Use when adding or debugging vector, hybrid, fuzzy or RAG retrieval in Postgres, even if the user only says search results are bad: HNSW, IVFFlat or halfvec choices, filtered or thresholded vector queries returning too few rows, a keyword arm that matches nothing, websearch_to_tsquery or unaccent problems, autocomplete and typo tolerance, non-English or inflected-language corpora, query translation, choosing or placing a cross-encoder reranker, or measuring Hit@K and MRR before adopting a change. For general Postgres schema, RLS or query tuning unrelated to retrieval, use supabase-postgres-best-practices.

A100/100content scan

Is the postgres-semantic-search skill safe?

Clean: nothing in its files matched our rules. We read 6 files in the folder on 2026-09-28.

No findings.

Install the postgres-semantic-search skill

A skill is a folder. Copy it into your agent's skills folder and the agent loads it when the task matches its description. 

git clone --depth 1 https://github.com/laguagu/claude-code-nextjs-skills.git /tmp/claude-code-nextjs-skills
mkdir -p ~/.claude/skills
cp -r /tmp/claude-code-nextjs-skills/skills/postgres-semantic-search ~/.claude/skills/postgres-semantic-search
available in every project

In the Claude apps, zip the folder and upload it from the Skills settings. The folder on GitHub

The instructions your agent would load

SKILL.md as published, without the frontmatter. Read it on GitHub

PostgreSQL Semantic Search

Decisions, measured findings and silent failure modes for search built on Postgres, plus tested SQL building blocks in scripts/. Syntax the official docs cover well is left to them; what is here goes wrong without an error.

Build order

terms, full questions, which languages. That decides which arms you need.

  1. Look at what users actually type: identifiers and codes, one-to-three-word

baseline.

  1. Start with vector search, and keep exact search (no index) as the recall

(evaluation.md).

  1. Build two eval sets, long questions and short terms, before tuning

queries that need it (hybrid-search.md).

  1. Add a keyword arm and fuse with RRF only where it beats vector-only on the

(reranking.md).

  1. Add a reranker last, over the head of a good shortlist

Change one thing at a time, and keep a change only if its gain exceeds the run-to-run spread on both eval sets.

Choosing

up to 4,000 index a halfvec cast (or use a halfvec column); above that, binary quantization or Matryoshka truncation.

  • Column type by dimensions, not provider: vector(N) indexes up to 2,000;

count decides it: measure recall and latency against exact search.

  • HNSW by default, IVFFlat when memory or build time rules HNSW out. No row

the provider's current docs, prefer multilingual models for non-English text, and evaluate on the target language before committing.

  • Models change every few months. Pick embedding and reranker models from

pg_search). Plain FTS with the fixes below is often enough.

  • BM25 is not available everywhere: managed hosts differ (Neon removed

Silent failures

These return fewer rows, zero rows or plausible results, never an error.

pgvector (pgvector.md):

LIMIT, or a selective WHERE, silently returns fewer. Enable hnsw.iterative_scan (off by default), or use partial indexes or partitions.

  • An HNSW scan returns at most hnsw.efsearch rows** (default 40). A larger

recall against exact search stops moving. If EXPLAIN shows a seq scan, tuning does nothing, and the seq scan may be the faster plan.

  • The default efsearch can cost recall** with no warning. Raise it until

inside it.

  • A distance threshold goes outside a MATERIALIZED CTE, other filters

same transaction, or a function-level SET.

  • SET is per connection. Behind a transaction pooler use SET LOCAL in the

returns nothing for another. 1 - (a <#> b) is not cosine similarity.

  • Similarity cutoffs are model-specific: a cutoff tuned for one model

sql template expands an array into ($1, $2, ...); vector input rejects both. Send JSON.stringify(embedding) and cast with ::vector.

  • Clients mangle JS arrays: node-postgres sends {0.1,0.2} and Drizzle's

Keyword (keyword-search.md):

nothing. Rewrite plaintotsquery output to OR and rank with tsrank_cd.

  • Both stock tsquery parsers AND every term, so a long question matches

Fold only decorative accents, inside a text search configuration.

  • unaccent merges distinct words in Finnish, Swedish, German or Turkish.

stemming. Strip them at ingest and query time.

  • Zero-width characters from CMS exports glue onto tokens and block

an ORM migration. If hybrid and vector-only return identical lists, the keyword arm is dead.

  • A generated tsvector column can silently become a plain NULL column after

hyphenated tokens (prefix_tsquery.sql).

  • Prefix matching in inflected languages must OR-join terms and expand

against long text.

  • % compares whole strings; use <% for prefixes and short queries

Non-English and chunking

characters per token against English's 4; an English-derived cap overflows the embedding endpoint and can fail the whole batch.

  • Cap chunks by the language's token rate. Finnish runs about 2.5

Translate the query into a sentence, not a keyword list (a keyword list scored 14 points below no translation), and consider two-pass fusion.

  • Off-language queries: hybrid silently degenerates to vector search.

and its generic word takes over the ranking. Trim parts an order of magnitude commoner than the rest of their own phrase.

  • A multi-word synonym expansion enters the tsquery as independent words,

fine-grained search over the subtitle cues inside the matched segment finds the moment.

  • Transcripts: chunk length barely changed which video was found. A second,

More skills from laguagu/claude-code-nextjs-skills

  • Aai-appFull-stack AI application generator with Next.js, AI SDK, and ai-elements. Use when creating chatbots, agent dashboards, or custom AI applications.
  • Aai-elementsBuild AI chat interfaces with pre-built shadcn-style components (Message, Conversation, PromptInput, Reasoning, Sources, Tool, Artifact, CodeBlock, Suggestion, Task, Image, ChainOfThought, InlineCitation, WebPreview, Checkpoint, Plan, Queue, ModelSelector, and more). Use when adding AI chat UI to a Next.js + AI SDK app, installing AI Elements components via the CLI (`bun x ai-elements@latest add message` or `npx shadcn@latest add @ai-elements/message`), composing message displays with markdown, building prompt inputs with attachments, or rendering streaming reasoning and tool output.
  • Aai-sdkAnswer questions about the AI SDK and help build AI-powered features. Use when developers ask about Vercel AI SDK, generateText, streamText, ToolLoopAgent, useChat, providers, tools, structured output, embeddings, streaming, or adding AI to an app. First identify the installed major version and route version-specific work: use ai-sdk-7 for AI SDK 7 features/migrations such as WorkflowAgent, HarnessAgent, reasoning, runtime/tools context, toolApproval, telemetry, realtime, or v6-to-v7 upgrades; use ai-sdk-6 for v6 code.
  • Aai-sdk-6Vercel AI SDK v6 development, for projects already on ai@6. Use when building or maintaining AI agents, chatbots, tool integrations, streaming apps, or structured output in a v6 codebase. New projects and ai@7 code use ai-sdk-7; an unknown version goes through ai-sdk. Covers ToolLoopAgent, useChat, generateText, streamText, tool approval, smoothStream, provider tools, MCP integration, and Output patterns.
  • Aai-sdk-7Vercel AI SDK v7 development and migration. Use when building or upgrading AI SDK 7 apps, especially ToolLoopAgent, WorkflowAgent, HarnessAgent, Claude Code/Codex/Pi harnesses, runtimeContext, toolsContext, toolApproval, telemetry, reasoning, file or skill uploads, realtime, video generation, or v6-to-v7 breaking changes. For AI SDK v6 code use ai-sdk-6; for version discovery and general doc lookup use ai-sdk.
  • Acache-componentsExpert guidance for Next.js Cache Components and Partial Prerendering (PPR). Use when implementing 'use cache' directive, configuring cache lifetimes with cacheLife(), tagging cached data with cacheTag(), invalidating caches with updateTag()/revalidateTag(), optimizing static vs dynamic content boundaries, instant navigation validation, 'use cache: private', pass-through/interleaving patterns, GET Route Handler caching, debugging cache issues, and reviewing Cache Component implementations.
  • Achrome-devtoolsTests in real browsers via Chrome DevTools MCP. Use when building or debugging anything that runs in a browser. Use when you need to inspect the DOM, capture console errors, analyze network requests, profile performance (LCP/CLS/INP), or verify visual output with real runtime data. Complements Playwright — use this for live debugging and performance work, Playwright for stable E2E test suites.
  • Afrontend-designGuidance for distinctive, intentional visual design when building new UI or reshaping an existing one. Helps with aesthetic direction, typography, and making choices that don't read as templated defaults.
  • AgoOpens the running app in a browser and verifies that recent UI changes actually work. Use for any quick smoke test of recent work — "go", "test in browser", "check in browser", "make sure it works", "verify it works", "did it work", "works on mobile" — including when the user appends "...and make sure it works" to a UI request. For design critique, use go-ui or web-design-guidelines.
  • AhandoffWrite or update a HANDOFF.md so a fresh agent can continue this work. Use when the user says "handoff", "compact this", "context is full", or "/clear and continue".
  • Chetzner-cloudManage Hetzner Cloud infrastructure with the `hcloud` CLI — servers, networks, firewalls, load balancers, volumes, DNS zones, SSH keys, primary/floating IPs, snapshots, certificates, placement groups, storage boxes. Use whenever the user mentions Hetzner, hcloud, VPS provisioning, or Hetzner location codes (fsn1, hel1, nbg1, ash, hil, sin) — even if they don't say "hcloud". CLI-only; does NOT cover Hetzner Robot (dedicated servers, separate product and API).
  • AiconsFind, fetch, and install the right icon or logo from the right source — brand marks, country flags, file-type icons (PDF, DOCX, ZIP), and UI glyphs — and keep them visually consistent with the app. Use when the project's icon library has no match, when svgl comes up empty, or when the user asks for a flag, a file-type badge, a brand logo, or just "an icon for X". Covers the Iconify search API (200k+ icons across flags, file types, logos and UI sets), the svgl shadcn registry for full-colour brand logos, family and stroke-weight matching so a borrowed icon does not look pasted in, and fallback sources when neither Iconify nor svgl has the mark. Triggers on "add an icon", "country flag", "flag icon", "PDF icon", "file type icon", "brand logo", "sign in with Google/GitHub", "language switcher", "svgl", "iconify", "find an icon". For overall visual direction rather than sourcing one specific mark, use frontend-design; for installing shadcn components generally, use shadcn.

All agent skills → · MCP servers