PHASE 5Intermediate ~21 min· topic 2 of 10

Topic 5.2

PostgreSQL with pgvector

In one line

pgvector adds a vector column type, three distance operators and two index types to PostgreSQL, so embeddings live in a normal table next to your other data and a search is an ordinary SQL query.

Think of it like this

A library already has a catalogue with a card for every book. Adding a new column to each card, "where it sits on the map of ideas", lets the same librarians answer "find books near this idea" with the same cabinet. pgvector is that extra column inside the database you may already run.

Words you'll meet

New words in this topic, in plain English. Come back here whenever one feels fuzzy.

pgvector: A PostgreSQL extension that adds the vector type, distance operators and approximate indexes.
Extension: Add-on code installed into a database with CREATE EXTENSION.
Operator class: Tells an index which distance to use, such as vector_cosine_ops.
ef_search: How many candidates an HNSW query keeps while walking the graph. Higher is more accurate and slower.
lists / probes: IVFFlat settings: how many clusters the index has, and how many a query visits.
Negative inner product: The dot product times -1, so that smaller means closer like the other operators.
halfvec: A pgvector type that stores each number in 2 bytes instead of 4.

Step by step

01Step 1: turn the extension on and make a table

One statement per database installs the extension. A normal table gets a vector(384) column next to the text and its metadata.

schema.sqlwhole filesql

CREATE EXTENSION vector;

CREATE TABLE chunks (
  id        text PRIMARY KEY,
  doc_id    text NOT NULL,
  text      text NOT NULL,
  embedding vector(384)
);

02Step 2: insert and replace

INSERT ... ON CONFLICT (id) DO UPDATE is the upsert pattern from the pgvector README. The vector is written as text like '[1,2,3]' or passed as a parameter. The snippet shows only 3 numbers so it fits on a line: a vector(384) column rejects anything but exactly 384.

upsert.sqlwhole filesql

INSERT INTO chunks (id, doc_id, text, embedding)
VALUES ('D3', 'D3', 'To reset your password ...', '[0.12, -0.03, 0.44]')
ON CONFLICT (id) DO UPDATE
  SET text = EXCLUDED.text, embedding = EXCLUDED.embedding;

03Step 3: an exact query

Order by the distance operator and limit. Without an index this scans every row: slow for millions, exact always. The similarity column is just 1 - distance.

query.sqlwhole filesql

SELECT id, text, 1 - (embedding <=> $1) AS similarity
FROM chunks
ORDER BY embedding <=> $1
LIMIT 5;

04Step 4: add an approximate index

Pick the operator class that matches your query operator. HNSW does not need data first; IVFFlat does.

index.sqlwhole filesql

CREATE INDEX ON chunks USING hnsw (embedding vector_cosine_ops)
  WITH (m = 16, ef_construction = 64);

-- per session, before querying: wider search, better recall, slower
SET hnsw.ef_search = 100;

Step 4: add an approximate indexdiagram

Rendering diagram…

05Step 5: understand the operators on tiny vectors

The example computes <->, <#> and <=> by hand on four 2-D vectors. On raw vectors of different lengths the three orderings disagree: L2 prefers B, which is close in space, while cosine prefers A, which points the same way. After scaling every vector to length 1, all three agree. This is why the model's recommended distance matters, and why normalised embeddings make the choice easy.

06Step 6: read the sign of <#>

