Vector storage

What you will be able to do

Keep the ticket vectors in PostgreSQL so that a search never mixes two models. Find similar tickets fast with an approximate index, and still return enough results when the search filters by team.

Before you startWhat to know and to have readyAbout 55 minutes

What you need to know

• What a ticket vector is, and how <=> measures the distance between two vectors, from From SQLite to PostgreSQL. That a vector is a copy made from the ticket text, from Ownership and source of truth.
• Query plans and indexes, from Read a query plan and Index deliberately.
• Optional: the RAG course explains embeddings in Embeddings for search, and recall in Retrieval measures. This lesson explains the parts that it uses.

What you need on your computer

• The project after Object-storage patterns. python -m pytest prints 95 passed, 1 deselected.
• Optional: the embedding model of the first module (requirements-embed.txt, about 1 GB with the model), for a second embedding version. Without it, you can do every other step; the lesson shows its output.

This lesson adds two migrations, 010_embedding_versions.sql and 011_ticket_embeddings_hnsw.sql, and a test file. The similar-ticket endpoint that you copied in the last lesson starts to work.

Two maps of one city

You have two maps of the same city. On the first, the station is at square C4. On the second map, drawn by another company, C4 is a lake. Both maps are correct. But a square from one map means nothing on the other, and nothing on the paper warns you.

A vector is a position on a map of meaning. Each embedding model draws its own map.

The problem: two models, no error

In Ownership and source of truth, the ticket vectors came from intfloat/multilingual-e5-small. Priya wants to try a second model, sentence-transformers/all-MiniLM-L6-v2. It also makes 384 numbers per ticket. So the database accepts its vectors in the same vector(384) column.

The course made MiniLM vectors for the 200 tickets next to the e5 vectors. Then it asked for the tickets nearest to T-30002, “Charged twice for order LK-182074”. First with e5 vectors on both sides, then with T-30002’s e5 vector against the MiniLM vectors:

 ticket_id |              subject              | distance
-----------+-----------------------------------+----------
 T-30050   | Charged twice for order LK-710804 |    0.025
 T-30168   | Charged twice for order LK-919420 |    0.028
 T-30064   | Charged twice for order LK-167846 |    0.034
(3 rows)

 ticket_id |           subject           | distance
-----------+-----------------------------+----------
 T-30011   | Gift card balance           |    0.874
 T-30105   | Discount code did not apply |    0.895
 T-30153   | Discount code did not apply |    0.895
(3 rows)

The second query ran without an error and returned confident nonsense. On the large data, an e5 query against MiniLM vectors found 0.4% of the right neighbours. The database cannot see the problem: two lists of 384 numbers look alike. Even MiniLM against MiniLM agrees with e5 on only 60% of the neighbours, because each model has its own idea of “similar”.

The idea: every vector names its version

An embedding version is a name for one recipe: one model, one revision, one prefix. Migration 010 makes it a rule of the database:

• A table embedding_versions lists each recipe, and marks exactly one as current. A partial index covers only the rows that match its WHERE condition. Here, a unique partial index on the rows WHERE is_current allows only one true.
• Every vector row gets embedding_version, a foreign key to that table. The key of ticket_embeddings becomes (ticket_id, embedding_version), so a ticket can have one vector per version.
• Every vector row also gets source_sha256: the SHA-256 checksum of the ticket text that it was made from. If somebody edits the ticket later, the checksum no longer matches. The vector is then stale: a copy of an old text.

The search then follows one rule: use the current version on both sides. Here is T-30002 with both versions:

One ticket, two vectors, two versions. The version column keeps them apart, and the checksum shows when the ticket text has changed since the vector was made.

Text description of the diagram

The ticket T-30002 is the original, in the tickets table. The SHA-256 checksum of its subject and body starts with bf5d3388. The ticket_embeddings table has the key ticket_id plus embedding_version, and two rows for T-30002. The first is version e5-small-v1, with the checksum bf5d3388 and the numbers 0.0501, 0.0100, -0.0195 and 381 more. The second is version minilm-v1, with the same checksum and 384 other numbers that mean something else. The partial HNSW index holds only the e5-small-v1 rows. The embedding_versions table marks e5-small-v1 as current and minilm-v1 as not current. Exactly one version is current, and every search uses it on both sides. Mixing them gives no error and 0.4% of the right neighbours.

