PHASE 5Intermediate ~23 min· topic 10 of 10

Topic 5.10

Playbook: Vector Store Recipes

In one line

Ready-to-copy recipes for any vector store: a schema and index template, a query template with parameters, the sizing function, a compaction trigger, a migration overlap check and a blue-green re-index.

Think of it like this

A restaurant kitchen keeps recipe cards by the stove: how much salt, how long, what to do if the sauce splits. A good cook can improvise, but under pressure the card is faster and safer. These are recipe cards for running a vector store.

Words you'll meet

New words in this topic, in plain English. Come back here whenever one feels fuzzy.

Alias: A stable name that points to one collection and can be repointed in one step.
Blue-green: Run the new version beside the old one, switch when ready, keep the old for rollback.
Overlap at k: The share of one store's top k ids that another store also returns.
Dead ratio: The share of rows that are tombstones.
Rollback: Going back to the previous version after a bad change.
Test set: Questions with known right answers, used to compare versions.

Step by step

01Step 1: create the table and the index

Run this once. The table fixes the vector length, the index matches the operator you query with. For a big first load, create the HNSW index (the last line) only after the rows are in, because loading into an existing HNSW index is slower.

setup.sqlwhole filesql

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE chunks (
  id        text PRIMARY KEY,
  doc_id    text NOT NULL,
  version   text NOT NULL,
  model     text NOT NULL,
  embedding vector(384) NOT NULL
);

CREATE INDEX ON chunks (doc_id);
CREATE INDEX ON chunks USING hnsw (embedding vector_cosine_ops);

02Step 2: write with an upsert

One statement per chunk, or a batch of rows, with parameters. Delete the chunks of a document that no longer exist in a separate statement.

write.sqlwhole filesql

INSERT INTO chunks (id, doc_id, version, model, embedding)
VALUES ($1, $2, $3, $4, $5)
ON CONFLICT (id) DO UPDATE
  SET version = EXCLUDED.version, model = EXCLUDED.model, embedding = EXCLUDED.embedding;

DELETE FROM chunks WHERE doc_id = $1 AND id <> ALL($2::text[]);

03Step 3: query with a filter

The user's filter is added by the server. The select returns the score as a similarity.

query.sqlwhole filesql

SELECT id, doc_id, 1 - (embedding <=> $1) AS score
FROM chunks
WHERE version = $2 AND doc_id = ANY($3::text[])
ORDER BY embedding <=> $1
LIMIT $4;

04Step 4: watch the dead ratio

The first example's should_compact returns yes only above 20 percent dead rows. Run your store's own statistics query on a schedule and call it.

05Step 5: compare before you migrate

The overlap check prints the overlap of each question, the mean and the questions below the floor. In the example the login and pass questions fall under 0.70 and are flagged; the refund question is identical.

06Step 6: switch with an alias

The second example builds lumen_v2, measures it on three test questions and switches the alias only because the new version scores higher. Rollback is repointing the alias.

Step 6: switch with an aliasdiagram

Rendering diagram…

Try it yourself

1
Tune the trigger
Call should_compact(80, 20). Is exactly 20 percent dead a yes or a no, and why? Change the comparison to >= and say when you would want that.
2
Raise the floor
Change the minimum overlap to 0.9 and run. Which questions are flagged? What would you look at for each?
3
Make the new version worse
In the second example remove money from V2. What does the script do, and what does that say about the rule new_rate > old_rate?
4
Add a critical question
Add a fourth test question and require that the new collection answers every critical question right before switching. Where would you store that flag?

Code & diagrams

Compaction trigger and the migration overlap check New tab

Expected output

live=90 dead=10 -> compact: no
live=75 dead=25 -> compact: yes
live=0 dead=0 -> compact: no
refund   overlap 1.00  old=D1,D6,D2 new=D1,D6,D2
login    overlap 0.67  old=D3,D5,D1 new=D3,D1,D10
pass     overlap 0.67  old=D8,D6,D12 new=D8,D12,D4
mean overlap 0.78, flagged: login, pass

Blue-green re-index with an alias New tab

The two groups of words stand in for two embedding models. With a real model you would measure the hit rate on your test set in the same way.

Expected output

serving: lumen_v1, hit rate 0.67
built lumen_v2, hit rate 1.00
switched alias: lumen -> lumen_v2 (lumen_v1 kept for rollback)
top answer for the refund question now: D1

Running the query from your applicationpython

Plain PostgreSQL drivers with a vector sent as text and cast with ::vector. A Python list or JavaScript array for ANY(...) becomes a PostgreSQL array (in JDBC, createArrayOf does it), and the Java text block needs Java 15 or later. The pgvector project also publishes helper packages for each language (pgvector-python, pgvector-node, pgvector-java) that register the vector type so you can pass lists directly. Install the driver for your language; the connection string comes from an environment variable. Not run here: the browser has no database.

import os

import psycopg  # pip install "psycopg[binary]"


def to_pg(vector):
    return "[" + ",".join(str(x) for x in vector) + "]"  # pgvector reads this text form


SQL = """
SELECT id, doc_id, 1 - (embedding <=> %s::vector) AS score
FROM chunks
WHERE version = %s AND doc_id = ANY(%s)
ORDER BY embedding <=> %s::vector
LIMIT %s
"""


