pgvector: Vector Search in PostgreSQL for AI and RAG Apps

AI applications turn text, images and other content into embeddings: arrays of numbers where similar meanings end up close together. Finding “the most similar documents” then becomes a nearest-neighbour search over vectors. pgvector adds this to PostgreSQL, so your embeddings live next to your regular data, with SQL, transactions, joins, backups and access control included. For many RAG (retrieval-augmented generation) apps, it removes the need for a separate vector database.

Install

# Debian/Ubuntu with the PGDG repository (match your PostgreSQL major version)
sudo apt install postgresql-17-pgvector

Or use the pgvector/pgvector:pg17 Docker image. Then, in your database:

CREATE EXTENSION IF NOT EXISTS vector;

See PostgreSQL extensions.

Store embeddings

The dimension must match your embedding model (for example 384, 768, 1024 or 1536):

CREATE TABLE documents (
  id bigserial PRIMARY KEY,
  source text,
  content text NOT NULL,
  embedding vector(1536)
);

Insert vectors as array literals. Your application computes them with an embedding model (a hosted API or a local model):

INSERT INTO documents (source, content, embedding)
VALUES ('docs/nginx.md', 'To install Nginx on Ubuntu…', '[0.012, -0.034, …]');

Query by similarity

Operator	Distance	Typical use
`<=>`	Cosine distance	Most text embeddings
`<->`	Euclidean (L2)	Some image models
`<#>`	Negative inner product	Normalized embeddings (fastest)

SELECT id, source, content, embedding <=> $1 AS distance
FROM documents
ORDER BY embedding <=> $1
LIMIT 5;

$1 is the query’s embedding. Use the distance your model was trained for (most text models: cosine).

Python example

import psycopg
from pgvector.psycopg import register_vector

conn = psycopg.connect("dbname=app user=app")
register_vector(conn)

query_vec = embed("How do I enable HTTPS in Nginx?")      # your embedding function
rows = conn.execute(
    "SELECT content FROM documents ORDER BY embedding <=> %s LIMIT 5",
    (query_vec,),
).fetchall()
context = "\n\n".join(r[0] for r in rows)                 # pass to the LLM prompt

(pip install psycopg pgvector, plus numpy for array support.)

Indexes: exact vs approximate

Without an index, PostgreSQL compares the query to every row: exact, but slow beyond about 100,000 rows. Approximate indexes trade a little recall for big speed gains:

-- HNSW: best query speed and recall; slower to build, more memory
CREATE INDEX ON documents USING hnsw (embedding vector_cosine_ops);

-- IVFFlat: faster to build, smaller; create after loading data
CREATE INDEX ON documents USING ivfflat (embedding vector_cosine_ops) WITH (lists = 1000);

The operator class must match the distance you query with (vector_cosine_ops for <=>, vector_l2_ops for <->, vector_ip_ops for <#>).

Tuning at query time:

SET hnsw.ef_search = 100;      -- default 40; higher = better recall, slower
SET ivfflat.probes = 20;       -- default 1; higher = better recall, slower

For IVFFlat, a rule of thumb is lists ≈ rows / 1000 (up to 1M rows), and probes ≈ sqrt(lists).

Indexes support up to 2,000 dimensions for vector. For larger models, use the halfvec type (half precision, up to 4,000 dimensions), which also halves storage.

Filtering plus similarity

Real queries combine filters with vector search:

SELECT id, content FROM documents
WHERE source LIKE 'docs/%'
ORDER BY embedding <=> $1
LIMIT 5;

With approximate indexes, strict filters can return fewer rows than requested, because filtering happens after the index scan. Recent pgvector versions add iterative index scans (SET hnsw.iterative_scan = relaxed_order;) to keep scanning until enough rows match. Partial indexes per category, or partitioning, also help.

Hybrid search

Combine vector similarity with keyword matching for better results. Exact terms like error codes or product names often get lost in embeddings. Run both a full-text query and a vector query, and merge the rankings (for example with reciprocal rank fusion). See PostgreSQL full-text search.

Sizing and performance

A 1536-dimension vector is about 6 KB per row. One million embeddings take roughly 6 GB, plus the index.
HNSW indexes work best in RAM. Give PostgreSQL enough shared_buffers and memory, and set maintenance_work_mem high (1–4 GB) during index builds.
Build indexes after bulk loading, and in parallel (max_parallel_maintenance_workers).

See PostgreSQL performance tuning.

pgvector vs dedicated vector databases

	pgvector	Qdrant / Milvus / Weaviate
Operations	One database you already run	Extra service
Joins, transactions, SQL	Yes	Limited
Scale	Millions of vectors on one server	Built for very large, distributed sets
Features	Core ANN search	Advanced filtering, quantization, multi-tenancy

For most apps up to a few million vectors, pgvector is the simplest choice. See vector databases.

Quick reference

Task	SQL
Enable	`CREATE EXTENSION vector;`
Column	`embedding vector(1536)`
Nearest 5 (cosine)	`ORDER BY embedding <=> $1 LIMIT 5`
HNSW index	`USING hnsw (embedding vector_cosine_ops)`
Recall vs speed	`SET hnsw.ef_search = 100;`
Big models	`halfvec(n)`

PostgreSQL
AI
Database