pgvector for semantic search

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE documents (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  title TEXT NOT NULL,
  content TEXT NOT NULL,
  embedding vector(768),
  created_at TIMESTAMPTZ DEFAULT NOW(),
  user_id UUID NOT NULL
);

-- vector_cosine_ops matches the distance operator used in queries
CREATE INDEX idx_documents_embedding 
ON documents 
USING hnsw (embedding vector_cosine_ops);

import { Pool } from 'pg';
import { openai } from '@ai-sdk/openai';
import { embed } from 'ai';

const pool = new Pool({ connectionString: process.env.DATABASE_URL });

async function upsertDocument(title: string, content: string, userId: string) {
  // Generate embedding
  const { embedding } = await embed({
    model: openai.embedding('text-embedding-3-small'),
    value: `${title} ${content}`,
  });

  // Single transactional upsert
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    await client.query(
      `INSERT INTO documents (title, content, embedding, user_id)
       VALUES ($1, $2, $3, $4)
       ON CONFLICT (id) DO UPDATE 
       SET 

title = $1, content = $2, embedding = $3 RETURNING id, [title, content, [${embedding.join(',')}]`, userId] ); await client.query('COMMIT'); } catch (err) { await client.query('ROLLBACK'); throw err; } finally { client.release(); } }

async function semanticSearch( queryText: string, userId: string, limit: number = 5 ) { const { embedding } = await embed({ model: openai.embedding('text-embedding-3-small'), value: queryText, });

const result = await pool.query( SELECT id, title, content, 1 - (embedding <=> $1) AS similarity FROM documents WHERE user_id = $2 ORDER BY embedding <=> $1 LIMIT $3, [[${embedding.join(',')}], userId, limit] );

return result.rows; }

