pgvector + Supabase — Semantic Search Without Pinecone Bill

Semantic search — finding things by meaning, not keywords — used to require a separate database. Pinecone ($200+/mo at scale), Weaviate, Qdrant, Milvus: all solid, all cost money. Last year, Postgres added pgvector, a native vector extension. It's not a vector-database replacement for Pinecone's 100M+ vector scale, but for docs search, ticket routing, customer Q&A, and content discovery under 100k embeddings? pgvector is free, fast, and hosted on the same Postgres instance you already pay for in Supabase. The architecture is simple: generate an embedding for each document using OpenAI's API (~$0.02 per 1M tokens for ada-002), store the vector in Postgres alongside the text, query using cosine similarity. No vendor lock-in, no SLA surprises, no separate infrastructure. Here's how to ship semantic search without breaking the bank.

What's a Vector, and Why Semantic Matters

A vector is a list of numbers that represents the meaning of text. "The cat sat on the mat" and "A kitten rested on the rug" are different words, but OpenAI's embedding model says they're ~0.95 cosine similarity (very close in meaning). A LIKE search for "cat" wouldn't find "kitten." Semantic search does.

Vectors let you ask "find the documents most similar to this query" using math. The query is turned into a vector, and you compute cosine distance from the query vector to all stored vectors. The smallest distances are the most similar documents. This is how RAG (retrieval-augmented generation) systems find context for LLM prompts, how customer-service chatbots route tickets to the right team, and how content discovery works in modern SaaS.

Compare three approaches:

-- Keyword search: exact match only
select * from docs where content ilike '%machine learning%';
-- Misses: AI, deep learning, neural networks, etc.

-- Full-text search (tsvector): word stemming + ranking
select * from docs where search_index @@ 'machine' & 'learning'
order by ts_rank(...) desc;
-- Better, but still misses semantic synonyms.

-- Semantic search (embeddings): meaning-based
select * from docs
order by embedding <-> query_embedding asc
limit 10;
-- Finds docs about "AI training" when you search "machine learning".

The `<->` operator is cosine distance. Postgres orders rows by distance and returns the top 10. This is semantic search.

Setup: pgvector Extension + Embeddings Table

Supabase has pgvector enabled by default on all new projects. If you're on an older project, enable it:

-- In Supabase SQL editor or psql
create extension if not exists vector;

Then create a table to store embeddings:

create table documents (
  id uuid primary key default gen_random_uuid(),
  title text not null,
  content text not null,
  embedding vector(1536),
  created_at timestamp default now()
);

-- Index for fast cosine search
create index on documents using ivfflat (embedding vector_cosine_ops)
with (lists = 100);

The `vector(1536)` matches OpenAI's ada-002 embedding size (1536 dimensions). The `ivfflat` index is Postgres' approximate nearest-neighbor index — it trades a tiny bit of accuracy for huge speed gains. On 100k vectors, ivfflat queries return in ~10ms vs ~500ms without an index.

For production, tune `lists` based on your dataset size: ~(rows / 1000) is a reasonable starting point. More lists = more accurate but slower index building.

Generate and Store Embeddings

Use OpenAI's API to turn text into vectors:

import { openai } from '@ai-sdk/openai';
import { embed } from 'ai';

const response = await embed({
  model: openai.embedding('text-embedding-3-small'), // or ada-002
  value: 'How do I reset my password?',
});

const embedding = response.embedding; // array of 1536 numbers

// Store in Supabase
const { error } = await supabase
  .from('documents')
  .insert({
    title: 'Password Reset',
    content: 'How do I reset my password?',
    embedding,
  });

One API call per document. OpenAI charges ~$0.02 per 1M tokens for text-embedding-3-small (cheapest), so 100k medium docs costs ~$0.50. Batch embedding generation (process docs at night, not on user action) keeps costs low.

For bulk operations, use a Postgres function to keep embeddings in sync:

create or replace function sync_embeddings()
returns table(id uuid, missing_count int) as $$
  with unembedded as (
    select id, content from documents
    where embedding is null
    limit 100
  )
  select id, count(*) from unembedded;
$$ language sql;

-- Call from a Netlify function every 30m to bulk-embed

Query by Semantic Similarity

Once stored, query is a single operation:

// Generate embedding for user's search query
const queryEmbedding = await embed({
  model: openai.embedding('text-embedding-3-small'),
  value: userQuery,
}).then(r => r.embedding);

// Find most similar docs
const { data } = await supabase
  .from('documents')
  .select('id, title, content')
  .rpc('search_by_embedding', {
    query_embedding: queryEmbedding,
    match_threshold: 0.6,
    match_count: 10,
  });

And the Postgres function:

create or replace function search_by_embedding(
  query_embedding vector,
  match_threshold float default 0.6,
  match_count int default 10
)
returns table(
  id uuid,
  title text,
  content text,
  similarity float
) as $$
  select
    documents.id,
    documents.title,
    documents.content,
    1 - (documents.embedding <=> query_embedding) as similarity
  from documents
  where (documents.embedding <=> query_embedding) < (1 - match_threshold)
  order by documents.embedding <=> query_embedding asc
  limit match_count;
$$ language sql stable;

grant execute on function search_by_embedding(vector, float, int) to authenticated;

