# pgvector: Vector Search Directly in PostgreSQL

> Installing pgvector for PostgreSQL: creating a vector column, the HNSW index, nearest-neighbor search, and common setup mistakes on a VDS.

## 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](https://www.zevshost.net/en/kb/vektornye-bd-sravnenie/).

## 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](https://www.zevshost.net/en/kb/semanticheskiy-poisk-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.

| Index | Accuracy | Speed on 1M rows | When to use |
| --- | --- | --- | --- |
| No index | 100% | slow | tables up to 10 thousand rows |
| IVFFlat | high | fast | medium tables, rare inserts |
| HNSW | high | very fast | large tables, frequent inserts |

```
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.

---

Source: https://www.zevshost.net/en/kb/pgvector-postgresql/