# Vector databases

> A vector database stores id, embedding and metadata per row, answers filtered top-k queries through an ANN index, and adds what a library lacks: durable CRUD, filter planning, sharding with scatter-gather and replication.

Source: https://www.mlfoundry.in/learn/vector-databases  
Section: Generative AI › Retrieval-Augmented Generation (RAG)  
Level: core · 8 min read · updated 2026-10-21

**Vector database:** is a data system whose primary query is "the $k$ rows whose embedding is closest to this vector, among rows matching this filter". It stores each row as an id, one or more dense vectors, structured metadata (often called a payload) and usually the source text. It keeps an approximate nearest neighbour index over the vectors and provides the usual database guarantees around them: durable writes, updates and deletes, replication and horizontal scaling. Milvus (Wang et al., SIGMOD 2021) describes one purpose-built design; Pan, Wang and Li survey the field in "Survey of Vector Database Management Systems" (VLDB Journal, 2024).

In a RAG system (see **RAG architecture**) the vector database sits between the indexing pipeline and the retriever. Offline, documents are cut into chunks (**Document chunking**), embedded (**Embedding models**) and upserted. Online, the query is embedded with the same model and sent as a vector plus a filter such as tenant, language, date or access group. The returned chunks then go to **Reranking** or straight into the prompt. The same store backs **Semantic caching**, recommendation and deduplication.

An ANN library such as FAISS or hnswlib gives you only the index: an in-process structure that you build, save and query. A vector database wraps that index in a server. It adds a schema, persistence and crash recovery, metadata indexes, filtered search, concurrent writes, access control, backups and distribution. General-purpose databases have converged on the same feature set: PostgreSQL with pgvector, Elasticsearch and OpenSearch with dense-vector fields. Which one to use depends far more on your existing stack, data size and filter patterns than on any single benchmark.

## What is stored, and how it is compared

$$
\cos(q,x)=\frac{q\cdot x}{\|q\|\,\|x\|},\qquad \|q-x\|^2=2-2\cos(q,x)\ \text{ when }\|q\|=\|x\|=1
$$

- $q,\;x$: query and stored embedding, from the same model and version
- $\|q-x\|^2$: squared L2 distance; for unit vectors it ranks rows exactly like cosine and inner product

Every collection fixes a dimension and a metric at creation time, and the metric must be the one the embedding model was trained with. Normalising vectors at write and query time makes the three common metrics interchangeable. Memory is dominated by the vectors: $N\cdot d\cdot b$ bytes for $b$ bytes per component, plus the index (about $2M\times4$ bytes per vector for HNSW layer-0 links; see **Approximate nearest neighbor search (HNSW, IVF, PQ)**).

> Figure: The two paths through a vector database. Writes: a record (id, 768-d vector, metadata, text) is logged in the WAL, lands in a small growing segment that is searched by flat scan, and is sealed into a segment with an ANN index when the buffer fills; deletes are tombstones until compaction rewrites the segment. Reads: a vector plus filter goes to a coordinator, every shard runs filter + ANN for its own top-10, and the coordinator merges 3 × 10 candidates into the global top-10.

## Metadata filtering

Filters are where vector search and databases collide. An ANN index orders rows by distance only, and the filter must be applied somewhere. Let selectivity $s$ be the fraction of rows that pass. There are three basic plans:

1. **Post-filter.** Ask the index for $k'$ nearest rows, then drop those that fail the filter. About $s\cdot k'$ survive, so returning $k$ rows needs $k'\gtrsim k/s$. A 1% filter with $k=10$ needs about 1,000 candidates.
2. **Pre-filter.** Use a metadata index (B-tree, inverted index, bitmap) to get the matching ids, then compute exact distances for all of them. Cost is $s\cdot N$ distances and recall is 1. This is cheap when $s\cdot N$ is small and hopeless when it is large.
3. **In-filter (filtered traversal).** Check a filter bitmap while walking the index and only accept matching rows, continuing until $k$ are found. On IVF this means probing more lists; on HNSW it means skipping non-matching nodes. Very selective filters can strand a graph search in regions with no matches, so some engines add extra edges per filter value or fall back to brute force.

