Skip to main content

AI and machine learning · Intermediate · 22 min

How to Do Vector Similarity Search in PostgreSQL with pgvector

By Padding · Updated 15 Nov 2026
Ubuntu 26.04.1 LTS · PostgreSQL 18 · pgvector 0.8

You will install PostgreSQL 18 with the pgvector extension on Ubuntu 26.04, store and compare vectors with SQL, load the chunks from the previous tutorial into a table, and add an HNSW index to make nearest-neighbour search fast.

On this page

An embedding is a list of numbers that represents the meaning of a piece of text, so that texts with similar meanings get lists that are close together. To find the passages most relevant to a question you embed the question, then ask the database for the stored vectors closest to it. PostgreSQL does this with an extension called pgvector. It adds a vector column type, operators that measure distance, and indexes that make the search fast, so your documents and their vectors live in one database you already know how to back up and query.

This tutorial uses the packages in Ubuntu 26.04's own archive: PostgreSQL 18 and pgvector 0.8. You will learn the three distance operators on a tiny table, load the chunks from the previous tutorial into the table the RAG app will use, then build an index on a larger table and see the difference it makes. Real embeddings come from an embedding model, which the next tutorial calls. Here you work with vectors you write or generate, because the database behaves the same whatever the numbers mean.

Step 1 · Install PostgreSQL 18 and pgvector

Both packages are in the Ubuntu archive. postgresql-18-pgvector depends on postgresql-18, so one command installs both:

Bash
sudo apt update
sudo apt install -y postgresql-18 postgresql-18-pgvector

The package creates and starts a database cluster called main. If you want the full tour of the server, files and users, read the PostgreSQL 18 install tutorial. Ubuntu keeps the server patched through normal updates. If you installed PostgreSQL from the PostgreSQL project's own repository, the same package name gives you the project's newer pgvector build, and everything below still applies.

Check it worked

Bash
pg_lsclusters
apt-cache policy postgresql-18-pgvector | grep -E 'Installed|Candidate'

pg_lsclusters shows 18 main 5432 with the status online. The second command prints an Installed line and a Candidate line, both beginning 0.8..

Step 2 · Create a database and turn on the extension

Create a database for the series. CREATE EXTENSION needs a superuser, so run it as the postgres user. The extension is enabled per database, so other databases on the server are unaffected:

Bash
sudo -u postgres createdb ragdemo
sudo -u postgres psql -d ragdemo -c 'CREATE EXTENSION vector;'

When you run sudo -u postgres psql from your home folder, psql may print could not change directory to "/home/you": Permission denied. The warning is harmless: the postgres user cannot enter your home folder. Run cd /tmp first if you want to avoid it, and the file loading later in this tutorial still works because your own shell opens the file.

Check it worked

Bash
sudo -u postgres psql -d ragdemo -c '\dx vector'

The table lists one extension named vector, with version 0.8.1. The version you see is the one Ubuntu packages.

Step 3 · Store vectors and measure distance

A vector(3) column holds exactly three numbers. Real embeddings have hundreds or thousands of dimensions, but three is enough to see what the operators do. Create a table with three items and compare each to the vector [1,0,0]:

Bash
sudo -u postgres psql -d ragdemo -v ON_ERROR_STOP=1 <<'SQL'
CREATE TABLE items (id serial PRIMARY KEY, name text, embedding vector(3));
INSERT INTO items (name, embedding) VALUES
  ('a', '[1,0,0]'),
  ('b', '[0.9,0.1,0]'),
  ('c', '[0,1,0]');
SELECT name,
       embedding <-> '[1,0,0]' AS l2_distance,
       embedding <=> '[1,0,0]' AS cosine_distance,
       embedding <#> '[1,0,0]' AS negative_inner_product
FROM items
ORDER BY embedding <=> '[1,0,0]';
SQL

Three operators measure how far apart two vectors are:

  • <-> is the straight-line (Euclidean, or L2) distance.
  • <=> is cosine distance, one minus the cosine of the angle between the vectors. It ignores length and compares direction, which is what you want for text embeddings.
  • <#> is the negative inner product. It is negative because PostgreSQL's index scans sort ascending, so pgvector flips the sign to make the biggest inner product come first.

A smaller number always means closer. Cosine similarity is 1 - (a <=> b).

Check it worked

