pgvector: Vector Search Directly in PostgreSQL

What pgvector Is and When You Need It

pgvector is a PostgreSQL extension that adds a vector data type and nearest-neighbor search operators right into a regular database. There is no need to run a separate service: vectors are stored in the same table as the rest of the data, and search is done with a plain SQL query.

The extension fits well for projects where PostgreSQL is already the main database: an online store, a knowledge base, a CRM. For catalogs with tens of millions of records and heavy search load, it is worth comparing pgvector with specialized databases — see the article comparing vector databases.

Installing the Extension on a VDS

pgvector installs like a regular PostgreSQL extension — from distribution packages or by building from source.

sudo apt update
sudo apt install -y postgresql-16-pgvector
sudo -u postgres psql -d mydb -c "CREATE EXTENSION IF NOT EXISTS vector;"

If the package is missing from your distribution's repository, build the extension from source: clone the project repository, run make and make install with the PostgreSQL headers installed.

Creating a Table with a Vector Column

A column of type vector requires a dimension — it must match the embedding model that generates the vectors.

CREATE TABLE articles (
  id serial PRIMARY KEY,
  title text,
  embedding vector(768)
);

A dimension of 768 fits the multilingual-e5-base model — more details on picking a model and generating vectors are in the article semantic search with embeddings.

An Index for Fast Nearest-Neighbor Search

Without an index, PostgreSQL compares the query against every row in the table — it works, but scales slowly with data volume. The HNSW index speeds up search on large tables at the cost of a small accuracy trade-off.

CREATE INDEX ON articles USING hnsw (embedding vector_cosine_ops);

The vector_cosine_ops operator sets the similarity metric — for normalized embeddings, cosine distance usually gives a better result than Euclidean.

Searching for the Nearest Articles by Query

Once the index is built, searching for the nearest records is a plain SELECT with the distance operator <=>.

SELECT id, title, embedding <=> '[0.12, 0.05, ...]' AS distance
FROM articles
ORDER BY distance
LIMIT 5;

The application substitutes the query vector after computing it with the same model used during indexing. Results sort by ascending distance — the smaller the value, the closer the article is in meaning to the query.

Common pgvector Setup Mistakes

Most problems come from mismatched parameters, not from the extension itself:

• The column dimension does not match the model's dimension — PostgreSQL returns an error on insert, check the model's output size in advance.
• The index is built for Euclidean distance while queries use cosine — search results end up essentially random.
• HNSW is created before the bulk data load — it is better to build the index after the initial import, otherwise inserts become noticeably slower.
• Vectors are not normalized before saving even though the model requires it — check the normalize_embeddings flag in the generation code.

Once configured, pgvector delivers full semantic search without an extra service. For high-load projects, it is worth measuring speed on real data and comparing it with a separate vector database like Qdrant.