Chapter 7: From Library to Database

Time: 50 minutes, plus a few minutes to download the database image the first time. Cost: $0. This lab needs Docker or Podman from the Setup page and reuses the shared dataset from Chapter 4.

The short version

• A vector library is fast, but it lives inside your program. A database adds what a product needs: data that survives a restart, filters, updates while people search, and backups.
• Filters are where approximate search breaks. Ask for 10 results for one customer and you may get 4, with no warning. A database can fix that, if you know which setting to change.
• If you already run PostgreSQL, start with pgvector, and check its recall regularly against its own exact search. One SQL setting does it.

The FAISS index from Chapter 5 answered a question in about a tenth of a millisecond. Now put it behind a real product and listen to the questions you get.

A customer uploads a document. When can people search it? Another customer cancels, and legal wants their documents gone today. Customer A must never see customer B's documents. The server restarts at 3 a.m. Someone asks where the backups are.

None of those are search problems. They are database problems. This chapter moves the 18,896 paragraphs from a Python library into PostgreSQL with pgvector, measures what changes, and ends with how I would choose between an extension like pgvector and a purpose-built vector database.

What a database adds

Think of the difference between a calculator and a bank. A calculator adds perfectly and forgets everything when you switch it off. A bank keeps a ledger, survives a power cut, lets thousands of people make deposits at once, and never shows you a transfer that is half done.

FAISS, Annoy, and hnswlib are libraries. The index lives inside your program's memory. If the program stops, the index is gone unless you saved it to a file yourself. There is no idea of a user, a transaction, or a column called customer_id. That is not a flaw. A library does one job, very fast, and leaves everything else to you.

A database takes on the everything else:

You need	With a library	With a database
Survive a crash or restart	Save and reload index files yourself	Every change is written to a log on disk first
Add and delete while people search	Rebuild, or coordinate it yourself	Inserts, updates, and deletes are normal operations
Search only one customer's documents	Filter the results yourself	A WHERE clause
Never show a half-finished update	Your problem	Transactions
Backups and a standby copy	Copy files around	Built-in backup and replication tools

That log in the first row is PostgreSQL's write-ahead log (WAL). pgvector writes its vectors and indexes through it, which is why pgvector's documentation can say replication and point-in-time recovery simply work.

Three ways the market got here

The tools arrived in three waves, and the lines between them have blurred since.

Libraries came first. Annoy from Spotify, FAISS from Facebook, and hnswlib, written alongside the HNSW research. They are still what many products run underneath.

Purpose-built vector databases came next. Milvus, Pinecone, Qdrant, Weaviate, Chroma, and others were built around the vector index and added database features around it.

Then existing databases added vectors. pgvector is an extension that adds a vector column type and vector indexes to PostgreSQL. Elasticsearch, MongoDB, and others added vector search the same way. These started from a database people already ran and added the index.

The two directions meet in the middle. pgvector adopted HNSW. Purpose-built databases added filters, updates, and backups. The choice today is less about whether a product can search vectors and more about which system you want to operate.

Who built what, and when

Erik Bernhardsson wrote Annoy at Spotify in 2013. Facebook released FAISS in 2017. Zilliz open-sourced Milvus in October 2019. Pinecone, founded in 2019 by Edo Liberty, launched its managed vector database as a public beta in January 2021.

Andrew Kane started pgvector in April 2021, and it added HNSW in version 0.5.0 in 2023. Elasticsearch shipped approximate kNN search in version 8.0 in February 2022, and MongoDB made Atlas Vector Search generally available in December 2023. Amazon RDS added pgvector in May 2023. pgvector 0.8.0 added iterative index scans in October 2024.

pgvector in four statements

Here is the whole setup from the lab. One statement turns the extension on, one creates a table with a vector column, one searches it, and one adds an HNSW index:

CREATE EXTENSION vector;

CREATE TABLE paragraphs (
    id          integer PRIMARY KEY,
    title       text,
    body        text,
    customer_id integer,
    year        integer,
    embedding   vector(768)
);

SELECT id FROM paragraphs ORDER BY embedding <=> '[0.01, -0.03, ...]' LIMIT 10;

CREATE INDEX ON paragraphs USING hnsw (embedding vector_cosine_ops);

<=> is cosine distance: 0 means the same direction, so smallest first is most similar. pgvector also has <-> for Euclidean distance and <#> for negative inner product, the three measures from Chapter 2. The vectors sit in the same row as the title and the text, which matters more than it looks. A search result is just a row, so you can join it to anything else in the database.

The index has one rule that catches people. It is only used when the ORDER BY is the distance operator itself, smallest first, followed by a LIMIT. Sort by a calculated similarity, such as ORDER BY 1 - (embedding <=> ...) DESC, and PostgreSQL falls back to checking every row.