The `<=>` operator is cosine distance (0 = identical, 2 = opposite). We subtract from 1 to get similarity (1 = identical, 0 = unrelated). The `match_threshold` filters out low-confidence matches. This is the entire semantic search engine.

Hybrid Search: Combine Keywords + Semantic

The best approach for production: use both full-text and semantic. User types "reset password" — you run both a tsvector search (for exact matches) and a semantic search (for related docs), then merge results.

create or replace function hybrid_search(
  query_text text,
  query_embedding vector,
  match_threshold float default 0.6
)
returns table(
  id uuid,
  title text,
  content text,
  bm25_rank float,
  semantic_similarity float
) as $$
  with keyword_results as (
    select
      documents.id,
      documents.title,
      documents.content,
      ts_rank(documents.search_index, websearch_to_tsquery('english', query_text)) as bm25_rank,
      null::float as semantic_similarity
    from documents
    where documents.search_index @@ websearch_to_tsquery('english', query_text)
  ),
  semantic_results as (
    select
      documents.id,
      documents.title,
      documents.content,
      null::float as bm25_rank,
      1 - (documents.embedding <=> query_embedding) as semantic_similarity
    from documents
    where (documents.embedding <=> query_embedding) < (1 - match_threshold)
  ),
  combined as (
    select * from keyword_results
    union all
    select * from semantic_results
  )
  select
    combined.id,
    combined.title,
    combined.content,
    combined.bm25_rank,
    combined.semantic_similarity
  from combined
  order by
    coalesce(combined.bm25_rank, 0) * 0.4 +
    coalesce(combined.semantic_similarity, 0) * 0.6 desc;
$$ language sql stable;

grant execute on function hybrid_search(text, vector, float) to authenticated;

Weight the scores however you like: 40% keyword relevance, 60% semantic. This handles both "exact match" users and "find related" explorers. It's what you'd pay Algolia + Pinecone $400+/mo to do. pgvector does it free.

When pgvector Isn't Enough

Scale beyond 100k embeddings. pgvector works great up to ~100k vectors on a standard Supabase plan. Beyond that, you're paying for larger Postgres instances and ivfflat index overhead gets expensive. At million-scale vectors, Pinecone's index structures are more efficient.

Real-time re-embedding. If you embed content on every update (high churn), OpenAI API costs add up fast. Batch generation at night scales better than synchronous embedding.

Multiple embedding models. If you need dense vectors (ada-002) for one use case and sparse vectors (BM42) for another, you'd store two embedding columns. This works, but hybrid search gets complex. Specialized vector DBs handle multiple models more elegantly.

Filtering + semantic together. "Find similar docs, but only in the 'billing' category, published in the last 30 days." Postgres handles this, but the query gets verbose. A specialized vector DB might have cleaner syntax.

For everything else — docs search, ticket triage, customer Q&A, content discovery, RAG context retrieval — pgvector is cheaper, simpler, and faster to ship than Pinecone.

Six FAQs

How much does OpenAI embedding cost per document?

text-embedding-3-small: ~$0.02 per 1M tokens. Average doc is 300–1000 tokens. Budget $0.05 per document. Embedding 10k docs costs ~$0.50. Embedding 100k costs ~$5. Pinecone's $200/mo minimum for 100k vectors = $0.002 per vector. But add retrieval, processing, and SLA uptime, and pgvector's zero monthly cost dominates at small scale.

Can I use a cheaper embedding model?

Yes. text-embedding-3-small is Anthropic's cheapest. Older ada-002 still works (backwards compatible). For free embeddings, Ollama's `nomic-embed-text` is decent and runs locally. Quality drops — it's not OpenAI-grade — but cost is zero. For production, OpenAI's small model is the move.

What if my documents change frequently?

Add a trigger to re-embed on update. Keep a `last_embedded` timestamp, and periodically scan for modified docs. If you're updating 1000 docs/day, that's $0.05/day in embedding costs ($1.50/mo). Still way under Pinecone's floor.

How do I handle multilingual search?

OpenAI embeddings are language-agnostic. The same 1536-dimensional vector space works for English, French, Japanese, etc. You can search across languages in one query. No configuration needed.

Should I use ivfflat or hnsw index?

ivfflat is Postgres' approximate index (tiny accuracy trade-off, fast). HNSW (hierarchical navigable small world) is newer and faster on very large datasets. For most projects, ivfflat is fine. If you hit 500k+ vectors, test HNSW: `create index on documents using hnsw (embedding vector_cosine_ops);`

Can I use pgvector for RAG without OpenAI?

Yes. Any embedding model works: Hugging Face, Anthropic (Claude's embeddings are excellent), local Ollama. You generate embeddings, store them in pgvector, query. The only dependency is your embedding source, not OpenAI specifically.

The Bottom Line

Semantic search is no longer a luxury feature for well-funded SaaS. pgvector makes it free. Generate embeddings, store them in Postgres, query by cosine distance. Hybrid search (keyword + semantic) combines exact matches with meaning-based discovery. You're not losing features vs Pinecone — you're losing the monthly bill and vendor lock-in. For docs, tickets, Q&A, and content discovery under 100k embeddings, pgvector wins. For billion-scale vector workloads with strict SLAs, Pinecone is the professional choice. But for most of us? Postgres is the move. Ready to ship semantic search? Explore Aidxn Design backend consulting for embedding architecture reviews. For more on Postgres at scale, check full-text search with tsvector.