Vector Search in PostgreSQL with pgvector: A Production Guide

You do not always need a separate vector database. If your data already lives in PostgreSQL, pgvector lets you store embeddings next to your relational data and query them with the same transactions and backups you already trust. Here is how to do it well.

Why keep vectors in PostgreSQL

One database to operate, back up and secure.
Join vector results with real tables and permissions.
Filter by tenant, date or category in the same query.
No extra service to keep in sync with your source of truth.

Enable the extension and store embeddings

The dimension of the vector column must match your embedding model exactly. Changing models means re-embedding everything, so decide the model before you scale.

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE documents (
  id          BIGSERIAL PRIMARY KEY,
  tenant_id   UUID NOT NULL,
  content     TEXT NOT NULL,
  embedding   vector(1536),
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

Choose the right index

Without an index, every query scans every row. HNSW gives the best recall and query speed at the cost of slower writes and more memory; IVFFlat uses less memory but needs a reasonably full table before it is effective. Use cosine distance when your embeddings are normalised.

CREATE INDEX documents_embedding_hnsw
  ON documents USING hnsw (embedding vector_cosine_ops)
  WITH (m = 16, ef_construction = 64);

Query with cosine distance

The cosine distance operator is <=>. Order ascending to get the closest matches, and convert the distance to a similarity score if you want to threshold results.

SELECT id, content, 1 - (embedding <=> $1) AS score
FROM documents
WHERE tenant_id = $2
ORDER BY embedding <=> $1
LIMIT 5;

Hybrid search beats vectors alone

Pure vector search struggles with exact terms such as order numbers, error codes and product SKUs. Combining keyword ranking with vector similarity fixes most of those misses.

SELECT id, content,
       ts_rank(to_tsvector('english', content), plainto_tsquery($1)) AS text_rank,
       1 - (embedding <=> $2) AS vector_score
FROM documents
WHERE to_tsvector('english', content) @@ plainto_tsquery($1)
ORDER BY vector_score DESC
LIMIT 5;

Production notes

Filter with a b-tree index on tenant_id and let the planner combine it with the vector index.
Batch inserts; index maintenance is the expensive part of writes.
Re-embed in the background when you upgrade your embedding model.
Track recall on a small labelled set, not just latency.
Run ANALYZE after large imports so the planner has good statistics.