postgresql-data-model.md 20.6 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 无法统一聚合的问题


0.1 为什么会感觉链路很多

本质上当前文档把 3 类问题 放在同一个总图里，所以看起来链路很长：


业务归属层：canonical_song / work / recording


解决“最终归哪个 song_id / work_id / recording_id”


物理音频层：recording_asset / audio_window


解决“实际文件是什么、切成了哪些检索窗口”


检索计算层：model_registry / feature_set_registry / audio_embedding / audio_fingerprint / retrieval_index_registry


解决“用了哪个模型、哪套特征、哪套索引”


所以这不是一条单链，而是：


一条 归属回溯链

一条 音频资产链

一条 特征/索引链


把三者混看，就会误以为每次查询都要手工经过所有表。实际上在线检索真正关心的是：

window -> candidate -> recording -> song


0.2 当前是否可以简化

可以。

如果当前阶段的目标是：


先服务版权保护场景，让 query 能快速稳定地命中正确 song_id


那么 Phase-1 完全可以收敛为下面这套 最小可用骨架：

song -> recording -> recording_asset -> audio_window
                                   -> audio_fingerprint
                                   -> audio_embedding


为了支持模型替换，再保留一个轻量版本登记层：

feature_set_registry


也就是说，Phase-1 最小主链可以压缩成：

song -> recording -> asset -> window -> feature


其中 feature 可具体落成：


audio_fingerprint
audio_embedding


0.3 哪些层建议 Phase-1 保留，哪些层可以弱化


建议保留


层
是否保留
原因


song
保留
最终业务返回对象


recording
保留
同一 song 下会有多个版本/录音


recording_asset
保留
一个 recording 可能有多个真实文件


audio_window
保留
检索和 evidence 的最小计算单元


feature_set_registry
保留
避免把 embedding/fingerprint 固化成表列


audio_embedding / audio_fingerprint

保留
真正的检索特征事实表


可以弱化或延期


层
当前建议
原因


work
可延期
如果当前只需稳定返回 song_id，可先不显式拆作品层


canonical_song
可与 song 合并理解
当前重点不是权利层深治理，而是先完成可用归属主键


retrieval_index_registry
可先弱化
Phase-1 可先把索引治理做轻，不必一开始做太重


match_decision 全量审计
可逐步补齐
先保证召回闭环，再加强审计/解释性


0.3.1 recording 和 recording_asset 能不能合并

可以合并，但只适合非常早期、非常受控的数据集。


什么时候可以临时合并

只有当下面条件基本都成立时，才可以把二者临时看成一个对象：


每个 song 只有一个可用录音版本
每个录音只有一个音频文件
不区分 master / distribution / captured / query_sample
不需要追踪同一录音的多个来源文件
不需要后续补高码率、补母带、补平台版本


在这种情况下，可以暂时把模型理解成：

song -> recording_asset -> audio_window


也就是让 recording_asset 同时承担“版本对象 + 物理文件对象”的职责。


为什么长期不建议合并

因为 recording 和 recording_asset 回答的是两个不同问题：


recording 回答：这是哪个录音版本


recording_asset 回答：这个录音版本对应哪个具体文件


一旦进入真实版权保护场景，下面几类情况会非常常见：


同一录音有多个文件版本
例如 wav/flac/mp3、不同码率、不同平台导出件。

同一 song 有多个录音版本
例如 official/live/remaster/short/bgm cut。

同一录音要接多个来源
例如平台抓取、业务导出、人工补档。

query 命中的是 asset，但归属要落到 recording/song
如果不拆层，后面聚合和去重会比较乱。


当前最推荐的判断

对于你现在这个目标：


约 100w 音频
约 30w 歌曲
面向版权保护 / 听歌识曲 / 版本归属


不建议把 recording 和 recording_asset 合并进正式 schema。

原因很直接：


数据量已经不小
后续大概率会遇到多版本、多来源、多文件问题
现在省掉一层，后面重构成本会更高


更务实的折中方案

如果你觉得当前实现心智负担太高，可以不在产品/算法讨论里反复强调 recording_asset，而是采用下面口径：

song -> recording -> asset -> window -> feature


也就是说：


概念上保留 recording 和 asset 两层

沟通上简写 为 recording -> asset


实现上继续分表，避免未来返工


这通常是 Phase-1 最稳妥的折中。


0.4 一个更容易理解的口径

建议把当前体系理解为下面两条核心链：


归属链

window -> asset -> recording -> song


作用：


检索命中后，回溯最终归属到哪个 song_id


特征链

window -> fingerprint / embedding -> candidate -> aggregate


作用：


真正完成召回、打分、聚合与排序


这样看时，整个设计就不再是“很多层没必要”，而是：


归属层负责回答是谁
窗口层负责回答命中了哪一段
特征层负责回答怎么检索出来


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 音频目标。


当前最建议的简化口径

如果团队正在进入 Phase-1 实施，不必把所有表同时视为“首批必须上线的复杂系统”。
更推荐按下面顺序理解和落库：


song -> recording -> recording_asset -> audio_window
feature_set_registry -> audio_fingerprint / audio_embedding

reference_set_registry 与更重的索引治理随后补齐


1.3 Phase-1 极简 schema 视图

如果只从“第一阶段必须落哪些表”来理解，推荐把正式设计压缩成下面这组最小表集合：


层
推荐保留表
当前作用


归属层

song（或当前 canonical_song 的等价口径）, recording

最终归属到 song，区分不同录音版本


资产层
recording_asset
管理真实音频文件、来源与编码版本


窗口层
audio_window
支撑 offset / evidence / 多段投票


特征层

feature_set_registry, audio_fingerprint, audio_embedding

管理 fingerprint / embedding 的生成事实


reference 层

reference_set_registry, reference_set_member

管理当前线上 reference 集


也就是说，Phase-1 真正应该优先落稳的是：

song -> recording -> recording_asset -> audio_window
feature_set_registry -> audio_fingerprint / audio_embedding
reference_set_registry -> reference_set_member


这版极简 schema 明确不要求第一天就重投入的内容

可以后补：


work
更重的 retrieval_index_registry

更细的 retrieval_candidate / match_decision 在线审计表
复杂的多 lane 重排治理表


但是极简不等于扁平

即使走极简版，也不建议退回到下面这种扁平结构：

song -> embedding
song -> fingerprint


原因：


没有 recording，版本信息会丢
没有 asset，文件来源与去重会乱
没有 window，evidence/offset/多段聚合会弱很多
没有 feature_set_registry，模型升级会把 schema 写死


一个最实用的实现口径

如果团队现在就要开干，最推荐的实施顺序是：


先落 song / recording / recording_asset / audio_window

再落 feature_set_registry / audio_fingerprint / audio_embedding

再落 reference_set_registry / reference_set_member

最后再补 work / retrieval_index_registry / match_decision 等增强层


这样既能保持当前 Phase-1 简洁，也不会破坏未来扩展。


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，冷数据通过外部文件或后续索引层接入