# 19.14 Embeddings and pgvector in depth: predict, then run (/building-ai-workers/business-data-and-retrieval/embeddings-and-pgvector-in-depth)

---
type: Document
title: "19.14 Embeddings and pgvector in depth: predict, then run"
description: "Embeddings and pgvector in depth, predict then run: cosine by hand, what embeddings miss (codes, amounts, negation), search by words beside search by meaning, reading a plan with EXPLAIN, what a vector costs, and how to choose passages."
status: stable
order: 319.14
ksor:
  owner: team:panaversity
  audience: [ public ]
  approval:
    by: process:panaversity
    at: 2026-11-04T22:13:33Z
chapter: "19"
part: III
expert_status: co-build
concepts: [ "19.14" ]
objectives:
  - { id: CCDV-F.D6.S1, label: supporting }
last_verified: 2026-11-02
generated:
  at: 2026-11-04T22:13:33Z
  by: process:panaversity
trust_tier: unverified
build_id: sha256:e1fbcb7d9a8f8b9b7cb96875022325c02c9ba4be94cef13b301cbd39a9edad85
dirty: true
ksor_version: 0.0.60
---

*About twenty-five minutes. Read [19.3](reading-sql.md) and [19.13](retrieval-from-zero.md) first. This concept opens up the search [19.13](retrieval-from-zero.md) built, with the same method as [19.3](reading-sql.md): you predict, then run, and the gap between the two is the lesson. It shows what embeddings do well, what they get wrong, how to see what the database really does, and what each choice costs.*

**In everyday life.** A good librarian finds "something about getting the van fixed cheaply" without a single matching word. Ask the same librarian for "call number 629.287" and she walks to the catalog, because a call number is not a meaning. It is an exact code. A good library uses both: the person who understands what you mean, and the catalog that matches exactly what you typed.

**What you will learn.** How a search by meaning behaves when you run it: cosine distance by hand, what embeddings miss, search by words beside search by meaning, and what a vector costs.

**Why it matters.** A search returns candidates, not answers. Knowing where it fails, with codes, names and amounts, tells you which part of the system must decide instead.

**One question, start to finish.** Luis asks two questions a minute apart: "Do we need a PO for a $400 repair?" and "Do we need a PO for a $4,000 repair?" The search returns the same two purchasing rules, in the same order, with the same scores. Line 5 says a purchase order is required above $500.00. Line 6 says $500.00 or less may be paid without one. The two rules give opposite answers, and the search cannot tell which applies: that depends on the amount, and the amount is the one thing the search did not weigh. The answer is decided by reading the cited rule against the number, which is the model's job, checked by the answer check ([19.16](measuring-retrieval.md)). Where an amount decides whether a payment needs approval, the connector's code compares the numbers itself, as `get_purchase_order` does with `needs_approval` ([19.11](each-companys-own-rules-and-data.md)). Search finds the rule. It never does the arithmetic.

### What does an embeddings model learn?

An embeddings model is trained on a very large amount of text, so that pieces of text used in similar ways end up with similar lists of numbers. No single number means anything on its own. Only the direction of the whole list does. Three things follow:

- **The same model must embed the question and the passages.** Two models put meanings in different places, so their numbers cannot be compared ([19.16](measuring-retrieval.md)).
- **Meaning is learned from usage, not from rules.** "PO" and "purchase order" land close together because people use them the same way. An invoice number or an amount has no usage to learn from, so the model has little to go on.
- **The search returns what is nearest, not what is right.** Something is always nearest, which is why [19.15](answers-that-cite-or-abstain.md) needs a threshold and a way to abstain.

Every output below was produced by running the block, with Cedar Ridge's passages embedded by the lab's **stand-in model**, `test-lexicon-v1`. The stand-in needs no account. It matches words through a small fixed list, so it puts "PO" next to "purchase order", but it is not a real model. A real model's scores differ, and [Lab 19](lab.md), Task 13, records them. The blocks are in `concept-blocks/sql/`, numbered 7 to 14, and run the same way as [19.3](reading-sql.md)'s. **Write your prediction down first.**

