# Vector columns with pgvector

> Enable pgvector in a SupaHost project, add vector and halfvec columns, pick a distance operator, and insert and read embeddings from SQL and supabase-js.

Source: https://supahost.app/docs/ai/pgvector · Updated 2026-11-11

`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:

```sql
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:

```sql
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](/docs/ai/embeddings) works.

## Vector types

| Type | Storage per dimension | Max dimensions in an index | Use it for |
|---|---|---|---|
| `vector(n)` | 4 bytes | 2,000 | The default |
| `halfvec(n)` | 2 bytes | 4,000 | Large tables or models above 2,000 dimensions; small accuracy cost |
| `sparsevec(n)` | Only non-zero values | 1,000 non-zero | Sparse embeddings from keyword-style models |
| `bit(n)` | 1 bit | 64,000 | Binary quantisation for very large sets |

`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

| Operator | Measures | Smaller means closer | Index operator class |
|---|---|---|---|
| `<=>` | Cosine distance (1 minus cosine similarity) | Yes | `vector_cosine_ops` |
| `<->` | Euclidean (L2) distance | Yes | `vector_l2_ops` |
| `<#>` | Negative inner product | Yes | `vector_ip_ops` |
| `<+>` | L1 (taxicab) distance | Yes | `vector_l1_ops` |

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](/docs/ai/vector-indexes).

```sql
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:

```sql
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:

```ts
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:

```ts
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:

```sql
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;
$$;
```

```ts
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](/docs/ai/semantic-search).

## Related

- [Generating embeddings](/docs/ai/embeddings)
- [Vector indexes (HNSW and IVFFlat)](/docs/ai/vector-indexes)
- [Postgres extensions](/docs/database/extensions)
- pgvector reference: [github.com/pgvector/pgvector](https://github.com/pgvector/pgvector)