Vector columns with pgvector

pgvector adds a vector type to Postgres, plus operators that measure how far apart two vectors are. With it, an embedding becomes an ordinary column, and "find the 10 most similar rows" becomes an ordinary query you can combine with filters, joins and Row Level Security.

Enable the extension

Run this once per project, in Studio's SQL editor or as a migration:

create extension if not exists vector with schema extensions;

select extversion from pg_extension where extname = 'vector';

Installing into extensions keeps the type and its functions out of public. The Data API's search path includes extensions, so vector resolves in ordinary SQL. Inside functions that set search_path = '', write extensions.vector and operator(extensions.<=>) in full, as the examples on this page do.

Add a vector column

The column's dimension must match your embedding model exactly. A model that returns 1536 numbers needs vector(1536); a 384-dimension model needs vector(384).

For a recipe app:

create table public.recipes (
  id bigint generated always as identity primary key,
  user_id uuid not null default auth.uid() references auth.users (id),
  title text not null,
  body text not null,
  is_public boolean not null default false,
  embedding extensions.vector(1536)
);

alter table public.recipes enable row level security;

create policy "Read own or public recipes"
  on public.recipes for select
  to authenticated
  using (is_public or (select auth.uid()) = user_id);

Leaving embedding nullable lets you save the row first and fill the vector a moment later, which is how the embedding Edge Function works.

Vector types

halfvec is worth considering on SupaHost because database size and RAM are shared across all projects on the instance. Halving storage also halves the size of the index.

Distance operators

If you're unsure, use cosine distance. Most text embedding providers normalise their vectors to length 1, and for normalised vectors inner product gives the same ranking as cosine but is slightly cheaper to compute. Whichever you choose, use the same operator in queries and in the index, or the index won't be used. See Vector indexes.

select '[1, 0, 0]'::extensions.vector operator(extensions.<=>) '[0.8, 0.6, 0]'::extensions.vector;
-- 0.2 (cosine similarity 0.8)

Insert and update vectors

From SQL, write a vector as a bracketed list cast to the type:

update public.recipes
set embedding = '[0.0123, -0.0456, ...]'::extensions.vector
where id = 7;

From supabase-js, pass a plain array of numbers. The Data API hands it to Postgres, which parses it into a vector:

import { createClient } from '@supabase/supabase-js'

const supabase = createClient(
  'https://app-one.acme.supahost.space',
  process.env.SUPABASE_SERVICE_ROLE_KEY!,
  { auth: { persistSession: false } }
)

const embedding: number[] = await embed(recipe.body) // 1536 numbers from your provider

const { error } = await supabase
  .from('recipes')
  .update({ embedding })
  .eq('id', recipe.id)

This example uses the service_role key because it runs on a server (in practice, inside an Edge Function). Don't compute embeddings in the browser: it would expose your provider's API key.

A vector with the wrong number of dimensions is rejected with expected 1536 dimensions, not 384. That usually means the column and the model don't match.

Reading vectors back

Selecting an embedding column through the Data API returns it as a string like "[0.0123,-0.0456,...]". You rarely need the raw numbers in the client, and 1536 floats per row add a lot of egress. Leave the column out of select() lists:

const { data } = await supabase.from('recipes').select('id, title, is_public')

If you do need it, JSON.parse(row.embedding) turns the string into an array.

Query by similarity

The Data API can't order rows by a vector operator directly, so similarity search goes through a database function called with rpc(). The short version:

create or replace function public.similar_recipes(recipe_id bigint, match_count int default 5)
returns table (id bigint, title text, distance float8)
language sql
stable
security invoker
set search_path = ''
as $$
  select r.id, r.title, r.embedding operator(extensions.<=>) src.embedding as distance
  from public.recipes r,
       (select s.embedding from public.recipes s where s.id = recipe_id) src
  where r.id <> recipe_id
    and r.embedding is not null
  order by r.embedding operator(extensions.<=>) src.embedding
  limit match_count;
$$;

const { data } = await supabase.rpc('similar_recipes', { recipe_id: 7, match_count: 5 })

This is a "more like this" query that uses a stored embedding, so no embeddings API call is needed. For searching by a typed query, see Semantic search.

Related

• Generating embeddings
• Vector indexes (HNSW and IVFFlat)
• Postgres extensions
• pgvector reference: github.com/pgvector/pgvector