Vector search over an open document collection

What you will be able to do

Build a “related papers” search over 15,469 real, openly licensed research abstracts, with PostgreSQL and pgvector on your own computer. Measure exact and approximate search, and keep a category filter from returning too few papers. Then switch to a better model without mixing two models, and remove a paper so that no vector of it is left.

Case studyPortfolio piece

Dataset

arXiv metadata: computer-science records, February and March 2024

Licence

CC0 1.0 (arXiv metadata)

Time

About 4 hours

Runs in

VS Code, Python 3.12+

Skills used

• pgvector
• Embedding versions
• Exact and HNSW search
• Recall@10
• Metadata filters
• Safe model switch
• Deletion and scans
• Migrations

Download the starter project (.zip)Finished project (.zip)

Unzip the starter, open the folder in VS Code, and follow the steps below. They start with a new virtual environment.

The starter project has the database in Docker, the papers table, a loader, and the scripts that make vectors, evaluate and measure. You write the parts that this course is about: the vector table with versions, the search, the indexes, the version switch, removal and the scan. The finished project is the result, for when you are stuck. The code is under the MIT licence, so you can publish your version.

Before you startWhat to know and to have readyAbout 240 minutes

What you need to know

This case study uses the whole course. The lessons it leans on most:

• Vectors as copies of an original, from Ownership and source of truth.
• Embedding versions, HNSW, recall@10 and the iterative scan, from Vector storage.
• Deletion across stores and the scan for orphans, from Recovery and retention.
• Migrations that never change once applied, and CREATE INDEX CONCURRENTLY, from Migrations.
• Reading EXPLAIN, from Read a query plan.
• Embeddings and cosine distance, from Embeddings for search in the RAG course.

What you need on your computer

• Python 3.12 or newer, VS Code, and Docker Desktop (or PostgreSQL 18 with pgvector 0.8), as in From SQLite to PostgreSQL.
• About 2 GB of free disk space. The packages take 1.1 GB (mostly PyTorch), the two models 91 MB and 133 MB, and the database 190 MB at the end.
• About 10 minutes of computer time to make the vectors, while you read.

Tested on macOS (Apple M4, 16 GB) with Python 3.12.13, PostgreSQL 18.6 and pgvector 0.8.7. The models ran with sentence-transformers 6.1.0 and PyTorch 2.14.1 on the CPU. We wrote the Windows and Linux commands but did not test them.

The situation

Hana Sato runs research support at a university library. Researchers ask her team the same question every week: “I have this paper. What else should I read?” The library wants a related papers search for computer science. A researcher pastes a title or a few sentences, and gets the closest papers, optionally from one category only, such as information retrieval.

Daniel Mensah, the library’s developer, will build it. Hana gives him four requirements:

1. Good results. Better than the keyword search that the library has now, and proven on a test, not on a feeling.
2. Fast. A search should take a few milliseconds in the database, so that the page feels instant.
3. The model can change. Better embedding models appear every year. A switch must never mix two models, and must be easy to undo.
4. Removals are final. When a paper is withdrawn, it must disappear from the search, including every vector made from it. A reload of the data must never bring it back.

Hana and Daniel are invented. The papers are real.

What you will build

You build paper-search, a small project with one PostgreSQL database and a few command-line scripts:

Part	What you add	What you measure
1	nothing: you look at the data	sizes, categories, odd records
2	a vector table with an embedding version	15,469 vectors on the CPU
3	the search function	keyword search against vectors, on 500 titles
4	an HNSW index	recall@10 and time, for five settings
5	(the filter is already there)	short results with and without the iterative scan
6	a second model, switched safely	the same 500 titles, then 500 new ones
7	removal and the scan	a removal, 300 new papers, a constructed failure

The design follows the course. The papers table is the original. Each vector is a copy made from an abstract, so it can always be made again. Every vector row records which model made it and from which text.

The data

arXiv is the open archive where many researchers post their papers. It publishes a description of every paper, the metadata: title, abstract, authors, categories and dates. arXiv’s terms put this metadata under CC0 1.0: “You are free to use descriptive metadata about arXiv e-prints under the terms of the Creative Commons Universal (CC0 1.0) Public Domain Declaration.” CC0 means no conditions. The papers themselves (the PDFs) have their own licences, and this project does not use them.

Nextia Learning took the computer-science records that last changed in February or March 2024. We used arXiv’s open interface, with one request every four seconds. That gave 15,469 records. A second file has 300 records from April 2024, for Part 7. The course’s labs repository keeps a copy of both, with their SHA-256 checksums. You download the copies, not arXiv itself. The dataset card, DATASET.md, lists the exact steps. We also looked at US government text (public domain), but its site blocked our script with a bot check. Wikipedia’s licence asks you to share your changes under the same licence, which this course avoids.

Column	Meaning
arxiv_id	arXiv’s ID, for example 2402.05035 (February 2024, paper 5035)
title, abstract	the paper’s title, and its summary of 10 to 486 words
primary_category, categories	for example cs.IR (information retrieval); the first category is the primary one
authors	the authors’ names, as arXiv publishes them
created, updated	the dates of the first and the newest version
comments	the authors’ note, such as “12 pages”, or that the paper was withdrawn

Set up the project

