pgvector RAG Embeddings

ELI5

Each requirement and document chunk gets a 1,536-number “fingerprint” that captures its meaning. To find similar text, Postgres compares fingerprints with cosine distance. An HNSW index keeps the search to milliseconds even with millions of rows, like a librarian who already knows which shelves are nearest to which.

Technical Deep Dive

Source: traceo-cat/migrations/009_pgvector_embeddings.sql:21-102, migrations/008_reconcile_schema.sql:334-348, traceo_mcp_server/traceo_mcp_server/config.py:115-116; traceo-cat origin/main 7d647ab, read 2026-10-25

Schema Additions (migration 009_pgvector_embeddings.sql)

CREATE EXTENSION IF NOT EXISTS vector;

ALTER TABLE requirements ADD COLUMN embedding vector(1536);

CREATE TABLE document_chunks (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  workspace_id UUID NOT NULL REFERENCES workspace(id) ON DELETE CASCADE,
  document_id TEXT NOT NULL,
  chunk_index INTEGER NOT NULL,
  content TEXT NOT NULL,
  embedding vector(1536),
  metadata JSONB NOT NULL DEFAULT '{}',
  UNIQUE (workspace_id, document_id, chunk_index)
);

Indexes

Both indexes use m=16, ef_construction=64. These are pgvector’s recommended starting defaults — higher m improves recall at index size cost; higher ef_construction improves recall at build-time cost. Query-time recall is tuned via SET hnsw.ef_search.

Dimensionality

vector(1536) matches OpenAI’s text-embedding-3-small / text-embedding-ada-002 output. AIConfig.embedding_model selects the model; switching to a different dimension requires a migration that drops and recreates both embedding columns and their indexes.

RLS

document_chunks uses both ENABLE and FORCE ROW LEVEL SECURITY, paired with policies:

• users_view_workspace_chunks — SELECT WHERE workspace_id = auth.workspace_id()
• members_create_chunks — INSERT with a workspace match and a "user" row whose role is in ('owner','admin','editor') (v1.0 wrongly listed a member role, which user_role does not have)
• members_update_chunks — UPDATE for the same three roles
• admins_delete_chunks — DELETE for owner/admin only

requirements.embedding inherits the requirements table’s RLS, which 008 also sets to ENABLE + FORCE, so the column needs no policy of its own. The default model is text-embedding-3-small (config.py:115-116).

erDiagram
  WORKSPACE ||--o{ REQUIREMENTS : scopes
  WORKSPACE ||--o{ DOCUMENT_CHUNKS : scopes
  REQUIREMENTS {
    vector embedding "vector(1536), HNSW cosine"
  }
  DOCUMENT_CHUNKS {
    text document_id
    int chunk_index
    vector embedding "vector(1536), HNSW cosine"
  }

Query Path

flowchart LR
  Q[Tool query string] --> EMB[embedding model]
  EMB --> VEC[vector 1536]
  VEC --> SQL["SELECT ... ORDER BY embedding <=> $1 LIMIT k"]
  SQL --> HNSW[HNSW cosine scan]
  HNSW --> RLS[RLS predicate]
  RLS --> ROWS[Top-k rows]

The <=> operator is cosine distance under vector_cosine_ops. RLS is applied as part of the same plan, so viewer-role callers already see only their workspace’s matches.

Key Terms

• HNSW → Hierarchical Navigable Small World; pgvector’s approximate-NN index.
• <=> → cosine distance operator (smaller = more similar).
• document_chunks → RAG-only table, separate from requirements, used by ask_documentation.

Q&A

Q: Which two RLS modes does document_chunks use? A: ENABLE and FORCE ROW LEVEL SECURITY, so even the table owner is filtered. The same pair is applied to requirements and the other domain tables in 008. The v1.0 claim that requirements was not forced described the never-deployed legacy 002.

Q: Can I store embeddings of a different dimension? A: Not without a migration. Both columns are vector(1536) and the HNSW index is bound to that dimension; mismatched inserts raise a Postgres error.

Q: What does ask_documentation do with these tables? A: It embeds the user question, runs an HNSW cosine search against document_chunks, takes the top-k passages within the caller’s workspace, and synthesises an answer with citations.

Examples

When semantic answers feel “almost but not quite” relevant, raise hnsw.ef_search for the session above pgvector’s default of 40. That trades a little latency for recall without rebuilding the index. No Traceo latency benchmark exists in the repo, so measure on your own data before quoting numbers.