Vector databases and selection criteria

Read these first

• Vector indexes

What you will know

• Justify pgvector for a real workload, or justify moving off it

  A decision record should tie corpus size, query rate, filter selectivity, SLOs, staffing, and failure behavior to the choice.

• Measure the scale at which your choice stops working

  Include load-test results for index construction, peak resident memory, recall and latency, and rebuild duration; a single happy-path query benchmark is insufficient.

• Inventory what you have to build yourself in each option

  The hidden work often includes sparse-dense fusion, metadata filtering semantics, backups, consistency guarantees, and repair tooling.

• Migrate an index without downtime

  Use shadow writes or dual reads, verify recall and counts, then switch traffic with a rollback path while old indexes remain usable.

• Run an operational readiness drill before adopting a vector database

  The team should demonstrate diagnosis, capacity recovery, backup restoration, upgrades, and failure handling before putting the service on the critical path.

Chapters

1. 1

   Vectors that live in the same transaction as the rows they describe

   When embeddings sit in a table beside the business data, an insert and its vector commit or fail together, a filter is an ordinary SQL predicate over columns you already index, and there is one backup, one replica story and one on-call rotation. That coherence, not raw query speed, is the reason to reach for pgvector first on a stack that already runs Postgres.

   Read chapter
2. 2

   Rebuilds becoming maintenance events is the signal, not the vector count

   No magic number at one million or three million vectors exists; what actually breaks is operational. An HNSW build that takes hours, holds a large working set in the same memory your OLTP queries need, and cannot be repeated inside a maintenance window has turned your primary database into a batch job. Learn to name that threshold in terms of build duration, peak resident memory and rebuild frequency instead of corpus size.

   Read chapter
3. 3

   A load test that supports a decision measures the build, not just the query

   One happy-path latency benchmark proves nothing, because the query path is the cheap part. Measure index construction time and peak resident memory during construction, recall and p95 under concurrent write load, and how long a rebuild takes on production-sized data. Then write the decision record that ties corpus size, query rate, filter selectivity, SLOs, staffing and failure behaviour to whichever store you picked.

   Read chapter
4. 4

   Each option hands you a different list of things to build yourself

   On pgvector you assemble sparse-dense fusion, per-index tuning and sharding. On a managed vector store you rebuild what Postgres gave away for free: joins and transactional consistency with the business data, filtering semantics you can reason about, a backup that restores in step with your primary, and repair tooling for when the two drift. Teams routinely underestimate the second list, having only ever noticed the first.

   Read chapter
5. 5

   A naive dual-write replicates inserts and quietly loses deletes

   Cutting 40M vectors over means shadow writes plus a historical backfill, and the skeleton is right, but removals and updates take paths the insert hook never sees: cascade deletes, bulk purges, retention jobs. Fixing that still leaves the corrected plan three ways to fail: a backfill row overwriting a newer live write unless the two streams are versioned or ordered, an acceptance check that passes because top-k overlap was measured on easy queries rather than the real query mix, and an old index that stops receiving writes at cutover, which makes rollback nominal. Agree the acceptance criteria before you start — a minimum top-k overlap against the old store, a p95 bound, and a row-count check — and keep the old index live, written to and reachable so the switch has a way back.

   Read chapter
6. 6

   Rehearse the failures before the new store is on the critical path

   Adopting a second datastore means somebody has to diagnose it at 3am. Before any retrieval traffic depends on it, the team should demonstrate a restore from backup, a version upgrade, recovery from an out-of-capacity condition, and what the application does while the store is unreachable. A drill that nobody on the rotation can complete is itself the answer to whether you should adopt it.

   Read chapter