pgvector is an open-source extension that adds a vector data type and similarity search to PostgreSQL, letting you store embeddings and run…
See how a Postgres extension turns your existing database into a vector store.
pgvector is an extension that teaches PostgreSQL a new column type — vector — plus operators to measure distance between vectors. Once installed, a table can hold an embedding beside its text and metadata, and you can query for the nearest vectors with plain SQL.
The big win for RAG is consolidation: your document chunks, their embeddings, and their metadata live in one database you already operate and back up. You retrieve semantically and filter with normal SQL in a single query, with no separate system to run and sync.
Put embeddings in a column and retrieve the nearest ones with SQL.
You add a vector column sized to your embedding's dimensions, insert each chunk's text, metadata, and embedding, and query by ordering rows by distance to the query embedding. pgvector's distance operators — cosine, inner product, and L2 — go straight into an ORDER BY with a LIMIT for top-k.
Because it is just SQL, you can combine semantic search with WHERE filters — 'nearest chunks, but only from 2024 docs' — in one statement, which is awkward with many standalone vector stores.
CREATE EXTENSION IF NOT EXISTS vector; -- chunks(id, content text, src text, embedding vector(768)) SELECT content, src FROM chunks WHERE src = 'handbook' -- ordinary SQL filter ORDER BY embedding <=> $1 -- <=> is cosine distance to the query vector LIMIT 5;
The <=> operator computes cosine distance between each row's embedding and the query vector $1; ORDER BY plus LIMIT returns the five closest, and the WHERE clause filters by metadata in the same query — semantic search and structured filtering together.
Add an approximate index so search stays fast as the table grows.
Without an index, pgvector scans every row for each query — exact but linear, fine for thousands of vectors and too slow for millions. Adding an approximate index trades a little recall for large speedups.
Two choices. HNSW builds a navigable graph: excellent recall and fast queries, at the cost of more memory and slower index builds, and it can be built on an empty table. IVFFlat partitions vectors into lists and probes only the nearest few: cheaper to build and lighter, but it should be built after you have representative data, and you tune how many lists to probe. HNSW is the common default when memory allows.
Decide when pgvector is enough and avoid the usual missteps.
pgvector is a great fit when you already use Postgres, want vectors and relational data together, and have up to millions of vectors — which covers a large share of RAG apps. You get transactions, backups, and SQL filtering for free.
Consider a dedicated vector database when you need billions of vectors, very high query throughput, or specialized features like distributed sharding at extreme scale. For most teams, starting with pgvector and moving only if you outgrow it is the pragmatic path.
Watch for: matching the distance operator to how your embeddings were trained (cosine for normalized text embeddings) and using the same one in the index and the query; setting the vector column to the wrong dimension for your model; forgetting to build an index so large tables crawl; and building IVFFlat before inserting representative data. As always, use the same embedding model for indexing and querying.
pgvector adds a vector type and similarity search to PostgreSQL, so RAG chunks, metadata, and embeddings live in one database queried with SQL — combining nearest-neighbor search with WHERE filters in a single statement. Add an approximate index (HNSW for recall, IVFFlat for lighter builds after loading data) as the table grows. It fits most RAG apps up to millions of vectors; consider a dedicated store only at extreme scale. Match the distance operator and embedding model on both sides.
Plan pgvector for a support-docs RAG app on an existing Postgres. Choose the vector dimension and distance operator for your embedding model, write the shape of a top-k query that also filters by product, and decide which index you'd add and when.
What is pgvector?
pgvector brings vector storage and similarity operators into Postgres, letting you keep chunks, metadata, and embeddings together with SQL.
What is a key RAG advantage of using pgvector?
Consolidating vectors with relational data means one system to run and the ability to combine nearest-neighbor search with WHERE filters.
What is the difference between HNSW and IVFFlat indexes in pgvector?
Both are approximate indexes trading recall for speed; HNSW favors recall and is a common default, while IVFFlat is cheaper but needs data before building and probe tuning.
When should you consider a dedicated vector database over pgvector?
pgvector handles up to millions of vectors well; a specialized store pays off only at extreme scale or throughput, so start with pgvector and move if you outgrow it.