1. Download the starter project from the box at the top of this page, and unzip it into your projects folder. You get the folder paper-search. In VS Code, choose File > Open Folder, open it, and open a terminal with Terminal > New Terminal.

2. Make and activate a virtual environment, and install the exact versions:

   macOS and Linux

   python3 -m venv .venv
   source .venv/bin/activate
   python -m pip install -r requirements-lock.txt
   cp .env.example .env

   Windows

   python -m venv .venv
   .venv\Scripts\Activate.ps1
   python -m pip install -r requirements-lock.txt
   Copy-Item .env.example .env

   The install takes a few minutes. On Linux without a graphics card, first install the CPU build of PyTorch, with the line in requirements.txt.

3. Start PostgreSQL with pgvector. The database listens on port 5433, so it does not collide with the course’s ticket-api:

   docker compose up -d --wait

   Without Docker, create a database papers with the user papers in your own PostgreSQL 18 with pgvector, and change DATABASE_URL in .env.

4. Get the data and load it:

   python -m scripts.get_data
   python -m scripts.load

   Output (end)

   Checked arxiv-cs-2024-papers.jsonl.gz: SHA-256 fb138597e9be... is correct (8.4 MB)
   Checked arxiv-cs-2024-04-new.jsonl.gz: SHA-256 1b924ae04f27... is correct (0.2 MB)
   applied  001_papers
   Database is up to date (1 migrations).
   Loaded 15,469 papers in 2.5 s.

5. Run the tests: python -m pytest. The last line is 6 passed.

What this tells us. The data is the file that we tested: a changed or broken download has another checksum, and the script stops. Migration 001 made the papers table with its rules: a valid ID, a title, an abstract, and the primary category equal to the first category. Run python -m scripts.load again, and it prints The papers table already has data. Nothing was loaded.

Part 1: look at the data that the search needs

Open psql with docker compose exec db psql -U papers -d papers. Look only at what the search depends on: how many abstracts, how long, which categories, and odd records.

SELECT count(*) AS papers, count(DISTINCT abstract) AS abstracts, min(created) AS first, max(created) AS last FROM papers;
SELECT primary_category, count(*) FROM papers GROUP BY 1 ORDER BY 2 DESC LIMIT 6;
SELECT primary_category, count(*) FROM papers WHERE primary_category IN ('cs.IR', 'cs.DB') GROUP BY 1;

 papers | abstracts |   first    |    last
--------+-----------+------------+------------
  15469 |     15468 | 2008-02-22 | 2024-03-28
(1 row)

 primary_category | count
------------------+-------
 cs.CV            |  3344
 cs.LG            |  3068
 cs.CL            |  2126
 cs.RO            |  1065
 cs.AI            |   648
 cs.CR            |   606
(6 rows)

 primary_category | count
------------------+-------
 cs.DB            |    91
 cs.IR            |   330
(2 rows)

The abstracts have 10 words at least, 174 in the middle, 240 at the 90th percentile and 486 at most. 1,451 contain LaTeX formulas with $. Two records say that the paper was withdrawn:

  arxiv_id  |                     title                     |                           comments
------------+-----------------------------------------------+--------------------------------------------------------------
 2402.02616 | The Virtues of Pessimism in Inverse Reinforce | This paper has been withdrawn by the authors pending edits f
 2402.05035 | A Survey on Domain Generalization for Medical | This is a withdrawn submission and will be considered invali
 2402.14602 | Don't mention it: An approach to assess chall | 17 pages, 8 figures, 8 tables. Revision of a submission to P

What this tells us. One abstract appears twice (2311.16203 and 2403.05029, two papers with different titles), so a search can return twins. Most papers are from early 2024, because a record that changed later is not in the sample. The categories are very uneven: a filter on cs.CV keeps 22% of the papers, a filter on cs.DB keeps 0.6%. That difference matters in Part 5. The third “withdrawn” line is a false alarm: it is a revised paper. A removal needs a person’s decision, not a text search.

Part 2: vectors with a version

Each vector is a list of 384 numbers that a model computed from an abstract. The first model is sentence-transformers/all-MiniLM-L6-v2 (Apache-2.0): small, fast and popular. Write the vector table so that every row says which recipe made it, as in Vector storage. Create migrations/002_embedding_versions.sql:

migrations/002_embedding_versions.sql

-- Vectors with a version. A version names one recipe: one model, one revision, one prefix.
-- Exactly one version is current, and every search uses it on both sides.
CREATE TABLE embedding_versions (
    version         text PRIMARY KEY,
    model           text NOT NULL,
    revision        text NOT NULL,
    dimensions      integer NOT NULL CHECK (dimensions = 384),
    query_prefix    text NOT NULL DEFAULT '',
    document_prefix text NOT NULL DEFAULT '',
    is_current      boolean NOT NULL DEFAULT false
);
CREATE UNIQUE INDEX embedding_versions_one_current ON embedding_versions (is_current) WHERE is_current;

INSERT INTO embedding_versions (version, model, revision, dimensions, is_current)
VALUES ('minilm-v1', 'sentence-transformers/all-MiniLM-L6-v2', '1110a243fdf4706b3f48f1d95db1a4f5529b4d41', 384, true);