Sam asks: “Why keep the MiniLM vectors at all?” To compare the two models on real searches before a switch. When Priya decides, one UPDATE makes MiniLM current, and the old vectors can wait until nobody needs them. The scan in the next lesson counts them as “vectors of other versions”.

Exact or approximate search

Now the help desk has 10,000 ticket vectors on the large data, and the agents want similar tickets while they type.

Think of looking for the ten cafés nearest to your hotel. The exact way: measure the distance to every café in the city, and sort. You are sure of the answer, but it takes long. The quick way: ask a few well-connected locals, who send you to their neighbours, who send you on. You get there fast, and you may miss a café in a quiet street.

PostgreSQL with the pgvector extension can do both. Without an index, ORDER BY embedding <=> query LIMIT 10 is an exact search: it computes the distance to every vector. With an HNSW index (Hierarchical Navigable Small World), it is an approximate search. The index is a graph of neighbours in layers. A search enters at the top layer, moves to closer and closer vectors, and keeps a list of the best candidates. The setting hnsw.ef_search (40 by default) says how long that list is. A longer list finds more of the true neighbours, and takes more time.

How good is “approximate”? Recall@10 is the share of the true ten nearest neighbours that the approximate search returned. Here is a made-up example. The exact search returns A, B, C, D, E, F, G, H, I and J. The index returns A, B, C, D, E, F, G, K, L and M. Seven of ten are right, so recall@10 is 0.7.

The course’s tickets add one twist. Many made-up tickets share a text, for example many “Charged twice” tickets: 10,000 vectors, only 5,484 different texts. If K has exactly the same distance as H, then K is just as good an answer. So the course reports recall counting ties as well: an answer counts if it is as close as the exact tenth result. Strict recall at the default setting was 0.70; counting ties, it was 0.96.

Migration 011 builds the index. On the large data it took 0.36 s and 12 MB, next to a table of 35 MB. Here are 200 searches on 10,000 vectors, each run both ways:

• HNSW index
• exact search

Each dot is the median of 200 searches on 10,000 vectors. The time axis is a log scale: each step to the right is about three times slower. The HNSW index is 20 to 45 times faster than the exact search, and finds 82 to 98% of the neighbours, counting ties.

Text description of the chart

The chart plots recall@10, counting ties, against the median time per search, for 200 searches on 10,000 vectors. The HNSW index with ef_search 10 took 0.15 ms and found 0.82 of the neighbours. 69 of its 200 searches returned fewer than 10 tickets. With ef_search 40, the default, it took 0.22 ms and found 96% of them. With ef_search 100, it took 0.33 ms and found 97.5% of them. The exact search took 6.6 ms and found all of them, by definition.

The default, ef_search 40, is the sweet spot here: 0.22 ms instead of 6.6 ms, and 96% of the neighbours. Going to 100 costs half as much time again and adds 1.5 points of recall. Going down to 10 saves little and loses a lot: 69 of 200 searches returned fewer than 10 tickets.

Larkfield’s search, with a filter

Amira’s endpoint, GET /v1/tickets/{id}/similar, has filters: team and status. An agent of the login team asks for login tickets only. The large data has 1,046 login vectors among 10,000. The course measured the same 200 searches with the filter team = 'login', and the result was a shock. 176 of 200 searches returned fewer than 10 tickets. Recall, counting ties, fell to 0.14.

The reason is the order of the work. The index does not know about teams. It hands over its 40 best candidates, and only then does PostgreSQL apply the filter. You can see it in psql on the large data, with T-399991, a billing ticket about a double charge:

The filter comes after the index. Without the iterative scan, all 39 candidates were billing tickets, and the login filter kept none. With it, PostgreSQL keeps reading the index until 10 rows pass.

Text description of the diagram

