Module 10: pgvector and Postgres

Roadmap

You are in Track 08, Embeddings & Vector Databases. Module 03 taught the four ways to turn two vectors into a number, Module 05 built the pipeline that produces vectors reliably, and Module 09: The Vector Database Landscape covers the other options. This module goes deep on the one most backend teams already run: Postgres with the pgvector extension.

After this module you can:

• create a vector(n) column, load embeddings into it, upsert them idempotently, and query them with ORDER BY embedding <=> $1 LIMIT k;
• say which operator is which metric, and why <#> returns a negative number;
• explain exact scan, HNSW and IVFFlat, set their build and query parameters, and create the index at the right moment with the matching operator class;
• predict why a WHERE clause can make an indexed query return fewer than k rows, and fix it;
• measure the recall of an index against exact search in your own database instead of trusting a default.

Why it matters: for many products the first vector store is "a column in the database we already have", which is a good choice when vectors should live next to the rows they describe, in the same transactions and backups. It stays a good choice only if you know the few ways it quietly returns slower or worse answers than you expect. Each one is demonstrated below.

Concept

Explanation

Why teams choose pgvector, and what they give up

The README's pitch is "store your vectors with the rest of your data", with ACID compliance, point-in-time recovery and JOINs (per the pgvector README, checked Oct 2026). Concretely:

• One system, one transaction. A chunk's text, metadata and embedding are written, updated and deleted together, so they cannot drift apart. Block 3 shows a failed transaction leaving nothing behind.
• Joins and ordinary filters. "The most similar posts by authors on this team" is one query, not a vector search plus a lookup in a second system (block 3).
• Operations you already have. The README says pgvector uses the write-ahead log, so replication and point-in-time recovery work, and suggests existing tools such as pg_stat_statements for monitoring. It lists clients for many languages.
• No sync job between two datastores.

What you give up: you scale the way Postgres scales. The README's scaling advice is to shrink the working set (halfvec, binary quantization), scale vertically, add replicas, or shard with Citus, PgDog or another approach. There are two approximate index types, and the index shares memory and CPU with the rest of your workload. For the dedicated alternatives, see Module 09 (The Vector Database Landscape); this module is about doing the "stay in Postgres" option well.

The type, the operators and the query shape

Run CREATE EXTENSION vector; once per database. A vector(3) column stores three single-precision floats per row. The README says a vector takes 4 * dimensions + 8 bytes, holds only finite numbers, and can have up to 16,000 dimensions, but the approximate indexes only accept vector columns of up to 2,000 dimensions (halfvec: 4,000). The declared size is enforced: four numbers into vector(3) fail with expected 3 dimensions, not 4 (block 2).

(per the pgvector README, checked Oct 2026; it also lists <~> Hamming and <%> Jaccard for the bit type, not used here). In the README's words, <#> is negative "since Postgres only supports ASC order index scans on operators": to make "largest inner product first" sort ascending, the sign is flipped.

That is why the query shape is always an ascending sort with a limit:

SELECT id, body FROM chunks ORDER BY embedding <=> $1 LIMIT 5;

The README's troubleshooting section says an index is used only when the query has an ORDER BY and a LIMIT, and the ORDER BY is "the result of a distance operator (not an expression) in ascending order". A distance filter such as WHERE embedding <-> q < 5 also needs the ORDER BY and LIMIT to use an index. Block 9 shows what each violation looks like in a plan.

Which operator to use is Module 03's decision, not a pgvector one: on unit-length vectors <->, <#> and <=> rank identically (block 6 confirms it inside real pgvector), and the README recommends inner product for best exact-search performance on normalized vectors. Match the operator to how the model was trained.

Exact scan first

With no index pgvector computes the distance to every row and sorts: exact neighbours, perfect recall. That is the default, and the README adds the sentence that matters most for evaluation: "Unlike typical indexes, you will see different results for queries after adding an approximate index." A B-tree never changes your answers; HNSW and IVFFlat can. Exact search is therefore your ground truth: the README's monitoring advice is to compare approximate results with exact ones using SET LOCAL enable_indexscan = off, which is how every recall figure below was computed.

Exact scan is also a real plan, not a toy. It is right for a small table, and the planner can choose it by itself when a WHERE keeps only a tiny fraction of rows (block 10). Without an index you can still add parallel workers (max_parallel_workers_per_gather, per the README). When to leave exact search is the subject of Module 13: Exact vs. Approximate Search.

Two approximate indexes

(per the pgvector README, checked Oct 2026; both index vector up to 2,000 and halfvec up to 4,000 dimensions). How the graph and the clusters work is the topic of Module 14: HNSW Explained and Module 15: IVF, Product Quantization and Other Indexes; here you only need the dials.

HNSW. m is the maximum number of connections per layer and ef_construction the candidate-list size while building; the README says a higher ef_construction gives better recall at the cost of build time and insert speed. At query time hnsw.ef_search is the candidate-list size for the search: higher is better recall, slower. It is also a ceiling on how many rows an indexed query can return, since the README's FAQ says results are "limited by the size of the dynamic candidate list". Block 8 shows ef_search = 5 with LIMIT 10 returning five rows.

IVFFlat. The README names "three keys to achieving good recall": create the index after the table has data, choose a sensible number of lists, and set a sensible probes at query time. People skip the first. IVFFlat's build runs k-means over the rows that exist at that moment (one of its progress phases is performing k-means), so on an empty table there is nothing to cluster. Postgres says so with a notice (ivfflat index created with little data) and block 7 measures the damage on the same 20,000 rows. The README adds that probes can be set to the number of lists to make the search exact.

One index per metric, and the operator class must match

An index serves one distance function. The README says to "add an index for each distance function you want to use", each with its own operator class (table above). A query benefits only if its operator is the one the index was built with; otherwise Postgres does an exact scan and sort, which is correct but slow, with no error. Block 9 prints the plans: a vector_cosine_ops index is used by <=> and ignored by <->; after a second index with vector_ip_ops is built, <#> uses that one.

Filtering: the WHERE clause runs after the index

The README: "filtering is applied after the index is scanned. If a condition matches 10% of rows, with HNSW and the default hnsw.ef_search of 40, only 4 rows will match on average." The index hands back about 40 candidates, the filter discards the 90% that do not match, and LIMIT 10 gets whatever is left. Block 10 measures 3.84 rows on average, and some queries return none. The README's remedies, in its order:

1. Index the filter column (B-tree or multicolumn). Postgres can then search the matching rows exactly; the README says this works well when the condition matches a low percentage of rows. (README only, not run here.)
2. Iterative index scans, added in 0.8.0: SET hnsw.iterative_scan = strict_order; or relaxed_order (and ivfflat.iterative_scan = relaxed_order). The scan continues until enough rows are found or it hits hnsw.max_scan_tuples (20,000 by default) or ivfflat.max_probes. Strict keeps results in exact distance order; relaxed allows them slightly out of order for better recall, and a materialized CTE can restore strict order.
3. Partial indexes when you filter on a few distinct values and partitioning when you filter on many.
4. For multi-tenant apps, a shared approximate index lets one tenant's vectors affect the recall and speed of the others; the README suggests list partitioning or separate tables.