The example prints the negative inner product: A (the longest vector in the query's direction) has the most negative value, -3, so ORDER BY embedding <#> $1 puts it first. Forgetting the sign when you display the number is a common bug.

Try it yourself

1
Predict before you run
Change QUERY to [0, 1] (pointing at C). Write down the order for each operator on the raw vectors, then run and compare.
2
Add a vector that breaks the rule
Add E = [0.5, 0] pointing the same way as A but shorter. Which operators put E before B, and which after?
3
Scale by hand
Pick one vector and compute its length and unit form with a calculator. Confirm that the printed unit-vector rows give the same ranking for all three operators.

Code & diagrams

pgvector's operators on tiny vectors New tab

These are the formulas pgvector evaluates, written by hand on 2-D vectors so you can check them. The operator names in the labels are pgvector's.

Expected output

raw vectors (different lengths)
<->  L2 distance        B=1.0000  A=2.0000  D=2.0000  C=3.1623
<#>  negative inner     A=-3.0000  B=-1.0000  C=0.0000  D=1.0000
<=>  cosine distance    A=0.0000  B=0.2929  C=1.0000  D=2.0000
after scaling every vector to length 1
<->  L2 distance        A=0.0000  B=0.7654  C=1.4142  D=2.0000
<#>  negative inner     A=-1.0000  B=-0.7071  C=0.0000  D=1.0000
<=>  cosine distance    A=0.0000  B=0.2929  C=1.0000  D=2.0000
similarity of B = 1 - distance = 0.7071

Filtered query with an index setting (SQL, not run)sql

From the pgvector README: iterative index scans (added in pgvector 0.8.0) keep scanning until enough rows pass the filter. Check the README for the version you run.

-- ask the index to keep scanning until LIMIT rows pass the WHERE
SET hnsw.iterative_scan = strict_order;   -- exact distance order
-- or: SET hnsw.iterative_scan = relaxed_order;  -- faster, slightly out of order
SET hnsw.max_scan_tuples = 20000;         -- how many index entries it may visit (default)

SELECT id, text
FROM chunks
WHERE doc_id = 'D3'
ORDER BY embedding <=> $1
LIMIT 5;

Break it on purpose

Errors are the best teachers. Make each change, read the error, guess what went wrong, then reveal the answer.

Break #1

Drop the sign

In the Python tab, change neg_inner to return the plain inner product: return sum(x * y for x, y in zip(a, b)). Run.

terminal

$ Run

── what you'll see ──

raw vectors (different lengths)

<-> L2 distance B=1.0000 A=2.0000 D=2.0000 C=3.1623

<#> negative inner D=-1.0000 C=0.0000 B=1.0000 A=3.0000

<=> cosine distance A=0.0000 B=0.2929 C=1.0000 D=2.0000

Myth vs fact

Myth

<#> is the inner product.

Fact

It returns the negative inner product, so that ascending order puts the closest first like the other operators.

Myth

Any index speeds up any operator.

Fact

The operator class on the index must match the operator in the query. A cosine query on an L2 index scans the table.

Myth

An HNSW index returns exact results.

Fact

It is approximate. hnsw.ef_search trades recall for speed. Check recall against an exact query on your own data (Topic 3.6).

Pro corner

Extra depth for experienced readers. New to this? Skip it for now and come back later.

▸
Build the index after bulk loading: loading rows into a table that already has an HNSW index is slower than loading and then indexing. Indexing takes memory, so give the build enough (the README documents the related settings).
▸
A column declared vector(384) rejects a vector of any other length. That is a feature: it stops a model change from silently mixing sizes.
▸
HNSW maintenance matters: the README notes that vacuuming an HNSW index can take a while, and suggests reindexing first (REINDEX INDEX CONCURRENTLY) then vacuuming. Plan for it if you delete a lot.
▸
SQL strings in the examples here are written with values inline for reading. In real code always use parameters ($1, %s, ?), never string concatenation: it prevents SQL injection and lets the database reuse query plans.

Remember this

1
Why this topic exists. If you already run PostgreSQL, the lowest-effort vector store is a table in it. The extension is called pgvector. You get durability, transactions, backups, joins and permissions you already know. Everything below was checked against the pgvector README (github.com/pgvector/pgvector); the SQL blocks are shown, not run, because the browser has no database.
2
The type. After CREATE EXTENSION vector; a column can have the type vector(n), where n is the number of dimensions. A vector can have up to 16,000 dimensions, and storage is 4 * dimensions + 8 bytes per vector. An index is limited to 2,000 dimensions for vector (there is also halfvec, with half-precision numbers, indexable up to 4,000). Many popular embedding sizes fit; very large ones need a smaller model, a reduced vector or halfvec.
3
The three operators you will use. <-> is L2 (straight-line) distance, <=> is cosine distance (1 minus the cosine similarity, so 0 means the same direction), and <#> is the negative inner product. All three return a distance-like number where smaller means closer, so ORDER BY embedding <=> $1 LIMIT 5 gives the five nearest. <#> is negative because PostgreSQL index scans return ascending order, and a larger dot product means closer. If you want the real inner product, multiply by -1. For cosine similarity, use 1 - (embedding <=> $1). (A fourth, <+>, is L1 distance.)
4
Which one to use. Use the distance your embedding model was trained for. For most text models that is cosine, and many return vectors of length 1, in which case L2, negative inner product and cosine order results identically (the example shows it). Inner product is the cheapest to compute. Mixing the wrong operator with the wrong index (see below) gives a slow query, not an error.
5
Exact by default. With no index, pgvector scans the table and gives exact results, like Phase 3's brute force. That is right for small tables. An approximate index is added with CREATE INDEX ... USING hnsw (embedding vector_cosine_ops) (or ivfflat). The operator class (vector_cosine_ops, vector_l2_ops, vector_ip_ops) must match the operator in your query, or the index is not used.
6
HNSW and IVFFlat. HNSW (Topic 3.4) is built with m (connections per node, default 16) and ef_construction (candidate list size while building, default 64). At query time SET hnsw.ef_search = 100; widens the search (default 40): higher means better recall and slower queries. IVFFlat (Topic 3.3) takes lists; the README suggests rows / 1000 for up to a million rows and the square root of the rows above that, and ivfflat.probes (default 1) sets how many lists a query visits, with the square root of lists as a starting point. IVFFlat needs data in the table before the index is built, because it clusters it.
7
Filters live in the same SQL. WHERE owner = $2 beside the ORDER BY is ordinary SQL. With an approximate index, though, the filter is applied after the index is scanned, so a strict filter can return fewer rows than LIMIT. Topic 5.5 covers the fixes: iterative scans, partial indexes and partitioning.

Explain it without notes

What does <#> return, and why?

What must match for an index to be used by a query?

Why can a filter plus an approximate index return fewer rows than the LIMIT?

When is a plain pgvector table with no index the right choice?

Practice

Write the cosine similarity of vectors [1, 2] and [2, 1] in Python and print it with 4 decimals, then the same value through the formula 1 - cosine distance.

Write a function to_pg(v) that turns a list of floats into the text form [0.1,0.2,0.3] (no spaces) that pgvector accepts. Print it for [0.5, -0.25, 1.0].

Trade-offs

↔
pgvector: one system to run and back up, joins and filters in plain SQL, but vector search shares CPU, memory and tuning with your other queries.
↔
HNSW versus IVFFlat: HNSW usually gives better speed and recall and takes more memory and build time. IVFFlat builds faster and is smaller but needs data first and tuning of lists and probes.
↔
vector versus halfvec: half the storage and a higher indexing limit, at lower number precision.

Done when you can

Done when you can write the table, the upsert and a nearest-neighbour query from memory.
Done when you can say what each of the operators <->, <#> and <=> returns.
Done when you can match an operator to its index operator class.
Done when you can name the setting that widens an HNSW query and the one that widens an IVFFlat query.

Command Palette

Words you'll meet

Step by step

01Step 1: turn the extension on and make a table

02Step 2: insert and replace

03Step 3: an exact query

04Step 4: add an approximate index

05Step 5: understand the operators on tiny vectors

06Step 6: read the sign of <#>

Try it yourself

Predict before you run

Add a vector that breaks the rule

Scale by hand

Code & diagrams

Break it on purpose

Drop the sign

Myth vs fact

Pro corner

Remember this

Explain it without notes

Practice

Trade-offs

Done when you can