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

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). For most apps you don't write this code at all — use the vectorEmbedding() mixin instead and the runtime embeds on write for you.

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). Set opclass directly to override (e.g. halfvec_cosine_ops for a half-precision column). Declaring kind: 'hnsw' on a non-vector column, on multiple fields, or on an expression throws at declaration time.

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()`

Most RAG apps want: write text → embedding gets generated automatically. The mixin adds the vector column, its HNSW index, and the re-embed behaviors in one line:

import { vectorEmbedding } from '@voltro/database'

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

Behaviour:

On INSERT, the runtime computes the embedding from body + writes it into the embedding column (override the column name with as).
On UPDATE of body, the embedding is recomputed.
The mixin also contributes the HNSW index (pass index: false to skip it, distance to pick the metric).

A query helper nearestNeighbours(queryString, k) embeds the string for you:

ctx.store.select('docs')
  .nearestNeighbours('how do mutations work?', 5)
  .all()

The runtime injects @voltro/ai's embed into the mixin's write hook — the embedding API calls are billed to your provider account and traced by @voltro/plugin-audit.

Backfilling pre-existing rows

The mixin only embeds rows written after it's in place. For rows that already existed (or after switching embedding model), seed them with the CLI:

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

#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