The query returns three rows in the order a, b, c. For a, which equals the query, every distance is 0 except the inner product, which is -1. For b, which points almost the same way, the cosine distance is about 0.0061. For c, which points at a right angle to the query, the cosine distance is 1, and the L2 distance is about 1.414.

Troubleshooting

ERROR: expected 3 dimensions, not 2 means the vector you inserted or searched with has a different length than the column. Every vector in a column must have the same number of dimensions, and it must match your embedding model. ERROR: type "vector" does not exist means you did not run CREATE EXTENSION vector in the database you connected to.

Step 4 · Create the table for your document chunks

The RAG app needs a table with the text and its vector. Voyage AI's voyage-4 model, which the next tutorial uses, returns 1,024 numbers by default, so the column is vector(1024). If you switch embedding models, the dimension changes and so does this column.

Create the table, and a staging table for loading the JSON Lines file:

Bash
sudo -u postgres psql -d ragdemo -v ON_ERROR_STOP=1 <<'SQL'
CREATE TABLE chunks (
  id        text PRIMARY KEY,
  source    text NOT NULL,
  heading   text NOT NULL,
  content   text NOT NULL,
  embedding vector(1024)
);
CREATE TABLE chunks_raw (doc jsonb);
SQL

The embedding column starts empty, since you have no vectors yet. Load chunks.jsonl into the staging table, then copy its fields into chunks. The COPY options use two control characters as the quote and delimiter, so that quotes and backslashes inside your JSON are read literally. psql reads the file from standard input, which means the postgres user does not need access to your home folder:

Bash
cd ~/rag-demo
sudo -u postgres psql -d ragdemo -v ON_ERROR_STOP=1 \
  -c "\copy chunks_raw (doc) FROM STDIN WITH (FORMAT csv, QUOTE E'\\x01', DELIMITER E'\\x02')" < chunks.jsonl
sudo -u postgres psql -d ragdemo -v ON_ERROR_STOP=1 -c "
INSERT INTO chunks (id, source, heading, content)
SELECT doc->>'id', doc->>'source', doc->>'heading', doc->>'text' FROM chunks_raw;
DROP TABLE chunks_raw;"

Check it worked

Bash
sudo -u postgres psql -d ragdemo -c "SELECT count(*) AS chunks, count(embedding) AS with_vector FROM chunks"

The output shows 16 chunks and 0 with a vector. If your previous tutorial produced a different number of chunks, the first number matches that. The next tutorial fills the vectors in.

Step 5 · Make search fast with an HNSW index

Without an index, a nearest-neighbour query compares your vector with every row, which is fine for 16 chunks and slow for millions. pgvector has two index types. HNSW (hierarchical navigable small world) builds a layered graph of close neighbours, searches it quickly and needs no training step. IVFFlat splits vectors into clusters and needs the table to hold data before you build it. HNSW is the usual first choice.

Both are approximate: they trade a small chance of missing the true nearest neighbour for large speed gains. To see the effect, create a table of 20,000 random 32-dimension vectors. Random vectors are not like real embeddings, so treat the timing as a demonstration of the mechanism, not a benchmark:

Bash
sudo -u postgres psql -d ragdemo -v ON_ERROR_STOP=1 <<'SQL'
CREATE TABLE big (id serial PRIMARY KEY, embedding vector(32));
INSERT INTO big (embedding)
SELECT (SELECT array_agg(random()::real) FROM generate_series(1, 32) WHERE g = g)::vector
FROM generate_series(1, 20000) g;
ANALYZE big;
EXPLAIN (COSTS OFF)
SELECT id FROM big ORDER BY embedding <=> (SELECT embedding FROM big WHERE id = 1) LIMIT 5;
SQL

Check it worked

The plan has a Seq Scan on big feeding a Sort, which means PostgreSQL reads every row and sorts by distance. Now create the index and ask for the plan again:

Bash
sudo -u postgres psql -d ragdemo -v ON_ERROR_STOP=1 <<'SQL'
CREATE INDEX ON big USING hnsw (embedding vector_cosine_ops);
ANALYZE big;
EXPLAIN (COSTS OFF)
SELECT id FROM big ORDER BY embedding <=> (SELECT embedding FROM big WHERE id = 1) LIMIT 5;
SQL

The plan now shows Index Scan using big_embedding_idx on big with Order By: (embedding <=> ...). The index operator class must match the operator in your query: vector_cosine_ops serves <=>, vector_l2_ops serves <->, and vector_ip_ops serves <#>. A mismatch makes PostgreSQL ignore the index and scan every row.

