Skip to content
AnvilBase

Vector Search

AnvilBase supports vector embeddings natively through pgvector. Because it’s a Postgres extension, your vectors live in the same database as the rest of your data — you can JOIN embeddings against application rows, filter them with RLS, and back them up with everything else. No separate vector database to run.

1. Enable pgvector

create extension if not exists vector;

Run this once per project via the SQL Editor or exec_sql.

2. Add a vector column

The Schema API and CLI accept the vector type directly. Pick a dimension that matches your embedding model (e.g. 1536 for OpenAI text-embedding-3-small, 768 for many open models):

documents.json
{
  "columns": [
    {"name":"id","type":"uuid","primary_key":true,"default":"gen_random_uuid()"},
    {"name":"content","type":"text","nullable":false},
    {"name":"embedding","type":"vector(1536)"}
  ],
  "enable_rls": true
}
Terminal window
anvilbase schema table create --project <id> --name documents --from ./documents.json

Or in SQL:

create table documents (
  id uuid primary key default gen_random_uuid(),
  content text not null,
  embedding vector(1536)
);

3. Add an index

For anything beyond a handful of rows, build an approximate-nearest-neighbor index. HNSW gives the best recall/speed trade-off:

-- cosine distance (most common for normalized embeddings)
create index on documents using hnsw (embedding vector_cosine_ops);


-- or IVFFlat (faster build, needs ANALYZE; choose lists ~ rows/1000)
create index on documents using ivfflat (embedding vector_l2_ops) with (lists = 100);

Operator classes: vector_cosine_ops (cosine), vector_l2_ops (Euclidean), vector_ip_ops (inner product).

StreamingDiskANN — the performance tier (diskann)

For large embedding tables, AnvilBase bundles pgvectorscale, Timescale’s DiskANN-backed index that scales further than pgvector’s built-in indexes. It’s enabled per project automatically (alongside pgvector), so you can build a diskann index directly:

-- StreamingDiskANN over cosine distance
create index on documents using diskann (embedding vector_cosine_ops);

When to reach for it:

  • Use diskann when your table is large (hundreds of thousands of rows and up) and you want high recall with low query latency without holding the whole index in RAM — DiskANN streams from disk and uses SBQ compression to keep the in-memory footprint small. It also builds incrementally as you insert.
  • Use hnsw (above) for small-to-medium tables where the index fits comfortably in memory — it’s simplest and has excellent recall/latency.
  • Use ivfflat when you need the fastest build time and can run ANALYZE, and approximate recall is acceptable.

If you enable it manually (e.g. in the SQL Editor), it depends on pgvector:

create extension if not exists vectorscale cascade;

Operator classes mirror pgvector: vector_cosine_ops, vector_l2_ops, vector_ip_ops. A diskann index accelerates the same distance operators (<=>, <->, <#>) — including the REST API’s order=embedding.cosine.[...] / order=embedding.l2.[...] vector ordering, so server-driven similarity queries get the index automatically.

Availability depends on the Postgres image. The AnvilBase image ships pgvectorscale; a stock Postgres without it still serves vector search via the hnsw/ivfflat indexes above.

4. Insert embeddings

Generate embeddings in your application, then write them as a bracketed array literal. Distance operators: <=> cosine, <-> L2, <#> negative inner product.

// TypeScript — embed with your model, then insert via the SDK
const embedding = await embed(content)          // number[] of length 1536
await db.from('documents').insert({
  content,
  embedding: JSON.stringify(embedding),          // '[0.01, -0.23, ...]'
})
# Python
emb = embed(content)                              # list[float]
db.table("documents").insert({
    "content": content,
    "embedding": str(emb),                        # '[0.01, -0.23, ...]'
}).execute()

5. Query by similarity

The cleanest way to expose similarity search to clients is a Postgres function you call over RPC — it keeps the vector math server-side and stays subject to RLS:

create or replace function match_documents(
  query_embedding vector(1536),
  match_count int default 5
)
returns table (id uuid, content text, similarity float)
language sql stable
as $$
  select
    d.id,
    d.content,
    1 - (d.embedding <=> query_embedding) as similarity
  from documents d
  order by d.embedding <=> query_embedding
  limit match_count
$$;

Call it from any client:

const queryEmbedding = await embed('how do I reset my password?')
const { data: matches } = await db.rpc('match_documents', {
  query_embedding: JSON.stringify(queryEmbedding),
  match_count: 5,
})
Terminal window
curl -X POST "http://localhost:39001/v1/rest/<project_id>/rpc/match_documents" \
  -H "apikey: $ANON_KEY" -H "Content-Type: application/json" \
  -d '{"query_embedding":"[0.01,-0.23, ...]","match_count":5}'

Hybrid search and filtering

Because it’s all SQL, you can combine vector similarity with ordinary predicates and full-text search in one query — and RLS still applies, so a user only matches documents they’re allowed to see:

select id, content, 1 - (embedding <=> $1) as similarity
from documents
where org_id = current_setting('app.current_user_id')::uuid   -- RLS-aligned filter
  and content ilike '%billing%'
order by embedding <=> $1
limit 10;

Tips

  • Normalize embeddings if your model isn’t already normalized, and use cosine distance for text.
  • Set hnsw.ef_search (or ivfflat.probes) per session to trade recall for latency: set hnsw.ef_search = 100;.
  • Keep the dimension in the column type (vector(1536)) so inserts of the wrong size fail loudly.
  • Embeddings count toward your project’s max_db_size_mb quota — they’re large.

Next: Migrations.