Exact search got slower

Before any index, PostgreSQL does exactly what Chapter 3 did: score every row and keep the best 10.

PART 2: exact search, no vector index
  Plan: Seq Scan on paragraphs
  21.0 ms/query, recall@10 1.00 (200 questions)

Seq Scan is PostgreSQL's name for reading the whole table. Recall is perfect, as it should be. The speed is worth a candid look. FAISS's brute force took 0.746 ms on the same vectors in Chapter 4. PostgreSQL took nearly 30 times longer. It reads each row through machinery built to run any query on any table, one row at a time, while FAISS runs one block of math over a tightly packed array. You pay for generality. That is fine, because you would not run exact search at scale in either one.

The HNSW index

PART 3: HNSW index with pgvector's defaults (m = 16, ef_construction = 64)
  PostgreSQL notice: hnsw graph no longer fits into maintenance_work_mem after 16757 tuples
  Built in 3.2 s, index size 74 MB
  Plan: Index Scan using paragraphs_embedding_idx on paragraphs
  ef_search  ms/query  recall@10
         10      0.55       0.82
         40      0.76       0.95  (the default)
        100      1.18       0.99
        200      1.76       1.00

These are the settings from Chapter 5 under SQL names, and the numbers behave the same way. At the default hnsw.ef_search of 40, recall is 0.95. You change it per session or per transaction with SET, so an important query can ask for more recall without touching anyone else's.

Look at the notice, because you will see it in production. PostgreSQL builds the graph inside a memory budget called maintenance_work_mem. The container's default is 64 MB, and the graph outgrew it after 16,757 of the 18,896 paragraphs. pgvector kept going, more slowly. On a real server you would raise that budget for the build, which pgvector's documentation recommends, as long as you leave enough memory for everything else on the machine.

Filters are the hard part

Every real search has a filter. This customer only. Published after 2020. Only documents this user may read. Here is where approximate indexes and databases collide.

Imagine asking a shoe store clerk for the 40 pairs most like the pair in your hand, and then throwing out every pair that is not in your size. If your size is rare, you walk out with two pairs.

That is what pgvector's HNSW index does. It applies the filter after the graph search. It collects ef_search candidates, 40 by default, then throws away the ones that fail the WHERE clause. The lab adds two made-up columns to test this. Each Wikipedia article belongs to one of 20 customers, so each customer owns about 5% of the paragraphs. Each paragraph also gets a random year from 2017 to 2026, so one year is about 10%. The query asks for 10 results:

  filter                                 rows back  recall@10  ms/query
  customer (5% of rows, follows topic)         5.2       0.52      0.71
  year 2020 (10% of rows, random)              4.0       0.40      0.71

You asked for 10 rows and got 4. No error, no warning. Just fewer results. The year filter lands almost exactly where pgvector's documentation says it will: 40 candidates, about 10% of them pass, about 4 rows.

The customer row is the more interesting one. It matches half as many rows as the year filter, so you would expect half the results. It returned more. Each question here searches the customer that owns its source paragraph, and that customer's paragraphs are about the same topics as the question. The 40 nearest candidates are full of them. The rule of thumb in the documentation assumes a filter that has nothing to do with the query. Real filters often do. Sometimes that helps, as here, and sometimes it hurts: search for "refund policy" inside a customer whose documents are all engineering notes, and almost none of the 40 nearest candidates will pass.

There are three ways out, and a database can pick between them.

Filter first, then search exactly

If a filter leaves only a few hundred rows, do not use the vector index at all. Find those rows with an ordinary B-tree index, the kind databases have used since the 1970s, and compute exact distances for just those rows. That is Chapter 3's brute force on a tiny slice.

You do not have to tell PostgreSQL to do this. Its planner, the part that decides how to run a query, estimates how many rows a filter will match and picks the cheapest plan. With a B-tree index on title and a filter for one article:

  Plan: Bitmap Heap Scan on paragraphs > Bitmap Index Scan on paragraphs_title_idx
  article (0.2% of rows)                      10.0       1.00      0.41

The planner skipped the HNSW index entirely. Perfect recall, and faster than the HNSW searches above, because it only scored about 40 paragraphs. A library cannot make that choice for you. It only knows about vectors.

Keep walking until enough rows pass

For broader filters, pgvector 0.8.0 added iterative index scans. Back in the shoe store, the clerk keeps bringing more pairs until 10 of them are in your size. When too few candidates pass the filter, the index keeps searching the graph for more, up to a limit you can set:

  Same filters with hnsw.iterative_scan = relaxed_order:
  customer (5% of rows, follows topic)        10.0       0.97      1.50
  year 2020 (10% of rows, random)             10.0       0.95      1.27

