pgvector: Vector Search & Embeddings in PostgreSQL

Q: Can I combine vector search with normal SQL filters?

Yes — that's pgvector's headline strength. You can WHERE by metadata, JOIN other tables, and enforce access control in the same query as the nearest-neighbor ORDER BY embedding <= $q, with full transactional consistency. Just make sure the filter columns are indexed too, since the vector index doesn't accelerate the WHERE clause.

pgvector

pgvector is a PostgreSQL extension that adds a vector data type and vector similarity search to the database you already have. Instead of standing up a separate vector database for your AI features, you store embeddings in a regular Postgres column, index them, and query for nearest neighbors with SQL — alongside your normal relational data, transactions, and joins.

Its rise tracks the rise of AI applications. Retrieval-augmented generation, semantic search, and recommendations all need to find "the most similar vectors to this one," and pgvector lets teams do that without adopting new infrastructure. For the large number of applications whose vector needs are moderate, "just use Postgres" is now a serious, often preferred, answer.

TL;DR

pgvector adds a vector type and nearest-neighbor search to PostgreSQL — vector search without a separate database.
Store embeddings in a column, index them, and query similarity with distance operators: <-> (L2), <=> (cosine), <#> (inner product).
Two index types: HNSW (fast, accurate queries; slower/larger to build) and IVFFlat (faster to build, less memory, needs tuning). HNSW is the usual default.
The killer feature is combining vector search with SQL — filter by metadata, join tables, and enforce transactions in one query.
Vector indexes are approximate (ANN): you trade a little recall for big speed. Tune the accuracy/speed knobs (ef_search, lists/probes).
Great when you already run Postgres and have moderate scale; a dedicated vector DB may win at very large scale or extreme query volume.

Quick Example

Enable the extension, store embeddings, index them, and find the nearest neighbors to a query vector — all in SQL:

The ORDER BY embedding <=> $query LIMIT 5 is the nearest-neighbor search; everything else is ordinary Postgres.

Core Concepts

The Vector Type and Distance Operators

pgvector introduces a vector(n) column type holding an n-dimensional float array — n must match your embedding model's output dimension (e.g. 1536). Similarity is expressed with distance operators, and you must use the one matching how your embeddings were trained/normalized:

Most text-embedding workflows use cosine (<=>). Match the operator to the index operator class (vector_cosine_ops, vector_l2_ops, vector_ip_ops).

Approximate Nearest Neighbor (ANN)

Finding the exact nearest vectors means scanning every row — fine for thousands of rows, far too slow for millions. Vector indexes use approximate nearest-neighbor search: they find almost the true nearest neighbors, trading a small amount of recall for enormous speedups. This approximation is the whole point of a vector index; you tune how much accuracy you trade for speed.

HNSW vs IVFFlat

HNSW is the common default — best query performance and accuracy, and it can be built before data is loaded. IVFFlat builds faster and uses less memory but should be built after representative data exists (it clusters the data) and needs probes tuned per query.

Building RAG on Postgres

The reason pgvector is compelling: your embeddings live next to your relational data, so retrieval is just SQL. A typical RAG retrieval combines similarity with real filters and joins:

No separate system to keep in sync, no dual writes, and transactional consistency between your documents and their embeddings. This tight integration — vector search with SQL filtering, joins, and transactions — is pgvector's biggest advantage over a standalone vector store.

Best Practices

Choose the Distance That Matches Your Model

Use the distance metric your embedding model expects — cosine for most text embeddings. Using the wrong operator (or a mismatched index op-class) silently returns worse results. Confirm your model's recommended metric and be consistent between the index and the query.

Tune the Accuracy/Speed Knob

Recall is tunable. For HNSW, raise ef_search for higher accuracy (slower) or lower it for speed; for IVFFlat, increase probes for accuracy and set lists appropriately at build time. Measure recall on a labeled set and pick the point that meets your quality bar at acceptable latency.

Filter and Index Together

If you filter by metadata (tenant, category, date) alongside vector search, ensure those filters are efficient too — a vector index doesn't help the WHERE clause. Index the filter columns, and be aware that heavy pre-filtering can interact with ANN recall; test realistic queries.

Right-Size Dimensions and Storage

Embedding vectors are large; millions of high-dimensional rows consume real memory and disk, and HNSW indexes are memory-hungry. Consider models with smaller dimensions or dimensionality reduction if scale is a concern, and provision memory for the index to stay resident.

Keep Embeddings in Sync With Source Rows

Because embeddings live in the same table/transaction as your data, update them when the source text changes. A stale embedding retrieves the wrong content. Regenerate on content change, ideally within the same write path.

Common Mistakes

Exact Search at Scale (No Index)

Mismatched Distance Metric

Training embeddings for cosine similarity but querying with L2 (<->), or indexing with vector_l2_ops while querying <=>, degrades results quietly. Keep the model's metric, the operator, and the index op-class aligned.

Ignoring Recall

Assuming ANN results are exact leads to "why did it miss the obvious match?" surprises. ANN is approximate by design — measure recall and tune ef_search/probes until quality is acceptable.

FAQ

Do I still need a dedicated vector database if I have pgvector?

Often no. If you already run PostgreSQL and your scale is moderate, pgvector lets you do vector search with SQL filtering, joins, and transactions in one system — simpler than operating a separate store. A dedicated vector database may win at very large scale, extreme query throughput, or when you need specialized features, but "just use Postgres" is a strong default for many apps.

HNSW or IVFFlat — which index should I use?

HNSW is the usual default: best query speed and accuracy, and it can be built before data is loaded. IVFFlat builds faster and uses less memory but must be built after representative data exists (it clusters the data) and needs its probes tuned per query. Start with HNSW unless build time or memory is a hard constraint.

Which distance operator do I use?

Match your embedding model's metric. Most text embeddings use cosine distance (<=>); use L2 (<->) when magnitude matters and inner product (<#>) for certain normalized embeddings. Crucially, align the query operator with the index operator class (vector_cosine_ops, etc.) — a mismatch silently returns worse results.

Why are my results approximate / sometimes missing the best match?

Because vector indexes use approximate nearest-neighbor search, trading a little recall for large speedups — that's how they stay fast on millions of rows. If quality is off, raise the accuracy knob (ef_search for HNSW, probes for IVFFlat), measure recall on a labeled set, and tune to your latency budget.

Can I combine vector search with normal SQL filters?

Yes — that's pgvector's headline strength. You can WHERE by metadata, JOIN other tables, and enforce access control in the same query as the nearest-neighbor ORDER BY embedding <=> $q, with full transactional consistency. Just make sure the filter columns are indexed too, since the vector index doesn't accelerate the WHERE clause.