pgvector: vector search in PostgreSQL

#The argument: don't add a service

A local document-processing setup already runs a model server, an encoding step, and document storage. Adding a dedicated vector database means one more container, one more port, one more backup, and one more thing that can become out of sync with your relational data when a document is deleted.

If PostgreSQL is already there — and in a business application, it almost always is — pgvector eliminates this entire category of problems. Your text chunks live in a table alongside the documents they came from, with foreign keys keeping them consistent, and a deletion propagates as you’d expect, with no separate cleanup task to write or monitor. The project also adds exact and approximate search, single-precision, half-precision, binary, and sparse vectors, five distances (L2, dot product, cosine, L1, Hamming, Jaccard), and inherits PostgreSQL’s ACID compliance, point-in-time recovery, and joins for free.

#How it works in practice

You store a vector column alongside your usual columns. A query sorts rows by their distance from the question vector and returns the closest ones. Three distance operators cover the common cases, and the one you choose must match the embedding model’s convention: this is the most common source of quietly mediocre results. For vectors normalized to length 1 (as with OpenAI and most recent embedding models), the dot product is the fastest to calculate and produces the same ranking as cosine similarity.

#The minimum SQL to get started

1. 01
   Enable the extension

   CREATE EXTENSION IF NOT EXISTS vector; — a single command, to run once per database.
2. 02
   Add the column

   ALTER TABLE chunks ADD COLUMN embedding vector(1024); — the dimension must exactly match that of the embedding model used.
3. 03
   Load the data before indexing

   Bulk loading via COPY is faster without an existing index; create the index once a representative number of rows is present.
4. 04
   Create the index

   CREATE INDEX CONCURRENTLY ON chunks USING hnsw (embedding vector_cosine_ops); — the CONCURRENTLY option prevents writes from being blocked during construction, which can take a long time on a large table.
5. 05
   Query

   SELECT contenu FROM chunks WHERE document_id IN (SELECT id FROM documents WHERE utilisateur_autorise($1)) ORDER BY embedding <=> $2 LIMIT 5; — authorization filtering and similarity sorting in the same query.

#Dimension limits, in concrete terms

pgvector defines four column types, each with its own indexable dimension limit. The standard vector type (single precision, 4 bytes per element) can be indexed up to 2 000 dimensions, covering most common open-source embedding models (384 to 1 024 dimensions). A larger model such as OpenAI's text-embedding-3-large, with 3 072 dimensions by default, exceeds this limit: the documented workaround is either to reduce the dimension when generating the embedding (the API supports this) or switch to the halfvec type, which stores values in half precision (2 bytes per element, half the space) and can be indexed up to 4 000 dimensions. The bit type (binary vectors, Hamming or Jaccard distances) supports up to 64 000 indexable dimensions, while sparsevec (sparse vectors) supports 1 000 indexed nonzero elements—beyond that, PostgreSQL still stores the column (up to 16 000 dimensions for vector, halfvec, and sparsevec) but cannot index it, requiring an exact scan.

#Filtering and access rights

A real-world question rarely concerns the entire corpus: you want the passages from this service, after this date, in the documents this user is authorized to read. In a dedicated vector database, this is a metadata filter with its own syntax and edge cases. In PostgreSQL, it is a WHERE clause alongside a similarity sort, joined to your users table if necessary.

A pitfall documented by the project itself is worth knowing before you discover it in production: with an approximate index (HNSW or IVFFlat), the filter is applied after the index scan, not before. If a condition retains only 10% of rows and the default hnsw.ef_search parameter is 40, only 4 rows surface on average—not the ten requested. The official workaround, available since version 0.8.0, is called iterative index scanning (SET hnsw.iterative_scan = strict_order), which automatically reruns the scan until it finds enough results instead of silently returning a truncated set.

#Where it stops

Large-scale memory

Millions of full-precision vectors are bulky; halfvec and binary quantization reduce the footprint, but dedicated engines push compression further natively. This is the clearest gap.

Index build time

An HNSW index on a very large table is slow and resource-intensive; in production, building it with CREATE INDEX CONCURRENTLY avoids blocking writes during the operation.

Competition

Putting heavy vector search alongside your transactional workload puts both on the same server. Read replicas help; separating the roles helps more.

The vector dimension

Indexed vectors have a limit based on their type (2,000 for vector, 4,000 for halfvec). Common models work; a very high-dimensional model must be reduced or quantized.

Hybrid search

PostgreSQL supports full-text search (tsvector), and you can combine it with vector distance, but the ergonomics are up to you: you have to run the two queries separately and then merge the rankings, for example with Reciprocal Rank Fusion, instead of getting a native hybrid score in a single query.

Horizontal scaling

Beyond a single server, the documented approach uses PostgreSQL read replicas or a distribution tool such as Citus or PgDog—an additional component, contrary to the original argument.

#pgvector or a dedicated database

The question isn’t which one is objectively better, but which one fits your situation today. pgvector wins when PostgreSQL is already the application’s system of record: billing, accounts, and source documents. A dedicated engine wins when the vector volume or query throughput exceeds what a single transactional server can handle without degrading the rest of the application, or when the team prefers to isolate the AI component from the rest of the information system for operational rather than pure performance reasons.

• Qdrant: the dedicated vector database
• Milvus, when the volume exceeds what pgvector can handle
• FAISS: the library behind vector search
• Build the complete RAG pipeline
• Choose your embedding model
• The QuelLLM local RAG kit: all components on one page
• Source: official pgvector repository on GitHub
• Source: pgvector index and type documentation
• Source: pgvector scaling guide

#FAQ