postgresql-data-model.md 12.7 KB

Raw Blame History Permalink



PostgreSQL 数据模型与 DDL 设计说明


更新：2026-06-04

关联 SQL：acr-engine/sql/acr_pg_schema_v2.sql

目标：给出面向版权保护 / 大规模曲库 / 可替换 encoder 的 PostgreSQL 数据字典、DDL 设计意图、流程图与典型使用路径。


一页结论

当前推荐的 PostgreSQL 设计，不再围绕“某一个模型的 embedding 表”来建，而是围绕下面这条稳定主链来建：

canonical_song -> work -> recording -> recording_asset -> audio_window
                                            -> model_registry -> feature_set_registry -> audio_embedding / audio_fingerprint
                                            -> reference_set_registry -> retrieval_index_registry -> retrieval_candidate -> match_decision


这套设计解决的是：


song/work/recording 混在一起的问题
未来换模型就得改表的问题
窗口级检索无法回溯证据的问题
exact / semantic / future cover lane 无法统一聚合的问题


1. 设计意图


1.1 这套设计想解决什么


问题 A：同一首歌可能有多个录音版本

所以必须区分：


canonical_song：业务最终归一 song

work：作品层

recording：具体录音版本


问题 B：一个录音可能有多个文件资产

所以必须有：


recording_asset


问题 C：检索真正命中的是片段，不是整首歌

所以必须有：


audio_window


问题 D：未来底座会切换

所以必须有：


model_registry
feature_set_registry


问题 E：你会同时存在多个索引后端

所以必须有：


retrieval_index_registry


1.2 为什么不用“reference_embeddings / query_embeddings”那种原型表继续扩

因为原型表有几个限制：


维度写死，如 vector(192)

数据对象太扁平，只围绕 song_id

无法优雅支持多个 encoder
无法表达同一 recording 下的多资产、多窗口、多 feature_set


所以原型版 SQL 适合 demo，不适合你现在的 100w 音频目标。


2. 数据主链

flowchart LR
    A[canonical_song] --> B[work]
    B --> C[recording]
    C --> D[recording_asset]
    D --> E[audio_window]
    E --> F[audio_embedding]
    E --> G[audio_fingerprint]
    F --> H[retrieval_index_registry]
    G --> H
    H --> I[retrieval_candidate]
    I --> J[match_decision]


3. 表分组


分组
表
作用


版权与实体

canonical_song, work, recording

统一业务归属


资产层
recording_asset
管理真实文件资产


窗口层
audio_window
管理检索最小证据片段


模型与特征

model_registry, feature_set_registry, audio_embedding, audio_fingerprint

管理模型版本与特征事实


reference 集

reference_set_registry, reference_set_member

管理热 reference 集与版本化切换


索引层
retrieval_index_registry
记录后端索引


匹配层

retrieval_candidate, match_decision

在线召回与最终归一


4. 关键表说明


4.1 canonical_song


最终业务主键。

用途：


服务最终返回 canonical_song_id

权利归属、产品展示、对外业务都以它为准


4.2 work


作品层。

用途：


同一首歌的不同翻唱/演绎归一到作品层
future phase 的 cover/version lane 常常先聚到 work_id


4.3 recording


录音层。

用途：


official/live/remaster/cover/ugc 等不同版本分开管理
允许多个 recording 最终映射到同一个 canonical_song


4.4 recording_asset


文件资产层。

用途：


同一个 recording 可有多个文件版本
可区分 master/reference/distribution/captured/query_sample


4.5 audio_window


窗口层。

用途：


建指纹
抽 embedding
在线输出 evidence window
对 intro/chorus 等片段做后续治理


4.6 model_registry


模型注册表。

用途：


记录 model_name/model_version/output_embedding_dim

未来切换 MERT/MuQ/其他底座时不改业务表


4.7 feature_set_registry


特征版本表。

用途：


记录窗长、hop、pooling、layer、metric
同一模型不同用法变成不同 feature_set


4.8 audio_embedding


embedding 元数据事实表。

用途：


记录某个 asset/window 由哪个 feature_set 生成了什么 embedding
可指向 pgvector，也可只指向外部 parquet/npy


4.9 reference_set_registry / reference_set_member