The FAQ adds that dead tuples can also reduce the result, and that NULL vectors are not indexed (nor zero vectors for cosine distance). Filtered search in general is the topic of Module 16: Metadata Filtering and Hybrid Queries; this module gives you pgvector's symptoms and switches.

Smaller vectors: halfvec

The README documents several ways to shrink the working set. The simplest is the halfvec type (half-precision elements, 2 * dimensions + 8 bytes per vector, indexable up to 4,000 dimensions). You can also keep the table as vector and index a cast of it, so the index is smaller while the stored data stays full precision ("half-precision indexing"):

CREATE INDEX ON items USING hnsw ((embedding::halfvec(3)) halfvec_l2_ops);

Queries must repeat the same cast or the planner will not match the index (block 11). The README also documents binary quantization (binary_quantize, a bit index with Hamming distance, then a re-rank by the original vectors) and indexing a subvector. Those are not run here; they trade recall for size, so measure them on your own data.

Operating it

Code

Python 3.13 and numpy 2.4.6 only, plus the docker command. The Python code reaches Postgres by piping SQL into psql inside the container, so no database driver is needed. Every random generator is seeded. HNSW and IVFFlat builds are not bit-for-bit repeatable (blocks 7 and 8 build twice to show it), so if you re-run these blocks expect recall figures to move in the second decimal place while the shapes stay the same.

1. Start a throwaway pgvector

docker run -d --rm --name pgvector-demo -e POSTGRES_PASSWORD=demo pgvector/pgvector:pg16
docker exec pgvector-demo pg_isready -U postgres

pgvector/pgvector is the image the README links for Docker, and pg16 is one of the tags it lists; per that tag list pg16 currently corresponds to pgvector 0.8.7, while the image on this machine, which produced every output below, has 0.8.6. I ran exactly these commands except for the container name, which I made unique so it could not clash with other containers here, and I passed it to the Python code through the PGV_CONTAINER environment variable that pgv.py reads. No port is published. Cleanup is docker stop pgvector-demo; --rm deletes the container when it stops.

2. The operators, the query shape and the dimension check, in real pgvector

Save as tiny.sql and run docker exec -i pgvector-demo psql -U postgres -X -q < tiny.sql.

CREATE EXTENSION vector;
SELECT extversion FROM pg_extension WHERE extname = 'vector';

-- 1. A vector column and the four distance operators (query vector: [3,1,2])
CREATE TABLE items (id bigserial PRIMARY KEY, name text, embedding vector(3));
INSERT INTO items (name, embedding)
VALUES ('a', '[1,2,3]'), ('b', '[4,5,6]'), ('c', '[6,2,4]'), ('d', '[-3,-1,-2]');

SELECT id, name,
       embedding <->  '[3,1,2]' AS l2,
       embedding <#>  '[3,1,2]' AS neg_ip,
       embedding <=>  '[3,1,2]' AS cosine_dist,
       embedding <+>  '[3,1,2]' AS l1
FROM items ORDER BY id;

-- 2. The query shape: ORDER BY <distance operator> (ascending) LIMIT k, once per operator
SELECT 'l2' AS op, array_agg(id ORDER BY d) AS top3
  FROM (SELECT id, embedding <-> '[3,1,2]' AS d FROM items ORDER BY d LIMIT 3) t
