pgvector + RAG cookbook

How to build retrieval-augmented generation on Mortar: store embeddings in a Mortar table, run similarity search, and combine with FTS for hybrid retrieval.

Mortar uses Postgres as its primary data store, so the pgvector extension is the canonical embedding store — no extra service to operate. For >10M vectors with sub-50ms latency you’ll outgrow pgvector and want a dedicated vector DB (Qdrant, Milvus); for the common “embed a few thousand support docs” case pgvector is plenty.

Enable pgvector

Mortar doesn’t enable pgvector by default — embeddings are an opt-in workload. On managed Postgres (Aliyun RDS, Tencent CDB, AWS RDS, Supabase) the extension is pre-installed; you just CREATE it. On self-hosted Postgres you’ll need to install the OS package (apt install postgresql-15-pgvector on Debian) and restart.

CREATE EXTENSION IF NOT EXISTS vector;

Verify with \dx vector in psql.

Add an embedding column

Mortar rows are JSONB, but vectors need a typed column for the index to work. Add a sibling typed column to app_rows scoped to your table — Mortar’s RLS policy doesn’t care about extra columns:

ALTER TABLE app_rows ADD COLUMN IF NOT EXISTS embedding vector(1536);

Pick the dimension to match your embedding model:

Switching models means re-embedding the whole table. Pick one and stick with it; if you must support multiple, add a second column.

Create the index

ivfflat is the most compatible index across pgvector versions. The lists parameter is roughly √(row_count) — for 10k rows use lists=100, for 1M use lists=1000:

CREATE INDEX idx_app_rows_embedding
  ON app_rows
  USING ivfflat (embedding vector_cosine_ops)
  WITH (lists = 100);

Choose the operator class to match your distance metric:

• vector_cosine_ops — cosine (most common, normalises out length)
• vector_l2_ops — Euclidean
• vector_ip_ops — inner product (use only if your vectors are already L2-normalised)

For pgvector ≥ 0.5 you can use hnsw instead — higher quality, more RAM. Recommended once your row count exceeds 100k:

CREATE INDEX ON app_rows USING hnsw (embedding vector_cosine_ops);

Insert embeddings via Mortar SDK

The Mortar TypeScript SDK exposes a from(table).insert(...) method. The embedding column lives outside the JSONB blob, so it goes through a separate raw-SQL bridge. SDK shape:

import OpenAI from 'openai'
import { createClient } from '@mortar/client'

const openai = new OpenAI({ apiKey: process.env.OPENAI_API_KEY })
const mortar = createClient({ url: process.env.MORTAR_URL, apiKey })

async function ingestDoc(title: string, body: string) {
  const { data } = await openai.embeddings.create({
    model: 'text-embedding-3-small',
    input: `${title}\n\n${body}`,
  })
  const vector = data[0].embedding // number[1536]

  await mortar.from('docs').insert({
    data: { title, body },
    embedding: vector, // SDK serialises as pgvector literal
  })
}

Switching to Aliyun Tongyi:

import { dashscope } from '@aliyun/dashscope-sdk'

const ali = dashscope({ apiKey: process.env.DASHSCOPE_API_KEY })
const { output } = await ali.textEmbedding.call({
  model: 'text-embedding-v2',
  input: { texts: [`${title}\n\n${body}`] },
})
const vector = output.embeddings[0].embedding

Same downstream mortar.from('docs').insert(...) — only the upstream provider call changes.

Similarity search

The <=> operator is cosine distance (matches vector_cosine_ops):

SELECT id, data->>'title' AS title
FROM app_rows
WHERE table_name = 'docs'
  AND tenant_id = current_setting('mortar.tenant_id')::uuid
ORDER BY embedding <=> $1
LIMIT 10;

$1 is the query vector (a vector(1536) literal). Mortar’s TypeScript SDK exposes this as:

async function search(query: string, k = 10) {
  const { data } = await openai.embeddings.create({
    model: 'text-embedding-3-small',
    input: query,
  })
  const queryVector = data[0].embedding

  return mortar.from('docs').similar({
    column: 'embedding',
    vector: queryVector,
    limit: k,
  })
}

const hits = await search('how do I deploy on a custom domain?', 5)
for (const hit of hits) console.log(hit.data.title)

Operator quick reference

The negative inner product is for ascending-order ranking; multiply by -1 to get the actual inner-product score.

Hybrid search (FTS + vector)

Vector search returns semantically-related docs but loses exact keyword matches (proper nouns, error codes, identifiers). Combining with FTS recovers them.

The canonical “Reciprocal Rank Fusion” recipe:

WITH
fts AS (
  SELECT id, ROW_NUMBER() OVER (
    ORDER BY ts_rank(to_tsvector('english', data->>'body'),
                     plainto_tsquery('english', $1)) DESC
  ) AS rank
  FROM app_rows
  WHERE table_name = 'docs'
    AND tenant_id = current_setting('mortar.tenant_id')::uuid
    AND to_tsvector('english', data->>'body') @@ plainto_tsquery('english', $1)
  LIMIT 50
),
vec AS (
  SELECT id, ROW_NUMBER() OVER (ORDER BY embedding <=> $2) AS rank
  FROM app_rows
  WHERE table_name = 'docs'
    AND tenant_id = current_setting('mortar.tenant_id')::uuid
  ORDER BY embedding <=> $2
  LIMIT 50
)
SELECT
  COALESCE(fts.id, vec.id) AS id,
  -- RRF score: 1/(k + rank).  k = 60 is the standard fudge factor.
  COALESCE(1.0 / (60 + fts.rank), 0) + COALESCE(1.0 / (60 + vec.rank), 0) AS score
FROM fts FULL OUTER JOIN vec USING (id)
ORDER BY score DESC
LIMIT 10;

$1 is the keyword string, $2 is the embedding vector. Both arrive from the SDK — keyword from user input, vector from embedding the same input string.

Cost model

Mortar bills database storage + queries; the embedding API calls are billed by the model provider. Rough per-1k-embeddings cost (2026 prices, USD):

For most apps the indexing cost is a one-off; query-time embedding is per request and dwarfs storage cost only above ~10k QPS.

Storage cost in Mortar’s pricing (docs/pricing.md): 1536-dim float32 vector = 6 KB; 100k docs = 600 MB; at ¥0.15/GB/month that’s < ¥0.10/month. Storage is rarely the dominant cost.

End-to-end examples

1. Build a docs FAQ bot

import OpenAI from 'openai'
import { createClient } from '@mortar/client'

const openai = new OpenAI()
const mortar = createClient({ url: process.env.MORTAR_URL, apiKey })

// Ingest
async function ingestAll(docs: { title: string; body: string }[]) {
  for (const d of docs) {
    const { data } = await openai.embeddings.create({
      model: 'text-embedding-3-small',
      input: `${d.title}\n\n${d.body}`,
    })
    await mortar.from('docs').insert({
      data: d,
      embedding: data[0].embedding,
    })
  }
}

// Ask
async function ask(question: string) {
  const { data } = await openai.embeddings.create({
    model: 'text-embedding-3-small',
    input: question,
  })
  const hits = await mortar.from('docs').similar({
    column: 'embedding',
    vector: data[0].embedding,
    limit: 5,
  })
  const context = hits.map(h => `# ${h.data.title}\n${h.data.body}`).join('\n\n')

  const resp = await openai.chat.completions.create({
    model: 'gpt-4o-mini',
    messages: [
      { role: 'system', content: `Answer from the docs below.\n\n${context}` },
      { role: 'user', content: question },
    ],
  })
  return resp.choices[0].message.content
}

2. Semantic search over notes (using Tongyi)

const ali = dashscope({ apiKey: process.env.DASHSCOPE_API_KEY })

async function embed(text: string) {
  const r = await ali.textEmbedding.call({
    model: 'text-embedding-v2',
    input: { texts: [text] },
  })
  return r.output.embeddings[0].embedding
}

async function semanticSearch(q: string) {
  const v = await embed(q)
  return mortar.from('notes').similar({
    column: 'embedding', vector: v, limit: 10,
  })
}

Swap to Zhipu by replacing the embed function — schema + query shape don’t change.

3. Hybrid keyword + vector for shopping search

async function shop(query: string) {
  const [{ data }] = await Promise.all([
    openai.embeddings.create({
      model: 'text-embedding-3-small',
      input: query,
    }),
  ])

  // Mortar exposes hybrid via raw SQL through a compute function.
  return mortar.compute('search_products').invoke({
    method: 'POST',
    body: JSON.stringify({ q: query, embedding: data[0].embedding }),
  })
}

Inside the search_products function run the RRF SQL above against the products table. Cache the per-query embedding for 5 minutes (Redis via mortar.cache) to drop repeat-query cost.

Caveats

• ivfflat recall vs speed: the index is approximate. Set SET ivfflat.probes = 10 (default 1) before query for higher recall at ~5× latency. Tune per workload.
• Re-embedding cost: changing models requires re-running the whole ingest. Tag the row with its model name in JSONB so you can identify what’s stale.
• PII in embeddings: vectors leak content — they’re closer to hashed-with-meaning than to one-way hashes. Treat as sensitive data; don’t ship to untrusted clients.
• Dimension cap: pgvector supports up to 16k dims; the ivfflat index supports up to 2k. For 3072-dim models (text-embedding-3- large) use hnsw instead.

See also

• full-text-search.md — Postgres FTS
• architecture.md § data feature
• pgvector upstream docs: https://github.com/pgvector/pgvector