reference 集版本表。

用途：


把“当前线上热 reference 集”提升成显式对象
支持 A/B、灰度、回滚、历史回放
让 is_reference 从单条 recording 标签升级为“可切换集合”


4.10 retrieval_index_registry


索引注册表。

用途：


同一 feature_set 可挂多个 backend / shard / version
支持 pgvector / faiss / milvus 并存


4.11 retrieval_candidate


召回候选。

用途：


保存 exact lane / semantic lane / future cover lane 的候选
便于线下分析与线上回放


4.12 match_decision


最终判定。

用途：


输出 canonical_song_id / work_id / recording_id

保留判定理由与分数


5. 示例流程图


5.1 离线建库流程

flowchart TD
    A[导入音频资产] --> B[写 recording_asset]
    B --> C[切窗并写 audio_window]
    C --> D[注册 model_registry / feature_set_registry]
    D --> E[抽取 embedding / fingerprint]
    E --> F[写 audio_embedding / audio_fingerprint]
    F --> G[构建 retrieval index]
    G --> H[登记 retrieval_index_registry]


5.2 在线检索流程

sequenceDiagram
    participant Q as Query Audio
    participant DB as PostgreSQL
    participant IDX as Retrieval Index
    participant SVC as Matching Service

    Q->>SVC: 输入 query
    SVC->>DB: 读取 active feature_set
    SVC->>IDX: exact lane / semantic lane 查询
    IDX-->>SVC: 候选 window / recording
    SVC->>DB: 回查 window -> recording -> work -> canonical_song
    SVC->>DB: 写 retrieval_candidate
    SVC->>DB: 写 match_decision
    SVC-->>Q: 返回 canonical_song_id + evidence


5.3 生产冻结前建议补硬的 4 个点


A. lineage 硬约束

建议通过 trigger / transaction invariant 保证以下链路永远一致：


recording.work_id -> work.work_id
recording.canonical_song_id -> work.canonical_song_id
audio_window.asset_id -> recording_asset.recording_id -> recording/work/song
audio_embedding.window_id -> audio_window.recording/work/song


B. reference set 版本化

建议把“热 reference 集”提升成显式对象，而不是只依赖 is_reference。
这样可以支持：


hot/cold reference 切换
A/B 对照
encoder 升级期间的双索引并存
历史回放


C. 候选实体多态约束

candidate_level + candidate_id 很灵活，但生产化时至少要加枚举/约束，避免数据面上出现无效 level。


D. 向量维度扩展规则

当前 192/768 物理表是热路径实现，不是最终维度上限。新增 encoder 维度时应遵循固定 playbook：


新增一张 audio_embedding_vector_<dim> 物理表
回填对应 feature_set 的 embeddings
构建对应索引
通过 retrieval_index_registry 切换 active 热索引


6. 推荐 DDL 的主要原则


原则 1：对象关系稳定，模型可变

稳定的是：


song/work/recording/asset/window


可变的是：


model_name
feature_set
index_backend


原则 2：向量不要写死为唯一真相

推荐把向量事实拆成：


PostgreSQL 元数据主表
向量可在 pgvector 分表或外部文件中存放


原则 3：窗口是最小证据粒度

因为版权保护最终不只是“命中这首歌”，还要回答：


命中的是哪一段
哪个录音版本
归属到哪个 work/song


7. 推荐的物理实现思路


7.1 PostgreSQL 负责


主数据
模型注册
特征注册
索引注册
检索候选
审核/决策


7.2 pgvector 负责


热 reference 集合
线上低延迟近邻查询


7.3 外部对象存储/文件层负责


原始音频
标准化音频
大体量 embedding parquet/npy
索引 shard 文件


8. 为什么这个设计更适合 SOTA 演进

因为未来你最可能变化的不是 canonical_song 结构，而是：


会变化的东西
对应表


底座模型
model_registry


特征版本
feature_set_registry


embedding dim
model_registry.output_embedding_dim


池化与层选择
feature_set_registry.pooling_strategy/layer_selection


索引后端
retrieval_index_registry.index_backend


所以 schema 的目标是：


允许模型变、索引变、特征变，但不让主数据和业务归属逻辑跟着崩。


