Store and search Ollama embeddings in PostgreSQL with pgvector

The pgvector README describes it as open-source vector similarity search for Postgres. It supports exact and approximate nearest neighbor search; single-precision, half-precision, binary and sparse vectors; and L2 distance, inner product, cosine distance, L1 distance, Hamming distance and Jaccard distance. Because the vectors are ordinary PostgreSQL columns, they also get ACID compliance, point-in-time recovery and JOINs.

For a search over your documents, you store one row per chunk with its text and its vector, embed the question with the same model, and sort the rows by their distance to the question's vector.

pgvector supports PostgreSQL 13 and later. The README lists several ways to install it, including these:

A column of type vector(n) holds vectors with exactly n dimensions, and n must be the length of the vectors from your embedding model. A vector with a different length is rejected: when we inserted a vector with 2 dimensions into a vector(3) column, PostgreSQL returned "expected 3 dimensions, not 2". all-minilm returns 384 dimensions, so its column is vector(384).

The README gives the storage of each vector as 4 × dimensions + 8 bytes, with every element a single-precision floating-point number, and allows up to 16,000 dimensions in a column. An index supports fewer dimensions, as shown below. The halfvec type stores half-precision values in 2 × dimensions + 8 bytes.

pgvector reads a vector as text in square brackets, such as '[1,2,3]', and the README says all elements must be finite (no NaN, Infinity or -Infinity). Each array in the embeddings field of an /api/embed response already has this form, and the response keeps the order of the input, so the first text goes with the first vector.

The README shows INSERT for a few rows, INSERT … ON CONFLICT for upserts, and COPY for bulk loading. The converter below pairs your texts with the response and writes INSERT statements with the quotes escaped. We ran its output with texts containing quotes, backslashes, line breaks and emoji, and they came back unchanged.

Ollama's documentation recommends cosine similarity for most semantic search use cases and says /api/embed returns vectors with a length of 1. For such vectors, cosine distance, L2 distance and inner product put the rows in the same order, because the squared L2 distance is 2 minus 2 times the cosine similarity. In our test with all-minilm, the top three rows were the same with all three, and the L2 distance of 0.931 matched the cosine similarity of 0.566.

Each distance has its own operator. A search sorts by the operator and takes the first rows with LIMIT, and an index is built for one operator class.

Without an index, pgvector does an exact nearest neighbor search, which the README says provides perfect recall. An approximate index trades some recall for speed, and the README warns that queries can return different results after you add one.

An index is only used when the query has ORDER BY and LIMIT and the ORDER BY is the distance operator itself in ascending order. ORDER BY embedding <=> $1 LIMIT 5 can use the index; ORDER BY 1 - (embedding <=> $1) DESC cannot. The README also notes that a table scan may be faster when the table is small.

With HNSW, the results are limited by hnsw.ef_search, which is 40 by default. On a test table of 200 rows with an HNSW index, LIMIT 100 returned 40 rows; after SET hnsw.ef_search = 100, it returned 100. Set hnsw.ef_search to at least the number of rows you need. SET LOCAL inside a transaction changes it for a single query.

Filtering also reduces the results. With approximate indexes, the README says the WHERE condition is applied after the index is scanned: if a condition matches 10% of rows, HNSW with the default hnsw.ef_search of 40 returns only 4 rows on average. Since version 0.8.0, iterative index scans (SET hnsw.iterative_scan = strict_order, or relaxed_order) scan more of the index until enough rows are found, up to hnsw.max_scan_tuples (20,000 by default).

Once the search returns the closest rows, the model needs them in its prompt. Ollama's /api/chat takes a list of messages, each with a role (system, user, assistant or tool) and its content. A simple layout is a system message with the rules and a user message that lists the rows with a number each, such as [1] and [2], followed by the question. With numbers, the answer can say which rows it used, and you can map each number back to its row.

To get the citations in a fixed shape, give the format field a JSON Schema with the answer and a list of citations, each with the source number and a short quote. Ollama's structured outputs page recommends also writing the schema in the prompt, and lowering the temperature, for example to 0, for more deterministic output. In psql, the json_agg function collects the rows of a query into a JSON array, which the tool below reads directly.

The rules, the rows and the question all have to fit into the context length. Ollama's context length page gives a default of 4k tokens with less than 24 GiB of VRAM, and its FAQ gives 4096 tokens; ollama ps on our test machine showed 4096. num_ctx in options or the OLLAMA_CONTEXT_LENGTH environment variable changes it.

When the prompt is longer, /api/chat does not return an error by default. In Ollama's source code (0.40.11), the server first drops whole earlier messages while keeping the system messages and the last message. If the remaining prompt is still too long, it keeps the first few tokens (num_keep, 4 by default) and the end of the prompt and discards the part in between. In our test with qwen2.5:7b and num_ctx 1024, a prompt of 2,068 tokens was cut to 514. The system message and the first sources were lost: with the answer in source [1], the model replied with a code word that was not in any source, and with the answer in the last source, it answered correctly but without the source number that the system message asked for. The response contained no error; the server log had the warning "truncating input prompt".

Ollama's source code also has a truncate field for chat requests, which is not listed on the /api/chat page. With "truncate": false, the same request returned HTTP 400 with the error "request (2068 tokens) exceeds the available context size (1024 tokens), try increasing it". The prompt_eval_count field of a response is the number of tokens in the prompt; in the truncated test it was 514, not 2,068.

A citation can be checked mechanically: the number must refer to a source that was sent, and the quote should appear in that source. In our test with four sources and qwen2.5:7b, the quotes in the JSON answers matched the sources word for word. That does not prove the answer: asked about the size of a 384-dimension vector, the model quoted the correct rule (4 times the dimensions plus 8 bytes) and still answered 1552 bytes instead of 1,544.

Look at answers without citations too. When the sources did not contain the answer, the model once answered from its own knowledge with no citations, and once said that it did not know but still cited an unrelated source. If done_reason in the response is "length", the answer was cut off by num_predict and may be missing its citations.

The README says an HNSW index builds significantly faster when the graph fits into maintenance_work_mem, shows a notice when it no longer fits, and warns not to set maintenance_work_mem so high that it exhausts the memory on the server. As with other indexes, it is faster to create the index after loading the initial data. Indexes do not have to fit into memory, but you will likely see better performance if they do; SELECT pg_size_pretty(pg_relation_size('index_name')) shows the size of an index.

For a smaller working set and smaller indexes, the README suggests halfvec, half-precision indexing or binary quantization. If Ollama runs on the same server, PostgreSQL and the models share the same memory; our article on watching Ollama's memory explains what decides how much the models use.