CREATE TABLE paper_embeddings (
    arxiv_id          text NOT NULL REFERENCES papers (arxiv_id) ON DELETE CASCADE,
    embedding_version text NOT NULL REFERENCES embedding_versions (version),
    source_sha256     text NOT NULL CHECK (source_sha256 ~ '^[0-9a-f]{64}$'),
    embedding         vector(384) NOT NULL,
    PRIMARY KEY (arxiv_id, embedding_version)
);

Three choices carry the rest of the case study. The revision pins the exact model files, so “MiniLM” cannot quietly change. source_sha256 is the checksum of the abstract that the vector was made from: if the abstract changes, the vector is stale. ON DELETE CASCADE makes the database delete a paper’s vectors with the paper.

Apply it, and make the vectors. The first run downloads the model (91 MB) to your Hugging Face cache:

python -m paper_search.migrate
python -m scripts.embed

Output (end)

15,469 papers need a vector of minilm-v1 (sentence-transformers/all-MiniLM-L6-v2).
Embedded 15,469 papers with sentence-transformers/all-MiniLM-L6-v2 in 147 s (105 per second; loading the model took 4.5 s).

What this tells us. A laptop CPU embeds this collection in about two and a half minutes; your time will differ. scripts/embed.py commits every 500 papers. It only embeds papers that have no vector of this version, or a stale one. So an interrupted run continues where it stopped. A second run prints Every paper has an up-to-date vector of minilm-v1. The vectors take 31 MB, about half the size of the papers table.

Part 3: a baseline, the first model and one fair test

Before you trust vectors, prove that they beat what the library has: keyword search. That needs a test with one right answer per question.

The test. Pick 500 papers with a fixed seed. Use each title as the question, and count a hit when the paper’s own abstract is in the first ten results. Vectors are made from abstracts only, so the title is never part of the searched text. Two numbers measure it. hit@10 is the share of questions with a hit. MRR@10 is the mean of 1 divided by the rank of the right paper: 1 for first place, 0.5 for second, 0 when it is missing. Every method answers the same 500 titles. It is a stand-in for real questions, but it is fair and repeatable.

Now write the search. Create paper_search/search.py. The core:

paper_search/search.py (the core)

def search(conn, query_vector, version, *, k=10, category=None, exclude=None, exact=False,
           ef_search=40, iterative="relaxed_order"):
    where = [sql.SQL("e.embedding_version = {}").format(sql.Literal(version))]
    params = {"q": query_vector, "k": k}
    if category is not None:
        where.append(sql.SQL("p.primary_category = %(category)s"))
        params["category"] = category
    if exclude is not None:
        where.append(sql.SQL("p.arxiv_id <> %(exclude)s"))
        params["exclude"] = exclude
    query = sql.SQL(
        "SELECT p.arxiv_id, p.title, p.primary_category, e.embedding <=> %(q)s::vector AS distance "
        "FROM paper_embeddings e JOIN papers p USING (arxiv_id) "
        "WHERE {where} ORDER BY e.embedding <=> %(q)s::vector LIMIT %(k)s"
    ).format(where=sql.SQL(" AND ").join(where))
    with conn.transaction():
        # Set all three every time. SET LOCAL lasts to the end of the outer transaction.
        conn.execute(sql.SQL("SET LOCAL enable_indexscan = {}").format(sql.SQL("off" if exact else "on")))
        conn.execute(sql.SQL("SET LOCAL hnsw.ef_search = {}").format(sql.Literal(int(ef_search))))
        conn.execute(sql.SQL("SET LOCAL hnsw.iterative_scan = {}").format(sql.Literal(iterative)))
        rows = conn.execute(query, params).fetchall()
    return sorted((Hit(*r) for r in rows), key=lambda h: h.distance)

The code writes the version into the SQL as a literal. PostgreSQL uses a partial index only when it sees the value, as Vector storage explained. It comes from the embedding_versions table, never from a user. The category and the vector stay parameters. The finished project’s file also has the imports, the Hit class and the docstring. Copy scripts/search.py from the finished project too: it embeds your question and calls search.

The keyword baseline is PostgreSQL’s own full-text search, already in migration 001: a tsvector column of each abstract, with a GIN index. The evaluation searches it with any word of the title, ranked by ts_rank_cd. Run the test:

python -m scripts.evaluate

method                      hit@1  hit@10  MRR@10  ms/query   (500 titles, seed 7)
keyword (full-text search)  0.364  0.734   0.482      59.0
minilm-v1                   0.846  0.968   0.890     12.57

What this tells us. Keyword search finds the right paper in the first ten for 73% of the titles; MiniLM for 97%, and in first place for 85%. A title and its abstract often use different words for the same idea, and the vectors see that. This test uses the exact search for the vectors, so it compares models, not indexes. Hana’s first requirement is met. Try a question in your own words: python -m scripts.search "approximate nearest neighbour search with metadata filters" returns papers on locality-sensitive hashing, vector search with structured predicates, and graph-based nearest-neighbour search.

Part 4: exact or approximate

The exact search above computes the distance to all 15,469 vectors and sorts them. An HNSW index is a graph of neighbours. A search walks it from far to near, and keeps a list of hnsw.ef_search candidates. It is much faster, and it can miss a true neighbour. Vector storage explains the graph. Recall@10 is the share of the exact ten nearest that the index returned.

