pgvector-setup（pg 向量设置）

pgvector-setup

Instructions

This skill provides complete pgvector setup for Supabase databases, enabling vector search capabilities for AI applications, RAG systems, and semantic search.

Phase 1: Enable pgvector Extension

1. Run the setup script to enable pgvector: 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: # 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

1. Apply the schema: psql $SUPABASE_DB_URL < templates/embedding-table-schema.sql

Phase 3: Create Vector Indexes

Choose index type based on your data size:

HNSW (Recommended for most cases):

• 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]

IVFFlat:

• 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]

Performance Tuning:

• 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: -- 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: const {data} = await supabase.rpc('match_documents', {query_embedding: embedding match_threshold: 0.78 match_count: 10});

Phase 5: Setup Hybrid Search (Optional)

For combining keyword and semantic search:

1. Run hybrid search setup: 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

1. Use the hybrid search function: 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 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

Distance Metric Selection:

• Cosine distance (<=>): Safe default, handles varying vector magnitudes
• Inner product (<#>): Faster for normalized vectors (OpenAI embeddings)
• Euclidean distance (<->): Use when absolute distances matter

Index Choice:

• Start with HNSW for most applications
• Switch to IVFFlat only if:

- You have > 1M vectors - Insert performance is critical - You can tolerate lower recall

Dimension Size:

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

Common Patterns

Pattern 1: Document Search

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

Pattern 2: User Preference Matching

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

Pattern 3: Product Recommendations

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

Troubleshooting

Slow queries (> 100ms):

• Check if index is being used: EXPLAIN ANALYZE
• Increase HNSW ef_search parameter
• Consider reducing result limit
• Add WHERE clauses to reduce search space

Poor recall (missing relevant results):

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

High memory usage:

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

Insert performance issues:

• 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

Row Level Security (RLS):

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

API Key Protection:

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

Files Reference

Scripts:

• 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:

• 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:

• 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

Plugin: supabase Version: 1.0.0 Last Updated: 2025-10-26