Add Semantic Search to Postgres in 5 Commands: pgvector and the Supabase vecs Client

TL;DR: pgvector extends Postgres with a native VECTOR column type; the Supabase vecs Python client turns create-collection, upsert, and cosine-similarity search into a 10-line script you can run against any Postgres instance.

What you'll be able to do after this:

• Store float embeddings in your existing Postgres database using a native vector column — no separate vector DB to operate
• Upsert embeddings with metadata and query by cosine similarity using the vecs Python client
• Filter similarity searches by metadata conditions so vector search and structured queries run together

Why pgvector instead of a standalone vector database?

ChromaDB and Pinecone are purpose-built, but they add a new service to operate and a new credential to manage. pgvector runs inside Postgres as an extension — your embeddings live in the same database as your application data, with regular Postgres transactions, row-level security, and backup semantics. On Supabase, pgvector is enabled on every project at no extra cost.

Step 1: Enable pgvector

On Supabase Cloud, run this once in the SQL editor (or it's already enabled):

CREATE EXTENSION IF NOT EXISTS vector;

For self-hosted Postgres:

# Ubuntu / Debian
sudo apt install postgresql-16-pgvector

Step 2: Install the vecs Python client

pip install vecs

vecs is Supabase's Python client for unstructured vector stores. It wraps pgvector's VECTOR column type and handles index creation automatically.

Step 3: Connect and create a collection

import vecs

# Supabase connection string: Settings → Database → Connection string
vx = vecs.create_client("postgresql://postgres:password@db.yourproject.supabase.co:5432/postgres")

# Create (or open) a collection — 1536 dims for text-embedding-3-small
docs = vx.get_or_create_collection(name="docs", dimension=1536)

get_or_create_collection is idempotent — safe to call on every startup.

Step 4: Embed your documents and upsert

from openai import OpenAI

client = OpenAI()

def embed(text: str) -> list[float]:
    return client.embeddings.create(
        input=text, model="text-embedding-3-small"
    ).data[0].embedding

documents = [
    ("doc-001", "Claude Code ships built-in OpenTelemetry telemetry.", {"source": "blog"}),
    ("doc-002", "pgvector adds VECTOR columns to Postgres.", {"source": "docs"}),
    ("doc-003", "vecs wraps pgvector for Python in 10 lines.", {"source": "docs"}),
]

vectors = [(id, embed(text), meta) for id, text, meta in documents]
docs.upsert(vectors=vectors)

Each record is a tuple: (id, embedding, metadata_dict). Metadata is plain JSON — any key/value pairs you want to filter on later.

Step 5: Create an index and query by similarity

# One-time: create an approximate nearest-neighbor index
docs.create_index()

# Find the 2 most similar docs, limited to source=docs
query_vec = embed("How do I add vector search to Postgres?")

results = docs.query(
    data=query_vec,
    limit=2,
    filters={"source": {"$eq": "docs"}},
)
print(results)
# ['doc-002', 'doc-003']

create_index() builds an IVFFlat index (cosine similarity by default). Call it once after bulk-loading data, not on every upsert.

The big picture

pgvector closes the gap between "I have embeddings" and "I need to search them" without adding a new service. Once your vectors are in Postgres, you can join them with your application tables, scope searches to a user's rows via metadata filters, and back everything up with your regular database backups.

Sources: Supabase vecs Python client docs | pgvector GitHub | Supabase AI & Vectors guide | DataCamp pgvector tutorial