PostgreSQL通过pgvector扩展支持向量存储与相似性检索，结合用户、物品、行为及特征表设计，可高效实现推荐系统。核心包括：1. 建立users、items、interactions、user_features、item_features等表，合理组织结构化与向量数据；2. 使用pgvector存储embedding并创建IVF索引加速相似度查询；3. 利用JSONB支持稀疏特征更新，结合分区与TTL策略优化性能；4. 在线实时检索近似物品，离线更新特征向量，支撑协同过滤与混合推荐。该方案适用于中小规模场景，降低架构复杂度。
PostgreSQL 在构建推荐系统时，不仅能存储传统用户和物品信息，还能高效处理向量与特征数据，尤其在引入向量扩展后，具备了近实时相似性检索能力。合理建库结构和数据组织方式是实现高性能推荐的关键。
1. 数据库设计：核心表结构
推荐系统的数据库通常包含以下几类核心表：
● 用户表（users）：存储用户基础信息和静态特征。字段建议：user_id（主键）、age、gender、region、注册时间、活跃等级等。
● 物品表（items）：存储推荐内容元数据。字段建议：item_id（主键）、title、category、tags、发布时间、热度评分等。
● 行为表（interactions）：记录用户与物品的交互行为。字段建议：user_id、item_id、action_type（如点击、收藏、购买）、timestamp、score（隐式反馈权重）。建议按时间分区提升查询效率。
● 用户特征表（user_features）：存储用户动态 Embedding 向量或特征向量。字段建议：user_id（外键）、embedding（向量字段）、updated_at。用于协同过滤或深度模型的输入。
● 物品特征表（item_features）：存储物品的 Embedding 或多模态特征。字段建议：item_id（外键）、text_embedding、image_embedding、category_vector、updated_at。
2. 向量存储与相似性检索
PostgreSQL 通过 pgvector 扩展支持向量存储与余弦相似度、欧氏距离等计算，适合做基于内容或协同过滤的近似最近邻搜索。
● 安装 pgvector：在支持的 PostgreSQL 版本中安装扩展：CREATE EXTENSION IF NOT EXISTS vector;
例如在 item_features 表中添加文本嵌入：
ALTER TABLE item_features ADD COLUMN text_embedding vector(768);其中 768 是常见 BERT 类模型输出维度。
● 创建索引加速向量查询：使用 IVF（Inverted File Index）索引提升检索速度：
CREATE INDEX ON item_features USING ivfflat (text_embedding vector_cosine_ops) WITH (lists = 100);
● 查询最相似物品：例如查找与某向量最相似的 10 个物品：
SELECT item_id FROM item_features ORDER BY text_embedding '[0.1, 0.5, ...]' LIMIT 10;
3. 特征数据组织建议
为支持灵活的推荐策略，特征存储需兼顾结构化与高维向量数据。
● 统一特征服务接口表：可设计 feature_store 表，统一管理各类实体的特征：
entity_type（'user'/'item'）、entity_id、features_json、embedding_vector、version、updated_at适合多场景复用，便于离线更新与在线读取。
● 支持部分更新：使用 JSONB 字段存储稀疏特征，如用户兴趣标签权重：
{"interests": {"sports": 0.9, "tech": 0.6}, "last_login_days_ago": 3}
● 分区与 TTL 策略：行为表按天或月分区，定期归档旧数据；特征表根据更新频率设置缓存过期机制。
4. 推荐流程中的应用示例
典型协同过滤流程：
● 离线阶段：通过模型生成用户和物品的 Embedding，写入 user_features 和 item_features 表。
● 实时阶段：当用户访问时，查出其最新 embedding，通过向量相似度检索 top-K 相似物品，结合热度或多样性重排后返回。
● 混合推荐：结合 content-based（文本向量）与 collaborative filtering（用户行为向量），加权融合结果。
基本上就这些。PostgreSQL 配合 pgvector 能力已足够支撑中小规模推荐系统的向量存储与检索需求，无需立即引入复杂向量数据库，降低架构复杂度。