Create the index as a partial index for the current version, built without blocking writes:

migrations/003_minilm_hnsw.sql

-- migrate: no-transaction
-- An approximate index for the current version only (a partial index), built without blocking writes.
CREATE INDEX CONCURRENTLY paper_embeddings_minilm_v1_hnsw ON paper_embeddings
    USING hnsw (embedding vector_cosine_ops) WITH (m = 16, ef_construction = 64)
    WHERE embedding_version = 'minilm-v1';

python -m paper_search.migrate applied it in 1.4 s. The index takes 30 MB, as much as the vectors themselves. Then measure. scripts/bench.py takes 200 papers with a fixed seed, and searches for the papers most like each one, with its own stored vector. It runs the exact search and the index with five settings, five times each after a warm-up, on the same queries:

python -m scripts.bench

minilm-v1: 15,469 vectors, 200 queries, k = 10
search                       recall@10  short  ms/search
exact (no index)                 1.000      0     12.074
index, ef_search 20              0.997      0      1.668
index, ef_search 40              0.999      0      1.725
index, ef_search 80              1.000      0      2.003
index, ef_search 160             1.000      0      2.250
index, ef_search 320             1.000      0      3.115

Median milliseconds per search for 200 papers-like-this searches on 15,469 MiniLM vectors. The index is about 7 times faster at the default ef_search 40, and it still finds 99.9% of the true ten nearest.

Text description of the chart

Median time per search over 200 queries: the exact search took 12.1 ms. The HNSW index took 1.7 ms with ef_search 20 (recall 0.997) and 1.7 ms with 40 (recall 0.999). With 80, 160 and 320 it took 2.0, 2.3 and 3.1 ms, all with recall 1.000.

What this tells us. The default, ef_search = 40, is the operating point: 1.7 ms instead of 12.1 ms, and 1,998 of the 2,000 true neighbours. A longer candidate list costs time and gains almost nothing on this collection. Real abstracts rarely tie, so recall is near 1, unlike the course’s made-up tickets. These times are medians of 5 passes on a busy, shared Apple M4. Between two runs, the exact search moved from 8.5 to 12.1 ms. Your numbers will differ, but the order should not: the index is several times faster at the same recall.

Common mistake

The index is exactly as slow as the exact search, and its recall is a perfect 1.000

Symptom: in our first run, every index row showed recall 1.000 and about 10 to 12 ms, the same as the exact search. A result that is too good is a result to check.

Cause: the first search set SET LOCAL enable_indexscan = off only for exact searches. bench.py had already run a query, so its connection was inside a transaction, and conn.transaction() made a savepoint. A SET LOCAL in a savepoint lasts until the outer transaction ends. After the first exact search, every later “index” search was exact too.

Diagnose: run SHOW enable_indexscan; after a search in the same session, or EXPLAIN the query. The index line, Index Scan using paper_embeddings_minilm_v1_hnsw, is missing.

Fix: set all three settings on every search, as the code above does. The finished project’s test test_an_exact_search_does_not_turn_the_index_off_for_the_next_one keeps it fixed.

Part 5: a category filter that returns too few papers

A researcher asks for related papers in information retrieval only: --category cs.IR. Only 330 of 15,469 papers (2%) match. Run the bench with the filter:

python -m scripts.bench --category cs.IR

minilm-v1: 15,469 vectors, 200 queries, k = 10, filter primary_category = 'cs.IR' (330 papers)
search                       recall@10  short  ms/search
exact (no index)                 1.000      0      2.280
index, iterative scan off        0.033    198      2.161
index, strict_order              0.939      0     12.043
index, relaxed_order             0.980      0     11.424

Recall@10 of 200 searches with the filter primary_category = 'cs.IR' (330 of 15,469 papers): exact, the index with the iterative scan off, strict_order and relaxed_order. With the scan off, 198 of 200 searches returned fewer than 10 papers.

Text description of the chart

Recall@10 with the filter: the exact search 1.00 in 2.3 ms. The index with the iterative scan off: 0.03 in 2.2 ms, with 198 of 200 searches short. With strict_order: 0.94 in 12.0 ms. With relaxed_order: 0.98 in 11.4 ms. Only the scan-off search returned short results.

What this tells us. Without the iterative scan, the index hands over its 40 nearest candidates, and only then does the filter keep the cs.IR ones. Near most papers, almost none are. 198 of 200 searches came back short, the problem that Vector storage showed on tickets. The iterative scan fixes it: relaxed_order (0.98) returns full lists, and search() sorts them again. You see it directly in psql, for the papers like the withdrawn 2402.05035:

SET hnsw.iterative_scan = off;   -- the second time: relaxed_order
SELECT count(*) AS found FROM (
  SELECT p.arxiv_id FROM paper_embeddings e JOIN papers p USING (arxiv_id)
  WHERE e.embedding_version = 'minilm-v1' AND p.primary_category = 'cs.IR' AND p.arxiv_id <> '2402.05035'
  ORDER BY e.embedding <=> (SELECT embedding FROM paper_embeddings
                            WHERE arxiv_id = '2402.05035' AND embedding_version = 'minilm-v1')
  LIMIT 10) AS s;

psql: count the cs.IR papers that the search returns (shortened)

