Episode 107 · 60 million vectors

Choosing a vector database

Monday, 9:30: the concierge's knowledge index will hold 60 million vectors, filtered per hotel and fresh within 5 minutes. Karim has two quotes on his desk and one question: can Postgres still carry it?

Every vector database does the same core search. What really decides the choice is everything around it, and often the answer is the database you already have.

30 min advancedpgvector vs dedicated vector databasesFiltered vector search (pre vs post filtering)Sizing vector index memory and costManaged services vs self-hostingTotal cost of ownershipMetadata filtering in retrieval

Problem
You need semantic search over hotel content (reviews, policies, listings) that is fast, correctly filtered per hotel or tenant, fresh, and affordable.
Why the simple system fails
Picking a database from a benchmark chart or a trend: a second datastore for 200k vectors brings sync bugs and deleted data in search results; or 300M vectors crammed into the OLTP Postgres starve checkout queries of memory.
Engineering concept
Decide on measurable requirements: vector count × dimensions (memory), filter selectivity and tenancy, hybrid needs, write freshness, query rate, team operations capacity, and total cost. Default to the database you already run until a requirement breaks it.
Technology
pgvector (HNSW/IVFFlat, iterative filtered scans, partitioning), managed services (Pinecone), open-source engines with managed offerings (Weaviate, Qdrant, Milvus), search engines with vector support (Elasticsearch/OpenSearch), embedded libraries (FAISS, LanceDB, Chroma).
Trade-offs
One system vs best-in-class features; transactional consistency vs a sync pipeline; operations effort vs vendor dependency; usage-based vs provisioned pricing.

The problem

2 min

Monday, 9:30. The concierge's knowledge index: reviews, policies, room descriptions, 9 languages.

Karim · CTO
60 million vectors. Quote A: a bigger Postgres replica, €6k/month. Quote B: a managed vector database, €9k/month.
Omar · On-call SRE
Every search is for one hotel. Policy edits searchable within 5 minutes. Deleted reviews gone immediately: it's a legal request.
Lina · Senior engineer, your mentor
You lead this one. I'll check your answer.

The big idea

4 min

Decide on requirements, not charts

The big idea

The best vector store is usually the one you already run, until a requirement you measured says otherwise.

So you don't compare charts; you check requirements with numbers. Memory first, because the index wants to live in RAM. Then filters, freshness, deletes and the cost of running it.

How it works

5 min

Here are StayHub's requirements, one by one, the way you'd write them for Karim.

What actually decides a vector store

1
Memory
Vectors × dimensions × bytes: 60M × 1,536 × 4 B ≈ 369 GB. Quantization stores each number with fewer bits (). With 768 dimensions and 16-bit numbers: ≈92 GB + ~12 GB graph ≈ 105 GB. Fits a 256 GB replica.
2
Filtered search
Filtered search limits nearest-neighbour search to rows matching a condition, like one hotel (). It must happen inside the search: recent pgvector keeps scanning the index until enough rows match.
3
Freshness and deletes
In Postgres the vector is a column of the row: a delete removes it in the same transaction. A second store needs a sync pipeline, the jobs that copy every change across, with lag and lost events.
4
Hybrid search
Room numbers and hotel names need keyword search too. Postgres full-text sits next to the vectors.
5
Ops and cost
Replica ≈ €6k/month on a system we already run. Managed DB ≈ €9k/month, plus building and being on call for the sync pipeline.
6
Triggers
Write down what would reopen the decision: memory past ~180 GB, filtered p95 above 80 ms, more than ~2,000 searches/s, or a feature Postgres lacks.

Watch a decision being made

4 min

Worked example

You choose the store for 60M vectors; Lina checks

Recommend where the concierge's 60M-vector index should live, with numbers, and the triggers that would change the answer.

1Size the memory
369 GB raw won't fit. Truncate to 768 dims (recall on 500 test queries: 0.97 → 0.96) and store 16-bit: ≈105 GB with the graph.
Why: HNSW wants RAM. Memory decides which machines and products are even possible.
2Test filtered search where it fails
Run 500 real questions per hotel-size bucket, filter inside the search, compare with exact search: recall@8 ≥ 0.95 for the smallest hotels too.
sql
```
SET hnsw.iterative_scan = relaxed_order;
SELECT id FROM knowledge_chunks
WHERE hotel_id = $2 ORDER BY embedding <=> $1 LIMIT 8;
```
Why: Filtered search fails as missing results, not errors, and small hotels fail first.
3Check freshness and load
Policy edits re-embedded within ~1 minute; deletes in the same transaction. At 500 searches/s on a dedicated replica: p95 41 ms, CPU 55%.
Why: Both requirements are met with no sync pipeline, and vector load stays away from the replicas serving hotel pages.
4Write the decision and triggers
pgvector on a dedicated replica (~€6k/month). Revisit at memory > 180 GB, filtered p95 > 80 ms, > 2,000 searches/s, or a missing feature. Lina: "Checked. Nothing to add."
Why: Written triggers make the next decision happen on time, not after an incident.

In real life

3 min

Where should vector search live?

Pinecone: a dedicated vector database

