Your Database Already Has a Vector Store: Semantic Search in PostgreSQL in 5 Steps With pgvector and Supabase

pgvector is a PostgreSQL extension that adds a native vector column type and nearest-neighbor operators — enable it in any Postgres database (Supabase's free tier included) to get semantic search without a separate vector service.

What you'll be able to do after this:

• Store embeddings in the same database that holds the rest of your application data — no extra service, no sync lag, JOIN-able with your users and metadata
• Build an HNSW index that keeps nearest-neighbor queries under 10ms at 1M+ vectors
• Write a complete semantic search pipeline — raw text to ranked results — entirely in PostgreSQL and Python

Why put vectors in Postgres?

Running a separate vector database doubles your operational surface area and breaks JOIN semantics: you can't filter by user ID, access control, or any column in your app database in a single query. pgvector keeps everything in one place — and on Supabase, you get a free tier with a REST API and Python client out of the box.

Step 1: Enable pgvector

On Supabase: Dashboard → Database → Extensions → toggle vector. Or raw SQL:

CREATE EXTENSION IF NOT EXISTS vector;

Step 2: Create a table with a vector column

CREATE TABLE documents (
  id        BIGSERIAL PRIMARY KEY,
  content   TEXT NOT NULL,
  embedding VECTOR(384)   -- match your embedding model's output dimension
);

Common dimensions: 384 for all-MiniLM-L6-v2, 768 for all-mpnet-base-v2, 1536 for OpenAI's text-embedding-3-small.

Step 3: Insert embeddings from Python

from sentence_transformers import SentenceTransformer
from supabase import create_client

model = SentenceTransformer("all-MiniLM-L6-v2")  # 384 dims, fast, free
sb = create_client(SUPABASE_URL, SUPABASE_KEY)

texts = [
    "Claude Code can spawn parallel subagents for independent tasks.",
    "Use HNSW indexes for fast approximate nearest-neighbor search.",
]
embeddings = model.encode(texts).tolist()

sb.table("documents").insert([
    {"content": t, "embedding": e}
    for t, e in zip(texts, embeddings)
]).execute()

Step 4: Build an HNSW index

CREATE INDEX ON documents
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);

HNSW is the recommended index type for 2026 — it builds a navigable graph so queries skip exhaustive comparison. Benchmarks show 95%+ recall on 1M-vector datasets with sub-10ms p99 latency on a standard Supabase Pro instance.

Step 5: Query

query_vec = model.encode("How do I run agents in parallel?").tolist()
# Using raw SQL via psycopg2:
cur.execute("""
    SELECT content,
           1 - (embedding <=> %s::vector) AS similarity
    FROM   documents
    ORDER  BY embedding <=> %s::vector
    LIMIT  5;
""", (query_vec, query_vec))

The <=> operator is cosine distance; <-> is L2; <#> is negative inner product. Pick <=> for text embeddings — it's distance-normalized by default.

Sources: Semantic Search — Supabase Docs · pgvector Extension — Supabase Docs · HNSW Indexes — Supabase Docs