SET hnsw.iterative_scan = off;            ...  found: 0
SET hnsw.iterative_scan = relaxed_order;  ...  found: 10

But look at the last column: the exact search with the filter took 2.3 ms, five times faster than the iterative scan. EXPLAIN shows why. For cs.IR the planner walks the HNSW index and filters afterwards. For cs.DB (91 papers) it chooses a different plan by itself:

EXPLAIN (COSTS OFF), the main lines of the two plans

 cs.IR:  ->  Index Scan using paper_embeddings_minilm_v1_hnsw on paper_embeddings e
               ->  Index Scan using papers_pkey on papers p
                     Filter: (primary_category = 'cs.IR'::text)

 cs.DB:  ->  Sort
               Sort Key: ((e.embedding <=> (InitPlan 1).col1))
               ->  Nested Loop
                     ->  Bitmap Heap Scan on papers p
                           Recheck Cond: (primary_category = 'cs.DB'::text)

With cs.DB, PostgreSQL reads the 91 papers through the B-tree index on the category, and sorts their distances: an exact search over a small set. Every cs.DB row of the bench had recall 1.000 and no short result. This is the strength of keeping vectors and metadata in one database: the planner can choose. Daniel keeps relaxed_order as the safety net, so a filter never returns too few papers. For rare categories he notes a faster path for later: force the exact search below a size that he measures.

Part 6: a better model, switched safely

A newer small model, BAAI/bge-small-en-v1.5 (MIT), also gives 384 numbers, and its card says it is better at search. bge also asks for an instruction in front of a short query: Represent this sentence for searching relevant passages: . That instruction is part of the recipe, so it goes into the version row. Hana’s third requirement decides the order of the work:

The safe order: the new version is complete, measured and indexed before one UPDATE makes it current. Searches never wait and never mix two models.

Text description of the diagram

Step 1: migration 004 adds the version bge-small-v1, not current. Step 2: the embed script makes 15,469 bge vectors in batches, in 316 seconds. Step 3: the evaluation compares the models on the same 500 titles: hit@10 goes from 0.968 to 0.984. Step 4: migration 005 builds the HNSW index for bge concurrently, in 1.3 seconds. Step 5: set_current checks that the version is ready, then runs one UPDATE. Step 6: MiniLM stays for a rollback and is deleted later. During steps 1 to 4, searches use MiniLM; from step 5, they use bge. A switch before step 4 is refused. A bge query vector searched against MiniLM vectors gives no error and hit@10 of 0.180.

1. Add the version, not current:

   migrations/004_bge_small_version.sql

   -- A second version: BAAI/bge-small-en-v1.5 (MIT), also 384 numbers. It is not current yet.
   -- bge asks for an instruction in front of a short query that looks for passages.
   INSERT INTO embedding_versions (version, model, revision, dimensions, query_prefix, is_current)
   VALUES ('bge-small-v1', 'BAAI/bge-small-en-v1.5', '5c38ec7c405ec4b44b94cc5a9bb96e735b38267a', 384,
           'Represent this sentence for searching relevant passages: ', false);

   Run python -m paper_search.migrate.

2. Backfill: python -m scripts.embed --version bge-small-v1. It downloads the model (133 MB) and makes the vectors in committed batches, while searches keep using MiniLM.

   Embedded 15,469 papers with BAAI/bge-small-en-v1.5 in 316 s (49 per second; loading the model took 2.8 s).

3. Compare. First, the mistake that the version column prevents: bge question vectors searched against MiniLM vectors. Both have 384 numbers, so nothing stops it:

   python -m scripts.evaluate --mix bge-small-v1:minilm-v1

   method                                          hit@1  hit@10  MRR@10  ms/query   (500 titles, seed 7)
   bge-small-v1 queries against minilm-v1 vectors  0.060  0.180   0.093     11.27

   Then the fair comparison, every method on the same titles: python -m scripts.evaluate.

   method                      hit@1  hit@10  MRR@10  ms/query   (500 titles, seed 7)
   keyword (full-text search)  0.364  0.734   0.482     56.13
   minilm-v1                   0.846  0.968   0.890     10.77
   bge-small-v1                0.906  0.984   0.936      12.0

4. Copy scripts/set_current.py from the finished project, and try to switch now: python -m scripts.set_current bge-small-v1.

   Not switched. bge-small-v1 is not ready:
   - No valid HNSW index covers bge-small-v1: every search would read every vector. Build it first, with a migration (CREATE INDEX CONCURRENTLY).

   Write migrations/005_bge_small_hnsw.sql, the same as 003 with bge_small_v1 in the name and 'bge-small-v1' in the WHERE. Apply it (1.3 s).

5. Switch: python -m scripts.set_current bge-small-v1.

   The current version is now bge-small-v1 (index paper_embeddings_bge_small_v1_hnsw). minilm-v1 and its vectors are kept: to go back, run this command with minilm-v1.

hit@10 on the same 500 titles: the share whose own paper is in the first ten results. The last bar is the mistake that the version column prevents.

Text description of the chart

hit@10 on 500 titles: keyword full-text search 0.73, MiniLM 0.97 and bge 0.98. bge question vectors against MiniLM vectors: 0.18.

