pgvector Tutorial: Vector Search in Postgres

pgvector is a PostgreSQL extension that adds a vector data type and similarity search. It turns the database you already have into a vector database — so your embeddings live next to your products, documents and users, with the same backups, permissions and transactions. For most apps building semantic search or RAG, it's the simplest place to start.

(If embeddings are new: what are embeddings?)

1. Enable the extension

pgvector is available on most managed Postgres providers and installable on your own server. Enable it per database:

CREATE EXTENSION IF NOT EXISTS vector;

2. Store embeddings

The column size must match your embedding model's output dimensions:

CREATE TABLE doc_chunks (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  doc_id     bigint NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
  org_id     bigint NOT NULL,
  content    text   NOT NULL,
  embedding  vector(1024) NOT NULL
);

Insert from your app, passing the embedding as a string like '[0.012,-0.08,...]' (most Postgres client libraries and ORMs have pgvector helpers):

await db.query(
  "INSERT INTO doc_chunks (doc_id, org_id, content, embedding) VALUES ($1, $2, $3, $4)",
  [docId, orgId, chunk, JSON.stringify(embedding)]
);

3. Query by similarity

pgvector adds distance operators:

Operator	Distance
`<=>`	cosine distance (most common for text embeddings)
`<->`	Euclidean (L2) distance
`<#>`	negative inner product

Find the five chunks most similar to a question:

SELECT id, content, 1 - (embedding <=> $1) AS similarity
FROM doc_chunks
WHERE org_id = $2
ORDER BY embedding <=> $1
LIMIT 5;

$1 is the question's embedding, created with the same model as the stored ones. Smaller distance = more similar; 1 - cosine distance gives a similarity score.

4. Add an index

Without an index, Postgres compares the query against every row — exact, but slow beyond tens of thousands of rows. pgvector offers approximate nearest-neighbour (ANN) indexes, trading a little accuracy for a lot of speed.

HNSW is the usual choice:

CREATE INDEX ON doc_chunks USING hnsw (embedding vector_cosine_ops);

Use the operator class matching your query: vector_cosine_ops for <=>, vector_l2_ops for <->, vector_ip_ops for <#>. A mismatch means the index isn't used.
Building HNSW on a large table is slow and memory-hungry; raise maintenance_work_mem for the build, and build with CREATE INDEX CONCURRENTLY on a live table. (Postgres migrations on large tables.)
Tune recall at query time with SET hnsw.ef_search = 100; (default 40) — higher is more accurate and slower.

IVFFlat builds faster and uses less memory, but should be created after the table has data and needs tuning (lists, probes). HNSW is generally the better default.

Check the index is used with EXPLAIN. (Postgres EXPLAIN ANALYZE.)

5. Filtering correctly

Real queries filter — by organisation, by user's permissions, by document type. With an approximate index, Postgres may find the 40 nearest candidates and then apply the WHERE, leaving you fewer than LIMIT results — or none — when the filter is selective.

Fixes:

Iterative index scans (pgvector 0.8+) keep scanning until enough rows pass the filter:
```
SET hnsw.iterative_scan = relaxed_order;
```
A B-tree index on the filter column (org_id) lets Postgres choose a filtered exact search when the filter is very selective.
Partial indexes or partitioning per large tenant or category.

Always filter by permissions in SQL. In multi-tenant apps, never retrieve across tenants and filter afterwards — and never let unpermitted chunks reach the model. (Multi-tenant SaaS on Postgres.)

6. Dimensions and storage

Embeddings are big: 1,024 dimensions × 4 bytes ≈ 4 KB per row, before the index.

halfvec stores half-precision floats — half the size, with negligible quality loss for most uses.
Many embedding models let you request fewer dimensions.
HNSW indexes support up to 2,000 dimensions for vector and 4,000 for halfvec.

7. Hybrid search

Vector search finds meaning; it's weak on exact terms like product codes and names. Combine it with Postgres full-text search — run both and merge results (a common method is reciprocal rank fusion). Hybrid search often beats either alone.

Keeping embeddings fresh

Re-embed a chunk whenever its text changes — a background job is a natural fit. (Background jobs.)
Store which model produced each embedding; switching models means re-embedding everything.
ON DELETE CASCADE from documents to chunks avoids orphaned embeddings.

When you've outgrown pgvector

pgvector handles millions of vectors comfortably on a well-sized server. Consider a dedicated vector database when you have hundreds of millions of vectors, very high query throughput, or need features like built-in multi-vector search — and benchmark first. For most apps, one database is a real advantage.

The summary

CREATE EXTENSION vector; → a vector(n) column → ORDER BY embedding <=> $1 LIMIT k.
Add an HNSW index with the operator class matching your distance.
Handle filters with iterative scans and supporting indexes; filter permissions in SQL.
Use halfvec or fewer dimensions to save space; combine with full-text search for hybrid results.

EasySpawn servers include PostgreSQL alongside your app with daily backups, and Claude Code works on the same server — so it can run the queries in this guide against your real data and show you the EXPLAIN output, not a guess. See how it works or join the waitlist.

pgvector Tutorial: Vector Search in Postgres for RAG and Semantic Search