/pgvector-semantic-search

name: pgvector-semantic-search
description: |
  Use this skill for setting up vector similarity search with pgvector for AI/ML embeddings, RAG applications, or semantic search.

  **Trigger when user asks to:**
  - Store or search vector embeddings in PostgreSQL
  - Set up semantic search, similarity search, or nearest neighbor search
  - Create HNSW or IVFFlat indexes for vectors
  - Implement RAG (Retrieval Augmented Generation) with PostgreSQL
  - Optimize pgvector performance, recall, or memory usage
  - Use binary quantization for large vector datasets

  **Keywords:** pgvector, embeddings, semantic search, vector similarity, HNSW, IVFFlat, halfvec, cosine distance, nearest neighbor, RAG, LLM, AI search

  Covers: halfvec storage, HNSW index configuration (m, ef_construction, ef_search), quantization strategies, filtered search, bulk loading, and performance tuning.
license: Apache-2.0
compatibility: Requires PostgreSQL 15+ with the pgvector extension
metadata:
  author: tigerdata

pgvector for Semantic Search

Semantic search finds content by meaning rather than exact keywords. An embedding model converts text into high-dimensional vectors, where similar meanings map to nearby points. pgvector stores these vectors in PostgreSQL and uses approximate nearest neighbor (ANN) indexes to find the closest matches quickly—scaling to millions of rows without leaving the database. Store your text alongside its embedding, then query by converting your search text to a vector and returning the rows with the smallest distance.

This guide covers pgvector setup and tuning—not embedding model selection or text chunking, which significantly affect search quality. Requires pgvector 0.8.0+ for all features (`halfvec`, `binary_quantize`, iterative scan).

Golden Path (Default Setup)

Use this configuration unless you have a specific reason not to.

• Embedding column data type: `halfvec(N)` where `N` is your embedding dimension (must match everywhere). Examples use 1536; replace with your dimension `N`.
• Distance: cosine (`<=>`)
• Index: HNSW (`m = 16`, `ef_construction = 64`). Use `halfvec_cosine_ops` and query with `<=>`.
• Query-time recall: `SET hnsw.ef_search = 100` (good starting point from published benchmarks, increase for higher recall at higher latency)
• Query pattern: `ORDER BY embedding <=> $1::halfvec(N) LIMIT k`

This setup provides a strong speed–recall tradeoff for most text-embedding workloads.

Core Rules

• **Enable the extension** in each database: `CREATE EXTENSION IF NOT EXISTS vector;`
• **Use HNSW indexes by default**—superior speed-recall tradeoff, can be created on empty tables, no training step required. Only consider IVFFlat for write-heavy or memory-bound workloads.
• **Use `halfvec` by default**—store and index as `halfvec` for 50% smaller storage and indexes with minimal recall loss.
• **Index after bulk loading** initial data for best build performance.
• **Create indexes concurrently** in production: `CREATE INDEX CONCURRENTLY ...`
• **Use cosine distance by default** (`<=>`): For non-normalized embeddings, use cosine. For unit-normalized embeddings, cosine and inner product yield identical rankings; default to cosine.
• **Match query operator to index ops**: Index with `halfvec_cosine_ops` requires `<=>` in queries; `halfvec_l2_ops` requires `<->`; mismatched operators won't use the index.
• **Always cast query vectors explicitly** (`$1::halfvec(N)`) to avoid implicit-cast failures in prepared statements.
• **Always use the same embedding model for data and queries**. Similarity search only works when the model generating the vectors is the same.

Type Rules

• Store embeddings as `halfvec(N)`
• Cast query vectors to `halfvec(N)`
• Store binary quantized vectors as `bit(N)` in a generated column
• Do not mix `vector` / `halfvec` / `bit` without explicit casts
• Never call `binary_quantize()` on table columns inside `ORDER BY`; store it instead
• Dimensions must match: a `halfvec(1536)` column requires query vectors cast as `::halfvec(1536)`.

Standard Pattern

-- Store and index as halfvec
CREATE TABLE items (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  contents TEXT NOT NULL,
  embedding halfvec(1536) NOT NULL  -- NOT NULL requires embeddings generated before insert, not async
);
CREATE INDEX ON items USING hnsw (embedding halfvec_cosine_ops);

-- Query: returns 10 closest items. $1 is the embedding of your search text.
SELECT id, contents FROM items ORDER BY embedding <=> $1::halfvec(1536) LIMIT 10;

For other distance operators (L2, inner product, etc.), see the [pgvector README](https://github.com/pgvector/pgvector).

HNSW Index

The recommended index type. Creates a multilayer navigable graph with superior speed-recall tradeoff. Can be created on empty tables (no training step required).

CREATE INDEX ON items USING hnsw (embedding halfvec_cosine_ops);

-- With tuning parameters
CREATE INDEX ON items USING hnsw (embedding halfvec_cosine_ops) WITH (m = 16, ef_construction = 64);

HNSW Parameters

| Parameter | Default | Description | |-----------|---------|-------------| | `m` | 16 | Max connections per layer. Higher = better recall, more memory | | `ef_construction` | 64 | Build-time candidate list. Higher = better graph quality, slower build | | `hnsw.ef_search` | 40 | Query-time candidate list. Higher = better recall, slower queries. Should be ≥ LIMIT. |

**ef_search tuning (rough guidelines—actual results vary by dataset):**

| ef_search | Approx Recall | Relative Speed | |-----------|---------------|----------------| | 40 | lower (~95% on some benchmarks) | 1x (baseline) | | 100 | higher | ~2x slower | | 200 | very-high | ~4x slower | | 400 | near-exact | ~8x slower |

-- Set search parameter for session
SET hnsw.ef_search = 100;

-- Set for single query
BEGIN;
SET LOCAL hnsw.ef_search = 100;
SELECT id, contents FROM items ORDER BY embedding <=> $1::halfvec(1536) LIMIT 10;
COMMIT;

#