### Block 7. Cosine distance by hand

Arrows with two numbers, small enough to draw on paper.

```sql
SELECT round(('[3,4]'::vector <=> '[6,8]'::vector)::numeric, 2)   AS same_direction,
       round(('[3,4]'::vector <=> '[4,3]'::vector)::numeric, 2)   AS close,
       round(('[3,4]'::vector <=> '[4,-3]'::vector)::numeric, 2)  AS at_right_angles,
       round(('[3,4]'::vector <=> '[-3,-4]'::vector)::numeric, 2) AS opposite;
```

*Predict:* `[6,8]` is twice as long as `[3,4]`. Is its distance 0, or more?

```text
same_direction | close | at_right_angles | opposite
---------------+-------+-----------------+---------
0.00           | 0.04  | 1.00            | 2.00
```

In plain words: `'[3,4]'::vector` reads the text `[3,4]` as a vector of two numbers, and `round(..., 2)` keeps two decimals. Cosine distance looks only at direction. `[6,8]` points exactly the same way as `[3,4]`, so its distance is 0, however long it is. Arrows at a right angle are 1 apart, and arrows pointing opposite ways are 2 apart, the most there is. The search's **similarity**, `1 - distance`, is therefore 1 for the same direction and 0 at a right angle.

### Block 8. The search, for real

```sql
SELECT document_id, line_start,
       round((1 - (embedding <=> $1::vector))::numeric, 2) AS similarity
  FROM policy_passages
 WHERE tenant_id = 'org_cedar' AND embedding_model = 'test-lexicon-v1'
 ORDER BY embedding <=> $1::vector
 LIMIT 3;
```

*Predict:* `$1` is the embedding of "do we need a PO for a $400 repair?". Which purchasing line comes first: line 5, which requires a purchase order, or line 6, which says a small repair needs none?

```text
document_id | line_start | similarity
------------+------------+-----------
purchasing  | 5          | 0.72
purchasing  | 6          | 0.63
approvals   | 6          | 0.42
```

In plain words: both purchasing rules come back, line 5 first, although line 6 is the one that answers a $400 question. In the stand-in's terms, both rules match three of the question's four ideas, but line 6 carries five other ideas ("less", "small", "truck", "paid", "without") that pull its arrow away from the question's. A longer passage points less exactly at any one question. This is why [19.15](answers-that-cite-or-abstain.md) returns several passages with citations, not one: the nearest passage is a candidate, not an answer.

### Block 9. Search by words

Postgres has a second kind of search built in: **full-text search**, which matches words, not meanings. `to_tsvector` turns a passage into its words, and `@@` asks whether the words of a query are among them.

```sql
SELECT document_id, line_start FROM policy_passages
 WHERE tenant_id = 'org_cedar'
   AND to_tsvector('english', content) @@ plainto_tsquery('english', 'PO');

SELECT document_id, line_start FROM policy_passages
 WHERE tenant_id = 'org_cedar'
   AND to_tsvector('english', content) @@ plainto_tsquery('english', 'blanket');
```

*Predict:* the rules say "purchase order", never "PO". What does a search for the word "PO" find? And for "blanket", which appears in one rule, purchasing line 7: "Utilities are paid against one blanket purchase order raised each January"?

```text
document_id | line_start
------------+-----------
(0 rows)

document_id | line_start
------------+-----------
purchasing  | 7
(1 row)
```

In plain words: search by words misses "PO", because the word is not there, and search by meaning found it. But search by words finds an exact word, or an exact code, every time it is there. The two searches fail in opposite places.

### Block 10. Amounts are not meaning

```sql
SELECT round((1 - ($1::vector <=> $2::vector))::numeric, 2) AS similarity_of_the_two_questions;
```

*Predict:* `$1` is "do we need a PO for a $400 repair?" and `$2` is the same question with $4,000. How similar are the two questions to the search?

