ARTICLE DETAIL

建站实战干货

来自一线的建站与推广经验沉淀，每一条都经过真实交付验证。

首页 / 资讯中心 / 正文

Python后端AI专题20：为什么主项目选 pgvector：表结构、距离与 HNSW

2026/9/25 16:49:37 拓冰建站 浏览量
Python后端AI专题20：为什么主项目选 pgvector：表结构、距离与 HNSW Python后端AI专题20为什么主项目选 pgvector表结构、距离与 HNSWKnowFlow 已经能在内存里写向量但企业项目还要同时管理租户、知识库、文档状态、来源和事务。主项目选择 PostgreSQL pgvector不是因为它在所有规模都最快而是当前阶段“业务过滤与向量检索同库完成”比再引入一套分布式向量系统更容易保证一致性和权限。续建决策与指纹答案变更决策原因同一 503 后重试继续旧 job数据语义未变batch_size 32→16谨慎重建批次计划已有游标不能直接解释游标按批次编号不再指向相同 chunk 范围chunk_size 800→512新索引版本chunk 内容与 ordinal 全变384→768 维新索引版本/新列旧列类型和向量空间均不兼容Celery 并发数变化继续旧 job调度变化不改变索引语义为了避免只比较一个模型名新增完整指纹模块dataclass(frozenTrue,slotsTrue)classIndexPipelineVersion:parser_version:strcleaner_version:strchunk_size:intchunk_overlap:intembedding_model:strembedding_dimensions:intdeffingerprint(self)-str:canonicaljson.dumps(asdict(self),ensure_asciiFalse,sort_keysTrue,separators(,,:),)returnhashlib.sha256(canonical.encode(utf-8)).hexdigest()排序键与紧凑 JSON 保证相同配置序列化稳定。测试证明 chunk_size 或维数改变会得到不同的 64 位十六进制哈希1 passed in 0.08s。为什么此时不优先选独立向量库pgvector 的优势tenant_id/knowledge_base_id/status与向量距离能在一条 SQL 中过滤文档、chunk 与索引任务可用外键和事务约束团队已有 PostgreSQL 运维经验当前数据规模不要求独立集群的水平扩展。当向量达到数亿、需要跨区域水平扩展、专用稀疏向量或复杂分片时Qdrant/Milvus/Weaviate 等可能更合适。Provider 合同保留了替换空间但课程不会为了简历关键词同时运行四个数据库。距离与相似度不要混用pgvector 的 cosine distance 越小越近业务 API 常返回 similarity 越大越近。本项目查询后转换score1.0-float(distance_value)排序必须仍按distance ASC不能一边写距离、一边按降序排。索引操作符类也要一致vector_cosine_ops对应余弦距离如果查询改成内积或 L2索引和归一化策略都要重新审查。HNSW 是近似索引不是魔法没有 ANN 索引时数据库要与大量向量逐一计算。HNSW 构建多层邻接图加速近邻搜索代价是更多内存、写入成本和近似误差。参数会影响召回与性能因此必须用评测集验证不能只看 SQL 延迟。模型声明Index(ix_document_chunks_embedding_hnsw,embedding,postgresql_usinghnsw,postgresql_ops{embedding:vector_cosine_ops},)完整知识模型模块from__future__importannotationsimportuuidfrompgvector.sqlalchemyimportVectorfromsqlalchemyimport(BigInteger,CheckConstraint,ForeignKey,Index,Integer,JSON,String,Text,UniqueConstraint,)fromsqlalchemy.ormimportMapped,mapped_columnfromapp.models.baseimportBase,TimestampMixin,UUIDPrimaryKeyMixinclassKnowledgeBase(UUIDPrimaryKeyMixin,TimestampMixin,Base):__tablename__knowledge_bases__table_args__(UniqueConstraint(tenant_id,name),Index(ix_knowledge_bases_tenant_id,tenant_id),)tenant_id:Mapped[uuid.UUID]mapped_column(ForeignKey(tenants.id,ondeleteCASCADE),nullableFalse)name:Mapped[str]mapped_column(String(160),nullableFalse)description:Mapped[str]mapped_column(Text,default,nullableFalse)classStoredDocument(UUIDPrimaryKeyMixin,TimestampMixin,Base):__tablename__documents__table_args__(UniqueConstraint(knowledge_base_id,content_hash),CheckConstraint(status IN (pending_upload,uploaded,indexing,ready,failed),namevalid_document_status,),Index(ix_documents_tenant_id_kb,tenant_id,knowledge_base_id),)tenant_id:Mapped[uuid.UUID]mapped_column(ForeignKey(tenants.id,ondeleteCASCADE),nullableFalse)knowledge_base_id:Mapped[uuid.UUID]mapped_column(ForeignKey(knowledge_bases.id,ondeleteCASCADE),nullableFalse)filename:Mapped[str]mapped_column(String(255),nullableFalse)content_type:Mapped[str]mapped_column(String(100),nullableFalse)content_hash:Mapped[str]mapped_column(String(64),nullableFalse)object_key:Mapped[str]mapped_column(String(500),nullableFalse,uniqueTrue)size_bytes:Mapped[int]mapped_column(BigInteger,nullableFalse)status:Mapped[str]mapped_column(String(24),defaultuploaded,nullableFalse)error_code:Mapped[str|None]mapped_column(String(80))classDocumentChunk(UUIDPrimaryKeyMixin,TimestampMixin,Base):__tablename__document_chunks__table_args__(UniqueConstraint(document_id,ordinal),Index(ix_document_chunks_tenant_id_kb,tenant_id,knowledge_base_id),Index(ix_document_chunks_embedding_hnsw,embedding,postgresql_usinghnsw,postgresql_ops{embedding:vector_cosine_ops},),)tenant_id:Mapped[uuid.UUID]mapped_column(ForeignKey(tenants.id,ondeleteCASCADE),nullableFalse)knowledge_base_id:Mapped[uuid.UUID]mapped_column(ForeignKey(knowledge_bases.id,ondeleteCASCADE),nullableFalse)document_id:Mapped[uuid.UUID]mapped_column(ForeignKey(documents.id,ondeleteCASCADE),nullableFalse)ordinal:Mapped[int]mapped_column(Integer,nullableFalse)text:Mapped[str]mapped_column(Text,nullableFalse)page:Mapped[int|None]mapped_column(Integer)section:Mapped[str|None]mapped_column(String(500))start_char:Mapped[int]mapped_column(Integer,nullableFalse)end_char:Mapped[int]mapped_column(Integer,nullableFalse)embedding_model:Mapped[str]mapped_column(String(160),nullableFalse)embedding:Mapped[list[float]]mapped_column(Vector(384),nullableFalse)source_metadata:Mapped[dict[str,object]]mapped_column(JSON,defaultdict,nullableFalse)classIndexJob(UUIDPrimaryKeyMixin,TimestampMixin,Base):__tablename__index_jobs__table_args__(CheckConstraint(status IN (queued,parsing,chunking,embedding,ready,failed),namevalid_index_job_status,),Index(ix_index_jobs_document_id,document_id),)document_id:Mapped[uuid.UUID]mapped_column(ForeignKey(documents.id,ondeleteCASCADE),nullableFalse)status:Mapped[str]mapped_column(String(24),defaultqueued,nullableFalse)next_batch:Mapped[int]mapped_column(Integer,default0,nullableFalse)total_chunks:Mapped[int]mapped_column(Integer,default0,nullableFalse)attempts:Mapped[int]mapped_column(Integer,default0,nullableFalse)error:Mapped[str|None]mapped_column(Text)迁移先执行CREATE EXTENSION IF NOT EXISTS vector再由 metadata 建表从空库验证了 10 张产品表、vector(384)与 HNSW。模型与真实 PostgreSQL 检查当前结果........ [100%] 8 passed in 1.01s本篇最终完整模块knowledge.py前面的代码片段用于解释本次改动下面是本篇结束时可直接核对和替换的磁盘完整版本。from__future__importannotationsimportuuidfromdatetimeimportdatetimefrompgvector.sqlalchemyimportVectorfromsqlalchemyimportBigInteger,CheckConstraint,DateTime,ForeignKey,Index,Integer,JSON,String,Text,UniqueConstraintfromsqlalchemy.ormimportMapped,mapped_columnfromapp.models.baseimportBase,TimestampMixin,UUIDPrimaryKeyMixinclassKnowledgeBase(UUIDPrimaryKeyMixin,TimestampMixin,Base):__tablename__knowledge_bases__table_args__(UniqueConstraint(tenant_id,name),Index(ix_knowledge_bases_tenant_id,tenant_id),)tenant_id:Mapped[uuid.UUID]mapped_column(ForeignKey(tenants.id,ondeleteCASCADE),nullableFalse)name:Mapped[str]mapped_column(String(160),nullableFalse)description:Mapped[str]mapped_column(Text,default,nullableFalse)classStoredDocument(UUIDPrimaryKeyMixin,TimestampMixin,Base):__tablename__documents__table_args__(UniqueConstraint(knowledge_base_id,content_hash),CheckConstraint(status IN (pending_upload,uploaded,indexing,ready,failed),namevalid_document_status,),Index(ix_documents_tenant_id_kb,tenant_id,knowledge_base_id),)tenant_id:Mapped[uuid.UUID]mapped_column(ForeignKey(tenants.id,ondeleteCASCADE),nullableFalse)knowledge_base_id:Mapped[uuid.UUID]mapped_column(ForeignKey(knowledge_bases.id,ondeleteCASCADE),nullableFalse)filename:Mapped[str]mapped_column(String(255),nullableFalse)content_type:Mapped[str]mapped_column(String(100),nullableFalse)content_hash:Mapped[str]mapped_column(String(64),nullableFalse)object_key:Mapped[str]mapped_column(String(500),nullableFalse,uniqueTrue)size_bytes:Mapped[int]mapped_column(BigInteger,nullableFalse)status:Mapped[str]mapped_column(String(24),defaultuploaded,nullableFalse)error_code:Mapped[str|None]mapped_column(String(80))classDocumentChunk(UUIDPrimaryKeyMixin,TimestampMixin,Base):__tablename__document_chunks__table_args__(UniqueConstraint(document_id,ordinal),Index(ix_document_chunks_tenant_id_kb,tenant_id,knowledge_base_id),Index(ix_document_chunks_embedding_hnsw,embedding,postgresql_usinghnsw,postgresql_ops{embedding:vector_cosine_ops},),)tenant_id:Mapped[uuid.UUID]mapped_column(ForeignKey(tenants.id,ondeleteCASCADE),nullableFalse)knowledge_base_id:Mapped[uuid.UUID]mapped_column(ForeignKey(knowledge_bases.id,ondeleteCASCADE),nullableFalse)document_id:Mapped[uuid.UUID]mapped_column(ForeignKey(documents.id,ondeleteCASCADE),nullableFalse)ordinal:Mapped[int]mapped_column(Integer,nullableFalse)text:Mapped[str]mapped_column(Text,nullableFalse)page:Mapped[int|None]mapped_column(Integer)section:Mapped[str|None]mapped_column(String(500))start_char:Mapped[int]mapped_column(Integer,nullableFalse)end_char:Mapped[int]mapped_column(Integer,nullableFalse)embedding_model:Mapped[str]mapped_column(String(160),nullableFalse)embedding:Mapped[list[float]]mapped_column(Vector(384),nullableFalse)source_metadata:Mapped[dict[str,object]]mapped_column(JSON,defaultdict,nullableFalse)classIndexJob(UUIDPrimaryKeyMixin,TimestampMixin,Base):__tablename__index_jobs__table_args__(CheckConstraint(status IN (queued,parsing,chunking,embedding,ready,failed),namevalid_index_job_status,),CheckConstraint(dispatch_status IN (pending,claimed,dispatched,failed),namevalid_index_job_dispatch_status,),Index(ix_index_jobs_document_id,document_id),Index(ix_index_jobs_dispatch_pending,status,dispatch_status,dispatch_lease_until,),)document_id:Mapped[uuid.UUID]mapped_column(ForeignKey(documents.id,ondeleteCASCADE),nullableFalse)status:Mapped[str]mapped_column(String(24),defaultqueued,nullableFalse)next_batch:Mapped[int]mapped_column(Integer,default0,nullableFalse)total_chunks:Mapped[int]mapped_column(Integer,default0,nullableFalse)attempts:Mapped[int]mapped_column(Integer,default0,nullableFalse)error:Mapped[str|None]mapped_column(Text)dispatch_status:Mapped[str]mapped_column(String(16),defaultpending,server_defaultpending,nullableFalse)dispatch_token:Mapped[str|None]mapped_column(String(32))dispatch_attempts:Mapped[int]mapped_column(Integer,default0,server_default0,nullableFalse)dispatch_lease_until:Mapped[datetime|None]mapped_column(DateTime(timezoneTrue))dispatched_at:Mapped[datetime|None]mapped_column(DateTime(timezoneTrue))dispatch_error:Mapped[str|None]mapped_column(String(80))本篇练习手算距离并检查 SQL 顺序给定查询向量(1,0)A(0.8,0.6)B(1,0)C(-1,0)手算三者余弦相似度与 distance1-similarity写出按distance ASC的顺序。然后在真实 pgvector 测试中插入两个租户各一条极相似记录断言租户 A 查询永远拿不到租户 B即使 B 距离更小。下一篇给出计算和完整集成测试并实现第一个受权限约束的向量检索接口。