What this tells us. The mixed search ran without an error and found the right paper in only 18% of the cases, against 97% and 98% for each model on its own vectors. Only the version column and the one-current rule stop it. bge wins on every measure: 0.906 against 0.846 in first place, and MRR@10 0.936 against 0.890. The gain is modest, and it costs a model twice as slow to run (49 against 105 abstracts per second) and a second 30 MB index. Hana accepts that cost for better first results. set_current checks two things before its one UPDATE: every paper has an up-to-date bge vector, and a valid HNSW index covers the version. A switch without the index would have made every search an exact one.

The final test, once. The 500 titles of seed 7 helped choose the model, so they flatter it a little. A fresh set, seed 8, checked the chosen model once: python -m scripts.evaluate --only bge-small-v1 --seed 8 --misses 3.

method        hit@1  hit@10  MRR@10  ms/query   (500 titles, seed 8)
bge-small-v1  0.896  0.966   0.922     12.29
missed 2310.20707: "What's In My Big Data?"
    first result 2402.11001: 'idwMapper: An interactive and data-driven web mapping framework for vi'
missed 2312.01629: 'CLAMP: Contrastive LAnguage Model Prompt-tuning'
    first result 2211.04118: 'ConsPrompt: Exploiting Contrastive Samples for Fewshot Prompt Learning'
missed 2401.08572: 'The illusion of artificial inclusion'
    first result 2403.18588: 'From Virtual Reality to the Emerging Discipline of Perception Engineer'

What this tells us. On new titles, bge finds 96.6% in the first ten, close to the 98.4% of the choice set. The misses are honest failures of the task: a witty title (“What’s In My Big Data?”) says little about its abstract, and an acronym title (CLAMP) lands on another prompt-tuning paper. Users with real questions will meet the same limits. The bench on bge gave the same picture as MiniLM: exact 12.0 ms, index 1.5 ms at recall 0.998.

Part 7: removal that reaches every vector

2402.05035 is a withdrawn submission. Hana decides to remove it. Search for its title first, with python -m scripts.search "A Survey on Domain Generalization for Medical Image Analysis" -k 3:

0.095  2402.05035   cs.CV    A Survey on Domain Generalization for Medical Image Analysis
0.104  2403.15647   cs.CV    RetiGen: A Framework for Generalized Retinal Diagnosis Using Multi-View Fundus I
0.176  2401.08865   cs.CV    The Effect of Intrinsic Dataset Properties on Generalization: Unraveling Learnin

Create the list of removals. It keeps only the ID and the reason, no title and no abstract:

migrations/006_removals.sql

CREATE TABLE removals (
    arxiv_id   text PRIMARY KEY,
    removed_at timestamptz NOT NULL DEFAULT now(),
    reason     text NOT NULL CHECK (reason <> '')
);

Once this table exists, the starter’s loader skips every ID in it. Now write scripts/remove_paper.py (the finished project has the whole file). Its heart is one transaction:

scripts/remove_paper.py (the core)

with connect() as conn, conn.transaction():
    vectors = conn.execute("SELECT count(*) FROM paper_embeddings WHERE arxiv_id = %s", (args.arxiv_id,)).fetchone()[0]
    deleted = conn.execute("DELETE FROM papers WHERE arxiv_id = %s RETURNING title", (args.arxiv_id,)).fetchone()
    if deleted is None:
        print(f"No paper {args.arxiv_id}. Nothing changed.", file=sys.stderr)
        return 1
    left = conn.execute("SELECT count(*) FROM paper_embeddings WHERE arxiv_id = %s", (args.arxiv_id,)).fetchone()[0]
    conn.execute("INSERT INTO removals (arxiv_id, reason) VALUES (%s, %s)", (args.arxiv_id, args.reason))
print(f"Removed {args.arxiv_id} ({deleted[0][:60]}): 1 paper, {vectors} vectors ({left} left).")

python -m scripts.remove_paper 2402.05035 --reason "withdrawn by the authors (arXiv comment)"

Removed 2402.05035 (A Survey on Domain Generalization for Medical Image Analysis): 1 paper, 2 vectors (0 left).

One transaction removes the paper and, through the foreign key, every vector of it. The removals list stops a reload, and the scan checks what a bug or a restore could still leave behind.

Text description of the diagram

The remove_paper script runs one transaction: it records the ID 2402.05035 and the reason in removals, and deletes the paper. ON DELETE CASCADE deletes its vectors of both versions: 2 vectors, 0 left. The HNSW index entries stay until VACUUM, as 2 dead rows, but no search sees them. The loader skips IDs in removals, so a reload never brings the paper back. The scan has five checks. After 300 new papers and a constructed failure, it reported 1 orphan vector, 1 removed but searchable, 1 stale and 300 missing. 15,468 vectors were of other versions. The fix option and the embed script bring the first four to 0.

The same search no longer returns the paper; RetiGen is now first. The paper had two vectors, one per version, and the foreign key deleted both. Even the old MiniLM vector is gone, so a rollback cannot bring it back into use. What about the index? HNSW entries are not removed at once. pg_stat_user_tables showed 2 dead rows in paper_embeddings. No search can see them, because PostgreSQL checks each row’s visibility. VACUUM paper_embeddings; (or autovacuum, a little later) removed them: 0 dead rows. A second removal prints No paper 2402.05035. Nothing changed.

