Technology · Datastore

pgvector

Vector search inside PostgreSQL backs the memory service's hybrid retrieval and the capabilities model's data factory.

PostgreSQL extensionFAFOHive memory and code indexFAFO Core research functions
A memory observation, as stored by meaning · by exact identifier · in this project only
  • 1,024dimensions declared on the memory vector column, checked at the boundary

One row for meaning, identifiers and project

In the memory capability of FAFOHive I wanted one query to search by meaning, match exact identifiers and filter by project, so I kept vectors in PostgreSQL with pgvector, in the same row as the text and metadata they describe. Retrieval, filtering and audit are then one query. Each observation, a decision or fix captured from a coding session, carries an embedding beside a weighted full-text column, provenance fields and token-savings accounting. The weighted full text catches exact identifiers that embeddings blur.

Weighted full text
ABD
Dense, by meaning
BCA
Fused, then reranked
BACD
Bar lengths are illustrative. Rows found by both rankings rise; when the cross-encoder is saturated, the fused order stands as it is.

Embeddings come from a the multilingual embedding model model on a local embedding server. The code index stores chunk embeddings the same way, partitioned per index so I can add or drop one cleanly. The graph tools beside it are in A code explainer that is not allowed to invent.

Vectors as a typed column

In FAFO Core and Function Forge functions read and write tables they were not compiled against, so vectors had to be a first-class type in a schema-agnostic data layer. A resolver reads PostgreSQL's system catalogs at runtime and binds parameters by actual column type; for a vector column it reads the declared dimension from the type modifier, so an embedding of the wrong length fails at the boundary.

A vector column of declared length
the declared lengthbound
A shorter vectorrefused
Text where a vector belongsrefused

Research steps on the substrate

The research functions on the substrate rely on it. A validator scores candidate question-and-answer pairs with a deterministic accept, hold or reject decision over those vectors, and a rebalancing step finds near neighbours through an inverted-file cosine index. That index is approximate, and the bulk steps that use it never assume exact ordering from it.

Validator · deterministic
accept
hold
reject
Rebalancing · inverted-file cosine indexthe square is the pair being rebalanced; the ring holds candidates, not a ranking
Counts and positions are illustrative.

When an earlier research dataset turned up split between a working database and a large backup, with placeholders where content belonged, I had it recovered phase by phase under an independent check, and the original centroid computation was reconstructed and confirmed before anything was recomputed. The full account is in Research data forensics.

Where vectors belong

In the high-throughput inference work I kept vectors out of SQL entirely, in a GPU-native store tied to relational metadata. pgvector goes where the database is the right home for the vectors.

In the row · pgvectorRetrieval joined and filtered with everything elsememory observations, code-index chunks, research question-and-answer pairs text · metadata · provenance · embedding
Outside SQL · GPU-native storeThroughput dominateshigh-throughput inference work; vectors on the GPU, tied back to relational metadata vectors on the device · a key into the database

For me pgvector is a decision about where vectors belong.

pgvector is an open-source PostgreSQL extension used as published; the schemas, retrieval and data layer are mine.