The query asks for 10 login tickets similar to T-399991, a billing ticket. Without the iterative scan, the HNSW index hands over its 40 nearest vectors. They were 39 candidates, all billing tickets. The filter team = login keeps none, so the search returns 0 results. Over 200 searches with this filter, 176 came back short. With the iterative scan, set to relaxed_order, PostgreSQL reads the index again, with more candidates each round, until 10 rows pass the filter. The search returns 10 results. Over 200 searches, 5 came back short. It costs 9.1 ms per search, near the exact search at 12.5 ms.

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

pgvector 0.8 added the fix, the iterative scan. With relaxed_order, the index keeps searching until enough rows pass the filter. Over the 200 searches, 5 came back short, and recall rose to 0.957. It is not free: 9.1 ms per search, close to the exact search with the same filter (12.5 ms). A rare filter forces the index to read much of the graph. repository.similar_tickets sets relaxed_order for each search, and sorts the k rows again, because “relaxed” allows a slightly wrong order.

Of 200 searches with the filter team = login (1,046 of 10,000 vectors), how many returned fewer than 10 tickets.

Text description of the chart

Of 200 searches with the filter team = login, 176 returned fewer than 10 tickets when the HNSW index ran alone. With the iterative scan, 5 did. The exact search returns 10 whenever 10 login tickets exist, so 0 came back short.

Why the version is written into the SQL

The index of migration 011 is a partial index too: WHERE embedding_version = 'e5-small-v1'. PostgreSQL can use it only if it sees, while it plans the query, that the query asks for that version. psycopg prepares a statement that runs often, and after 5 runs PostgreSQL may switch to a generic plan, made without the values of the parameters. The course forced that plan on the large data to show the difference:

SET plan_cache_mode = force_custom_plan;   ->  Index Scan using ticket_embeddings_e5_small_v1_hnsw on ticket_embeddings e
SET plan_cache_mode = force_generic_plan;  ->  Seq Scan on ticket_embeddings e
                                                 Filter: (embedding_version = $1)

With $1, PostgreSQL cannot prove that the partial index covers the rows, so the generic plan scans and sorts every vector. So similar_tickets writes the version into the SQL text with psycopg.sql.Literal. That is safe here, for two reasons: psycopg quotes the value, and the value comes from the embedding_versions table, never from the request. Every value that a user sends, such as team, stays a parameter, as Safe queries taught.

pgvector or a dedicated vector database

Tomás repeats his idea from the first module: “Let’s move the vectors to a vector database. It has metadata too, so we can put the ticket text and status there, and stop using PostgreSQL for search.”

Mei answers with what this lesson measured. A dedicated vector database is built for one job, and it can be the right tool. Use one when you have tens of millions of vectors, or when search load must grow apart from the main database. But it is a second copy, and every copy has a price:

• Filters need the metadata in the same place. The team filter above works because the vectors and tickets.team are in one database, in one query. In a separate system, you copy the team, the status and the customer to every vector, and keep them in step on every change.
• Deletion must reach it. When a customer is erased, the vectors in the other system must go too, with their copied text. The next lesson shows how hard “everywhere” already is with two stores.
• It is never the original. The text stays in PostgreSQL, as the first module decided. Then any vector store can be rebuilt.

For 10,000 vectors, pgvector answered in 0.22 ms with 96% recall, inside the database that already has the filters. The help desk stays with pgvector, and Mei writes down the size at which they will look again.

Guided practice: versions, the index and the endpoint

1. Copy the two migrations and the test file from the snapshot. On macOS or Linux:

   S=../nextia-learning-labs/C20/snapshots/end-of-m05
   cp $S/migrations/010_embedding_versions.sql $S/migrations/011_ticket_embeddings_hnsw.sql migrations/
   cp $S/tests/test_vectors.py tests/

   On Windows: Copy-Item "$S\migrations\010_*.sql", "$S\migrations\011_*.sql" migrations\ and Copy-Item "$S\tests\test_vectors.py" tests\, with $S as in the last lesson.

2. Read 011_ticket_embeddings_hnsw.sql. Its first line is -- migrate: no-transaction, because CREATE INDEX CONCURRENTLY cannot run in a transaction, as in Migrations. Apply both:

   python -m ticket_api.migrate

   applied  010_embedding_versions
   applied  011_ticket_embeddings_hnsw
   Database is up to date (11 migrations).

