Vector search

• An embedding is a list of numbers that describes the meaning of a text or image. Your app gets it from its AI provider (for example OpenAI's text-embedding-3-small, 1536 numbers). Espobase stores and searches embeddings; it doesn't create them.
• A vector(1536) column holds one embedding per row. Similar meanings have a small distance, so the nearest rows are the best matches.
• pgvector is already set up in every project: no extension to install, no settings. It works in the SQL editor, the table editor (column type vector) and the AI builder.

In your project's SQL editor:

create table documents (
  id bigint generated always as identity primary key,
  content text not null,
  embedding vector(1536) not null
);

-- Fast similarity search (cosine distance)
create index on documents using hnsw (embedding vector_cosine_ops);

-- Who may read: row level security is on for every table
create policy "Anyone can read documents" on documents
  for select using (true);

Or ask the AI builder, for example: “a documents table with an embedding for semantic search”. It proposes the vector column and the similarity index for you to review.

A similarity search is a small SQL function. You create it once; your app calls it through the REST API.

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

The function runs as the calling user, so row level security applies: a signed-in user only finds the rows their policies allow.

import { createClient } from "@espobase/client";

const espobase = createClient(PROJECT_URL, PUBLISHABLE_KEY);

// Store: your app gets the embedding from its AI provider (1536 numbers).
await espobase.from("documents").insert({
  content: "How to reset your password",
  embedding: JSON.stringify(embedding),
});

// Search: the 5 most similar documents to the user's question.
const { data } = await espobase.rpc("match_documents", {
  query_embedding: JSON.stringify(questionEmbedding),
  match_count: 5,
});

Vectors travel as text such as [0.12,-0.03,…]. With the REST API directly, call POST /rest/v1/rpc/match_documents with the same JSON body.

• Distances: <=> cosine, <-> Euclidean (L2), <#> negative inner product. Use the index operator class that matches your queries (vector_cosine_ops, vector_l2_ops, vector_ip_ops).
• HNSW indexes support up to 2,000 dimensions.
• Building an index over many existing rows takes a while. Create the index first, or build large ones over a direct database connection.
• Embeddings count toward your plan's database storage (1536 dimensions ≈ 6 KB per row).