pgvector
Postgres extension that adds the `vector` type and distance operators (`<->` for L2, `<=>` for cosine, `<#>` for negative inner product, `<+>` for L1). Lets you do RAG without deploying a dedicated DB, the same Postgres serving your app also serves your embeddings. Available on Supabase, to enable once (dashboard or `create extension vector with schema extensions;`). Decision threshold: stay on Postgres + pgvector while you have a few million vectors. The vector lives in the same table as the row, so when a price changes or a product is deleted, the vector follows in the same transaction. Towards tens of millions of vectors, the approximate nearest neighbor (ANN) index that makes search fast eats the memory of the server, and transactional queries, a checkout for instance, slow down: the AI starves your main database. A dedicated vector database (Pinecone, Weaviate, Qdrant) protects it, at the price of two systems to keep in sync: forgotten deletes, delays, ghost vectors.
Strengths
- Single DB for your app and embeddings
- Native SQL joins between app tables and embedding table
- Available on Supabase with nothing to install: one `create extension` line and go
Limitations
- HNSW index slower than a real dedicated vector DB beyond a few million vectors
- Requires minimal SQL to query (vs ChromaDB which is full-Python)
Best for
- Apps already running Postgres or Supabase
- RAG with SQL joins (e.g. filter by user_id then vector search)