3. Run the tests: python -m pytest. The last line is 101 passed, 1 deselected. One new test proves that the approximate and exact searches agree on the small data. Another proves that a vector of another version is never compared.

4. In psql, look at the table with \d ticket_embeddings. The key is now (ticket_id, embedding_version), and the index list ends with:

   "ticket_embeddings_e5_small_v1_hnsw" hnsw (embedding vector_cosine_ops) WHERE embedding_version = 'e5-small-v1'::text

5. Start the API with fastapi dev, and ask for the five tickets most similar to T-30002:

   curl -s 'http://127.0.0.1:8000/v1/tickets/T-30002/similar?k=5'

   {"embedding_version":"e5-small-v1","items":[{"ticket_id":"T-30050","subject":"Charged twice for order LK-710804","team":"billing","status":"pending","created_at":"2026-09-28T19:48:43Z","distance":0.025122697629444524},{"ticket_id":"T-30168","subject":"Charged twice for order LK-919420","team":"billing","status":"pending","created_at":"2026-10-05T15:19:12Z","distance":0.02812230084139522},{"ticket_id":"T-30064","subject":"Charged twice for order LK-167846","team":"billing","status":"closed","created_at":"2026-09-29T15:45:40Z","distance":0.033563429076056694},{"ticket_id":"T-30076","subject":"Charged twice for order LK-140806","team":"billing","status":"open","created_at":"2026-09-30T09:50:54Z","distance":0.03663163940528669},{"ticket_id":"T-30095","subject":"Charged twice for order LK-539653","team":"billing","status":"resolved","created_at":"2026-10-01T11:09:23Z","distance":0.04021954056983024}]}

   All five are other “Charged twice” tickets, closest first. On Windows, type curl.exe instead of curl.

6. Add a filter that the double charge does not match: curl -s 'http://127.0.0.1:8000/v1/tickets/T-30002/similar?k=3&team=login'. You still get three tickets: T-30192, T-30065 and T-30025, all “Locked out of my account”, at distances 0.109 to 0.120. They are far away, but they are the closest login tickets.

7. Optional, with the model. If you installed requirements-embed.txt in the first module, make vectors with MiniLM as a second version:

   python -m scripts.embed --version minilm-v1

   Embedded 200 tickets with sentence-transformers/all-MiniLM-L6-v2 in 3.2 s.
   Stored 200 vectors of version minilm-v1.

   In psql, run SELECT version, model, is_current FROM embedding_versions; and SELECT embedding_version, count(*) FROM ticket_embeddings GROUP BY 1;. You see two versions, 200 vectors each, and only e5-small-v1 is current. The endpoint still answers with "embedding_version":"e5-small-v1". Keep these vectors for the next lesson.

Common mistake

A filtered search returns fewer results than it asked for

Symptom: similar?k=10&team=login returns 3 tickets, or none, although the team has hundreds of tickets. There is no error.

Cause: the approximate index hands over a fixed number of candidates (hnsw.ef_search, 40 by default). The filter runs after that. If the nearest candidates belong to other teams, the filter removes them all. On the large data, 176 of 200 login searches came back short in this way.

Diagnose: run the query in psql with EXPLAIN. The plan shows Index Scan using ticket_embeddings_e5_small_v1_hnsw, with Filter: (team = 'login'::text) on the join. Count the results with SET hnsw.iterative_scan = off; and again with relaxed_order. For T-399991 on the large data: 0, then 10.

Fix: turn on the iterative scan for the search, as similar_tickets does with SET LOCAL hnsw.iterative_scan = relaxed_order. It needs pgvector 0.8 or newer. For a filter that is always very rare, the exact search can be just as fast. A partial index per team is the other option, if the teams are few and fixed.

Independent variation: see it on the large data

This step takes about a minute, and it replaces your small data for a while. Load the large data with python -m scripts.load --size large --reset. In psql, count the login tickets that the index finds for T-399991, first with the iterative scan off:

SET hnsw.iterative_scan = off;
SELECT count(*) AS found FROM (
  SELECT e.ticket_id FROM ticket_embeddings e JOIN tickets t USING (ticket_id)
  WHERE e.embedding_version = 'e5-small-v1' AND t.team = 'login'
  ORDER BY e.embedding <=> (SELECT embedding FROM ticket_embeddings WHERE ticket_id = 'T-399991')
  LIMIT 10) AS s;

