Set up pgvector in PostgreSQL for document search

If you already run PostgreSQL, pgvector adds vector search without a second database. This tutorial installs the extension, stores embeddings for company documents and queries them by similarity.

Niklas Schult4 min read

Illustration: A filing cabinet with an open drawer and a magnet on a string pulling toward the index cards standing closest together.

What you will have at the end and what you need

At the end you have a PostgreSQL database with a table of documents, a vector column for their embeddings and a query that returns the closest matches. The example uses three-dimensional vectors so that every value stays readable. Real embedding models produce far more dimensions, and the column definition has to match your model.

You need a running PostgreSQL server, version 13 or newer according to the pgvector README. You also need a compiler toolchain with the Postgres development files, or Docker. Rights to create an extension in the target database are required as well. The embeddings themselves come from a model of your choice, which the README does not cover.

The README also describes other installation routes, among them Homebrew, PGXN, APT, Yum and conda-forge. Which one fits depends on how your servers are managed, and the build from source shown below works on any Linux or Mac machine with the development files.

Install and enable pgvector

On Linux and Mac the README compiles the extension from source. The next block clones the tagged release v0.8.7, builds it and installs it. The README notes that the last command may need sudo on your system.

Shell
cd /tmp
git clone --branch v0.8.7 https://github.com/pgvector/pgvector.git
cd pgvector
make
make install

If you run Postgres in a container, the project publishes an image that adds pgvector to the official Postgres image. The tag below targets Postgres 18. The README says to replace 18 with your server version and to run the image the same way as the standard one.

Shell
docker pull pgvector/pgvector:pg18-trixie

The extension is enabled per database, so run the following statement once in each database that should use it.

Text
CREATE EXTENSION vector;

Create a table and store embeddings

The table below keeps the file name, the text and the embedding together, so a hit can be shown with its context. The column type is vector(3) for this demonstration. With a real model you would use that model's dimension count, and the README states that an HNSW index supports vector columns of up to 2,000 dimensions.

Text
CREATE TABLE dokumente (
  id bigserial PRIMARY KEY,
  dateiname text,
  inhalt text,
  embedding vector(3)
);

The insert statement writes two sample documents with hand-made vectors. In practice your application computes the embedding for each text and passes it in the same bracket notation. For larger loads the README recommends COPY, and it advises adding indexes only after the initial data is in.

Text
INSERT INTO dokumente (dateiname, inhalt, embedding) VALUES
  ('vertrag_a.pdf', 'Kündigungsfristen im Rahmenvertrag', '[1,2,3]'),
  ('handbuch.pdf', 'Wartung der Fräsmaschine', '[4,5,6]');

If your embedding model produces more than 2,000 dimensions, the README names options such as half-precision vectors, which can be indexed up to 4,000 dimensions, or binary quantization for up to 64,000. Check these limits against your model before you fix the column type.

Query by similarity and speed it up

This query orders the rows by cosine distance to the query vector and returns the five nearest. The README gives cosine similarity as one minus the cosine distance, which the select list uses. The ORDER BY keeps the plain distance operator, because the README says an index is only considered when the ordering is a distance operator in ascending order and a LIMIT is present.

Text
SELECT dateiname, inhalt, 1 - (embedding <=> '[3,1,2]') AS aehnlichkeit
FROM dokumente
ORDER BY embedding <=> '[3,1,2]'
LIMIT 5;

Without an index, pgvector performs an exact search with perfect recall. An HNSW index switches to approximate search, which trades some recall for speed, so results can differ after you add it. The index below matches the cosine operator used in the query. The README advises one index per distance function you want to use.

Text
CREATE INDEX ON dokumente USING hnsw (embedding vector_cosine_ops);

According to the README, an HNSW index can be created on an empty table because it has no training step. An IVFFlat index, the other supported type, builds faster and uses less memory but has lower query performance, and it should be created after the table holds some data.

Check the result, avoid pitfalls and plan operation

The first statement below shows the installed extension version in the current database. The second prints the query plan with timings and buffer use, which the README recommends for debugging performance. On a table this small the planner may prefer a table scan, and the README says so explicitly.

Text
SELECT extversion FROM pg_extension WHERE extname = 'vector';
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM dokumente ORDER BY embedding <=> '[3,1,2]' LIMIT 5;

The README lists several typical problems. A query that sorts by an expression such as one minus the distance does not use the index. A missing postgres.h during compilation means the Postgres development files are not installed. The compiled extension may fail with Illegal instruction on another machine, in which case you build with make OPTFLAGS="". If an HNSW index returns fewer rows than expected, the default hnsw.ef_search of 40 or a filter in the query is the usual cause, and iterative index scans can help.

In operation, pgvector lives inside the database you already maintain. Backups, replication and point-in-time recovery apply because the README states that it uses the write-ahead log. Upgrades install the new version and then run ALTER EXTENSION vector UPDATE in each database. Personal data inside documents and embeddings falls under the same GDPR duties as the source files, and the database team should own access rights. If you would rather not build retrieval yourself, souveraen.ai offers knowledge management with AI that answers from connected sources and cites them, hosted in Germany or on-premise.

Sources

More articles

All articles

Run Text Generation Inference with Docker on Your Server

Text Generation Inference (TGI) serves open language models from a single Docker container on your own hardware. This tutorial starts it, sends requests over HTTP and keeps prompts inside your network.

Tutorial4 min

Cloud outage liability: who answers when Microsoft fails

On 12 November 2026 a software fault took Exchange Online and Outlook offline for about 36 hours. A legal analysis published shortly afterwards argues that responsibility for such outages often lies with the operator rather than the provider.

Regulation4 min

GitLab 19.6 adds an audit log for AI agents

GitLab 19.6 records what AI agents do through its MCP server. A Ruby flaw is patched, but several NGINX flaws remain open, so admins who run GitLab themselves have settings to enable and a workaround to apply.

Models4 min

Test it with your own documents.

Free for 3 days, then from €24.90 per user per month. Cancellable monthly.

Free for 3 days, then from €24.90 per user per month.Free for 3 days
Try free for 3 days