A vector database stores embeddings (lists of floats produced by a model) and answers one question fast: "which stored vectors are closest to this query vector?" That is the retrieval step of RAG, semantic search, recommendations, and de-duplication. Interviewers probe it because it tests whether you understand that this is a different kind of query from anything a B-tree does: the answer is "nearest by distance", not "equal to" or "in range", and doing it exactly is expensive so real systems approximate. For a Backend / AI engineer the practical question is usually not "which vector database is best" but "do I need a separate system at all, or does pgvector inside the PostgreSQL I already run do the job".
text / image / row embedding model vector (dimension d = 1536, say)
"can I return my order?" --> (a neural network, fixed) --> [0.021, -0.113, 0.048, ... 1536 floats]
Training goal: inputs with similar MEANING land near each other in this space.
axis 2
^
| * "reset password"
|
| * "refund policy"
| * "how to get money back" <-- query "can I return my order?" lands here
|
| * "shipping times"
+-----------------------------------------> axis 1
(real spaces have hundreds or thousands of axes; no single axis has a human meaning)An embedding is just an array of numbers, but the model that produced it was trained so that geometric closeness tracks semantic similarity. That gives you two consequences worth stating in an interview. First, a vector is only meaningful relative to other vectors from the same model: you cannot compare a vector from model A with one from model B, and you cannot compare vectors of different dimensions. Second, "closeness" needs a definition, and there are three common ones (next section). The database does not understand meaning; it only does arithmetic on the numbers, so retrieval quality is decided mostly by the embedding model and the chunking strategy, not by the storage engine.
metric pgvector operator returns index opclass
------------------ ------------------ -------------------------- -----------------
L2 (Euclidean) <-> straight-line distance vector_l2_ops
inner product <#> NEGATIVE inner product vector_ip_ops
cosine distance <=> 1 - cosine similarity vector_cosine_ops
Smaller is always "closer" for ORDER BY ... ASC. That is why <#> returns the
negative of the inner product: the biggest dot product must sort first.
cosine similarity = (a . b) / (|a| * |b|) only the ANGLE matters, not the length
if every vector has length 1: cosine ranking == inner-product ranking == L2 rankingCosine cares about direction only, so a long document and a short one about the same topic still match; L2 also punishes differences in vector length. Many embedding APIs return vectors normalized to length 1, and for those the three metrics give the same ranking, so the cheapest one (inner product) is enough. That claim is provider-specific, so check your model's documentation instead of assuming it. The operator you use in the query must match the operator class used when the index was built, otherwise the planner cannot use the index and silently falls back to scanning every row. The next block computes all three metrics in plain Python so you can see they can disagree when lengths differ.
# plain Python, run locally with python3 (no libraries)
import math
docs = {
"refund policy": [0.9, 0.1, 0.0],
"how to get money back": [0.8, 0.3, 0.1],
"reset password": [0.1, 0.9, 0.2],
"shipping times": [0.0, 0.2, 0.9],
}
query = [0.85, 0.2, 0.05] # "can I return my order?"
def dot(a, b): return sum(x*y for x, y in zip(a, b))
def norm(a): return math.sqrt(dot(a, a))
def cosine_distance(a, b): return 1 - dot(a, b) / (norm(a) * norm(b))
def l2(a, b): return math.sqrt(sum((x-y)**2 for x, y in zip(a, b)))
def neg_inner(a, b): return -dot(a, b) # what pgvector's inner-product operator returns
# ... print a table, then rank with sorted(docs.items(), key=lambda kv: metric(query, kv[1]))
# Real output (python3 3.14, run locally):
# doc cosine_dist l2 neg_ip
# refund policy 0.0089 0.1225 -0.7850
# how to get money back 0.0098 0.1225 -0.7450
# reset password 0.6610 1.0368 -0.2750
# shipping times 0.8946 1.2021 -0.0850
# top-2 by cosine : ['refund policy', 'how to get money back']
# top-2 by l2 : ['how to get money back', 'refund policy']
# unit vectors, same ranking under IP and cosine: TrueLook at the two top rows: the same two documents win under every metric, but their order flips between cosine and L2 because the vectors have different lengths. After normalizing to unit length the rankings agree. In practice the lesson is to pick one metric that the embedding model was trained for, and use that operator and that operator class everywhere.
EXACT (no index) IVFFlat (clusters) HNSW (layered graph)
scan all N rows, compute build: k-means-like lists build: link each vector to
distance to each, sort query: find nearest `probes` near neighbours across layers
cost ~ N * d per query lists, scan only those rows query: greedy walk from top
recall = 100% knob: ivfflat.probes layer down, keep `ef_search`
needs data BEFORE building candidates
knob: hnsw.ef_search
Both ANN indexes trade RECALL (did I find the true top-k?) for speed and both
cost memory, build time and write work.-- PostgreSQL + pgvector (not run here; requires the extension installed)
CREATE EXTENSION IF NOT EXISTS vector;
CREATE TABLE chunks (
id bigserial PRIMARY KEY,
doc_id bigint NOT NULL,
content text NOT NULL,
embedding vector(1536) NOT NULL
);
-- Exact nearest neighbours (works with no index; always 100% recall):
SELECT id, content
FROM chunks
ORDER BY embedding <=> '[0.01, 0.02, ...]' -- query vector, 1536 numbers
LIMIT 5;
-- Approximate: HNSW index, cosine operator class (must match the <=> operator above)
CREATE INDEX ON chunks USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);
SET hnsw.ef_search = 100; -- higher = better recall, slower query
-- Alternative: IVFFlat, build AFTER loading representative data
CREATE INDEX ON chunks USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100);
SET ivfflat.probes = 10; -- higher = better recall, slower queryExact search is a full scan doing N distance computations of d floats each; that is perfectly fine for tens of thousands of rows and becomes the bottleneck somewhere beyond that, depending on hardware and dimension. ANN indexes avoid the full scan by only looking at candidates likely to be near the query. IVFFlat partitions the space into lists and searches the nearest few; HNSW keeps a multi-layer neighbour graph and walks it. The catch is recall: the result can miss a true neighbour. The toy below (pure Python, random unit vectors, a crude cluster index, NOT pgvector's real implementation or speed) shows the mechanism of the recall/work trade-off: more probes means more candidates scanned and higher recall.
# toy IVF-style index, plain Python, random.seed(7), 3000 random 16-d unit vectors, 30 lists
# Real output (python3 3.14, run locally; illustrates the trade-off, not pgvector performance):
# probes= 1 recall@10=0.28 avg vectors scanned=104 of 3000
# probes= 3 recall@10=0.54 avg vectors scanned=305 of 3000
# probes=10 recall@10=0.90 avg vectors scanned=1002 of 3000
# probes=30 recall@10=1.00 avg vectors scanned=3000 of 3000Probing every list degenerates to exact search, which is exactly what you would expect: the knob slides you between fast-and-approximate and slow-and-exact. Random data is also a harsh case (real embeddings cluster), so real recall numbers differ; measure recall on your own data by comparing ANN results with exact results for a sample of queries.
Query: nearest 10 chunks WHERE tenant_id = 42
ANN index returns its top candidates first, THEN the filter removes rows:
candidates (say 40) ... only 3 belong to tenant 42 ... result has 3 rows, not 10
Options (choose by tenant size and count):
* few big tenants : partition or partial index per tenant (CREATE INDEX ... WHERE tenant_id = 42)
* many small tenants : composite filter + iterative index scan (pgvector 0.8+, hnsw.iterative_scan)
* strict isolation need : Row-Level Security policy on tenant_id AND the app always sets the tenant
* tiny tenant : the B-tree on tenant_id narrows to a few hundred rows, so exact scan is fine-- PostgreSQL (not run here)
ALTER TABLE chunks ADD COLUMN tenant_id bigint NOT NULL DEFAULT 0;
CREATE INDEX chunks_tenant_idx ON chunks (tenant_id);
-- Row-Level Security: the database itself refuses cross-tenant rows
ALTER TABLE chunks ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON chunks
USING (tenant_id = current_setting('app.tenant_id')::bigint);
-- per request, inside the transaction/connection that serves that customer:
-- SET LOCAL app.tenant_id = '42';
-- SELECT id FROM chunks ORDER BY embedding <=> '[...]' LIMIT 10;Combining vector search with a WHERE clause is where naive designs break. A filter applied after an approximate index scan can leave fewer than LIMIT rows, or none, even though enough matching rows exist. Newer pgvector versions (0.8.0 and later) add iterative scans that keep pulling more candidates until enough survive the filter, but it is worth checking your installed version's behaviour. The other tool is structure: partitioning by tenant, or a partial index for a large tenant, means each index only contains one customer's vectors. Row-Level Security is the database-enforced safety net so a forgotten WHERE clause in application code cannot leak another customer's data; it protects correctness, it does not make the search faster.
<-> L2 distance, <#> negative inner product, <=> cosine distance. Smaller sorts first; the index operator class must match the operator you query with.lists and probes.vector(d) column next to the chunk text and its metadata.ORDER BY embedding <=> query LIMIT k using the index: HNSW walks its neighbour graph, IVFFlat scans the nearest lists, both returning approximate top-k.ef_search / probes, and rebuild or reindex when the data distribution changes.# 1. Reproduce the distance table locally (no libraries needed):
# save the Python from "Distance Metrics" as sim.py and run: python3 sim.py
# Change the query vector and watch which metric reorders the top results.
#
# 2. Storage arithmetic (Python one-liner):
# python3 -c "print(1536*4*1_000_000/2**30)" # GiB for 1M float32 vectors of 1536 dims
# Real output: 5.7220458984375
#
# 3. If you have PostgreSQL with pgvector installed, try the SQL above and compare:
# EXPLAIN SELECT id FROM chunks ORDER BY embedding <=> '[...]' LIMIT 5;
# - before creating the index (expect a sequential scan plus sort)
# - after creating the HNSW index (expect an index scan on the hnsw index)
# - after switching the query operator to <-> while the index uses vector_cosine_ops
# (expect the index to be ignored: operator and operator class no longer match)
# Plan text and timings depend on your data and version, so run it and read your own output.Q: Why use PostgreSQL + pgvector?
A: You keep embeddings in the same transactional database as the rest of your data, so you get joins, metadata filters, ACID updates, backups, replication and access control (including Row-Level Security) without running and syncing a second system. For a corpus in the range of hundreds of thousands to a few million vectors and a moderate query rate that is usually enough, and far simpler to operate. The trade-off is that vector search shares CPU, memory and I/O with your OLTP workload and does not scale out by itself the way a purpose-built vector engine can.
Q: What is an embedding?
A: A fixed-length array of floats produced by a model so that inputs with similar meaning get vectors that are geometrically close. It turns "find similar meaning" into "find nearest vector". The trade-offs are that it is lossy (nuance and exact keywords can be lost), only comparable to vectors from the same model, and costs storage proportional to its dimension.
Q: How would you scale vector search?
A: Roughly in order of cost. First fix the basics: an HNSW index that fits in RAM, the right ef_search, smaller vectors (a lower-dimensional model, halfvec, or quantization), and only the columns you need. Then move heavy reads to read replicas, then partition or shard by tenant or hash so each index is smaller, and only if that still fails move to a dedicated vector engine. Every step trades complexity or recall for capacity, and partitioning adds routing and a merge step for queries that span partitions.
Q: How would you isolate vectors between different customers?
A: The choices form a spectrum: a tenant_id column with Row-Level Security in one shared table (cheapest, needs careful testing), partitions or partial indexes per large tenant (better search locality), a schema or database per tenant (stronger isolation, more operational overhead), or a separate cluster for regulated customers. Whatever the layer, enforce the tenant in the database, not only in application code, and never cache retrieved chunks across tenants. The trade-off is isolation strength against cost and the number of indexes you have to maintain.
Follow-up: You filter by tenant_id and the query with LIMIT 10 returns only 3 rows. Why, and what do you do?
A: An approximate index returns its nearest candidates first and the filter is applied afterwards, so rows from other tenants consume the candidate budget. Options are a larger ef_search (works but costs latency), iterative index scans in newer pgvector versions, a partial index or partition for that tenant, or letting the B-tree on tenant_id narrow the rows and doing an exact scan when the tenant is small. Which one is right depends on tenant size distribution.
Follow-up: After bulk-loading 10 million new rows, recall from your IVFFlat index dropped. Why?
A: IVFFlat computes its list centres from the data present at build time; if the distribution changes, new vectors fall into lists that no longer represent them well, so the probed lists miss true neighbours. The usual fix is to rebuild the index after large data shifts and to choose lists for the final data size, or use HNSW, which incrementally adapts as vectors are inserted at the cost of a larger, slower-to-build index.
Follow-up: Why not always use approximate search, or always use exact search?
A: Exact search is the right answer while N is small or when a filter reduces the candidate set to a few thousand rows, since it has perfect recall and no index to maintain. Approximate search is needed when a full scan per query breaks your latency budget, and then you must accept that it can miss neighbours and validate recall. Choosing by measured latency and measured recall on your data is the defensible answer.
In a RAG system the vector store sits on the retrieval path, so its latency and recall show up directly in answer quality and response time. A pragmatic design keeps chunk text, metadata, tenant id and embedding in one PostgreSQL table, uses HNSW for the embedding column, and puts retrieval behind an API that always applies the tenant scope. Combine vector search with keyword search or metadata filters where exact terms matter, and evaluate retrieval separately from generation so you know which half is at fault. You then scale in steps: tune the index and memory, add read replicas, partition by tenant, and split out a dedicated vector service only when measured load justifies operating a second store and its ingest pipeline. The source of truth stays the relational data, and the embeddings are derived data that can be rebuilt from it.
Choosing pgvector buys operational simplicity, transactional consistency between documents and their vectors, and relational joins and filters, at the cost of sharing resources with your main database and having fewer scale-out and specialised features than dedicated vector engines. Choosing an ANN index buys latency at the cost of recall, memory, build time and slower writes; choosing exact search buys perfect recall at the cost of linear scan time. It is the wrong choice to use pgvector as the only store when you expect hundreds of millions of vectors with tight latency and heavy write throughput on the same instance that runs your transactions, and it is equally wrong to add a separate vector database for a 50,000-chunk corpus that a single indexed table handles comfortably. Decide with numbers: corpus size, dimension, queries per second, latency target and measured recall, not with the name of the technology.