A separate, fully managed service just for vector search
No servers to run; built for very large collections
Your data lives in two places, so you sync it

Fits when vector search is big enough to deserve its own system.

Elastic: vectors inside an existing engine

Added vector search to Elasticsearch, next to keyword search
Teams already running it get hybrid search with no new system
Shares resources with everything else in the cluster

Fits when you already run the engine and want one system.

What goes wrong

3 min

Filtering after the nearest-neighbour search

Selective filters leave few or zero results, and averages hide it.

Instead

Filter inside the search and measure recall for the smallest tenants.

Adding a second store without pricing the sync

Outbox, workers, lag alerts, and deleted data that stays searchable.

Instead

Count the pipeline and on-call in the cost; keep one source of truth and an index you can rebuild.

Ignoring memory until it's full

At 60M vectors, float32 doesn't fit and performance collapses.

Instead

Size first; cut dimensions and use quantization, checking recall after each.

Trade-offs

3 min

Option	Fits when	You pay
pgvector in Postgres you run	The index fits a replica's RAM with headroom	Tuning; shares an engine with other work
Managed vector database	Memory, load or features exceed your database	Usage bill, a new vendor, a sync pipeline
Self-hosted open-source engine	Very large scale, with a platform team	Another cluster to run and page for, same sync

Wednesday, 11:00. Karim and Omar read your recommendation.

Karim · CTO
The managed database is €3k more, but no vector tuning. I'd rather pay.
Omar · On-call SRE
€3k plus a sync pipeline I'm on call for, and deletes that can lag. I'd rather tune Postgres.
Karim · CTO
And when we hit 250M vectors next year?

Your job as tech lead

4 min

Claude Code can write the code for this. You still own the decisions, and you have to know what good looks like to accept or reject its work.

Decisions you own

The requirements, in numbers, before anyone picks a product: 60M vectors, ~500 searches/s, a hotel filter on every search, 5-minute freshness, immediate deletes.
The memory plan: 768 dimensions in 16-bit (≈105 GB) instead of 1,536 float32 (≈369 GB), checked against recall on our own questions.
The triggers that would make you move later, written down now.

Tell Claude Code

We're keeping the concierge knowledge index (60M chunks, 9,000 hotels) in pgvector on a dedicated read replica. First write `docs/decisions/vector-store.md` with our numbers and the triggers for revisiting. Then benchmark a 5M-vector sample: recall@10 and p95 with the hotel filter, reporting the 50 smallest hotels separately. Filter inside the search, not top-40 then filter. Don't touch the primary database or booking tables. Show me the plan and numbers before changing production.

Check before you accept

Redo the memory math: vectors × dimensions × bytes, plus the index. It fits 256 GB with headroom.
The smallest hotels get results in the benchmark (the prototype returned nothing for 31% of their questions).
A deleted review disappears in the same transaction; no nightly re-sync job.

Red flags

"A dedicated vector DB scales better" with no memory math or failed requirement.
Top-k first, then filter by hotel, tested only on a big hotel.
A benchmark on random vectors or without filters.

Check yourself

2 min

Q1/3Debugging

Searching reviews for a small hotel (0.1% of all chunks) often returns only 0–2 results, though the hotel has hundreds of relevant reviews. Unfiltered searches look fine. What's the likely cause?

How confident are you?

Next episode: StayHub's new staff assistant confidently invents a late-checkout policy that no hotel has, from a 400-page handbook that changes every month.

Finished?

Mark it complete to update your skill map.

Next: RAG: answering from your documents

Go deeper (optional)You don't need this to understand the lesson. Open it when a project needs the details: Technical deep dive, Interactive simulation, Visual diagram, Analogy, Why it matters, Check your understanding, Practice ladder, Explain it yourself.

Technical deep dive

Filters, freshness and the sync tax

Vectors	Dims	float32 (4 B)	16-bit (2 B)	int8 (1 B)	binary (1 bit)
4.2M	1,536	26 GB	13 GB	6.5 GB	0.8 GB
60M	1,536	369 GB	184 GB	92 GB	11.5 GB
60M	768	184 GB	92 GB	46 GB	5.8 GB
250M	768	768 GB	384 GB	192 GB	24 GB

Raw vector bytes only; the HNSW graph and metadata come on top. Fewer dimensions and quantization are the two biggest levers. Quantized search usually rescores the top candidates at full precision.

Worked example

You choose the store for 60M vectors

Recommend where the concierge's 60M-vector knowledge index should live, with numbers, and the triggers that would change the decision. Lina only checks the final note.