The scan. A removal that works today does not prove that nothing is left after a bug, a restore or a new load. Copy scripts/scan.py from the finished project. It counts five things, each with one query:

scripts/scan.py (the checks, in words)

CHECKS = {
    "orphan vectors": "... a vector whose paper does not exist",
    "removed but searchable": "... a vector of a paper in removals",
    "stale vectors": "... current version, source_sha256 <> sha256 of the abstract now",
    "missing vectors": "... a paper with no vector of the current version",
    "other versions": "... vectors of versions that are not current",
}

Now load the 300 April papers, and break things on purpose. Constructed: an edited abstract for 2402.02616, as a correction would do, and an orphan vector for the removed paper, inserted with SET session_replication_role = replica. That setting switches off foreign-key checks, as a data-only restore with --disable-triggers does.

python -m scripts.load --updates data/arxiv-cs-2024-04-new.jsonl.gz
python -m scripts.scan

Updates from arxiv-cs-2024-04-new.jsonl.gz: 300 new papers, 0 changed abstracts, 0 other changes, 0 removed (skipped).
orphan vectors:               1
removed but searchable:       1
stale vectors:                1
missing vectors:            300
other versions:          15,468

python -m scripts.scan --fix deleted the orphan (fixed orphan vectors: 1 deleted), which was also the “removed but searchable” vector. Stale and missing vectors need the model: python -m scripts.embed made 301 bge vectors in 7 s, and the next scan showed 0 for the first four lines. The 15,468 MiniLM vectors stay as “other versions” until Hana retires that version. Finally, copy tests/test_vectors.py and tests/test_removal.py from the finished project, and run python -m pytest: 19 passed.

What this tells us. The foreign key makes the normal removal complete, in one step, for every version. The scan catches what the foreign key cannot. It finds a vector made from an old text, a paper without a vector, and rows that slipped in with the checks off. The 300 new papers show the most common case. A load without an embedding run leaves papers that no search can find, and no error says so.

The final search, end to end

A researcher types a new question, filtered to information retrieval:

python -m scripts.search "approximate nearest neighbour search with metadata filters" --category cs.IR

Version bge-small-v1 (BAAI/bge-small-en-v1.5); embed 32 ms, search 11.9 ms
0.188  2402.04713   cs.IR    Theoretical and Empirical Analysis of Adaptive Entry Point Selection for Graph-b
0.203  2403.04871   cs.IR    ACORN: Performant and Predicate-Agnostic Search Over Vector Embeddings and Struc
0.209  2402.11855   cs.IR    TriSampler: A Better Negative Sampling Principle for Dense Retrieval
0.213  2302.14534   cs.IR    Spacerini: Plug-and-play Search Engines with Pyserini and Hugging Face
0.214  2305.04359   cs.IR    ParlayANN: Scalable and Deterministic Parallel Graph-Based Approximate Nearest N

What this tells us. The whole path works. The current version’s model and instruction embed the question. The HNSW index of that version finds candidates, and the iterative scan keeps the filter full. ACORN, the second result, is a paper about exactly this problem: vector search with filters on structured data. Embedding the question took 32 ms, more than the database search. The search time includes the iterative scan, which Part 5 measured at about 11 ms on this data.

Insights and limits

• Vectors beat keywords on this task. On the same 500 titles, hit@10 was 0.73 for full-text search, 0.97 for MiniLM and 0.98 for bge. On 500 new titles, bge reached 0.966. The task is a stand-in: a title is a short, clean question. Real questions are messier.
• The index is the easy part. At 15,469 vectors, HNSW gave 7 times faster searches at recall 0.999 with its default settings. The surprises came from filters, settings and versions, not from the index.
• Filters need a decision per value. A 2% category made 198 of 200 searches short without the iterative scan. A 0.6% category was handled best by the planner’s exact plan. Measure the rare values of every filter.
• A version switch is a data migration. Backfill, compare, index, then one UPDATE, with the old version kept for a rollback. The 384-number column fixes the size: a model with 768 numbers needs a new column or table, and its own migration.
• Limits. Abstracts only, English only, one two-month sample of one archive’s computer science, two small models, and timings from one busy laptop. The relevance test uses titles, not real users’ questions.

Responsible use

• Metadata, not papers. arXiv’s CC0 covers titles, abstracts, authors and categories. It does not cover the PDFs, which have their own licences, and some do not allow redistribution. Link to the paper on arXiv; do not copy it. Do not present the project as endorsed by arXiv.
• Real people. Author names are real and public, but this collection is for finding papers, not for profiling people. Do not rank, score or cluster authors with it.
• Removals. A withdrawn paper should leave the search everywhere: the vectors of every version, the indexes after vacuum, and any copy such as a cache, an export or a backup. The removals list must travel with the data, or a restore can bring the paper back, as the backup drill of Recovery and retention showed.
• Similar is not good. A close vector means “about the same topic”, not “correct” or “important”. Show results as suggestions, and keep the keyword search for exact titles and names.
• Bias. Both models were trained mostly on English web text. Papers with unusual wording, other languages or heavy formulas are found less well.

Make it a portfolio piece

This project shows what a team needs from vector search in production. It has a measured baseline and an index tuned on recall and time. Its filters do not fail quietly, its model change is safe, and its deletion reaches every copy.