Then predict the count with SET hnsw.iterative_scan = relaxed_order;, and run it. Finally, predict what SET hnsw.ef_search = 400; with the scan off would do, and try it. Load the small data again with python -m scripts.load --reset when you finish. The load also deletes the MiniLM vectors of step 7. If you made them, run step 7 again: the next lesson uses them.

Show the answer

With the scan off, found is 0; with relaxed_order, it is 10. With the scan off and ef_search = 400, the course’s run also found 10: a list ten times longer reached far enough to include login tickets. But the session then builds that longer list for every search, filtered or not. A rarer filter would need an even longer one. The iterative scan does the extra work only when the filter needs it. That is why the project changes the scan, not ef_search.

When to use an approximate index, and what it costs

Use the exact search while it is fast enough. With 200 vectors, it costs nothing to notice, and it is always right. Add an HNSW index when the exact search becomes too slow for the person waiting. For Larkfield, that is at 10,000 vectors, 6.6 ms, many times per page.

The index has costs that the chart does not show:

• Recall below 1. Some true neighbours are missed. Measure recall on your own data, with ties, before you trust a setting.
• Build time and memory. 0.36 s and 12 MB for 10,000 vectors. Both grow with the data, and every insert updates the graph.
• Filters need care. Turn on the iterative scan, and test the rare filters.
• One index per version. A partial index covers one embedding version. A new current version needs its own index before the switch, or its searches are exact.

See it end to endVector search over an open document collectionCase study

The arXiv case study stores 15,469 abstracts as versioned vectors and compares exact and HNSW search. It also fixes a filter that returns too few papers.

Knowledge check

Check your understanding

A colleague stores the MiniLM vectors in the same table without a version column. Searches still run. What goes wrong, and which two parts of migration 010 prevent it?

Show the answer

A search can compare an e5 query vector with MiniLM vectors. Both have 384 numbers, so PostgreSQL gives no error, but the result is nonsense: 0.4% of the right neighbours on the large data. Migration 010 gives every vector an embedding_version (a foreign key to embedding_versions). It also makes exactly one version current, with a unique partial index on is_current. The search then uses the current version on both sides.

Check your understanding

Explain recall@10 with the course's numbers, and why the course also reports it counting ties.

Show the answer

Recall@10 is the share of the exact ten nearest neighbours that the approximate search returned. With the HNSW index at ef_search 40, strict recall was 0.70. Many made-up tickets share the same text: 5,484 texts for 10,000 vectors. So several tickets are exactly as close as the tenth exact result, and any of them is an equally good answer. Counting those ties, recall was 0.96, at 0.22 ms per search instead of 6.6 ms.

Check your understanding

Predict: the version in similar_tickets is changed to a normal parameter (%s). The tests pass. What happens on the large data after the endpoint has run a few times, and why?

Show the answer

After 5 runs of the prepared statement, PostgreSQL may choose a generic plan, made without the version’s value. It cannot then prove that the partial index (WHERE embedding_version = 'e5-small-v1') covers the rows. So it scans and sorts every vector, the plan that the course forced with force_generic_plan. The answers stay correct, but each search takes about the time of an exact search. The small data hides it, because a scan of 200 rows is fast anyway.

Recap

• Vectors of two models look alike and mix without an error: 0.4% of the right neighbours. Every vector now has an embedding version, and exactly one version is current.
• source_sha256 records the text that each vector was made from, so a changed ticket shows up as a stale vector.
• Exact search reads every vector (6.6 ms for 10,000). The HNSW index is approximate: 0.22 ms and recall 0.96 counting ties, at the default ef_search 40.
• A filter runs after the index: 176 of 200 login searches came back short. pgvector’s iterative scan fixed it (5 short) for 9.1 ms per search.
• The partial index needs the version as a literal in the SQL; a parameter can lead to a generic plan that scans every vector.
• Your project has migrations 010 and 011 and a working /similar endpoint. python -m pytest prints 101 passed, 1 deselected.

Next: Recovery and retention