9. DDL 文件说明

推荐直接使用：


acr-engine/sql/acr_pg_schema_v2.sql


其中包含：


主数据表
模型注册表
特征表
向量物理表（192/768 维示例）
索引建议


而原有：


acr-engine/sql/pgvector_schema.sql


建议视为：


原型版 / demo 版 / 兼容参考


10. 实施顺序建议


第一批必须先落


canonical_song
work
recording
recording_asset
audio_window
model_registry
feature_set_registry
audio_embedding
retrieval_index_registry


第二批再补


retrieval_candidate
match_decision
audio_fingerprint
更多维度的向量物理表


11. 典型注册与查询示例


11.1 注册一个开源模型

insert into model_registry (
    model_name, model_family, model_version, model_source, model_uri,
    input_sample_rate, default_window_sec, default_hop_sec, output_embedding_dim,
    pooling_supported, layer_selection_supported, is_trainable
) values (
    'mert', 'music_ssl', 'v1-95m', 'github', 'https://github.com/yizhilll/MERT',
    24000, 5.0, 2.5, 768,
    array['mean','cls'], true, false
);


11.2 注册一个 feature set

insert into feature_set_registry (
    model_id, feature_name, feature_level, extraction_granularity,
    window_sec, hop_sec, embedding_dim, pooling_strategy, layer_selection,
    normalize_l2, distance_metric, quantization_type, feature_schema_version
)
select
    model_id, 'semantic_embedding', 'window', 'sliding_window',
    5.0, 2.5, 768, 'mean', 'final',
    true, 'cosine', 'none', 'v1'
from model_registry
where model_name = 'mert' and model_version = 'v1-95m';


11.3 查询当前激活的 reference feature set

select fs.feature_set_id, mr.model_name, mr.model_version,
       fs.window_sec, fs.hop_sec, fs.pooling_strategy, fs.distance_metric
from feature_set_registry fs
join model_registry mr on mr.model_id = fs.model_id
where fs.status = 'active'
  and fs.feature_level = 'window'
  and fs.feature_name = 'semantic_embedding'
order by fs.feature_set_id desc;


11.4 从候选 window 回查到最终 song

select rc.query_id, rc.rank_no, rc.normalized_score,
       aw.window_id, aw.start_sec, aw.end_sec,
       r.recording_id, r.version_type,
       w.work_id,
       cs.canonical_song_id, cs.title, cs.primary_artist
from retrieval_candidate rc
join audio_window aw on aw.window_id = rc.evidence_window_id
join recording r on r.recording_id = aw.recording_id
join work w on w.work_id = aw.work_id
join canonical_song cs on cs.canonical_song_id = aw.canonical_song_id
where rc.query_id = :query_id
order by rc.rank_no asc;


11.5 查询某个 song 的全部 reference 资产和窗口

select cs.canonical_song_id, cs.title,
       r.recording_id, r.version_type, r.is_reference,
       ra.asset_id, ra.storage_uri,
       aw.window_id, aw.window_index, aw.start_sec, aw.end_sec
from canonical_song cs
join recording r on r.canonical_song_id = cs.canonical_song_id
join recording_asset ra on ra.recording_id = r.recording_id
left join audio_window aw on aw.asset_id = ra.asset_id
where cs.canonical_song_id = :canonical_song_id
order by r.reference_priority asc, ra.asset_id asc, aw.window_index asc;


11.6 查询某个 feature set 是否已完成索引构建

select fs.feature_set_id, mr.model_name, mr.model_version,
       ri.index_backend, ri.index_type, ri.row_count, ri.index_status, ri.built_at
from feature_set_registry fs
join model_registry mr on mr.model_id = fs.model_id
left join retrieval_index_registry ri on ri.feature_set_id = fs.feature_set_id
where fs.feature_set_id = :feature_set_id;


12. 当前建议结论

如果你今天就要开始 PostgreSQL 落库，最推荐的做法是：


先把 song/work/recording/asset/window 落稳
同时把 model_registry / feature_set_registry 落稳
Phase-1 只注册开源 encoder feature set，不写死到某个 embedding 列
先把热 reference 集上 pgvector，冷数据通过外部文件或后续索引层接入