1Size the memory
369 GB raw at 1,536 float32. The model supports truncation to 768 dims (recall on our 500 test queries: 0.97 → 0.96), and 16-bit storage costs almost nothing: ≈92 GB + ~12 GB graph.
Why: HNSW wants to live in RAM. Memory decides which machines and products are even possible.
2Test filtered search
Every search is one hotel. Run 500 real questions per hotel-size bucket with iterative index scans, and compare with exact search: recall@8 ≥ 0.95 for the smallest hotels too.
sql
```
SET hnsw.iterative_scan = relaxed_order;
SET hnsw.ef_search = 100;
EXPLAIN ANALYZE
SELECT id FROM knowledge_chunks
WHERE hotel_id = $2 ORDER BY embedding <=> $1 LIMIT 8;
```
Why: Filtered ANN fails as missing results, not errors. Small hotels are where it fails first.
3Check freshness and deletes
Policy edits: re-embedded by a worker within ~1 minute. Deletes: same transaction as the row. Both requirements met without any sync pipeline.
Why: A second store would need an outbox and sync workers, with lag and lost-event bugs to monitor.
4Load-test at peak
500 searches/s on a dedicated replica: p95 41 ms, CPU 55%. Partition by region if it grows.
Why: A dedicated replica keeps vector load away from the replicas that serve hotel pages.
5Write the decision and triggers
pgvector on a dedicated replica (~€6k/month). Revisit if: memory > 180 GB, filtered p95 > 80 ms, > 2,000 searches/s, or we need a feature Postgres lacks. Lina: "Checked. Nothing to add."
Why: The right answer changes with scale. Written triggers make the next decision happen on time, not after an incident.

Interactive simulation

Visual diagram

Search API

Postgres (+ pgvector)

Embedding model

Outbox / CDC stream

Sync worker

Dedicated vector DB

Click a component

Top: pgvector, one source of truth. Bottom: a dedicated vector DB fed by an outbox/CDC pipeline. The second path buys scale and features at the price of a sync system.

Analogy

Storing spices for a kitchenStoring vectors

Storing spices for a kitchenA drawer in the kitchenStoring vectorspgvector inside your Postgres
Storing spices for a kitchenEverything in one placeStoring vectorsOne transaction, one backup
Storing spices for a kitchenRenting a warehouse for cateringStoring vectorsA dedicated vector database
Storing spices for a kitchenVan and delivery scheduleStoring vectorsA sync pipeline between stores
Storing spices for a kitchenInventory list matching the kitchenStoring vectorsKeeping both copies consistent

Don't rent the warehouse for three jars. Don't run catering out of a drawer. Where the analogy breaks: spices don't change, but your data does, constantly, so every sync gap shows up as wrong search results.

Why it matters

Two real mistakes, in opposite directions

Too much, too early

200k reviews, a small dataset
Picked a separate service
Sync failed, reviews unsearchable
Deleted reviews kept appearing

A drawer would have done.

Too little, too long

120 million vectors
Same database as bookings
Index builds take a day
Checkout slowed at peak

Should have moved a year earlier.

Both teams skipped the same step: writing down their needs with real numbers. How many vectors? How much memory? Which filters? How fresh?

Writing that down takes an afternoon. Skipping it can cost months.

The first team paid for complexity they didn't need. The second team let search hurt the part of the business that makes money. A one-page requirements note would have saved both.

Check your understanding

Q1/3Reasoning

How much RAM do the raw vectors need for 5 million chunks with 768-dimensional float32 embeddings? (Answer in GB, ignoring index overhead; 1 GB = 10⁹ bytes.)

How confident are you?

Practice ladder

Step 1 · Trace

Trace a post-filtered search

Estimation Warm-up

The prototype searches the 60M-vector index for the top 40 nearest chunks overall, then keeps those for the guest's hotel.

Hotel Ribeira (Porto, 120 rooms) holds 18,000 chunks.
Pensão Azul (Lisbon, 8 rooms) holds 1,200 chunks.

Assume, roughly, that the top 40 for a generic question ("what time is breakfast?") are spread across hotels in proportion to their share of the index. Trace how many results each hotel can expect, and what the concierge does.

Step 2 · Complete

Finish the requirements table

Decision Practice

Lina's comparison has two rows missing. Fill in Filtering and Deletes for both options, with one line each.

Requirement	pgvector on a dedicated replica	Managed vector DB
Memory (≈105 GB)	Fits 256 GB with headroom	Fits, priced by usage
Filtering by hotel	___	___
Deletes (legal: immediate)	___	___
Hybrid (codes, names)	Postgres full-text next to vectors	Depends on product; often supported
Cost	≈ €6k/month	≈ €9k/month + sync pipeline

Step 3 · Solve

Photo similarity at 250M vectors

Design Practice

Maya wants "show me hotels with rooms that look like this photo". StayHub has 250M photo crops, each with a 768-dimension image embedding. Searches: up to 2,500 a second at peak, filtered by city. Photos are added in batches nightly; deletes can wait until the next day. The current Postgres replicas have 256 GB RAM.

Size it, choose where it lives, and say what you'd measure first.

Step 4 · Decide

Write the decision record

Decision Stretch

Write the decision record Karim will file for the 60M-vector concierge index. Use the form "We chose X over Y because…, accepting the risk of…", then list the triggers to revisit and who checks them.

Facts: ≈105 GB at 768-dim 16-bit; filtered recall@8 ≥ 0.95 on the smallest hotels with iterative scans; p95 41 ms at 500 searches/s; deletes must be immediate; replica ≈ €6k/month vs managed ≈ €9k/month + sync pipeline.

Explain it yourself

Your CTO asks: "Should we use Pinecone, Qdrant or just pgvector?" Explain how you'd decide and what would make you change your mind later.

0 words