pgvector Explained: Vector Search Inside PostgreSQL

pgvector is a PostgreSQL extension that adds vector similarity search to the database you already run - a

vector

Embeddings, in one section

An embedding model turns content - text, images, code - into a list of numbers (a vector, typically 384 to 3,072 dimensions) where semantic similarity becomes geometric closeness. 'Reset my password' and 'I can't log in' land near each other in that space despite sharing no words. Similarity search is then just: given a query vector, find the stored vectors closest to it. That is the entire trick behind RAG, semantic search and 'related items'.

The type and the operators

Enable the extension, add a column, insert vectors from your embedding model:

CREATE EXTENSION IF NOT EXISTS vector;
​
CREATE TABLE docs (
  id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  content   text NOT NULL,
  tenant_id bigint NOT NULL,
  embedding vector(1536)  -- match your model's dimensions
);
​
-- nearest neighbours by cosine distance
SELECT id, content
FROM docs
ORDER BY embedding <=> $1
LIMIT 10;

Three distance operators cover the standard metrics:

<->

<=>

<#>

Indexes: HNSW vs IVFFlat

Without an index, every similarity query scans every row - exact, but linear. Both pgvector index families make search approximate and fast; they spend resources differently.

CREATE INDEX ON docs USING hnsw (embedding vector_cosine_ops);

Filtered search: the quiet superpower

Real applications never search all vectors - they search this tenant's documents, published articles, products in stock. In a dedicated vector store, combining filters with similarity is a feature you configure and often fight. In Postgres it is a WHERE clause:

SELECT id, content
FROM docs
WHERE tenant_id = $2          -- just SQL
ORDER BY embedding <=> $1
LIMIT 10;

And because the vectors live in the same transactional database as the rows they describe, there is no sync pipeline to build and no window where a deleted document still surfaces in search results. Delete the row, the embedding is gone - atomically. That single property eliminates a whole class of RAG bugs.

A working RAG schema

Retrieval-augmented generation needs exactly three things from the database: store chunks with their embeddings, retrieve the nearest chunks for a question, and keep them scoped to the right user or tenant. In Postgres that is one table and one query:

CREATE TABLE chunks (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  source_id  bigint NOT NULL REFERENCES sources(id) ON DELETE CASCADE,
  tenant_id  bigint NOT NULL,
  body       text NOT NULL,
  embedding  vector(1536) NOT NULL
);
CREATE INDEX ON chunks USING hnsw (embedding vector_cosine_ops);
CREATE INDEX ON chunks (tenant_id);
​
-- retrieval for one tenant's question
SELECT body FROM chunks
WHERE tenant_id = $1
ORDER BY embedding <=> $2
LIMIT 8;

The ON DELETE CASCADE line is doing more work than it looks like: when a source document is removed, its chunks and embeddings vanish in the same transaction. In a two-database architecture that guarantee is a sync pipeline you have to build, monitor and apologise for.

Tuning that actually matters

• Index build memory. Set maintenance_work_mem generously before CREATE INDEX on a large table - HNSW builds are dramatically faster when they fit in memory.

• Recall vs speed at query time. hnsw.ef_search (default 40) is the dial: raise it for better recall, lower it for latency. Test against a labelled sample rather than guessing.

• Dimensions cost real money. 1,536 dimensions is ~6 KB per row; models with 3,072 double that. If your model supports shortened embeddings, smaller often loses little recall and halves storage and index size.

• Vacuum still applies. Embeddings update when content changes; dead tuples bloat the index like any other. Ordinary Postgres hygiene carries over unchanged.

Where the honest ceiling is

pgvector comfortably serves millions of vectors on ordinary hardware - HNSW queries in single-digit milliseconds, with memory (the index wants to live in RAM) as the main sizing constraint. The dedicated stores - Pinecone, Qdrant, Weaviate, Milvus - earn their place past tens of millions of vectors, at very high query throughput, or for specialized capabilities like tuned hybrid reranking at scale. If you are not sure whether you are past the ceiling, you are not past it.

The mistakes everyone makes once

• Mismatched metrics. Building the index with vector_l2_ops and querying with <=> means the index is silently ignored and every query scans the table. Same metric in both places, always.

• Forgetting the LIMIT. An approximate index answers 'top K nearest'; a similarity query without LIMIT degenerates into sorting the whole table.

• Embedding at query time, twice. Cache the query embedding if the same question feeds retrieval and reranking - embedding API calls are the slow, billable part of the pipeline.

• Testing recall on toy data. Ten sample rows make every index look perfect. Evaluate against a realistic corpus before promising quality.

Running it in production

pgvector is among the most widely supported extensions on managed Postgres platforms - though each provider allows a vetted list, so verify before committing (our PostgreSQL hosting comparison covers how to evaluate providers generally). Size RAM for the index, monitor recall if your data churns, and remember the embedding column dominates storage: 1,536 floats is ~6 KB per row before the text itself. A million embedded documents is a perfectly ordinary Postgres database - which is precisely the point.

Prices quoted here were checked in July 2026. Vendors change them; treat the structure as the durable part and re-check the current numbers before you budget.