Ten rows every time, and recall back where an unfiltered search was, for about twice the time. The word relaxed means results may come back slightly out of distance order. The strict_order option keeps them in exact order but stops earlier, and in this lab recall dropped to about 0.9. If I were using pgvector with filters, I would turn on relaxed_order and sort the final 10 myself.

Purpose-built databases attack the same problem inside the graph itself. Qdrant's documentation describes adding extra links to its HNSW graph based on indexed filter values, so a filtered walk does not strand itself. Filtered-DiskANN, from Chapter 6, builds filter labels into the Vamana graph. pgvector's documentation also suggests partial indexes for a few common filter values, and table partitioning when you have many tenants and want each one isolated.

What a transaction buys you

The last part of the lab does something no library can. Two connections play two users. The writer deletes every paragraph about Virgil inside a transaction and does not commit yet. Both then ask the example question about a Roman poet:

PART 5: one connection deletes an article inside a transaction, another keeps searching
  Writer ran DELETE ... WHERE title = 'Virgil' but has not committed.
  Writer's top result: 'Alps'
  Reader's top result: 'Virgil'
  Writer rolled back.
  Writer's top result: 'Virgil'

The writer already sees its own change. The reader sees the database as it was, because the change is not finished. After the rollback, the delete never happened. PostgreSQL does this by keeping old and new versions of a row side by side until the transaction ends, which is called multiversion concurrency control (MVCC). It works like a shared document where your edits stay a private draft until you publish them.

Here is why that matters for search. When a document changes, you usually delete its old chunks and insert new ones with new embeddings. Do that in one transaction and no user ever searches a document that is half old and half new, or briefly missing. With a library, you build that guarantee yourself.

Checking quality in production

Chapter 3 described two checks. The kitchen check asks whether the index returns what exact search would. Every lab so far ran it against answers computed in numpy. In production there is no numpy copy of the right answers, so you ask the database.

pgvector's documentation suggests a simple way. Inside a transaction, turn index scans off, and the same query becomes exact search:

BEGIN;
SET LOCAL enable_indexscan = off;  -- exact search, this transaction only
SELECT id FROM paragraphs ORDER BY embedding <=> $1 LIMIT 10;
COMMIT;

LOCAL means the setting ends with the transaction, so no other query is affected. Run each saved question both ways and count how many of the exact top 10 the index returned. The last part of the lab does that for 50 questions:

PART 6: a recall check inside PostgreSQL, as you would run it in production
  Plan with index scans off: Seq Scan on paragraphs
  recall@10 at ef_search 40, against PostgreSQL's exact search: 0.95
  PostgreSQL's exact answers matched numpy's for 50 of 50 questions

There is a trap here that I walked straight into while writing the lab. My first version reported a recall of 1.00, which looked great and was wrong. Only 36 of the 50 "exact" answers matched numpy. The Python driver had prepared the query, because the script ran it thousands of times, and PostgreSQL kept using a plan made while the index was allowed. The setting changed and the plan did not. Asking the driver not to prepare that one query (prepare=False in psycopg) fixed it.

Worse, EXPLAIN did not catch it. Even the broken version printed Seq Scan for the exact query, because EXPLAIN plans the query fresh, while the real query reused the saved plan.

The lesson is broader than one driver. A recall check that always says 1.00 is a check that is not checking. Two habits catch it. Send the exact query unprepared, or with its own text, so it never shares a saved plan with the normal search. And test the check once on purpose: set ef_search to 10, where the lab measured about 0.82, and make sure the check reports a drop. If it still says 1.00, the check is broken, not the index.

Here is the routine I would set up:

• Keep a few hundred real questions from your search logs, with the filters people actually used. Filters are where recall drops, as Part 4 showed, so a check without them is too kind.
• Run the check on a schedule and after every change that can move recall: a new ef_search, a large batch of new rows, a rebuilt index, a new embedding model.
• Run it on a read replica if you have one. Exact search is slow by design. At this size it took about 21 ms per question. At 10 million rows, expect seconds each, which is fine for a nightly job and not something to put next to live traffic.
• Write down the number at launch and alert on a drop. The recall you measured on day one is your baseline. A slow slide usually means the data changed under the index.

That covers the index. The customer check, whether search finds what people actually needed, needs a labeled set instead, and Chapter 8 starts measuring with one.

Extension or purpose-built?

This is the question I get most often, and my answer starts with what you already run.

If you already run PostgreSQL, start with pgvector. Your vectors sit next to the data they describe, so a filter is just SQL, a document update is one transaction, and there is one system to secure, back up, and monitor instead of two. You also skip keeping two systems in sync, which is a real source of bugs. Amazon RDS and most other managed PostgreSQL services offer pgvector, so it is usually one CREATE EXTENSION away.

Know its limits before you commit:

• One machine does the work. pgvector scales up with a bigger server and out with read replicas. Splitting one collection across machines needs another tool on top, such as Citus.
• Index builds want memory. You saw the notice. At tens of millions of vectors, builds take real time and memory, and pgvector's documentation notes that vacuuming an HNSW index can be slow.
• Dimension limits. The vector type can be indexed up to 2,000 dimensions. A 3,072-dimension model needs halfvec, the half-precision type from Chapter 6, which indexes up to 4,000.
• Keyword ranking is not BM25. PostgreSQL's built-in full-text search ranks documents differently from the BM25 scoring most search engines use. Chapter 8 shows why that matters.

When I would look at a purpose-built vector database:

• The collection outgrows one machine, and you want sharding and replication built in rather than assembled.
• Search is heavily filtered, and filter-aware graph search makes a measurable difference on your data.
• Search runs as its own service with its own team anyway, so sharing a database buys you little.
• You want a fully managed service where you never think about index builds or memory settings.

Either way, measure on your own data the way these labs do: recall against brute force, with your real filters. The chart in a vendor's blog post was not run on your queries.

Hands-on lab: pgvector in Docker

You will start PostgreSQL with pgvector in a container, load the 18,896 paragraphs, compare exact and HNSW search, measure what filters do to approximate search, and watch a transaction hide an unfinished delete.

Full instructions: download the Vector Databases labs ZIP. If you have not built the shared dataset yet, follow labs/vector-databases/dataset/README.md first. Then open labs/vector-databases/07-library-to-database and follow its README. It starts the container with a single docker run command, which works with podman too.

The output is the tables in this chapter. Timings depend on your machine, and recall can differ by about 0.01 between runs, because pgvector builds the graph with several workers at once.

Checkpoint

The year filter matches 10% of rows. Why did a request for 10 results return only about 4?

pgvector's HNSW index collects ef_search candidates, 40 by default, and applies the WHERE clause afterward. About 10% of 40 candidates pass, which leaves about 4 rows. Iterative index scans fix this by searching more of the graph until enough rows pass.

Your weekly recall check has reported exactly 1.00 for three months, including the week someone lowered ef_search to 10. What would you check first?

Whether the "exact" side of the check is really exact. A recall of 1.00 at an ef_search of 10 is not believable: the lab measured about 0.82. Most likely the exact query is still using the index, for example because the driver reused a prepared plan. EXPLAIN will not show that, because it plans the query fresh. Send the exact query unprepared, then rerun the check at ef_search 10 and confirm it reports a drop.

In Part 5, why did the reader still see Virgil after the writer deleted it?

The writer's delete was inside a transaction that had not committed. PostgreSQL keeps the old version of each row visible to everyone else until the transaction commits, so readers never see a half-finished change. When the writer rolled back, the delete disappeared entirely.

Check Your Knowledge

Click to start the quiz

1. You added an HNSW index, but EXPLAIN still shows a Seq Scan for this query: SELECT id FROM docs ORDER BY 1 - (embedding <=> $1) DESC LIMIT 10. What is wrong?

The ORDER BY sorts by a calculated similarity instead of the distance operator itself, smallest first, so the index cannot be usedThe index needs to be rebuilt after every insertHNSW indexes only work with Euclidean distanceLIMIT 10 is too small for the planner to use an index

2. A small customer owns 300 documents in a table of 5 million. Searches filtered to that customer, using the HNSW index, often return zero or one result. Which fix is the most direct?

Raise m from 16 to 64 and rebuild the indexSwitch from cosine distance to inner productRaise ef_construction so the graph is built more carefullyAdd a B-tree index on the customer column, so the planner can find the 300 rows and compare them exactly

3. A team already runs PostgreSQL for its app. It wants to search 2 million document chunks, filter by account, and update a document's chunks without users ever seeing a half-updated version. What would you start with?

FAISS inside the application, saved to a file every nightA purpose-built vector database, because PostgreSQL cannot store 2 million vectorspgvector in the existing PostgreSQL, so filters are SQL and each document update is one transactionTwo databases, so search and app data never share a server

4. You switch to an embedding model with 3,072 dimensions, and creating an HNSW index on a vector(3072) column fails. What does pgvector's documentation suggest?

Split each vector into two columns of 1,536 and index bothStore the vectors as halfvec, which can be indexed up to 4,000 dimensionsUse IVFFlat, which has no dimension limitThere is no fix; PostgreSQL cannot search vectors that large

What's next

Everything so far has measured one thing: how close an index gets to the exact nearest neighbors. But remember the warning in Chapter 4. The exact top 10 contained the paragraph each question was written from only 59% of the time. Tuning the index will not reliably beat that number. Chapter 8 raises it, by adding the oldest search method in this track back in: matching the actual words. You will build BM25 keyword search, fuse it with vector search, and see which questions each one gets right.