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.

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.
cd /tmp
git clone --branch v0.8.7 https://github.com/pgvector/pgvector.git
cd pgvector
make
make installIf 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.
docker pull pgvector/pgvector:pg18-trixieThe extension is enabled per database, so run the following statement once in each database that should use it.
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.
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.
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.
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.
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.
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
- pgvector README, pgvector