UNION ALL
SELECT 'neg_ip', array_agg(id ORDER BY d)
  FROM (SELECT id, embedding <#> '[3,1,2]' AS d FROM items ORDER BY d LIMIT 3) t
UNION ALL
SELECT 'cosine', array_agg(id ORDER BY d)
  FROM (SELECT id, embedding <=> '[3,1,2]' AS d FROM items ORDER BY d LIMIT 3) t
UNION ALL
SELECT 'l1', array_agg(id ORDER BY d)
  FROM (SELECT id, embedding <+> '[3,1,2]' AS d FROM items ORDER BY d LIMIT 3) t;

-- 3. Turning distances back into the scores you may expect (README recipes)
SELECT id, (embedding <#> '[3,1,2]') * -1 AS inner_product,
       1 - (embedding <=> '[3,1,2]') AS cosine_similarity
FROM items ORDER BY id;

-- 4. The classic bug: DESC on a negative inner product returns the LEAST similar row first
SELECT id, name FROM items ORDER BY embedding <#> '[3,1,2]' DESC LIMIT 1;

-- 5. A wrong-sized vector is rejected by the column type
INSERT INTO items (name, embedding) VALUES ('bad', '[1,2,3,4]');

Output (trailing blanks trimmed):

 extversion
------------
 0.8.6
(1 row)

 id | name |         l2         | neg_ip |     cosine_dist     | l1
----+------+--------------------+--------+---------------------+----
  1 | a    |  2.449489742783178 |    -11 |  0.2142857142857143 |  4
  2 | b    |  5.744562646538029 |    -29 | 0.11673988938389968 |  9
  3 | c    | 3.7416573867739413 |    -28 |                   0 |  6
  4 | d    |  7.483314773547883 |     14 |                   2 | 12
(4 rows)

   op   |  top3
--------+---------
 l2     | {1,3,2}
 neg_ip | {2,3,1}
 cosine | {3,2,1}
 l1     | {1,3,2}
(4 rows)

 id | inner_product | cosine_similarity
----+---------------+--------------------
  1 |            11 | 0.7857142857142857
  2 |            29 | 0.8832601106161003
  3 |            28 |                  1
  4 |           -14 |                 -1
(4 rows)

 id | name
----+------
  4 | d
(1 row)

ERROR:  expected 3 dimensions, not 4

Four different winners for the same query: <-> and <+> pick a (closest tip), <#> picks b (the biggest inner product, 29, so the smallest negative), and <=> picks c, which is the query doubled ([6,2,4] against [3,1,2]), so its cosine distance is 0 although it is 3.74 away in L2. The README's two recipes turn distances back into scores. ORDER BY embedding <#> q DESC LIMIT 1 returns row 4, the vector pointing the opposite way: the DESC bug. The extension version query returned 0.8.6, and the last statement shows the type enforcing its declared size.

3. One database: a join, a rolled-back transaction, and an idempotent upsert

Save as relational.sql and run it the same way, in the same database (it needs the extension from block 2).

-- One database: vectors next to ordinary tables, joins and transactions
CREATE TABLE authors (id int PRIMARY KEY, name text NOT NULL);
CREATE TABLE posts (id serial PRIMARY KEY, author_id int NOT NULL REFERENCES authors (id),
                    title text NOT NULL, embedding vector(3) NOT NULL);
INSERT INTO authors VALUES (1, 'Asha'), (2, 'Ben');
INSERT INTO posts (author_id, title, embedding) VALUES
    (1, 'Intro to indexes', '[1,2,3]'), (1, 'Vacuum and bloat', '[4,5,6]'),
    (2, 'Query planning',   '[6,2,4]'), (2, 'Backups',          '[-3,-1,-2]');

-- A similarity query with a JOIN and an ordinary WHERE
SELECT p.title, a.name AS author, p.embedding <=> '[3,1,2]' AS cosine_dist
FROM posts p JOIN authors a ON a.id = p.author_id
WHERE a.name = 'Ben'
ORDER BY p.embedding <=> '[3,1,2]' LIMIT 2;

-- A transaction: the second insert violates a foreign key, so the first one disappears too
BEGIN;
INSERT INTO posts (author_id, title, embedding) VALUES (1, 'Draft', '[0,0,1]');
INSERT INTO posts (author_id, title, embedding) VALUES (99, 'Orphan', '[0,1,0]');
ROLLBACK;
SELECT count(*) AS posts_after_rollback FROM posts;

-- Upsert keyed on (model, content_hash): Module 05's cache key, enforced by the database
CREATE TABLE chunks (id bigserial PRIMARY KEY, model text NOT NULL, content_hash text NOT NULL,
                     body text NOT NULL, embedding vector(3) NOT NULL, UNIQUE (model, content_hash));
INSERT INTO chunks (model, content_hash, body, embedding) VALUES ('m1', 'h1', 'hello', '[1,2,3]')
    ON CONFLICT (model, content_hash) DO UPDATE SET embedding = EXCLUDED.embedding;
INSERT INTO chunks (model, content_hash, body, embedding) VALUES ('m1', 'h1', 'hello', '[9,9,9]')
    ON CONFLICT (model, content_hash) DO UPDATE SET embedding = EXCLUDED.embedding;
SELECT count(*) AS rows, max(embedding::text) AS stored FROM chunks;

Output (trailing blanks trimmed):

     title      | author | cosine_dist
----------------+--------+-------------
 Query planning | Ben    |           0
 Backups        | Ben    |           2
(2 rows)

ERROR:  insert or update on table "posts" violates foreign key constraint "posts_author_id_fkey"
DETAIL:  Key (author_id)=(99) is not present in table "authors".
 posts_after_rollback
----------------------
                    4
(1 row)

 rows | stored
------+---------
    1 | [9,9,9]
(1 row)

The join returns only Ben's posts, ranked by cosine distance. In the transaction the first insert succeeds and the second violates the foreign key, so after ROLLBACK the table still has four rows and the draft vector is gone. The upsert wrote the same (model, content_hash) twice and left one row holding the second embedding. That uniqueness constraint is Module 05's cache key moved into the database, which makes re-running an embedding pipeline safe.

4. The settings, and their defaults as the server reports them

Save as settings.sql and run it the same way (each psql run is a fresh session, which matters for the first statement).

-- A brand-new session has not loaded pgvector's library yet, so its settings do not exist yet
SHOW hnsw.ef_search;
-- Any use of the extension loads it
SELECT '[1,2,3]'::vector AS touch_the_extension;
SELECT name, setting FROM pg_settings WHERE name LIKE 'hnsw.%' OR name LIKE 'ivfflat.%' ORDER BY name;

-- SET LOCAL, and set_config(name, value, is_local), last until the end of the transaction
BEGIN;
SET LOCAL hnsw.ef_search = 100;
SELECT current_setting('hnsw.ef_search') AS after_set_local,
       set_config('hnsw.ef_search', '150', true) AS set_config_returned;
SELECT current_setting('hnsw.ef_search') AS after_set_config;
COMMIT;
SELECT current_setting('hnsw.ef_search') AS after_commit;

Output (trailing blanks trimmed):

ERROR:  unrecognized configuration parameter "hnsw.ef_search"
 touch_the_extension
---------------------
 [1,2,3]
(1 row)

           name           | setting
--------------------------+---------
 hnsw.ef_search           | 40
 hnsw.iterative_scan      | off
 hnsw.max_scan_tuples     | 20000
 hnsw.scan_mem_multiplier | 1
 ivfflat.iterative_scan   | off
 ivfflat.max_probes       | 32768
 ivfflat.probes           | 1
(7 rows)

 after_set_local | set_config_returned
-----------------+---------------------
 100             | 150
(1 row)

 after_set_config
------------------
 150
(1 row)

 after_commit
--------------
 40
(1 row)

The four defaults the README states all match the server: hnsw.ef_search 40, ivfflat.probes 1, hnsw.max_scan_tuples 20,000 and hnsw.scan_mem_multiplier 1. The README does not state the other two defaults; the server reports iterative_scan as off for both index types (iterative scans are opt-in) and ivfflat.max_probes as 32768. Two more observations: pgvector's settings do not exist in a brand-new session until the extension's library has been loaded, so the first SHOW fails; and SET LOCAL and set_config(..., true) both revert at the end of the transaction, which is why the Python client uses the latter.

5. Helpers for the experiments

The next blocks use a 20,000-row table of 32-dimensional unit vectors, clustered the way text embeddings are (not uniform noise), plus 100 query vectors drawn the same way and a random category_id from 0 to 9 per row. Save the helper file below as pgv.py next to the step files. Queries are read from a queries table through a scalar subquery (the README's "nearest neighbors to a row" shape), which keeps plans short; explain hides the sub-plan that fetches the query vector.

"""pgv.py - helpers shared by the steps below. Needs numpy and the `docker` CLI, nothing else.
It runs SQL by piping it into `psql` inside a running pgvector container, so no Postgres driver is needed."""
import os
import subprocess

import numpy as np

CONTAINER = os.environ.get("PGV_CONTAINER", "pgvector-demo")
K = 10                      # neighbours per query


def psql(sql):
    """Run a SQL script in the container; return its rows ('|'-separated) plus any NOTICE text."""
    cmd = ["docker", "exec", "-i", CONTAINER, "psql", "-U", "postgres",
           "-X", "-q", "-A", "-t", "-v", "ON_ERROR_STOP=1"]
    res = subprocess.run(cmd, input=sql, capture_output=True, text=True, encoding="utf-8")
    if res.returncode != 0:
        raise RuntimeError(res.stderr.strip())
    return res.stdout + res.stderr


def vec_text(v):
    """pgvector's text format '[x1,x2,...]'. Nine significant digits round-trip a float32 exactly."""
    return "[" + ",".join(f"{x:.9g}" for x in v) + "]"


def make_corpus(n=20_000, dim=32, n_clusters=40, n_queries=100, seed=0, noise=1.5):
    """Clustered unit-length float32 vectors (text embeddings are clustered, not uniform noise), 100 query
    vectors drawn the same way, and a random category 0-9 per row, independent of the vector."""
    rng = np.random.default_rng(seed)
    centres = rng.normal(size=(n_clusters, dim))

    def draw(m):
        pts = centres[rng.integers(0, n_clusters, size=m)] + noise * rng.normal(size=(m, dim))
        pts /= np.linalg.norm(pts, axis=1, keepdims=True)
        return pts.astype(np.float32)

    return draw(n), draw(n_queries), rng.integers(0, 10, size=n)


X, Q, CAT = make_corpus()


def ensure_loaded():
    """Create and fill `docs` (20,000 rows) and `queries` (100 rows) unless they are already there."""
    exists = psql("SELECT to_regclass('docs') IS NOT NULL;").strip() == "t"
    if exists and psql("SELECT count(*) FROM docs;").strip() == str(len(X)):
        return
    psql("CREATE EXTENSION IF NOT EXISTS vector;")
    lines = ["DROP TABLE IF EXISTS docs; DROP TABLE IF EXISTS queries;",
             "CREATE TABLE docs (id int PRIMARY KEY, category_id int, embedding vector(32));",
             "CREATE TABLE queries (qid int PRIMARY KEY, embedding vector(32));",
             "COPY docs (id, category_id, embedding) FROM STDIN;"]
    lines += [f"{i}\t{CAT[i]}\t{vec_text(X[i])}" for i in range(len(X))]
    lines += [r"\.", "COPY queries (qid, embedding) FROM STDIN;"]
    lines += [f"{i}\t{vec_text(Q[i])}" for i in range(len(Q))]
    lines += [r"\.", "ANALYZE docs; ANALYZE queries;"]
    psql("\n".join(lines) + "\n")


def reset_indexes():
    """Drop every index the steps create, so each step starts from the bare table."""
    psql("DROP INDEX IF EXISTS ivf, hnsw_cos, hnsw_ip, hnsw_f32, hnsw_f16;")


def run(settings=(), where="", op="<=>", cast="", table="docs"):
    """Top-K ids for every query vector, in one psql session: {qid: [ids in returned order]}.
    `settings` are SET statements; `cast` (e.g. '::halfvec(32)') is applied to both sides of the operator."""
    stmts = list(settings)
    w = f"WHERE {where} " if where else ""
    for i in range(len(Q)):
        stmts.append(f"SELECT {i}, id FROM {table} {w}"
                     f"ORDER BY embedding{cast} {op} (SELECT embedding{cast} FROM queries WHERE qid = {i}) LIMIT {K};")
    res = {i: [] for i in range(len(Q))}
    for line in psql("\n".join(stmts) + "\n").splitlines():
        if "|" in line:
            qid, doc_id = line.split("|")
            res[int(qid)].append(int(doc_id))
    return res


def recall(res, exact):
    """(mean fraction of the exact top-K that was returned, mean number of rows returned)."""
    hits = [len(set(res[i]) & set(exact[i])) / len(exact[i]) for i in res]
    return float(np.mean(hits)), float(np.mean([len(res[i]) for i in res]))


def explain(sql, settings=""):
    """The query plan without cost numbers, and without the sub-plan that just fetches the query vector."""
    lines = psql(f"{settings}EXPLAIN (COSTS OFF) {sql}").rstrip().splitlines()
    return "\n".join(l for l in lines if not any(s in l for s in ("InitPlan", "on queries", "qid = ")))

6. Exact scan: pgvector against numpy, and the identities from Module 03

import numpy as np
from pgv import *

ensure_loaded()
reset_indexes()
n_docs, n_queries = psql("SELECT count(*), (SELECT count(*) FROM queries) FROM docs;").strip().split("|")
print(f"docs: {n_docs} rows of 32 dimensions, queries: {n_queries}")

# 1. With no index every query is an exact scan. Is pgvector's exact answer numpy's exact answer?
exact = run()
ref = {i: [int(j) for j in np.argsort(-(X.astype(np.float64) @ Q[i].astype(np.float64)))[:K]] for i in range(len(Q))}
print("pgvector <=> top-10 identical (same order) to numpy cosine top-10:", sum(exact[i] == ref[i] for i in exact), "of", len(Q), "queries")

# 2. The unit-vector identities from Module 03, through four real operators
for op, name in [("<->", "L2"), ("<#>", "negative inner product"), ("<=>", "cosine"), ("<+>", "L1")]:
    res = run(op=op)
    same = sum(res[i] == exact[i] for i in res)
    overlap = np.mean([len(set(res[i]) & set(exact[i])) for i in res])
    print(f"{op}  {name:<22} same ordered top-10 as <=> in {same:>3} of {len(Q)} queries, mean overlap {overlap:.1f} of {K}")

# 3. What an exact scan looks like to the planner
print(explain("SELECT id FROM docs ORDER BY embedding <=> (SELECT embedding FROM queries WHERE qid = 0) LIMIT 10;"))

Output:

docs: 20000 rows of 32 dimensions, queries: 100
pgvector <=> top-10 identical (same order) to numpy cosine top-10: 100 of 100 queries
<->  L2                     same ordered top-10 as <=> in 100 of 100 queries, mean overlap 10.0 of 10
<#>  negative inner product same ordered top-10 as <=> in 100 of 100 queries, mean overlap 10.0 of 10
<=>  cosine                 same ordered top-10 as <=> in 100 of 100 queries, mean overlap 10.0 of 10
<+>  L1                     same ordered top-10 as <=> in   0 of 100 queries, mean overlap 5.7 of 10
Limit
  ->  Sort
        Sort Key: ((docs.embedding <=> $0))
        ->  Seq Scan on docs

With no index, pgvector's <=> top-10 matches a float64 numpy brute force in all 100 queries, in the same order. <->, <#> and <=> return the same ordered top-10 in all 100, as Module 03's identities predict for unit vectors; <+> (L1) returns the same order in none and shares 5.7 of 10 ids on average. The plan is a Seq Scan feeding a Sort: every row is visited.

7. IVFFlat: probes, and building the index too early

import numpy as np
from pgv import *

ensure_loaded()
reset_indexes()
exact = run()                                    # no index yet: exact
build = "SET max_parallel_maintenance_workers = 0; "     # one build worker, to keep the experiment simple

# README starting point for lists: rows / 1000 up to 1M rows  ->  20,000 / 1000 = 20.
# Build the index twice on the same data to see how much a rebuild changes the recall.
PROBES = (1, 2, 5, 10, 20)
tables = []
for attempt in (1, 2):
    reset_indexes()
    psql(build + "CREATE INDEX ivf ON docs USING ivfflat (embedding vector_cosine_ops) WITH (lists = 20);")
    tables.append({p: run([f"SET ivfflat.probes = {p};"]) for p in PROBES})
print("index size:", psql("SELECT pg_size_pretty(pg_relation_size('ivf'));").strip())

print("\nIVFFlat, lists = 20, built AFTER loading 20,000 rows (two separate builds)")
print("probes  recall@10 (build 1)  recall@10 (build 2)  rows returned")
for p in PROBES:
    r1, rows = recall(tables[0][p], exact)
    r2, _ = recall(tables[1][p], exact)
    print(f"{p:>6}  {r1:>19.3f}  {r2:>19.3f}  {rows:>13.1f}")

q0 = "SELECT id FROM docs ORDER BY embedding <=> (SELECT embedding FROM queries WHERE qid = 0) LIMIT 10;"
print("\nplan with probes = 20 (= lists):")
print(explain(q0, "SET ivfflat.probes = 20; "))

# The same index definition on an EMPTY table that is filled afterwards
psql("DROP TABLE IF EXISTS docs_early; CREATE TABLE docs_early (LIKE docs INCLUDING ALL);")
print("\ncreating the index on the empty table:")
print(psql(build + "CREATE INDEX ON docs_early USING ivfflat (embedding vector_cosine_ops) WITH (lists = 20);").strip())
psql("INSERT INTO docs_early SELECT * FROM docs; ANALYZE docs_early;")
print("\nsame 20,000 rows, same lists = 20, but the index was built before the data arrived")
print("probes  recall@10")
for p in (1, 5, 20):
    res = run([f"SET ivfflat.probes = {p};"], table="docs_early")
    print(f"{p:>6}  {recall(res, exact)[0]:>9.3f}")
psql("DROP TABLE docs_early;")

Output:

index size: 3000 kB

IVFFlat, lists = 20, built AFTER loading 20,000 rows (two separate builds)
probes  recall@10 (build 1)  recall@10 (build 2)  rows returned
     1                0.634                0.620           10.0
     2                0.749                0.751           10.0
     5                0.907                0.905           10.0
    10                0.983                0.984           10.0
    20                1.000                1.000           10.0

plan with probes = 20 (= lists):
Limit
  ->  Index Scan using ivf on docs
        Order By: (embedding <=> $0)

creating the index on the empty table:
NOTICE:  ivfflat index created with little data
DETAIL:  This will cause low recall.
HINT:  Drop the index until the table has more data.

same 20,000 rows, same lists = 20, but the index was built before the data arrived
probes  recall@10
     1      0.276
     5      0.724
    20      1.000

Recall@10 is the share of the exact top-10 the index returned. lists = 20 is the README's starting rule (20,000 / 1,000). Raising probes buys recall: about 0.62 to 0.63 at one probe, 0.98 at ten, and 1.000 at twenty, which is every list and therefore an exact search. The two builds differ by 0.014 at probes = 1 here (and by up to 0.04 in other runs I made), so treat gaps of a few hundredths between single builds as noise.

Two results to read closely. First, the README says that with probes equal to the number of lists the planner will not use the index; here EXPLAIN still shows an Index Scan at probes = 20, although the results were exact. I did not investigate why and a larger table may behave differently, so check your own plan instead of relying on either description. Second, building the same index on the empty table and then loading the same rows printed the notice and cut recall at probes = 1 to 0.276 from about 0.62, and at probes = 5 to 0.724 from about 0.91. Across every run I made while developing this module, the early-built index stayed between 0.25 and 0.29 at one probe and the late-built one between 0.62 and 0.66. Only probes = 20 was unaffected, since it scans everything.

8. HNSW: ef_search, and what a rebuild changes

import numpy as np
from pgv import *

ensure_loaded()
reset_indexes()
exact = run()
build = "SET max_parallel_maintenance_workers = 0; "

# No WITH clause: the default m and ef_construction. Build it twice to see how much a rebuild changes the recall.
sizes, tables = [], []
for attempt in (1, 2):
    reset_indexes()
    psql(build + "CREATE INDEX hnsw_cos ON docs USING hnsw (embedding vector_cosine_ops);")
    sizes.append(psql("SELECT pg_size_pretty(pg_relation_size('hnsw_cos'));").strip())
    tables.append({ef: run([f"SET hnsw.ef_search = {ef};"]) for ef in (5, 10, 20, 40, 100, 200)})
print("HNSW (default m and ef_construction) index sizes for two builds of the same data:", sizes)

print("\nef_search  recall@10 (build 1)  recall@10 (build 2)  rows returned")
for ef in (5, 10, 20, 40, 100, 200):
    r1, rows = recall(tables[0][ef], exact)
    r2, _ = recall(tables[1][ef], exact)
    print(f"{ef:>9}  {r1:>19.3f}  {r2:>19.3f}  {rows:>13.1f}")

# An approximate index changes the answers: compare the default setting with the exact scan, per query
changed = sum(set(tables[1][40][i]) != set(exact[i]) for i in exact)
print(f"\nwith ef_search = 40, {changed} of {len(Q)} queries return a different set of 10 ids than the exact scan")

Output:

HNSW (default m and ef_construction) index sizes for two builds of the same data: ['8496 kB', '8496 kB']

ef_search  recall@10 (build 1)  recall@10 (build 2)  rows returned
        5                0.448                0.451            5.0
       10                0.760                0.780           10.0
       20                0.885                0.891           10.0
       40                0.956                0.953           10.0
      100                0.993                0.992           10.0
      200                0.999                0.999           10.0

with ef_search = 40, 29 of 100 queries return a different set of 10 ids than the exact scan

Recall climbs with ef_search: about 0.77 at 10, 0.95 at the default of 40, 0.99 at 100 and 0.999 at 200. With ef_search = 5 and LIMIT 10 only five rows come back, so the candidate list really is a ceiling on the result size. The two builds differ by up to 0.02 (at ef_search = 10) and by 0.003 at the default. At the default setting 29 of the 100 queries return a set of ten ids that is not identical to the exact one: that is what the README means by seeing different results after adding an approximate index.

9. Does the planner use the index? Operator classes and query shapes

from pgv import *

ensure_loaded()
reset_indexes()
build = "SET max_parallel_maintenance_workers = 0; "
psql(build + "CREATE INDEX hnsw_cos ON docs USING hnsw (embedding vector_cosine_ops);")

q = "(SELECT embedding FROM queries WHERE qid = 0)"
cases = {
    "1. operator matches the index (<=> with vector_cosine_ops)":
        f"SELECT id FROM docs ORDER BY embedding <=> {q} LIMIT 10;",
    "2. a different operator (<->) on the cosine index":
        f"SELECT id FROM docs ORDER BY embedding <-> {q} LIMIT 10;",
    "3. the distance wrapped in an expression, DESC":
        f"SELECT id FROM docs ORDER BY 1 - (embedding <=> {q}) DESC LIMIT 10;",
    "4. no LIMIT":
        f"SELECT id FROM docs ORDER BY embedding <=> {q};",
}
for title, sql in cases.items():
    print(title)
    print(explain(sql))
    print()

# One index per distance function: add an inner-product index next to the cosine one
psql(build + "CREATE INDEX hnsw_ip ON docs USING hnsw (embedding vector_ip_ops);")
print("5. with a second index built with vector_ip_ops, the <#> query")
print(explain(f"SELECT id FROM docs ORDER BY embedding <#> {q} LIMIT 10;"))

Output:

1. operator matches the index (<=> with vector_cosine_ops)
Limit
  ->  Index Scan using hnsw_cos on docs
        Order By: (embedding <=> $0)

2. a different operator (<->) on the cosine index
Limit
  ->  Sort
        Sort Key: ((docs.embedding <-> $0))
        ->  Seq Scan on docs

3. the distance wrapped in an expression, DESC
Limit
  ->  Sort
        Sort Key: (('1'::double precision - (docs.embedding <=> $0))) DESC
        ->  Seq Scan on docs

4. no LIMIT
Sort
  Sort Key: ((docs.embedding <=> $0))
  ->  Seq Scan on docs

5. with a second index built with vector_ip_ops, the <#> query
Limit
  ->  Index Scan using hnsw_ip on docs
        Order By: (embedding <#> $0)

Only case 1 reads the index. A different operator (case 2), a distance wrapped in an expression with DESC (case 3) and a missing LIMIT (case 4) all fall back to Seq Scan plus Sort, with no error and correct results. With a second index built with vector_ip_ops, the <#> query uses hnsw_ip (case 5): one index per metric.

10. Filtering: fewer rows than LIMIT, and the fixes

import numpy as np
from pgv import *

ensure_loaded()
reset_indexes()
psql("SET max_parallel_maintenance_workers = 0; CREATE INDEX hnsw_cos ON docs USING hnsw (embedding vector_cosine_ops);")
q = "(SELECT embedding FROM queries WHERE qid = 0)"


def report(label, flt, settings, exact_f):
    res = run(settings, where=flt)
    rows = [len(v) for v in res.values()]
    print(f"{label:<34} {np.mean(rows):>8.2f} {min(rows):>4} {max(rows):>4} {sum(r == 0 for r in rows):>6} "
          f"{recall(res, exact_f)[0]:>9.3f}")


def header(title):
    return f"{title:<34} {'avg rows':>8} {'min':>4} {'max':>4} {'empty':>6} {'recall@10':>9}"


# --- A filter that keeps about 10% of the rows (categories are random 0-9) ---
flt = "category_id = 3"
print(f"rows matching '{flt}': {psql(f'SELECT count(*) FROM docs WHERE {flt};').strip()} of {len(X)}")
print(explain(f"SELECT id FROM docs WHERE {flt} ORDER BY embedding <=> {q} LIMIT 10;"))

exact_f = run(["SET enable_indexscan = off;"], where=flt)     # ground truth: exact scan with the filter
print("\n" + header("10% filter, LIMIT 10"))
report("default (ef_search = 40)", flt, [], exact_f)
report("ef_search = 100", flt, ["SET hnsw.ef_search = 100;"], exact_f)
report("ef_search = 200", flt, ["SET hnsw.ef_search = 200;"], exact_f)
report("iterative_scan = strict_order", flt, ["SET hnsw.iterative_scan = strict_order;"], exact_f)
report("iterative_scan = relaxed_order", flt, ["SET hnsw.iterative_scan = relaxed_order;"], exact_f)

# --- A filter that keeps about 1% of the rows ---
flt = "id % 100 = 7"
exact_f = run(["SET enable_indexscan = off;"], where=flt)
print(f"\nrows matching '{flt}': {psql(f'SELECT count(*) FROM docs WHERE {flt};').strip()} of {len(X)}")
print("plan the planner picks by itself:")
print(explain(f"SELECT id FROM docs WHERE {flt} ORDER BY embedding <=> {q} LIMIT 10;"))

no_seq = ["SET enable_seqscan = off;"]                          # force the planner to use the HNSW index
print("\n" + header("1% filter, LIMIT 10"))
report("planner's own choice", flt, [], exact_f)
report("index forced, default", flt, no_seq, exact_f)
report("index forced, ef_search = 200", flt, no_seq + ["SET hnsw.ef_search = 200;"], exact_f)
report("index forced, strict_order", flt, no_seq + ["SET hnsw.iterative_scan = strict_order;"], exact_f)
report("index forced, relaxed_order", flt, no_seq + ["SET hnsw.iterative_scan = relaxed_order;"], exact_f)

Output:

rows matching 'category_id = 3': 1945 of 20000
Limit
  ->  Index Scan using hnsw_cos on docs
        Order By: (embedding <=> $0)
        Filter: (category_id = 3)

10% filter, LIMIT 10               avg rows  min  max  empty recall@10
default (ef_search = 40)               3.84    0    9      3     0.384
ef_search = 100                        8.48    2   10      0     0.832
ef_search = 200                        9.96    8   10      0     0.982
iterative_scan = strict_order         10.00   10   10      0     0.924
iterative_scan = relaxed_order        10.00   10   10      0     0.964

rows matching 'id % 100 = 7': 200 of 20000
plan the planner picks by itself:
Limit
  ->  Sort
        Sort Key: ((docs.embedding <=> $0))
        ->  Seq Scan on docs
              Filter: ((id % 100) = 7)

1% filter, LIMIT 10                avg rows  min  max  empty recall@10
planner's own choice                  10.00   10   10      0     1.000
index forced, default                  0.31    0    2     77     0.031
index forced, ef_search = 200          1.83    0    5     14     0.183
index forced, strict_order            10.00   10   10      0     0.961
index forced, relaxed_order           10.00   10   10      0     0.996

At 10%, the default ef_search = 40 returns 3.84 rows on average (the README's arithmetic says about 4), between 0 and 9, and three queries return nothing. ef_search = 100 gives 8.48 and 200 gives 9.96, never a guarantee. Iterative scans return 10 rows for every query; relaxed_order has the higher recall (0.964 against 0.924) because it may return rows slightly out of order, which the README documents and I did not measure.

At 1% the planner makes a different and better choice by itself: it abandons the vector index, filters during an exact Seq Scan and sorts, so every query gets 10 rows with recall 1.000. Forcing the index with enable_seqscan = off (the README's way to encourage an index) shows what the index alone would have done: 0.31 rows on average, with 77 of 100 queries returning nothing (the README arithmetic gives 0.4). Iterative scans repair even that: 10 rows each, recall 0.961 (strict) and 0.996 (relaxed). The symptom depends on which plan you got, so read EXPLAIN before tuning.

11. halfvec: a smaller index, and the cast that must match

from pgv import *

ensure_loaded()
reset_indexes()
exact = run()
build = "SET max_parallel_maintenance_workers = 0; "
q = "(SELECT embedding{c} FROM queries WHERE qid = 0)"

print("table size:", psql("SELECT pg_size_pretty(pg_relation_size('docs'));").strip())

psql(build + "CREATE INDEX hnsw_f32 ON docs USING hnsw (embedding vector_cosine_ops);")
size32 = psql("SELECT pg_size_pretty(pg_relation_size('hnsw_f32'));").strip()
rec32 = {ef: recall(run([f"SET hnsw.ef_search = {ef};"]), exact)[0] for ef in (40, 100)}

reset_indexes()
psql(build + "CREATE INDEX hnsw_f16 ON docs USING hnsw ((embedding::halfvec(32)) halfvec_cosine_ops);")
size16 = psql("SELECT pg_size_pretty(pg_relation_size('hnsw_f16'));").strip()
rec16 = {ef: recall(run([f"SET hnsw.ef_search = {ef};"], cast="::halfvec(32)"), exact)[0] for ef in (40, 100)}

print(f"\n{'index':<36} {'size':>8} {'recall@10, ef=40':>17} {'recall@10, ef=100':>18}")
print(f"{'vector_cosine_ops on vector(32)':<36} {size32:>8} {rec32[40]:>17.3f} {rec32[100]:>18.3f}")
print(f"{'halfvec_cosine_ops on ::halfvec(32)':<36} {size16:>8} {rec16[40]:>17.3f} {rec16[100]:>18.3f}")

print("\nquery written to match the index expression:")
print(explain(f"SELECT id FROM docs ORDER BY embedding::halfvec(32) <=> {q.format(c='::halfvec(32)')} LIMIT 10;"))
print("\nquery that forgets the cast:")
print(explain(f"SELECT id FROM docs ORDER BY embedding <=> {q.format(c='')} LIMIT 10;"))

Output:

table size: 3584 kB

index                                    size  recall@10, ef=40  recall@10, ef=100
vector_cosine_ops on vector(32)       8504 kB             0.952              0.992
halfvec_cosine_ops on ::halfvec(32)   7216 kB             0.951              0.990

query written to match the index expression:
Limit
  ->  Index Scan using hnsw_f16 on docs
        Order By: ((embedding)::halfvec(32) <=> $0)

query that forgets the cast:
Limit
  ->  Sort
        Sort Key: ((docs.embedding <=> $0))
        ->  Seq Scan on docs

On this table the half-precision index is about 15% smaller (7,216 kB against 8,504 kB) with the same recall at both ef_search settings, within build noise. I measured only 32 dimensions, so I make no claim about the saving at other sizes. The query that repeats the cast embedding::halfvec(32) uses the index; the one that forgets it falls back to Seq Scan plus Sort.

12. A Python client with psycopg and pgvector (not executed)

"""NOT EXECUTED in this module's verification: psycopg and the pgvector Python package are not installed on the
machine used here and nothing was installed. Names are taken from the pgvector-python README (checked Oct 2026).

    pip install "psycopg[binary]" pgvector
"""
import psycopg
from pgvector import Vector
from pgvector.psycopg import register_vector

DIM = 384                      # must equal the output size of your embedding model
DSN = "postgresql://postgres:demo@localhost:5432/postgres"   # the container needs a published port: docker run -p 5432:5432 ...

with psycopg.connect(DSN, autocommit=True) as conn:
    conn.execute("CREATE EXTENSION IF NOT EXISTS vector")
    register_vector(conn)                       # after the extension exists; teaches psycopg the vector type
    conn.execute(f"""
        CREATE TABLE IF NOT EXISTS chunks (
            id           bigserial PRIMARY KEY,
            model        text NOT NULL,
            content_hash text NOT NULL,         -- the cache key from Module 05
            body         text NOT NULL,
            embedding    vector({DIM}) NOT NULL,
            UNIQUE (model, content_hash)
        )""")

    def upsert(model, content_hash, body, vec):
        """Idempotent write: re-running the embedding pipeline does not create duplicates."""
        conn.execute(
            """INSERT INTO chunks (model, content_hash, body, embedding) VALUES (%s, %s, %s, %s)
               ON CONFLICT (model, content_hash) DO UPDATE SET embedding = EXCLUDED.embedding""",
            (model, content_hash, body, Vector(vec)))

    def search(query_vec, k=5, ef_search=100):
        """Top-k chunks by cosine distance. The setting is local to the transaction, as in the README's SET LOCAL."""
        with conn.transaction():                # BEGIN ... COMMIT, even though the connection is autocommit
            conn.execute("SELECT set_config('hnsw.ef_search', %s, true)", (str(ef_search),))
            return conn.execute(
                """SELECT id, body, embedding <=> %s AS distance
                   FROM chunks ORDER BY embedding <=> %s LIMIT %s""",
                (Vector(query_vec), Vector(query_vec), k)).fetchall()

    # Build the index AFTER the first bulk load; use CONCURRENTLY on a live table (README).
    # conn.execute("CREATE INDEX CONCURRENTLY ON chunks USING hnsw (embedding vector_cosine_ops)")

Three details worth copying. register_vector(conn) comes after CREATE EXTENSION, because it teaches the driver about a type that must already exist. set_config('hnsw.ef_search', %s, true) is the parameterised form of SET LOCAL (block 4 ran it), so the setting cannot leak to the next user of a pooled connection. And CREATE INDEX CONCURRENTLY cannot run inside a transaction block, one reason the connection is in autocommit mode.

What these numbers do and do not show

Everything measured here is one synthetic corpus: 20,000 vectors, 32 dimensions, 40 clusters, one seed. The recall figures belong to that data and those settings, not to pgvector in general, and real embeddings at hundreds of dimensions will sit elsewhere on every curve. What transfers is the shape: recall rises as the dial turns, an ef_search below LIMIT caps the result, a filter thins what an index returns, an IVFFlat index built on no data is poor, and a mismatched operator silently gives an exact scan. Run this harness on your own vectors before deciding anything.

Visual diagram

  Where pgvector sits

  WRITE PATH (Module 05)                                READ PATH
  ┌────────────┐    ┌───────────────────┐               ┌─────────────────────┐
  │ chunk text │───►│ embed: batched,   │               │ user query text     │
  │ + metadata │    │ cached by hash    │               └──────────┬──────────┘
  └────────────┘    └─────────┬─────────┘                          ▼
                              ▼                          embed with the SAME model
                  INSERT .. ON CONFLICT / COPY                     │
                              │                                    │
  ┌───────────────────────────▼────────────────────────────────────▼──────────┐
  │ Postgres table chunks(id, model, content_hash, body, category_id,         │
  │                       embedding vector(n))                                │
  │                                                                           │
  │  B-tree on filter columns      HNSW or IVFFlat index on embedding         │
  │                                (one per operator class you query with)    │
  │  same transactions, joins, backups and replication as your other data     │
  └───────────────────────────────────────────────────────────────────────────┘
      SELECT .. FROM chunks JOIN .. WHERE .. ORDER BY embedding <=> $1 LIMIT k


  Why a filtered query can return fewer than k rows (measured: 10% filter, ef_search = 40)

  ┌──────────────────┐     ┌───────────────────────┐     ┌─────────────────────────┐
  │ HNSW index scan  │────►│ WHERE category_id = 3 │────►│ rows returned           │
  │ ~40 candidates   │     │ keeps ~10% of them    │     │ ~4 on average (3.84),   │
  │ (ef_search)      │     │                       │     │ range 0 to 9            │
  └────────▲─────────┘     └───────────────────────┘     └────────────┬────────────┘
           │                                                          │
           └─ iterative_scan on: fewer than LIMIT survived? ◄─────────┘
              keep scanning, up to LIMIT rows or hnsw.max_scan_tuples (20,000)


  Choosing a path

  how many rows, and how selective is the WHERE?
      │
      ├── few rows, or a filter that keeps a tiny fraction ──► no vector index: exact scan
      │        (the planner may pick this itself: block 10, the 1% filter)
      │
      └── many rows, weak or no filter
              │
              ├── best speed-recall trade-off, more memory, slower build ──► HNSW
              │        dial: hnsw.ef_search   (up = better recall, slower)
              │        can be built on an empty table
              │
              └── faster build, less memory, lower trade-off ──► IVFFlat
                       dial: ivfflat.probes   (up = better recall, slower)
                       build it AFTER the data is loaded

Practice

1. Predict, then check

Using the four rows of block 2 (a=[1,2,3], b=[4,5,6], c=[6,2,4], d=[-3,-1,-2]) and the query [3,1,2], say which row each of <->, <#>, <=> and <+> ranks first, without running anything. Then check against block 2.

2. Size an IVFFlat index from the README's rules

Using the README's starting advice (lists = rows / 1000 up to 1M rows and sqrt(rows) above; probes = sqrt(lists)), give the starting lists and probes for tables of 20,000, 1,000,000 and 4,000,000 rows. What would you do before trusting them?

3. How many rows will a filtered query return?

An HNSW table has a filter that keeps 5% of the rows. With the default ef_search and LIMIT 10, about how many rows do you expect? What ef_search would the "average" arithmetic suggest to reach 10, and why is that still not a guarantee?

4. Find the bugs

Each has a bug or a silent performance problem. Say what is wrong and fix it.

1. SELECT id FROM docs ORDER BY 1 - (embedding <=> $1) DESC LIMIT 5;
2. SELECT id FROM docs ORDER BY embedding <#> $1 DESC LIMIT 5;
3. An index created USING hnsw (embedding vector_l2_ops), queried with ORDER BY embedding <=> $1 LIMIT 5.
4. An index created USING hnsw ((embedding::halfvec(384)) halfvec_cosine_ops), queried with ORDER BY embedding <=> $1 LIMIT 5.

Checkpoint quiz

1. Why does <#> return the negative inner product?
2. On unit-length vectors, which of <->, <#>, <=> and <+> give the same ranking, and what did block 6 measure for each?
3. A filtered query on an indexed table returns four rows with LIMIT 10. Give the cause and two fixes.
4. When must an IVFFlat index be created relative to loading data, and what does it look like when you get it wrong?
5. A colleague says "an index never changes query results, it only changes speed". Is that true for pgvector?

Project

Build a small semantic search over a corpus of your choice (a few thousand text chunks) with Postgres as the only datastore, and decide with measurements which index to ship. Reuse the embedding pipeline from Module 05 so the table is keyed by model and content hash and a re-run adds no duplicates. The deliverable is a short report plus the harness that produced it. The harness should load the data with COPY; hold out a set of query vectors; compute recall@10 of an HNSW index and an IVFFlat index against an exact scan for at least four values of each query-time dial, building each index twice; repeat the evaluation with a filter that keeps about half the rows, about 10% and about 1%, comparing the default settings, iterative scans, and one filter-aware layout of your choice (B-tree, partial index or partitioning); and record the Postgres and pgvector versions. Show the EXPLAIN output that proves which plan produced each result. Finish with a recommendation (index type, build parameters, query settings, what you would monitor in production) and two sentences on what your numbers do not tell you about other data. No code is given; design the schema and the harness yourself.

Interview questions

Common mistakes & troubleshooting

• A similarity query is slow and EXPLAIN shows Seq Scan plus Sort. The index is not matched: a different operator than the operator class, an expression or DESC in the ORDER BY, no LIMIT, or (for an expression index such as halfvec) a missing cast. Fix the query or build the matching index (blocks 9 and 11). The README adds that on a small table a table scan may simply be faster.
• Fewer rows than LIMIT. Either hnsw.ef_search is smaller than LIMIT (five rows at ef_search = 5, block 8), or a WHERE thins the candidates (block 10), or, per the README, dead tuples reduced them. For IVFFlat the README also points to too little data for the number of lists and to the probes setting. Enable iterative scans, raise the dial, or change the layout.
• Poor recall after creating an IVFFlat index, with the notice ivfflat index created with little data. The index was built before the table had data. DROP INDEX index_name; and recreate it after loading (block 7).
• ERROR: unrecognized configuration parameter "hnsw.ef_search" on SHOW. The extension's library has not been loaded in this session yet; use a vector in any statement first (block 4).
• ERROR: expected 3 dimensions, not 4. The vector does not match the column's declared size. Check that the embedding model is the one the column was sized for, and store the model name in the row (Module 05's cache key).
• Recall differs from run to run after a rebuild. Builds are not repeatable to the last decimal (blocks 7 and 8). Evaluate with a large query set and do not chase differences of a few hundredths from single builds.
• Some rows never appear in results. The README notes that NULL vectors are not indexed (nor zero vectors for cosine distance). Fix the input or keep them out of the table.
• A parallel HNSW build fails in Docker after raising maintenance_work_mem. From the README, not reproduced here: make --shm-size at least as large as maintenance_work_mem.

Further reading & sources

• pgvector: Open-source vector similarity search for Postgres - the README every default, parameter name and rule in this module comes from (operators, HNSW and IVFFlat options, iterative scans, halfvec, performance and troubleshooting notes).
• pgvector CHANGELOG - release notes used to date features (iterative scans arrived in 0.8.0) and to compare the 0.8.6 image used here with the 0.8.7 README.
• pgvector-python - the Python package behind the client example: installation, register_vector, Vector and the Psycopg 3 section.
• pgvector on Docker Hub - the Docker image, built on the official Postgres image, that the code blocks ran against.
• Psycopg 3: Transactions management - documents autocommit and the Connection.transaction() context used in the client example.
• PostgreSQL documentation: Using EXPLAIN - how to read the query plans that decide whether a vector index is used.
• PostgreSQL documentation: CREATE INDEX - reference for CONCURRENTLY and for partial and expression indexes, on which the README's filtering and half-precision advice builds.
• Efficient and robust approximate nearest neighbor search using Hierarchical Navigable Small World graphs (Malkov and Yashunin) - the paper behind pgvector's HNSW index, cited in the README; Module 14 explains it.

Next

Module 11: Chroma and Local Development