pgvector-setup

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

pgvector-setup

pgvector配置

Instructions

操作说明

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

本技能为Supabase数据库提供完整的pgvector配置方案，为AI应用、RAG系统和语义搜索赋能向量搜索能力。

Phase 1: Enable pgvector Extension

阶段1：启用pgvector扩展

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.
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

运行设置脚本以启用pgvector：
bash
```
bash scripts/setup-pgvector.sh [SUPABASE_DB_URL]
```
该脚本会创建pgvector扩展并设置基础嵌入表。
根据你的模型选择嵌入维度：
- OpenAI text-embedding-3-small：1536维
- OpenAI text-embedding-3-large：3072维
- Cohere embed-english-v3.0：1024维
- 自定义模型：查看模型文档

Phase 2: Create Embedding Tables

阶段2：创建嵌入表

Use the embedding table template:

bash

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

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

Apply the schema:

bash

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

使用嵌入表模板：

bash

# 复制模板并根据你的用例自定义
cat templates/embedding-table-schema.sql

自定义模式：
- 调整向量维度以匹配你的模型
- 添加元数据列（标签、时间戳、user_id等）
- 配置RLS策略以保障安全

应用该模式：

bash

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

Phase 3: Create Vector Indexes

阶段3：创建向量索引

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

根据数据规模选择索引类型：

HNSW（大多数场景推荐）：

最佳适用场景：向量数量<100万、高召回率需求
优点：查询速度快、召回率高，适用于中小规模数据集
缺点：插入速度较慢、内存占用较高

运行命令：

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

IVFFlat：

最佳适用场景：向量数量>100万、写入密集型工作负载
优点：插入速度快、内存占用低
缺点：需要训练、召回率较低

运行命令：

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

性能调优：

HNSW m参数（默认16）：值越高→召回率越好，内存占用越多
HNSW ef_construction参数（默认64）：值越高→索引质量越好，构建速度越慢
IVFFlat lists参数（默认sqrt(行数)）：列表越多→查询速度越快，召回率越低

Phase 4: Implement Semantic Search

阶段4：实现语义搜索

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 ...

Query from application:

javascript

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

创建匹配函数：

sql

-- 查看templates/match-function.sql获取完整示例
create or replace function match_documents(
  query_embedding vector(1536)
  match_threshold float
  match_count int
) returns setof documents ...

从应用中发起查询：

javascript

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

Phase 5: Setup Hybrid Search (Optional)

阶段5：设置混合搜索（可选）

For combining keyword and semantic search:

Run hybrid search setup:

bash

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

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

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
);

用于结合关键词搜索与语义搜索：

运行混合搜索设置脚本：

bash

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

该脚本会配置：
- 基于tsvector和GIN索引的全文搜索
- 基于HNSW索引的向量搜索
- 用于合并结果的RRF（Reciprocal Rank Fusion， reciprocal排名融合）
- 用于调整关键词与语义搜索权重的加权评分机制

使用混合搜索函数：

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

阶段6：测试与验证

Run validation tests:

bash

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

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

运行验证测试：

bash

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

该脚本会验证：
- pgvector扩展已启用
- 表的向量维度正确
- 索引已创建且正在被使用
- 查询性能符合预期
- 相似度函数返回结果正确

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)

距离度量选择：

余弦距离（
```
<=>
```
）：安全的默认选项，可处理不同幅度的向量
内积（
```
<#>
```
）：对归一化向量（如OpenAI嵌入）速度更快
欧氏距离（
```
<->
```
）：当绝对距离有意义时使用

索引选择：

大多数应用从HNSW开始
仅在以下情况切换到IVFFlat：
- 向量数量>100万
- 插入性能至关重要
- 可接受较低的召回率

维度大小：

维度越高→语义理解能力越强
维度越低→查询速度越快、存储占用越少
必须与嵌入模型完全匹配（切勿截断）

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

模式1：文档搜索

存储带元数据的文档片段
使用HNSW索引进行语义搜索
添加全文搜索以实现混合搜索
参考：examples/document-search-pattern.md

模式2：用户偏好匹配

存储用户画像嵌入
使用余弦相似度进行匹配
当偏好变化时更新嵌入
参考：examples/preference-matching-pattern.md

模式3：商品推荐

存储商品特征嵌入
使用混合搜索（关键词+语义）
按流行度或评分加权
参考：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

查询缓慢（>100ms）：

检查是否使用了索引：
```
EXPLAIN ANALYZE
```
增大HNSW的ef_search参数
考虑减少结果限制
添加WHERE子句以缩小搜索范围

召回率低（缺失相关结果）：

增大match_count
降低match_threshold
对于HNSW：增大m和ef_construction参数
对于IVFFlat：增大lists参数

内存占用高：

HNSW每个向量约占用10KB内存
减小m参数（需权衡质量）
对于大型数据集考虑使用IVFFlat
尽可能使用部分索引

插入性能问题：

HNSW批量插入速度慢
批量加载期间禁用索引，加载完成后重建
对于写入密集型工作负载使用IVFFlat
尽可能批量插入

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

行级安全（RLS）：

在所有嵌入表上启用RLS
按user_id或organization_id过滤
防止嵌入在用户之间泄露
参考模板中的RLS策略示例

API密钥保护：

切勿暴露嵌入API密钥
使用Supabase Edge Functions生成嵌入
将密钥存储在Supabase secrets中
对嵌入请求进行速率限制

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

脚本：

```
scripts/setup-pgvector.sh
```
- 启用扩展并创建基础表
```
scripts/create-indexes.sh
```
- 创建HNSW或IVFFlat索引
```
scripts/setup-hybrid-search.sh
```
- 配置混合搜索
```
scripts/test-vector-search.sh
```
- 验证配置

模板：

```
templates/embedding-table-schema.sql
```
- 带元数据的表结构
```
templates/hnsw-index-config.sql
```
- 带调优的HNSW索引
```
templates/ivfflat-index-config.sql
```
- IVFFlat索引配置
```
templates/hybrid-search-function.sql
```
- 带RRF的混合搜索函数
```
templates/match-function.sql
```
- 基础语义搜索函数

示例：

```
examples/embedding-strategies.md
```
- 索引选择指南
```
examples/vector-search-examples.md
```
- 常见搜索模式
```
examples/document-search-pattern.md
```
- 完整文档搜索实现
```
examples/preference-matching-pattern.md
```
- 用户匹配系统

examples/product-recommendations-pattern.md

- 推荐引擎

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