$$
\mathbb E[\text{rows returned}]\approx\min\!\big(k,\;s\cdot k'\big)\quad\Longrightarrow\quad k'\ge\frac{k}{s}\ \text{ for a full page}
$$

- $s$: selectivity: fraction of rows passing the filter, assumed independent of vector position
- $k'$: candidates fetched from the ANN index before filtering (post-filter)

Real engines combine these under a query planner that estimates $s$ from metadata statistics. They pre-filter with an exact scan when few rows match, filter during traversal otherwise, and skip filtering when everything matches. If the filter correlates with vector position (for example, all French documents cluster together), the independence assumption breaks in either direction, which is another reason to measure.

> Figure: Top-10 WHERE filter over 1,500 vectors with an IVF index (32 lists, nprobe = 4), 30 queries, against the exact filtered top-10. Default (10% selectivity, post-filter, 1× over-fetch): only 0.97 rows come back and recall@10 is 9.7%, as min(k, s·k′) = 1 predicts. 5× over-fetch gives 4.80 rows (48.0%), 20× gives 10 rows (94.0%). In-filter returns 10 rows at 94.0% for 49.7 distances; pre-filter is exact for 137. At 1%, in-filter keeps 95.3% recall and pre-filter needs 14 distances; at 20% the planner switches to in-filter (99.0% for 70.9 instead of 301).

## Writes: CRUD and index maintenance

ANN indexes are built for reads. IVF needs trained centroids, PQ needs trained codebooks, and HNSW inserts are expensive and deletes do not shrink the graph. Databases therefore borrow the log-structured design. Writes are appended to a write-ahead log for durability and buffered in a small mutable segment that is searched by brute force. When the buffer fills, it is sealed and an index is built for it in the background. A query searches every segment and merges the results.

A delete marks the id in a tombstone bitmap that every search treats as a filter. An update is a delete plus an insert, because the old vector sits inside an index structure. Compaction later merges small segments and drops tombstoned rows, which also restores recall. Graphs with many deleted nodes route searches through dead ends. Because a new row reaches the index asynchronously, systems expose read consistency as a setting. Milvus, for example, offers Strong, Bounded staleness, Session and Eventually levels, trading freshness for latency.

## Scaling out: shards and replicas

A collection larger than one machine is split into shards, usually by hashing the id. A query is scattered to every shard; each returns its local top-$k$ and the coordinator merges $S\cdot k$ candidates. Each shard must return $k$, not $k/S$, because all of the true top-$k$ may live on one shard. Latency is set by the slowest shard, so p99 grows with shard count. Replicas of each shard add read throughput and availability. Partitioning by tenant instead of by hash turns a tenant filter into routing: one shard, no wasted work, and per-tenant deletes become dropping a partition.

**pgvector as a concrete reference (PostgreSQL extension; as of the 0.8 releases, 2024)**

- **Distance operators:** `<->` L2, `<#>` negative inner product, `<=>` cosine distance; `ORDER BY embedding <=> $1 LIMIT 10` triggers the ANN index.
- **Index types:** `ivfflat` (needs data before building; `ivfflat.probes` defaults to 1) and `hnsw` (added in 0.5.0; `m` = 16, `ef_construction` = 64, `hnsw.ef_search` = 40 by default).
- **Filtering caveat:** The index scan is approximate and the `WHERE` clause is applied to its output. The documentation's own example: a condition matching 10% of rows with `ef_search` = 40 leaves about 4 rows. 0.8.0 added iterative index scans to keep fetching.
- **Transactions:** Rows, metadata and vectors share PostgreSQL's MVCC, WAL, backups and replication, so an upsert is just `INSERT … ON CONFLICT`.

## Worked example: sizing 10M chunks

10M chunks × 768-d embeddings, HNSW with M = 16 (layer-0 links 2M × 4 B), before metadata and text

| Item | Formula | Size |
| --- | --- | --- |
| float32 vectors | 10⁷ × 768 × 4 B | 30.72 GB |
| HNSW layer-0 links | 10⁷ × 32 × 4 B | 1.28 GB |
| Total per copy | 30.72 + 1.28 | 32.0 GB |
| 4 hash shards | 32.0 / 4 | 8.0 GB per shard |
| 2 replicas of everything | 32.0 × 2 | 64.0 GB cluster RAM |
| int8 scalar quantization | 10⁷ × 768 × 1 B | 7.68 GB of vectors |

The vectors, not the graph, dominate. Scalar quantization to int8 cuts them by 4×, or PQ by 32× or more, at some recall cost that a re-ranking step on full vectors (kept on disk) recovers. Metadata and the source text often add as much again, so many deployments keep the text in a separate store and return only ids.

```sql
CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE chunks (
  id        text PRIMARY KEY,          -- "doc-42#3": document id + chunk number
  tenant    text NOT NULL,
  year      int,
  body      text,
  embedding vector(768)
);
CREATE INDEX ON chunks USING hnsw (embedding vector_cosine_ops) WITH (m = 16, ef_construction = 64);
CREATE INDEX ON chunks (tenant, year);  -- metadata index for the planner

-- upsert = delete + insert under the hood; same statement for new and changed chunks
INSERT INTO chunks VALUES ($1, $2, $3, $4, $5)
ON CONFLICT (id) DO UPDATE SET body = EXCLUDED.body, embedding = EXCLUDED.embedding;

SET hnsw.ef_search = 100;
SET hnsw.iterative_scan = relaxed_order;  -- keep scanning until LIMIT rows pass the filter
SELECT id, body, embedding <=> $1 AS cosine_distance
FROM chunks
WHERE tenant = 'acme' AND year >= 2022
ORDER BY embedding <=> $1
LIMIT 10;
```

A filtered, tenant-scoped chunk store in PostgreSQL + pgvector (syntax as of pgvector 0.8; not run here)

> **Silent short pages:** Post-filtering never errors. It just returns fewer than $k$ rows, or the wrong ones, and the LLM answers from whatever came back. Log the number of rows returned per query, test with your most selective real filters (one tenant, one week, one access group), and compare against an exact filtered scan. In the figure, a 1% filter with 20× over-fetch still returns only 1.93 of 10 rows.

> **Re-embedding is a migration:** Vectors from different embedding models, or different versions of one model, are not comparable. Changing the model means re-embedding every row into a new collection or column, building its index, and switching reads over, much like a schema migration. Store the model name and version with each collection.

**Strengths**

- One query combines semantic similarity with exact metadata constraints and access control.
- Durable upserts and deletes keep the index in step with the source documents.
- Sharding and replicas scale to billions of vectors and high QPS.

**Limitations**

- Recall becomes a property of the plan: filters, segment count and tombstones all move it.
- Fresh writes reach the ANN index asynchronously; strong consistency costs latency.
- RAM-resident indexes are expensive; quantized or disk-based indexes trade recall or latency for cost.

**Question:** A filter matches 2% of rows. With post-filtering, about how many ANN candidates must you fetch to expect 10 results?

- A. 10
- B. 50
- C. 500
- D. All rows

**Answer:** C. 500 — About $s\cdot k'$ candidates survive, so $k'\ge k/s=10/0.02=500$. At that point a pre-filtered exact scan over the matching 2% may be cheaper, which is what query planners check.

**Question:** A collection is hash-sharded 4 ways and the query asks for top-10. What must each shard return for the merged result to equal an unsharded search?

- A. Its top-3 (10 / 4, rounded up)
- B. Its top-10
- C. Its top-40
- D. Only rows above a fixed similarity threshold

**Answer:** B. Its top-10 — All ten true neighbours could sit on one shard, so each shard must return $k=10$. The coordinator merges $4\times10=40$ candidates and keeps the best 10. Returning more than $k$ per shard adds nothing.

**Key points**

- A row is id + vector(s) + metadata (+ text); the metric and dimension are fixed per collection and must match the embedding model.
- Filters: post-filter needs $k'\ge k/s$ candidates; pre-filter costs $s\cdot N$ exact distances; in-filter checks a bitmap during traversal. Planners choose by estimated selectivity.
- Writes go WAL → growing segment (flat scan) → sealed, indexed segment; deletes are tombstones until compaction; updates are delete + insert.
- Hash shards: scatter the query, each shard returns its own top-k, merge S·k; tenant partitioning turns filters into routing.
- Memory is mostly vectors (N·d·4 B); quantize, and re-rank with full vectors, to cut it.

---

From MLFoundry (https://www.mlfoundry.in). The web page adds interactive figures and diagrams.