**4. Query-time tuning**
HNSW uses `ef_search` to control the candidate pool size during traversal. The default is 40. Increase it for higher recall at the cost of latency:
```sql
SET hnsw.ef_search = 100;

Apply this per-connection or via ALTER DATABASE for workloads requiring >95% recall.

Pitfall Guide

1. Using L2 distance for normalized embeddings

Cosine and L2 are mathematically equivalent only when vectors are unit-normalized. Most embedding APIs return normalized vectors. Using L2 (<->) on normalized data adds unnecessary CPU cycles and can distort ranking when magnitudes vary. Stick to <=> and vector_cosine_ops unless your pipeline explicitly preserves magnitude semantics.

2. Ignoring ef_search tuning

The default ef_search = 40 prioritizes speed over recall. In production semantic search, this yields 80–85% recall on benchmark datasets. Raising it to 100–200 typically pushes recall above 95% with <5ms additional latency. Failure to tune this parameter is the #1 cause of "inaccurate" search results in pgvector deployments.

3. Under-provisioning memory for HNSW

HNSW builds an in-memory graph structure. Index builds and high-concurrency queries consume RAM proportional to m (neighbors per node, default 16) and ef_construction. If maintenance_work_mem is too low, PostgreSQL spills to disk, causing index builds to take hours instead of minutes. Set maintenance_work_mem = '1GB' or higher before building indexes on >1M rows.

4. Skipping index maintenance after bulk updates

HNSW indexes do not auto-optimize like B-tree indexes. Bulk INSERT or UPDATE operations fragment the graph, increasing traversal depth and latency. Run REINDEX INDEX idx_documents_embedding; after loading >10% of the table, or schedule periodic reindexing during maintenance windows.

5. Relying solely on vector similarity

Vectors capture semantic proximity, not exact string matching, keyword presence, or structural constraints. A query for "API v2 migration guide" may return results about "API version 2" but miss documents explicitly titled "v2 migration". Combine HNSW with tsvector full-text search or ILIKE filters. Use UNION or JOIN to merge ranked results, or implement weighted scoring in application logic.

6. Assuming pgvector replaces full-text search

pgvector does not implement tokenization, stemming, or stop-word removal. It stores fixed-length float arrays. Full-text search and vector search solve different problems. Production systems use both: tsvector for exact/keyword matching, vector for conceptual similarity. Treat them as complementary layers, not alternatives.

Best Practices from Production

• Batch embeddings: Generate embeddings in chunks of 100–500 to respect API rate limits and reduce network overhead.
• Use connection pooling: pgvector queries are CPU-bound during traversal. Pool connections to avoid connection handshake overhead under load.
• Monitor index health: Query pg_stat_user_indexes for idx_scan, idx_tup_read, and idx_tup_fetch. Sudden drops indicate fragmentation or query plan degradation.
• Set hnsw.ef_construction during index creation: Higher values (default 64) improve index quality at build time. Use CREATE INDEX ... WITH (ef_construction = 128) for critical datasets.
• Implement circuit breakers: If embedding API latency spikes, degrade gracefully by falling back to text-only search or returning cached results.

Production Bundle

Action Checklist

• Enable pgvector: Run CREATE EXTENSION vector; in your target database before schema migrations.
• Match distance operators to ops class: Use vector_cosine_ops with <=>, vector_l2_ops with <->, vector_ip_ops with <#>.
• Tune ef_search per workload: Set SET hnsw.ef_search = 100; for recall-heavy queries; keep default for low-latency UI search.
• Provision maintenance_work_mem: Set to 1–4GB before building HNSW indexes on >500k rows to prevent disk spills.
• Schedule index reindexing: Run REINDEX after bulk updates exceeding 10% of table size or when query latency degrades.
• Implement hybrid search: Combine <=> ranking with tsvector or ILIKE filters to handle exact matches and structured constraints.
• Batch embedding generation: Process documents in chunks of 100–500 to avoid API rate limits and reduce transaction overhead.
• Monitor index statistics: Track pg_stat_user_indexes metrics weekly to detect fragmentation or plan regression.

Decision Matrix

Scenario	Recommended Approach	Why	Cost Impact
<500k vectors, strict ACID, existing Postgres	pgvector HNSW	Single-system transactionality, zero sync overhead, lower infra cost	-40% vs external vector DB
>10M vectors, sub-10ms latency SLA	External vector DB + pgvector metadata	HNSW memory footprint scales non-linearly; specialized stores optimize for massive graphs	+25% infra, but meets latency SLA
Multi-tenant SaaS with row-level security	pgvector with RLS + HNSW	Postgres RLS applies to vector tables natively; external stores require app-layer filtering	No additional cost, simplifies auth
Budget-constrained startup	pgvector on existing RDS/Supabase	Eliminates separate vector store subscription, reduces CI/CD complexity	-$100–$300/month
Hybrid search mandatory (exact + semantic)	pgvector + tsvector	Native SQL joins and UNION ranking avoid cross-system aggregation latency	Neutral, leverages existing Postgres features

Configuration Template

-- Enable extension
CREATE EXTENSION IF NOT EXISTS vector;

-- Table definition
CREATE TABLE items (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  title TEXT NOT NULL,
  body TEXT NOT NULL,
  embedding vector(768),
  status TEXT DEFAULT 'active',
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- HNSW index with tuned construction parameters
CREATE INDEX idx_items_embedding 
ON items 
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 128);

-- Query-time recall tuning (apply per session or globally)
SET hnsw.ef_search = 100;

-- Hybrid search example
SELECT id, title, 
       1 - (embedding <=> '[0.1,0.2,...]') AS vec_score,
       ts_rank(to_tsvector('english', body), plainto_tsquery('migration')) AS txt_score
FROM items
WHERE status = 'active'
ORDER BY (vec_score * 0.7 + txt_score * 0.3) DESC
LIMIT 10;

Quick Start Guide

1. Install the extension: Connect to your PostgreSQL instance and run CREATE EXTENSION IF NOT EXISTS vector;. If using a managed provider, ensure the version supports pgvector (PostgreSQL 14+).
2. Add the vector column: Alter your target table or create a new one with embedding vector(768) (match your model's dimensionality).
3. Generate and insert embeddings: Use your preferred SDK to generate embeddings, format them as [x1,x2,...,xn], and insert via parameterized queries. Wrap in a transaction if updating relational data simultaneously.
4. Build the HNSW index: Execute CREATE INDEX ON table USING hnsw (embedding vector_cosine_ops);. Set maintenance_work_mem to 1GB+ beforehand for datasets >100k rows.
5. Query with distance operators: Run SELECT * FROM table ORDER BY embedding <=> '[query_vector]' LIMIT 10;. Tune hnsw.ef_search in your session for recall/latency balance.