What to put in the repository:

paper-search/
  README.md               what it does, the results table, how to run and check it
  DATASET.md              source, licence (CC0 metadata), sample, limits
  LICENSE                 MIT, for the code
  compose.yaml            PostgreSQL 18 with pgvector 0.8.7
  requirements-lock.txt   the exact versions
  migrations/             001 to 006: papers, versions, indexes, removals
  paper_search/           search.py, versions.py, embedding.py, migrate.py
  scripts/                get_data, load, embed, evaluate, bench, search, set_current, remove_paper, scan
  tests/                  19 tests, with made-up vectors: no model needed

Keep data/ out of Git; get_data.py downloads and checks the files. Run python -m pytest from a fresh clone before you publish, and say in the README that it passes.

README.md (outline)

# paper-search

## What it is
Related-papers search over 15,469 arXiv computer-science abstracts (CC0 metadata),
in PostgreSQL with pgvector, on a laptop CPU.

## Results
- Same 500 titles, hit@10: keyword 0.734, MiniLM 0.968, bge-small 0.984 (0.966 on 500 new titles).
- Exact search 12.1 ms; HNSW (ef_search 40) 1.7 ms at recall@10 0.999.
- Filter on a 2% category: 198 of 200 searches short without the iterative scan, 0 with it.

## Design choices
- Papers are the original; vectors carry their version and the SHA-256 of their text.
- One current version; a switch is checked (coverage, index) and is one UPDATE.
- Removals: one transaction, ON DELETE CASCADE, a removals list; a scan for leftovers.

## Run it / Check it
## Data and limits

How to describe it in an interview, in two sentences: “I built a related-papers search over 15,000 real arXiv abstracts in PostgreSQL with pgvector; on a fixed test it finds the right paper in the top ten 98% of the time, against 73% for full-text search. I tuned HNSW against exact search, fixed a filter that came back short 198 times in 200, switched models with a checked cut-over, and made removals reach every vector.”

Go further:

• Retire MiniLM. A migration that drops its index and deletes its 15,468 vectors. Measure the database before and after (190 MB at the end of this case study).
• The exact path for rare filters. In search, count the papers of the category first, and use the exact search below a threshold that you measure.
• Hybrid search. Combine the full-text and the vector ranks (reciprocal rank fusion), and measure it with the same 500 titles.
• Half the storage. pgvector’s halfvec type stores 16-bit numbers. Measure the size, the recall and the time.

Knowledge check

Check your understanding

The bench showed recall 1.000 and the same time for the exact search and every index setting. Name two possible causes, and how you check each.

Show the answer

First, the index is not used. A setting such as enable_indexscan = off may still apply, for example a SET LOCAL inside a savepoint, which lasts to the end of the outer transaction. Or the planner prefers another plan. Check with SHOW enable_indexscan; and with EXPLAIN of the same query: the Index Scan using ..._hnsw line must be there. Second, the collection is so small or so easy that the index really finds every neighbour. Then the time is lower than the exact search, not the same. The same time with perfect recall points to the first cause.

Check your understanding

Why did the cs.DB filter give no short results, while cs.IR gave 198 of 200 short searches? What does Daniel's search do for both?

Show the answer

For cs.DB (91 papers), the planner estimated few rows and chose the B-tree index on primary_category. It read the 91 papers and sorted their distances: an exact search. For cs.IR (330 papers), it chose the HNSW index, which hands over its 40 nearest candidates before the filter. Near most papers, few candidates are in cs.IR, so the lists came back short. Daniel’s search turns on the iterative scan (relaxed_order). The index then keeps searching until k rows pass the filter, and the code sorts the rows again. Whatever plan PostgreSQL chooses, the result is full.

Check your understanding

A colleague restores last week's backup of paper_embeddings only, after the removal of 2402.05035. What goes wrong, and how does the project notice it?

Show the answer

The backup still has the two vectors of 2402.05035. A data-only restore with disabled triggers skips the foreign key, so they come back without their paper. The search joins papers, so it does not show them. But any code that reads only the vectors would show them, and they take candidate places in the index. scripts/scan.py reports them as orphan vectors and as removed but searchable, and --fix deletes them. The removals list is what lets the scan, and the loader, know that the paper must stay gone.

Recap

• The papers are the original; each vector records its embedding version and the SHA-256 of its text, so mixing and staleness are visible.
• On 500 titles, vectors beat full-text search (hit@10 0.97 and 0.98 against 0.73). One fixed test, the same for every method, made the choice fair; a second set checked it once (0.966).
• HNSW at the default ef_search 40 was about 7 times faster than the exact search, at recall 0.999. A benchmark that looks perfect needs a check of its own settings.
• A filter after the index returned short lists (198 of 200); the iterative scan fixed it, and for a very rare category the planner chose an exact plan by itself.
• A model switch is safe in order: add, backfill, compare, index, then one checked UPDATE; keep the old version for a rollback.
• A removal is one transaction with ON DELETE CASCADE and a removals list; a scan finds orphan, stale and missing vectors after failures.

You built a vector search that a library could run and trust. The final assignment asks you to design storage for tickets, documents and AI runs. It needs a measured index, and a plan for backup, restore and retention.

Next: Final assignment