def search(query_vector, version, allowed_docs, k):
    text = to_pg(query_vector)
    with psycopg.connect(os.environ["DATABASE_URL"]) as conn:  # the connection string stays out of the code
        return conn.execute(SQL, (text, version, allowed_docs, text, k)).fetchall()

Break it on purpose

Errors are the best teachers. Make each change, read the error, guess what went wrong, then reveal the answer.

Break #1

Switch without measuring

In the Python tab of the second example, replace the condition if new_rate > hit_rate(collections[alias["lumen"]]): with if True:, and make V2 worse by replacing its line with V2 = ["nothing", V1[1], "nothing", V1[3]]. Run.

terminal

$ Run

── what you'll see ──

serving: lumen_v1, hit rate 0.67

built lumen_v2, hit rate 0.33

switched alias: lumen -> lumen_v2 (lumen_v1 kept for rollback)

top answer for the refund question now: D1

Myth vs fact

Myth

A migration is done when the new store returns results.

Fact

It is done when the new store answers your test questions at least as well and you can roll back.

Myth

Compaction is optional housekeeping.

Fact

Without it dead rows pile up. Index size and search time rise, and deleted data may remain on disk.

Myth

The default index parameters are fine for every data set.

Fact

They are starting points. Measure recall on your own questions and change one setting at a time (Topic 3.6).

Pro corner

Extra depth for experienced readers. New to this? Skip it for now and come back later.

▸
Keep a standing canary set of 20 to 50 questions with their expected ids. Run it after every deploy, index rebuild and model change and alert when the hit rate drops.
▸
Store model and version on each point so a re-index is a query, and a part-way migration is visible.
▸
Rehearse the restore: take a backup, restore it on another machine, and run the canary set. A backup that has never been restored is a hope.

Remember this

1
Recipe: schema, index and query (pgvector). When you start with PostgreSQL. It guards against the three silent mistakes: a vector column without a fixed length, an index whose operator class does not match the query operator, and values pasted into SQL. Adapt the dimension, the operator class (vector_cosine_ops, vector_l2_ops, vector_ip_ops) and the filter columns. Build the HNSW index after the first bulk load.
2
Recipe: the sizing function. When you plan a machine or compare models: n x (bytes x dims + 8) plus an index overhead you measured on a sample (Topic 5.7). It guards against buying too little memory or choosing a vector size the budget cannot hold. Adapt it by replacing the assumed overhead with your measurement and adding copies.
3
Recipe: the compaction trigger. When a store tombstones deletes (Topic 5.6). Compact when the dead share passes a threshold, here more than 20 percent, a starting value to tune. It guards against an index that quietly grows and slows down. Adapt the threshold, and run the job in quiet hours.
4
Recipe: the migration overlap check. When you move to another store, index or model. Ask both the same questions and compare their top k: how many ids does the new one return that the old one did? Flag questions below a floor and read those by hand. It guards against a migration that looks fine on average and breaks specific questions. Overlap does not say which store is right: a test set with known answers (Phase 9) does.
5
Recipe: blue-green re-index. When the model, the distance or the chunker changes. Build a new collection beside the live one, measure it on a test set, switch an alias in one step, keep the old for rollback and drop it later. It guards against downtime and against switching to something worse. Adapt the pass rule, for example a minimum hit rate plus no regression on any critical question.
6
Recipe: the access filter. Every query carries the signed-in user's filter, added by the server (Topic 5.5). It guards against showing one user another's documents. Test with a user who owns nothing: they must get an empty result, not someone else's passages.
7
Before you ship. Use the checklist at the end of this topic. It turns the phase into questions you can answer yes or no.

Explain it without notes

Give the steps of a blue-green re-index and what each protects against.

What does an overlap check tell you, and what does it not?

Why does the schema fix the vector length?

What should a vector-store canary set contain?

Practice

Write overlap_at_k(old, new) and print it for ["D1", "D6", "D2"] against ["D1", "D2", "D9"] with 2 decimals.

Write switch_alias(aliases, name, new_collection, new_rate, old_rate) that points the alias to the new collection only if new_rate > old_rate and returns the collection now served. Print the result for rates 0.9 and 0.8, then for 0.7 and 0.8.

Trade-offs

↔
Blue-green re-index: no downtime and easy rollback, at the cost of two copies for a while.
↔
A strict overlap floor: catches regressions, flags harmless differences too.
↔
Frequent compaction: cleaner index, more background load.

Done when you can

Before you ship: the vector length is fixed in the schema and matches the model, and the model name is stored with each point.
Before you ship: the index operator class matches the query operator, and recall was measured against exact search on your own data.
Before you ship: every query carries the user's access filter, added on the server, and an empty-owner test passes.
Before you ship: updates use upserts with stable ids, deletes are tested, and a compaction rule exists with a metric behind it.
Before you ship: memory is sized with copies and headroom, and a canary set plus a restore test are in place.

Command Palette

Words you'll meet

Step by step

01Step 1: create the table and the index

02Step 2: write with an upsert

03Step 3: query with a filter

04Step 4: watch the dead ratio

05Step 5: compare before you migrate

06Step 6: switch with an alias

Try it yourself

Tune the trigger

Raise the floor

Make the new version worse

Add a critical question

Code & diagrams

Break it on purpose

Switch without measuring

Myth vs fact

Pro corner

Remember this

Explain it without notes

Practice

Trade-offs

Done when you can