Building AI-Powered Apps with Supabase and pgvector

copy-- In the Supabase SQL editor or via psql
CREATE EXTENSION IF NOT EXISTS vector;

copySELECT * FROM pg_extension WHERE extname = 'vector';

copyCREATE TABLE documents (
  id BIGSERIAL PRIMARY KEY,
  content TEXT NOT NULL,
  metadata JSONB DEFAULT '{}',
  embedding VECTOR(1536) NOT NULL,  -- 1536 for OpenAI ada-002, adjust for your model
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Create an index for fast similarity search
CREATE INDEX ON documents USING ivfflat (embedding vector_cosine_ops)
  WITH (lists = 100);  -- adjust lists based on your data size

#Generate embeddings

You need to generate embeddings for your content. Here's how with different backends:

Using OpenAI:

copy-- Create a function to generate embeddings via OpenAI API
CREATE OR REPLACE FUNCTION generate_embedding(text_content TEXT)
RETURNS VECTOR AS $$
DECLARE
  response JSONB;
BEGIN
  SELECT content INTO response
  FROM net.http_post(
    url := 'https://api.openai.com/v1/embeddings',
    headers := jsonb_build_object(
      'Authorization', 'Bearer ' || current_setting('app.settings.openai_api_key'),
      'Content-Type', 'application/json'
    ),
    body := jsonb_build_object(
      'model', 'text-embedding-3-small',
      'input', text_content
    )
  );
  
  RETURN (response->'data'->0->'embedding')::VECTOR;
END;
$$ LANGUAGE plpgsql;

Using Ollama (via Edge Function):

copy// supabase/functions/generate-embedding/index.ts
import { serve } from "https://deno.land/std@0.168.0/http/server.ts";

serve(async (req) => {
  const { text } = await req.json();
  
  const response = await fetch("http://host.docker.internal:11434/api/embeddings", {
    method: "POST",
    headers: { "Content-Type": "application/json" },
    body: JSON.stringify({
      model: "nomic-embed-text",
      prompt: text,
    }),
  });
  
  const { embedding } = await response.json();
  
  return new Response(JSON.stringify({ embedding }), {
    headers: { "Content-Type": "application/json" },
  });
});

#Semantic search

Search for similar content using cosine similarity:

copy-- Find the 10 most similar documents to a query
CREATE OR REPLACE FUNCTION search_documents(
  query_embedding VECTOR(1536),
  match_count INT DEFAULT 10,
  match_threshold FLOAT DEFAULT 0.5
)
RETURNS TABLE (
  id BIGINT,
  content TEXT,
  metadata JSONB,
  similarity FLOAT
)
LANGUAGE plpgsql
AS $$
BEGIN
  RETURN QUERY
  SELECT
    d.id,
    d.content,
    d.metadata,
    1 - (d.embedding <=> query_embedding) AS similarity
  FROM documents d
  WHERE 1 - (d.embedding <=> query_embedding) > match_threshold
  ORDER BY d.embedding <=> query_embedding
  LIMIT match_count;
END;
$$;

Use it:

copy-- Search for documents similar to a query
SELECT * FROM search_documents(
  (SELECT embedding FROM documents WHERE content LIKE '%machine learning%' LIMIT 1),
  10,
  0.5
);

#Build a RAG application

Retrieval-augmented generation (RAG) combines semantic search with LLM generation:

Step 1: Store your knowledge base

copy-- Insert documents with embeddings
INSERT INTO documents (content, metadata, embedding)
VALUES (
  'PostgreSQL is a powerful relational database with support for JSON, full-text search, and extensions like pgvector.',
  '{"source": "docs", "topic": "database"}',
  generate_embedding('PostgreSQL is a powerful relational database with support for JSON, full-text search, and extensions like pgvector.')
);

Step 2: Search for relevant context

copy-- Find context for a user question
WITH context AS (
  SELECT content
  FROM search_documents(
    generate_embedding('How do I use Postgres for AI?'),
    5,
    0.6
  )
)
SELECT string_agg(content, '\n\n') AS context
FROM context;

Step 3: Generate a response

Use an Edge Function to combine search results with an LLM:

copy// supabase/functions/chat/index.ts
import { serve } from "https://deno.land/std@0.168.0/http/server.ts";
import { createClient } from "https://esm.sh/@supabase/supabase-js@2";

serve(async (req) => {
  const { question } = await req.json();
  
  // 1. Generate embedding for the question
  const embedResponse = await fetch("http://host.docker.internal:11434/api/embeddings", {
    method: "POST",
    headers: { "Content-Type": "application/json" },
    body: JSON.stringify({
      model: "nomic-embed-text",
      prompt: question,
    }),
  });
  const { embedding } = await embedResponse.json();
  
  // 2. Search for relevant context
  const supabase = createClient(
    Deno.env.get("SUPABASE_URL")!,
    Deno.env.get("SUPABASE_SERVICE_ROLE_KEY")!
  );
  
  const { data: context } = await supabase.rpc("search_documents", {
    query_embedding: embedding,
    match_count: 5,
    match_threshold: 0.6,
  });
  
  // 3. Generate response with context
  const contextText = context?.map((c: any) => c.content).join("\n\n") || "";
  
  const llmResponse = await fetch("http://host.docker.internal:11434/api/generate", {
    method: "POST",
    headers: { "Content-Type": "application/json" },
    body: JSON.stringify({
      model: "llama3.2",
      prompt: `Context:\n${contextText}\n\nQuestion: ${question}\n\nAnswer based on the context:`,
      stream: false,
    }),
  });
  
  const { response } = await llmResponse.json();
  
  return new Response(JSON.stringify({ answer: response, context }), {
    headers: { "Content-Type": "application/json" },
  });
});

#Performance optimization

Index tuning: The IVFFlat index needs enough lists for your data size. General rule:

• < 1M rows: 100 lists
• 1M-10M rows: 1000 lists
• 10M+ rows: 10000 lists

Rebuild the index after significant data changes:

copyREINDEX INDEX documents_embedding_idx;

Query optimization: Use EXPLAIN ANALYZE to check query plans:

copyEXPLAIN ANALYZE
SELECT * FROM search_documents(
  generate_embedding('test query'),
  10,
  0.5
);

Connection pooling: Supabase uses PgBouncer for connection pooling. For high-volume AI applications, configure the pool size appropriately.

#Backups

Supabase backups include pgvector data automatically. For manual backups:

copydocker compose exec -T db pg_dump -U postgres supabase | gzip > supabase-ai-$(date +%F).sql.gz

The backup includes all tables, indexes, and extensions.

#Troubleshooting

pgvector not found. Make sure the extension is enabled: CREATE EXTENSION IF NOT EXISTS vector;. If it fails, check that your Supabase version includes pgvector.

Embedding dimension mismatch. The vector column dimension must match your embedding model's output. Check your model's documentation for the correct dimension.

Slow similarity search. Check that you have an index on the embedding column. For large datasets, increase the number of lists in the IVFFlat index.

Out of memory during embedding generation. Generating embeddings for large documents can use significant memory. Process documents in batches rather than all at once.

Edge Function timeouts. LLM calls can be slow. Set appropriate timeouts and consider streaming responses for better user experience.

#Verification + next steps

You're done when you can: store documents with embeddings, search by semantic similarity, and generate responses using retrieved context. Test with a small knowledge base, then scale up.

From here, explore advanced features like hybrid search (combining vector and full-text search), multi-tenant embeddings, and real-time embedding updates. For the base Supabase setup, see Self-Host Supabase. For document processing, see AnythingLLM. A Hetzner CX32 (4 vCPU / 8 GB) handles small to medium datasets; for large-scale vector search, consider a CX42 (8 vCPU / 16 GB) with more RAM. See Best VPS for AI & ML Workloads for the ranked picks.