Data & Infrastructure

Supabase pgvector Guide: Vector Search, HNSW Indexing & Cosine Distance in PostgreSQL

Build enterprise semantic search directly inside PostgreSQL using Supabase pgvector, HNSW indexing, and cosine distance operators.

August 17, 20263 min

Why a Single PostgreSQL Database Is All You Need

Using external vector databases (Pinecone, Qdrant) introduces data synchronization overhead, network hops, and separate billing.

Supabase pgvector keeps relational user data, permissions, and vector embeddings in a single ACID-compliant PostgreSQL database.

schema.sql

-- Enable pgvector extension
create extension if not exists vector;

-- Create documents table with 1536-dim embedding column
create table documents (
  id bigserial primary key,
  content text not null,
  metadata jsonb default '{}'::jsonb,
  embedding vector(1536)
);

-- Create HNSW index for ultra-fast cosine similarity search
create index on documents using hnsw (embedding vector_cosine_ops);

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