```text
similarity_of_the_two_questions
-------------------------------
1.00
```

In plain words: to the stand-in, the two questions are identical, because it replaces every amount with the same word. A real model keeps some trace of numbers, but it is not built to compare them, so the two questions are still likely to look almost the same. The block's second query, in the file, shows both questions getting the same two rules with the same scores. Treat any amount, date or limit in a question as something the cited rule and the code must decide, never the search.

### What does the database really do, and what does it cost?

Three more blocks are in [F4](field-guide-business-data-and-retrieval.md#f4-embeddings-and-vectors-from-zero), for when a company grows: Block 11 reads a search's plan with `EXPLAIN` and shows that Cedar Ridge's search measures every passage exactly, Block 12 shows a plan that uses the vector index, and Block 13 shows that one embedding takes about 4 KB, more than fifty times its text. Task 12b runs them.

### Where do embeddings surprise you?

Only the surprises that cause real mistakes:

| What you might expect | What happens |
| --- | --- |
| The nearest passage answers the question | It is the nearest. Opposite rules can both be near (Block 8) |
| A longer arrow is a stronger match | Cosine ignores length. Only direction counts (Block 7) |
| The search tells $400 from $4,000 | Barely, or not at all (Block 10). Code compares amounts |
| An exact code such as PO-3103 is found by meaning | It may rank poorly, because a code has no usage to learn from. Search by words finds an exact word whenever it is there, as Block 9 shows with "blanket", and Block 14 combines the two |
| Changing a rule's text keeps its embedding right | The embedding is now stale. The fingerprint check refuses the change until it is embedded again ([19.5](third-normal-form-in-detail.md)) |
| The vector index is always used | Not at this size. `EXPLAIN` shows which (field guide [F4](field-guide-business-data-and-retrieval.md#f4-embeddings-and-vectors-from-zero), Block 11) |

### Block 14. Hybrid search: both searches, merged

Blocks 8 and 9 failed in opposite places. Search by meaning put the wrong rule first, and search by words missed "PO". **Hybrid search** runs both and merges the two lists. The usual way to merge them is **reciprocal rank fusion**: each passage gets `1 / (60 + its place)` from each list it appears in, and the two are added. A passage near the top of either list rises, and one near the top of both rises furthest. The 60 is a commonly used constant that keeps one first place from outweighing everything else.

```sql
WITH meaning AS (
  SELECT document_id, line_start,
         row_number() OVER (ORDER BY embedding <=> $1::vector) AS place
    FROM policy_passages
   WHERE tenant_id = 'org_cedar' AND embedding_model = 'test-lexicon-v1'
   ORDER BY embedding <=> $1::vector
   LIMIT 5
), words AS (
  SELECT document_id, line_start,
         row_number() OVER (ORDER BY ts_rank(to_tsvector('english', content),
                                             to_tsquery('english', 'po | repair')) DESC) AS place
    FROM policy_passages
   WHERE tenant_id = 'org_cedar'
     AND to_tsvector('english', content) @@ to_tsquery('english', 'po | repair')
   LIMIT 5
)
SELECT document_id, line_start, round(sum(1.0 / (60 + place)), 4) AS fused_score
  FROM (SELECT * FROM meaning UNION ALL SELECT * FROM words) both_lists
 GROUP BY document_id, line_start
 ORDER BY fused_score DESC
 LIMIT 3;
```

In plain words, before the output: `WITH` gives two queries names, `meaning` and `words`, so the last query can use them. `meaning` is Block 8's search for "do we need a PO for a $400 repair?", with each passage's place in its list (`row_number()`: 1, 2, 3...). `words` searches for the words "po" or "repair" (`|` means or), and `ts_rank` puts the passages with more of those words first. `UNION ALL` stacks the two lists, and `GROUP BY` adds up each passage's scores. The block also runs the two searches on their own first.

*Predict:* by meaning alone, line 5 came first (Block 8). By words, only one passage contains "repair". Which rule does the merged list put first?

```text
by meaning:  purchasing 5, purchasing 6, approvals 6
by words:    purchasing 6

document_id | line_start | fused_score
------------+------------+------------
purchasing  | 6          | 0.0325
purchasing  | 5          | 0.0164
approvals   | 6          | 0.0159
```

In plain words: line 6, the rule that answers a $400 question, is now first. It was second by meaning (`1 / 62`) and first by words (`1 / 61`), so its two scores add up to 0.0325. Line 5 appears in only one list, first by meaning, so it gets `1 / 61`, or 0.0164. The word "repair" is rare in the policies, and it is the word that mattered. One good example is not proof, though. Hybrid search can also push a wrong passage up, when a common word matches. So it goes into the connector only if the question set ([19.16](measuring-retrieval.md)) says it finds more of the right rules, and the answer check ([19.16](measuring-retrieval.md)) says the answers improved.

### When does hybrid search earn its place?

Hybrid search earns its place when people search for things that have no meaning to learn from: invoice numbers, purchase order numbers, vendor ids, product codes and names. A search by words for "PO-3103" finds it every time it is written, and meaning search may not. Postgres does both searches in one database, in one transaction, under the same row-level security, so hybrid search adds no new place for one company's data to reach another's. A word search on a large table needs its own index, `USING gin (to_tsvector('english', content))` (field guide [F4](field-guide-business-data-and-retrieval.md#f4-embeddings-and-vectors-from-zero)). The lab's `search_policy` does not use hybrid search, because Brightline's and Cedar Ridge's policies hold no codes and few exact names. Which questions matter is the AP lead's call.

### How do you choose passages?

One passage per rule works for short, numbered rules ([19.13](retrieval-from-zero.md)). Longer documents need three more decisions, each measured with the question set ([19.16](measuring-retrieval.md)): how long a passage is (long enough to hold one idea, short enough that its citation points at it), whether neighboring passages overlap by a sentence, so a rule that runs across a break is not lost, and whether each passage carries its **heading path**, such as "Purchasing > Small purchases", in its `metadata`, a fact about the passage itself that 3NF allows ([19.5](third-normal-form-in-detail.md)).

### What do you ask the agent about any search?

As in [19.3](reading-sql.md), each answer is checked against a run:

- "Show me the plan ([F4](field-guide-business-data-and-retrieval.md#f4-embeddings-and-vectors-from-zero)). Did it use the vector index, or read the company's rows exactly?"
- "Which model made these embeddings, and is it the same one that embedded the question?"
- "Would a search by words find this question's answer better? Show me both."
- "Which of these passages actually answers the question, and why is it not first?"

**At work.** In Lab 19, Task 12b, the builder runs blocks 7 to 14 with Claude Code, predicting each answer first. Then Ana and Luis write down three questions people will really ask that contain a code, a name or an amount, and the builder asks Claude Code to run each one three ways, by meaning, by words and hybrid, and record which found the answer.

**The operating rule:** read a search's results as candidates, never as answers. Keep amounts and limits for the rule and the code. Add hybrid search when people search for exact codes and names, and only when the question set shows it helps. Read the plan with `EXPLAIN` when a company grows. Measure every change, of model, size or passage length, with the question set.

![Two searches for Cedar Ridge's policies, side by side, then merged. Left, search by meaning: the question do we need a PO for a $400 repair finds line 5 at 0.72 and line 6 at 0.63, with a note that both opposite rules are near. A second question, the same with $4,000, gets the identical results: the same two rules, the same scores. Right, search by words: PO finds nothing, because the word is not there, while blanket finds purchasing line 7 exactly. Below, the two lists flow into one merged list, labeled hybrid search: for the $400 question, line 6, the rule that answers, comes first. Under everything: search finds the rule, code compares the amount.](img/chapter-19-words-and-meaning.png)

*Figure 19.25. Search by meaning and search by words fail in opposite places. Search finds the rule. Code compares the amount.*