Troubleshooting

If the plan still shows a sequential scan, run ANALYZE big; so the planner has statistics. On a very small table the planner may prefer a sequential scan even with an index, and that is correct, because reading 16 rows is cheaper than walking a graph.

Step 6 · Tune recall and filter results

Because HNSW is approximate, how many of the true nearest neighbours it returns, called recall, depends on how widely it searches. The setting is hnsw.ef_search, and its default is 40. Compare the index result with the exact result, which you get by switching index scans off. The query counts how many of the 10 nearest neighbours the two agree on:

Bash
sudo -u postgres psql -d ragdemo -v ON_ERROR_STOP=1 <<'SQL'
CREATE TEMP TABLE approx AS
  SELECT id FROM big ORDER BY embedding <=> (SELECT embedding FROM big WHERE id = 1) LIMIT 10;
SET enable_indexscan = off;
CREATE TEMP TABLE exact AS
  SELECT id FROM big ORDER BY embedding <=> (SELECT embedding FROM big WHERE id = 1) LIMIT 10;
SELECT count(*) AS agree_out_of_10 FROM approx JOIN exact USING (id);
RESET enable_indexscan;
SET hnsw.ef_search = 200;
CREATE TEMP TABLE wider AS
  SELECT id FROM big ORDER BY embedding <=> (SELECT embedding FROM big WHERE id = 1) LIMIT 10;
SELECT count(*) AS agree_out_of_10_wider FROM wider JOIN exact USING (id);
SQL

Check it worked

The output has two numbers between 1 and 10, and 10 means the index returned exactly the true neighbours. On this table you will probably see 10 both times, because 20,000 vectors is a small graph to search. On millions of real embeddings the first number is often lower, and raising hnsw.ef_search buys it back at the cost of speed. A higher setting is usually right when answers matter more than milliseconds.

One more thing matters for a RAG app. An approximate index scan finds its candidates first and applies a WHERE filter afterwards. A query such as "the five nearest chunks where source = 'billing.md'" can therefore return fewer than five rows when most candidates fail the filter. pgvector 0.8 added iterative index scans for this case: with SET hnsw.iterative_scan = relaxed_order; the index keeps searching until it has enough rows that pass the filter. The setting is off by default. To see the difference, ask for the 10 nearest rows among the 2 percent of rows whose id is a multiple of 50, first with the default and then with iterative scans on. On a table this small the planner would read every row and ignore the index, so the script turns sequential scans off to make it behave as it does on a table of millions:

Bash
sudo -u postgres psql -d ragdemo -v ON_ERROR_STOP=1 <<'SQL'
SET enable_seqscan = off;
SELECT count(*) AS rows_returned FROM (
  SELECT id FROM big WHERE id % 50 = 0
  ORDER BY embedding <=> (SELECT embedding FROM big WHERE id = 1) LIMIT 10) s;
SHOW hnsw.iterative_scan;
SET hnsw.iterative_scan = relaxed_order;
SELECT count(*) AS rows_returned_iterative FROM (
  SELECT id FROM big WHERE id % 50 = 0
  ORDER BY embedding <=> (SELECT embedding FROM big WHERE id = 1) LIMIT 10) s;
SQL

Check it worked

The first count is below 10, because only a few of the index's 40 candidates pass the filter. SHOW prints off, and after the SET the second count is 10. A hnsw.* setting only exists in a session once the extension's library is loaded, which happens the first time the session touches a vector. That is why SHOW hnsw.iterative_scan works here, after the first query has used big, but answers unrecognized configuration parameter in a brand-new session that has run nothing with vectors yet.

Step 7 · Clean up the demo tables

Drop the two demonstration tables. Keep the ragdemo database and the chunks table, because the next tutorial uses them:

Bash
sudo -u postgres psql -d ragdemo -v ON_ERROR_STOP=1 -c 'DROP TABLE big, items;'
sudo -u postgres psql -d ragdemo -c '\dt'

The \dt listing shows only chunks. When you finish the whole series and want everything gone, run sudo -u postgres dropdb ragdemo, and sudo apt purge postgresql-18 postgresql-18-pgvector removes the packages. Purging does not delete the data directory in /var/lib/postgresql, so remove that too if you want the server's data gone.

Next steps

Need this done for you?

Padding does this work for growing companies, from the first diagnosis to a system that keeps working.

AI-Integrated Solutions

Get new tutorials

New tutorials, about twice a month.