Vector columns

Vectors and RAG — embedding columns, HNSW indexing, nearestNeighbours queries, the vectorEmbedding() mixin, across all five dialects.

For RAG and semantic search, vectors live in the same table as the rest of your data, in the same transactions, with the same tenant scoping. No separate vector DB. No two-store consistency model.

Vector storage works on all five dialects — the float array is always persisted. The HNSW index is emitted on postgres (via pgvector); MariaDB exposes native VEC_DISTANCE_* operators so its nearest-neighbour queries are correct, and the other dialects compute distance by sequential scan. The rule across the board: portable result everywhere, index-accelerated on postgres.

Prerequisites

On postgres, the vector extension provides the VECTOR(n) type, the distance operators, and the HNSW access method. The framework emits it automatically — voltro migrate prepends CREATE EXTENSION IF NOT EXISTS vector; to the migration whenever any table declares a vector(...) column. MariaDB / MySQL ship vectors natively (no extension); MSSQL / SQLite store the bytes with no extra step.

Declaring a vector column

import { table, id, text, vector } from '@voltro/database'

const docs = table('docs', {
  id:        id(),
  body:      text(),
  embedding: vector(1536),
})

The number is the dimensionality, persisted on the column so the migration emitter can size the DDL (VECTOR(1536) on postgres / MariaDB / MySQL; VARBINARY(MAX) on MSSQL; BLOB on SQLite):

Provider	Model	Dimensions
OpenAI	`text-embedding-3-small`	1536
OpenAI	`text-embedding-3-large`	3072
Anthropic	(via Voyage) `voyage-3`	1024
Cohere	`embed-english-v3.0`	1024

Pick once. Changing dimensionality means rebuilding the entire column. vector(0) and non-integer dimensions throw at declaration time.

Generating embeddings

For registered PostgreSQL vector(...) and half-precision columns, pass a number array directly to the store. Ordinary insert, update, upsert, insertIgnore and bulk writes encode it as pgvector's bracketed scalar value; native array(...) columns remain PostgreSQL arrays. Query results, returned write rows and inline change images contain number arrays again, including vectors in eager-loaded relations. Nullable vectors remain null. Half precision still rounds according to the database's representation. Raw SQL is not schema-decoded: callers of raw own parameter encoding and result decoding. This CRUD contract does not prove HNSW index-plan fidelity.

import { embed } from '@voltro/ai'

const queryVec = yield* embed('how do mutations work?')
// ReadonlyArray<number> of length 1536 (or whatever your provider returns)

await ctx.store.update('docs', id, { embedding: queryVec })

embed is an Effect — it runs on the server only (the provider call lives in @voltro/ai, never a browser bundle). Embed explicitly before writing or searching; declaring a vectorEmbedding() mixin is not evidence that the runtime injects an embedder.

Dimensions and retrieval purpose

Both embed(text, config) and embedMany(texts, config) pass config.providerOptions to the SDK, including every split batch. Use the provider's native options: OpenAI accepts openai.dimensions; Voyage accepts voyage.inputType (query or document) and voyage.outputDimension on models supporting dimension selection. Check your installed provider SDK and model's supported values. These are requests, not a promise that every model supports them. expectedDimensions separately checks the actual result, without truncating or padding it. Invalid expected dimensions fail before provider resolution; wrong lengths, empty/non-finite vectors or a wrong batch count fail as AiError before returning vectors to the caller.

import { embed, embedMany, type EmbeddingProviderConfig } from '@voltro/ai'

const embeddingConfig: EmbeddingProviderConfig = {
  name: 'voyage', model: 'voyage-3-large', expectedDimensions: 512,
  providerOptions: { voyage: { inputType: 'document', outputDimension: 512 } },
}
const documents = yield* embedMany(['Mutations commit atomically.'], embeddingConfig)
const question = yield* embed('How do mutations work?', {
  ...embeddingConfig,
  providerOptions: { voyage: { inputType: 'query', outputDimension: 512 } },
})

Use the same model and dimension for stored documents and queries, but the appropriate retrieval purpose for each. mockDim only sizes mock vectors; it does not change a real provider. Effect interruption aborts the SDK call. Changing a model or dimension requires re-embedding stored documents, not just changing the query config. These options belong to the explicit helper calls; the backfill CLI does not accept this config. For semantic caching, provide its existing deps.embed callback with your chosen config.

The option names follow the OpenAI embedding SDK and Voyage embedding SDK.

ANN search

ctx.store.select('docs')
  .nearestNeighbours('embedding', queryVec, { distance: 'cosine' })
  .limit(5)
  .all()

Each result row carries a synthetic distance field. Available distance metrics:

Metric	Operator (pgvector)	When
`cosine` (default)	`<=>`	Normalised text embeddings — most common.
`l2`	`<->`	Euclidean. Image embeddings often need this.
`inner`	`<#>`	Inner product. Useful for some recommendation models.

The choice must match the index's opclass (see HNSW below), or the index can't accelerate the search.

On MariaDB / MySQL the same query compiles to VEC_DISTANCE_COSINE(...) / VEC_DISTANCE_EUCLIDEAN(...). On MSSQL / SQLite there's no portable distance operator — the query still returns rows (sequential scan) but unranked; the migrate-time warning flags that the dialect has no ANN acceleration.

HNSW index

Without an index, nearestNeighbours is a sequential scan — fine for ≤10k rows, painful beyond. Indexes are declared at the table level (there is no column-level .index() modifier). Add an HNSW index over the vector column:

const docs = table('docs', {
  id:        id(),
  body:      text(),
  embedding: vector(1536),
}).index('docsEmbeddingHnsw', ['embedding'], { kind: 'hnsw' })

With explicit tuning:

const docs = table('docs', {
  id:        id(),
  body:      text(),
  embedding: vector(1536),
}).index('docsEmbeddingHnsw', ['embedding'], {
  kind: 'hnsw',
  kindOptions: {
    hnsw: {
      m:              24,         // links per node — higher = better recall + more memory (default 16)
      efConstruction: 128,        // index build effort — higher = better quality + slower build (default 64)
      distance:       'cosine',   // selects the opclass; MUST match query distance (default 'cosine')
    },
  },
})

The distance metric selects the pgvector opclass (cosine → vector_cosine_ops, l2 → vector_l2_ops, inner → vector_ip_ops). Half-precision columns automatically select the corresponding halfvec_* class. An explicit opclass overrides that choice and must be an unquoted, optionally schema-qualified identifier. Declaring kind: 'hnsw' on a non-vector column, on multiple fields, or on an expression throws at declaration time.

voltro db plan / db apply preserve the access method, operator class and construction parameters. A same-name B-tree is not an HNSW index: changing the method, metric or construction settings plans a drop and rebuild, without deleting table rows. Allow for index-build time and reduced query performance while replacing an index. Omitted HNSW settings normalize to m: 16 and efConstruction: 64, so an unchanged index produces no new operation. Both table creation and later index additions use the same HNSW SQL rules; concurrent PostgreSQL index builds retain these properties too. Provision the vector extension before using the declarative planner on a fresh database.

The HNSW DDL is postgres-only; on MariaDB / MySQL / MSSQL / SQLite the index is skipped with a migrate-time warning (a btree on a vector is meaningless). MariaDB queries still run correctly via its native VEC_DISTANCE_* operators — just without index acceleration.

Per-query effort

ctx.store.select('docs')
  .nearestNeighbours('embedding', queryVec)
  .efSearch(80)                              // search effort (default: 40)
  .limit(10)
  .all()

Higher efSearch = better recall, slower query. Sweet spot is usually 40-100; benchmark on your data. (pgvector hnsw.ef_search.)

Tenant scoping + vectors

If the table has the tenant() mixin, vector searches stay scoped — they only consider rows in the caller's tenant. The runtime AND-merges the tenant filter before the ANN order/limit, so cross-tenant rows never enter the candidate set. No cross-tenant leakage via similarity search.

const docs = table('docs', {
  id:        id(),
  body:      text(),
  embedding: vector(1536),
})
  .with(tenant())
  .index('docsEmbeddingHnsw', ['embedding'], { kind: 'hnsw' })

// Cross-tenant searches IMPOSSIBLE — the runtime injects the tenant filter.

Auto-embedding via `vectorEmbedding()`

The mixin declares a vector column, its HNSW index and re-embed behaviors:

import { vectorEmbedding } from '@voltro/database'

const docs = table('docs', {
  id:   id(),
  body: text(),
}).with(vectorEmbedding({
  from:       'body',
  model:      'text-embedding-3-small',
  dimensions: 1536,
}))

The behaviors require an injected ctx.embed. The current runtime does not wire that injection; the declaration alone does not provide automatic embedding. Likewise the string-only nearest-neighbour descriptor does not resolve a query vector in the runtime. Use explicit embed / embedMany calls and the array-based query shown above. The model declaration is not forwarded by computeEmbedding; choose the model on the explicit helper. The mixin contributes the HNSW index (index: false skips it, distance selects the metric). Automatic runtime wiring remains a separate limitation.

Backfilling pre-existing rows

For existing rows, the CLI offers a separate batch helper:

voltro embeddings backfill docs --text body --vector embedding [--model text-embedding-3-small] [--batch 50] [--dry-run]

It selects rows whose vector column is empty (or, with --model-field/--model, embedded under a different model), embeds them in batches via @voltro/ai, and writes the vectors back. --dry-run reports what would be embedded without writing.

Hybrid search

For best results, combine vector similarity with full-text search (FTS):

import { hybridSearch } from '@voltro/database'

ctx.store.select('docs').use(hybridSearch({
  vector: { col: 'embedding', query: 'how to deploy' },
  fts:    { indexName: 'docsBody', query: 'how to deploy' },
  alpha:  0.7,                                // 0=pure FTS, 1=pure vector
})).limit(10).all()

The FTS clause narrows the candidate set (lexical recall); the vector clause ranks those candidates by embedding distance (semantic ordering). alpha weights how much the vector ranking dominates when the two rank signals are fused (Reciprocal Rank Fusion). Beats pure vector on most knowledge-base style retrievals.

Storage cost

A 1536-dim float32 vector is ~6 KB per row. With HNSW overhead it's roughly 9-10 KB.

10k docs → ~100 MB
1M docs → ~10 GB

For very large corpora, downcast to half-precision — vector(1536, { precision: 'half' }) emits pgvector's HALFVEC(n), half the bytes, marginally lower recall. (MariaDB / MySQL have no half type — the emitter upcasts to float32 there.)

When NOT to use postgres / pgvector

Billions of vectors. Postgres + pgvector tops out around 10-50M vectors with reasonable latency. Beyond that, Qdrant / Weaviate / Pinecone.
Frequent re-embedding of entire corpus. A separate vector DB is easier to wipe + rebuild than a column.
Multi-tenant where each tenant has their own embedding model. A vector column pins one dimensionality.

For most B2B SaaS, postgres + pgvector is more than enough + you get transactional + tenant-scoped + same-backup-line semantics for free.

Vector columns

#Prerequisites

#Declaring a vector column

#Generating embeddings

#Dimensions and retrieval purpose

#ANN search

#HNSW index

#Per-query effort

#Tenant scoping + vectors

#Auto-embedding via vectorEmbedding()

#Backfilling pre-existing rows

#Hybrid search

#Storage cost

#When NOT to use postgres / pgvector