pgvector-setup

Instructions

Phase 1: Enable pgvector Extension

1. Run the setup script to enable pgvector:
   bash

   bash scripts/setup-pgvector.sh [SUPABASE_DB_URL]

   This creates the pgvector extension and sets up basic embedding tables.
2. Choose your embedding dimensions based on your model:
   • OpenAI text-embedding-3-small: 1536 dimensions
   • OpenAI text-embedding-3-large: 3072 dimensions
   • Cohere embed-english-v3.0: 1024 dimensions
   • Custom models: Check model documentation

Phase 2: Create Embedding Tables

1. Use the embedding table template:
   bash

   # Copy template and customize for your use case
   cat templates/embedding-table-schema.sql

2. Customize the schema:
   • Adjust vector dimensions to match your model
   • Add metadata columns (tags, timestamps, user_id, etc.)
   • Configure RLS policies for security
3. Apply the schema:
   bash

   psql $SUPABASE_DB_URL < templates/embedding-table-schema.sql

Phase 3: Create Vector Indexes

• Best for: < 1M vectors, high recall requirements
• Pros: Fast queries, good recall, works well with small-medium datasets
• Cons: Slower inserts, higher memory usage
• Run:

  bash scripts/create-indexes.sh hnsw [TABLE_NAME] [DIMENSION]

• Best for: > 1M vectors, write-heavy workloads
• Pros: Faster inserts, lower memory
• Cons: Requires training, lower recall
• Run:

  bash scripts/create-indexes.sh ivfflat [TABLE_NAME] [DIMENSION]

• HNSW m parameter (default 16): Higher = better recall, more memory
• HNSW ef_construction (default 64): Higher = better quality, slower builds
• IVFFlat lists (default sqrt(rows)): More lists = faster queries, lower recall

Phase 4: Implement Semantic Search

1. Create the match function:
   sql

   -- See templates/match-function.sql for complete example
   create or replace function match_documents(
     query_embedding vector(1536)
     match_threshold float
     match_count int
   ) returns setof documents ...

2. Query from application:
   javascript

   const { data } = await supabase.rpc('match_documents', {
     query_embedding: embedding
     match_threshold: 0.78
     match_count: 10
   });

Phase 5: Setup Hybrid Search (Optional)

1. Run hybrid search setup:
   bash

   bash scripts/setup-hybrid-search.sh [TABLE_NAME]

2. This configures:
   • Full-text search with tsvector and GIN indexes
   • Vector search with HNSW indexes
   • RRF (Reciprocal Rank Fusion) for combining results
   • Weighted scoring for tuning keyword vs semantic importance
3. Use the hybrid search function:
   sql

   select * from hybrid_search(
     'search query text'
     query_embedding
     match_count := 10
     full_text_weight := 1.0
     semantic_weight := 1.0
   );

Phase 6: Test and Validate

1. Run validation tests:
   bash

   bash scripts/test-vector-search.sh [TABLE_NAME]

2. This verifies:
   • pgvector extension is enabled
   • Tables have correct vector dimensions
   • Indexes are created and being used
   • Query performance is acceptable
   • Similarity functions return correct results

Key Decisions

• Cosine distance (

  <=>

  ): Safe default, handles varying vector magnitudes
• Inner product (

  <#>

  ): Faster for normalized vectors (OpenAI embeddings)
• Euclidean distance (

  <->

  ): Use when absolute distances matter

• Start with HNSW for most applications
• Switch to IVFFlat only if:
  • You have > 1M vectors
  • Insert performance is critical
  • You can tolerate lower recall

• Higher dimensions = better semantic understanding
• Lower dimensions = faster queries, less storage
• Match your embedding model exactly (never truncate)

Common Patterns

• Store document chunks with metadata
• Use HNSW index for semantic search
• Add full-text for hybrid search
• See: examples/document-search-pattern.md

• Store user profile embeddings
• Use cosine similarity for matching
• Update embeddings as preferences change
• See: examples/preference-matching-pattern.md

• Store product feature embeddings
• Use hybrid search (keywords + semantic)
• Weight by popularity or ratings
• See: examples/product-recommendations-pattern.md

Troubleshooting

• Check if index is being used:

  EXPLAIN ANALYZE

• Increase HNSW ef_search parameter
• Consider reducing result limit
• Add WHERE clauses to reduce search space

• Increase match_count
• Lower match_threshold
• For HNSW: increase m and ef_construction
• For IVFFlat: increase lists parameter

• HNSW uses ~10KB per vector
• Reduce m parameter (quality tradeoff)
• Consider IVFFlat for large datasets
• Use partial indexes if possible

• HNSW is slow for bulk inserts
• Disable index during bulk load, rebuild after
• Use IVFFlat for write-heavy workloads
• Batch inserts when possible

Security Considerations

• Enable RLS on all embedding tables
• Filter by user_id or organization_id
• Prevent embedding leakage between users
• See templates for RLS policy examples

• Never expose embedding API keys
• Use Supabase Edge Functions for embedding generation
• Store keys in Supabase secrets
• Rate limit embedding requests

Files Reference

• scripts/setup-pgvector.sh

  - Enable extension and create base tables

• scripts/create-indexes.sh

  - Create HNSW or IVFFlat indexes

• scripts/setup-hybrid-search.sh

  - Configure hybrid search

• scripts/test-vector-search.sh

  - Validate setup

• templates/embedding-table-schema.sql

  - Table structure with metadata

• templates/hnsw-index-config.sql

  - HNSW index with tuning

• templates/ivfflat-index-config.sql

  - IVFFlat index configuration

• templates/hybrid-search-function.sql

  - Hybrid search with RRF

• templates/match-function.sql

  - Basic semantic search function

• examples/embedding-strategies.md

  - Index selection guide

• examples/vector-search-examples.md

  - Common search patterns

• examples/document-search-pattern.md

  - Full document search implementation

• examples/preference-matching-pattern.md

  - User matching system

• examples/product-recommendations-pattern.md

  - Recommendation engine