pgvector, explained twice
Every section below is written two ways: the plain-English version on the left, the engineering version on the right. The two worked examples are reference designs — how I would build each pattern on pgvector — not accounts of systems I have deployed. boost, my open-source CLI, is a real system, but its shipped retrieval core is BM25 with an optional local sqlite-vec backend. I have not deployed pgvector, or any vector database, in production. The evaluation practice in §07 is the part drawn directly from work I have shipped.
What is pgvector?
Simple The plain version
Normal database search matches letters. If you search for "service contract" and the document says "maintenance agreement," you get nothing back — not a single character lines up.
pgvector is an add-on for PostgreSQL that lets the database match on meaning instead. Each piece of text gets turned into a long list of numbers that acts like a coordinate — a position on a map of ideas. Things that mean similar things land near each other on that map.
So the question stops being "which rows contain this word?" and becomes
"which rows are closest to this point?" Same Postgres, same SQL, same
backups, same permissions — one new column type and a new kind of ORDER BY.
Technical The engineering version
pgvector is a Postgres extension adding a vector(n) type
(plus halfvec, bit, and
sparsevec), distance operators, and two ANN index access
methods — HNSW and IVFFlat.
- Storage: a dense
float4[]-backed type with a fixed dimensionality declared per column. - Operators:
<=>cosine,<->L2,<#>negative inner product,<+>L1. - Query shape:
ORDER BY embedding <=> $1 LIMIT k— the planner uses the ANN index only when the ordering expression matches the indexed operator class. - It's still Postgres: joins,
WHEREfilters, transactions, RLS, PITR, and read replicas all apply. That's the whole reason to pick it over a bolt-on vector store — one system of record instead of two that drift.
Available as a managed extension on RDS and Aurora PostgreSQL, so adopting it is a
CREATE EXTENSION and a migration, not a new piece of
infrastructure to staff and on-call.
Where the vectors come from
Simple A map of meaning
A separate model — not the database — reads a piece of text and hands back a list of numbers. Think of it as pinning that text to a spot on an enormous map.
On this map, "service contract paperwork" and "maintenance agreement documents" end up practically on top of each other. "Kubernetes deployment" is way over on the other side of the map.
pgvector's job is narrow and important: store those pins and find the ones nearest a given point, fast. It doesn't create them and doesn't understand them.
Technical Model output, not DB output
Embeddings come from an external model — Bedrock Titan, a self-hosted sentence-transformer, an OpenAI endpoint. The dimensionality is a property of that model and is baked into your DDL.
all-MiniLM-L6-v2→ 384 dims (cheap, CPU-friendly)amazon.titan-embed-text-v2→ 1024 dims (configurable)text-embedding-3-large→ 3072 dims
Two hard consequences. One: the model is now part of your schema contract — changing it means a full re-embed and backfill, so version the model name on the row. Two: query text must be embedded by the same model, or you're measuring distance between two unrelated coordinate systems and the results will be confidently wrong.
Normalize to unit length at write time and cosine distance reduces to inner product —
cheaper, and it makes 1 - distance a clean similarity score.
Setting it up
Simple One column, one operator
Turn the extension on, add a column that holds the list of numbers, and search with
ORDER BY using a special "distance" symbol instead of the usual
=.
The rest of the table is ordinary: titles, tags, timestamps, foreign keys. That's the selling point — your meaning-search results can be filtered and joined with everything else you already store, in one query.
Technical DDL & query contract
Dimensionality is fixed at column definition and enforced on insert. Keep the model identifier on the row so a re-embed can be rolled forward incrementally rather than as a stop-the-world migration.
ORDER BY … LIMIT k is the only shape the ANN index can serve.
A WHERE distance < 0.3 threshold alone will not use it —
Postgres has no way to bound the scan.
-- reference schema: a semantic index over a corpus of agent skills CREATE EXTENSION IF NOT EXISTS vector; CREATE TABLE skill_embedding ( id bigserial PRIMARY KEY, skill_slug text NOT NULL, registry text NOT NULL, -- which tap it came from chunk_kind text NOT NULL, -- 'description' | 'body' | 'trigger' content text NOT NULL, -- the model is part of the contract: pin it on the row model text NOT NULL, -- pin it on the row: 'amazon.titan-embed-text-v2', 'voyage-4', … embedding vector(1024) NOT NULL, updated_at timestamptz NOT NULL DEFAULT now(), UNIQUE (skill_slug, chunk_kind, model) ); -- ANN index. Cosine ⇒ vector_cosine_ops. Must match the query operator. CREATE INDEX skill_embedding_hnsw ON skill_embedding USING hnsw (embedding vector_cosine_ops) WITH (m = 16, ef_construction = 64); -- ordinary btree for the metadata predicates we filter on CREATE INDEX skill_embedding_registry ON skill_embedding (registry, chunk_kind);
-- $1 = query embedding (same model!), $2 = k SELECT skill_slug, registry, 1 - (embedding <=> $1) AS similarity FROM skill_embedding WHERE chunk_kind = 'description' ORDER BY embedding <=> $1 -- ← this line is what the HNSW index serves LIMIT $2;
| Operator | Distance | Operator class | Use it when |
|---|---|---|---|
| <=> | Cosine | vector_cosine_ops | Default for text embeddings. Magnitude-insensitive. |
| <-> | L2 / Euclidean | vector_l2_ops | Image embeddings, or when magnitude carries signal. |
| <#> | Negative inner product | vector_ip_ops | Vectors already unit-normalized — fastest path. |
| <+> | L1 / Manhattan | vector_l1_ops | Rare; sparse or count-like features. |
vector_l2_ops and querying with
<=> does not error. Postgres silently falls back to a
sequential scan — correct results, terrible latency, and no warning anywhere. It shows up
as a p99 regression weeks later. Assert the operator class in a migration test.
HNSW vs. IVFFlat
Simple Two ways to avoid checking everything
Comparing your query against every single row works fine at a few thousand rows and falls apart past that. Both index types are shortcuts that accept "very likely the closest" instead of "provably the closest."
IVFFlat divides the map into neighbourhoods, then only searches the few neighbourhoods nearest your query.
HNSW builds a layered network of shortcuts — start with long hops across the map, then progressively shorter ones, like an express train down to local stops. Faster and more accurate; costs more memory and takes longer to build.
Technical Recall/latency knobs
- HNSW — multi-layer navigable small-world graph. Build:
m(edges/node, 16 default),ef_construction(64). Query:SET hnsw.ef_search = 100— the recall dial. - IVFFlat — k-means partitions. Build:
lists≈ rows/1000 up to 1M rows, then √rows. Query:SET ivfflat.probes = 10. - IVFFlat requires representative data present before you build it — an index built on an empty table produces garbage centroids and quietly awful recall. HNSW has no such trap, which is why it's the default choice for pipelines that backfill continuously.
- Both are approximate. Recall is a tuning outcome you must measure, not a guarantee — see §07.
| HNSW | IVFFlat | |
|---|---|---|
| build time | Slow (graph construction) | Fast (k-means) |
| memory | High — graph should fit in RAM | Low |
| recall @ speed | Better across the curve | Good, degrades faster |
| incremental writes | Handles them well | Centroids drift; needs periodic rebuild |
| empty-table build | Fine | Broken — must have data first |
| pick it when | Default. Read-heavy, latency-sensitive. | Huge corpus, tight RAM, batch refresh. |
Semantic skill discovery, on Postgres
boost catalogs 100+ classified Git-hosted registries of AI-agent
skills, rules and workflows. A user types
boost search "keep my PRs from going stale" — no keyword in
that sentence appears in the skill named babysit-prs. This is
the synonymy problem from Fig 1, at registry scale.
Simple Why a hybrid score
Pure meaning-matching has a blind spot: exact names. Someone searching
jira-integration wants that skill, not the five
conceptually-similar ticketing skills.
So take the top ~50 by meaning, then re-rank with a small bonus for literal name and trigger-word hits. Best of both: recall from the vectors, precision from the keywords.
Technical Over-fetch, then fuse
Retrieve k=50 from the ANN index and fuse with a lexical signal
(ts_rank_cd or trigram similarity) before truncating to 10.
Over-fetching is what buys back the recall that approximate search gives up.
Postgres makes this a single query — the lexical index and the vector index live in the same table. With a separate vector store you'd be doing two round trips and reconciling IDs in application code.
For the record, boost does not do this in Postgres. It over-fetches roughly 60 candidates from BM25 — or from a local sqlite-vec store when embeddings are configured — and then re-ranks that shortlist with an LLM, falling back to the BM25 order when no model is available. The principle is the same: over-fetch, then apply a second, sharper signal.
# reference implementation — not boost's shipped code; boost retrieves with BM25 from pgvector.psycopg import register_vector import psycopg SEARCH_SQL = """ WITH ann AS ( SELECT skill_slug, registry, content, 1 - (embedding <=> %(q)s) AS vec_score FROM skill_embedding WHERE chunk_kind = 'description' AND (%(registries)s::text[] IS NULL OR registry = ANY(%(registries)s)) ORDER BY embedding <=> %(q)s LIMIT %(fetch_k)s -- over-fetch, then rerank ) SELECT skill_slug, registry, vec_score, ts_rank_cd(to_tsvector('english', content), plainto_tsquery('english', %(raw)s)) AS lex_score FROM ann ORDER BY (0.75 * vec_score + 0.25 * LEAST(ts_rank_cd(to_tsvector('english', content), plainto_tsquery('english', %(raw)s)), 1.0)) DESC LIMIT %(top_k)s; """ def search(conn: psycopg.Connection, query: str, *, registries: list[str] | None = None, top_k: int = 10) -> list[Hit]: """Semantic skill lookup. Embeds with the SAME model used at index time.""" register_vector(conn) # The most commonly reported production bug with this stack is embedding # the query with a different model than the corpus. Read the pin, don't # hardcode it. model = current_index_model(conn) q_vec = embed(query, model=model) # unit-normalized with conn.cursor(row_factory=dict_row) as cur: cur.execute("SET LOCAL hnsw.ef_search = 100") # recall dial cur.execute(SEARCH_SQL, { "q": q_vec, "raw": query, "registries": registries, "fetch_k": top_k * 5, "top_k": top_k, }) return [Hit(**r) for r in cur.fetchall()]
$ boost search "keep my PRs from going stale" 1. babysit-prs watches open PRs, nudges reviewers 2. pr-review-loop iterative review until green 3. stale-branch-audit flags branches with no recent activity → 0 of 3 share a keyword with the query. (illustrative output)
Reference design: retrieval for a document-intelligence pipeline
The second pattern is retrieval-augmented generation. In a document-intelligence pipeline — unstructured documents in, structured findings out — the model cannot be handed a 200-page document, so something has to decide which few paragraphs it sees. This section works through pgvector as that component.
Simple Open to the right page first
A language model has a limited amount it can read at once, and its accuracy drops as you stuff more in. Feeding it the entire document is both expensive and worse.
So: split the document into paragraph-sized chunks ahead of time, pin each one on the map, and when a question comes in, retrieve only the handful of chunks nearest to it. Then hand those to the model.
It's the difference between reading a whole manual and flipping straight to the right page — and because every chunk keeps its page reference, the answer can cite where it came from.
Technical Retrieval as a context filter
- Chunking dominates quality. Split on document structure, not a fixed character count — a table row severed mid-way embeds to noise.
- Keep provenance on the row — doc id, page, bounding box. Without it you can't cite, and an uncitable extraction is not reviewable.
- Filter before you rank. A document- or scope-level predicate in
the same
WHEREas the vector ordering is a correctness and access-control requirement, not an optimization. - Embedding text is still the source text. If a data-classification review says the source is confidential, the embeddings and the model that produced them fall inside that same boundary — which is what pushes deployments from a managed API toward Bedrock or a self-hosted model.
// Spring Boot + JdbcTemplate. pgvector is just another column type; // the scope/document predicate rides in the SAME query as the ranking. @Repository public class DocChunkRepository { private static final String RETRIEVE = """ SELECT page, bbox, content, 1 - (embedding <=> CAST(? AS vector)) AS score FROM doc_chunk WHERE doc_id = ? AND scope_id = ? -- access control, not an optimization ORDER BY embedding <=> CAST(? AS vector) LIMIT ? """; public List<Chunk> retrieve(UUID docId, String scopeId, float[] queryVec, int k) { String vec = toVectorLiteral(queryVec); // "[0.021,-0.114,...]" return jdbc.query(RETRIEVE, new Object[]{ vec, docId, scopeId, vec, k }, CHUNK_MAPPER); } }
k results, or fall back to a slow scan. Two fixes:
partial indexes per high-traffic scope, or partitioning
by scope/doc so each partition carries its own smaller HNSW index. Verify with
EXPLAIN (ANALYZE, BUFFERS) that you're getting an Index Scan and not
a Seq Scan — this is the most commonly reported "it worked in staging" surprise.
Proving retrieval actually works
Simple "Looks right" isn't a result
Because the index is approximate, it can quietly get worse — after a model swap, a parameter change, or a corpus doubling — and nothing crashes. The queries still return ten results. They're just the wrong ten.
The fix is a fixed list of questions with known correct answers — a golden set — that runs in CI. If the score drops, the build fails, the same as a broken unit test.
Technical Golden-set gates
- recall@k — is the known-correct doc in the top k? The headline number for a retrieval layer.
- MRR — reciprocal rank of the first correct hit. Rewards putting it at position 1, not 8.
- nDCG@k — graded relevance with positional discount, for when "correct" isn't binary.
- Gate on a delta against the recorded baseline, and use a significance test when comparing two retrieval configurations — a 2-point move on 40 queries is noise.
This is the harness I built for boost's retrieval engine, with one distinction worth keeping straight. The golden-set retrieval metrics — recall@k, MRR, nDCG@k — are enforced as CI gates, against absolute floors and a seeded 10,000-resample paired-bootstrap regression test that requires a drop to be both large and statistically significant. The ragas-methodology faithfulness score is deliberately not a merge gate: it runs as a scheduled, non-blocking monitor, because gating a merge on a non-deterministic, token-costing metric buys flakiness and a bill, not quality.
# reference test — the pgvector equivalent of the harness described above. # boost's own gate has no Postgres and no ef_search; it scores BM25 and sqlite-vec. import pytest GOLDEN = load_golden("golden/queries.jsonl") # query → expected doc id BASELINE = load_baseline("baseline.json") def recall_at_k(hits, expected, k): return float(expected in [h.doc_id for h in hits[:k]]) def reciprocal_rank(hits, expected): for i, h in enumerate(hits, start=1): if h.doc_id == expected: return 1.0 / i return 0.0 @pytest.mark.parametrize("ef_search", [40, 100, 200]) def test_recall_does_not_regress(conn, ef_search): """HNSW is approximate — recall is measured, never assumed.""" results = [search(conn, g.query, ef_search=ef_search) for g in GOLDEN] r_at_10 = mean(recall_at_k(h, g.expected_doc, 10) for h, g in zip(results, GOLDEN)) mrr = mean(reciprocal_rank(h, g.expected_doc) for h, g in zip(results, GOLDEN)) floor = BASELINE[str(ef_search)] assert r_at_10 >= floor["recall@10"] - 0.02, \ f"recall@10 regressed: {r_at_10:.3f} < {floor['recall@10']:.3f}" assert mrr >= floor["mrr"] - 0.02, f"MRR regressed: {mrr:.3f}"
Operating it
Most pgvector failures are not vector problems. They are ordinary Postgres capacity problems wearing a new hat — which is the argument for keeping vectors inside Postgres: the tooling and the runbooks already exist. The notes below are what to plan for, drawn from the extension's behaviour rather than from a deployment of my own.
Index builds
HNSW build time is dominated by maintenance_work_mem. If the
graph doesn't fit, the build spills to disk and takes hours instead of minutes. Raise
it for the session, use max_parallel_maintenance_workers, and
build CONCURRENTLY against a live table.
Storage & bloat
At 1024 dims a vector is ~4 KB — larger than most rows you're used to, so it's
TOASTed. Re-embedding rewrites every row and generates real bloat; plan the vacuum.
halfvec halves storage at a usually-negligible recall cost.
Re-embedding
Model changes are migrations. Write the new model's rows alongside the old
(model is in the unique key for exactly this), cut reads over
once the backfill and evals pass, then drop the old generation.
Read replicas
Vector search is read-heavy and CPU-hungry. Route it to replicas so a burst of similarity queries doesn't contend with transactional write traffic on the primary.
Observability
Emit distance-score distributions, not just latency. A shift in the score histogram is the earliest signal that an upstream embedding change broke retrieval — long before anyone files a "search feels worse" ticket.
Sensitive data
Embeddings inherit the classification of their source text and are partially invertible. They belong inside the same boundary — which is what drives a move from a managed embedding API to Bedrock or a self-hosted deployment.