postgres_db_schema_samples.md 15.1 KB

Raw Blame History Permalink



PostgreSQL DB Schema Samples / 落库样例与 live 测试链路


更新：2026-06-04

目标：给后续开发一个可直接照着做的 PostgreSQL 落库样例，同时保留一次真实 pgvector live 测试的证据。


一页结论

这次已经在用户提供的 PostgreSQL 上完成了下面几件事：


真实连接 PostgreSQL 成功


DSN：postgres://d2:***@127.0.0.1:5432/d2

PostgreSQL：17.5

已确认扩展 vector 存在


真实应用 schema v2 成功


使用隔离 schema：acr_test

DDL 来源：acr-engine/sql/acr_pg_schema_v2.sql

已成功创建主数据、registry、embedding、candidate、decision 等表


真实插入了一套完整的样例数据链


canonical_song -> work -> recording -> recording_asset -> audio_window
model_registry -> feature_set_registry -> audio_embedding -> retrieval_index_registry
reference_set_registry -> reference_set_member


真实跑通了一轮 PostgreSQL + pgvector 检索评测


输入：acr-engine/data/pgvector_eval/music20/*.jsonl

输出：acr-engine/data/pgvector_eval/music20/live_pgvector_report.json

live pgvector 指标和现有 FAISS stand-in 指标一致：


overall top1=0.9091

overall top3=0.9545


query_type=1: top1=1.0


query_type=7: top1=0.0, top3=0.5


lineage trigger 已被验证有效


脚本主动构造了三类错误 lineage：


recording
audio_window
audio_embedding


PostgreSQL 都正确拒绝插入


本次使用的 live 测试资产


数据库


项目
值


Host
127.0.0.1


Port
5432


DB
d2


User
d2


PostgreSQL
17.5


扩展

vector, pg_trgm, ltree, hstore 等


本次测试 schema
acr_test


代码与产物


类型
路径


推荐 DDL
acr-engine/sql/acr_pg_schema_v2.sql


live 测试脚本
acr-engine/scripts/live_pgvector_music20_eval.py


live 报告
acr-engine/data/pgvector_eval/music20/live_pgvector_report.json


FAISS 对照报告
acr-engine/data/pgvector_eval/music20/songid_eval_report_fresh.json


历史对照报告
acr-engine/data/pgvector_eval/music20/songid_eval_report.json


这次实际落进去的数据链

flowchart LR
    A[reference_embeddings.jsonl] --> B[canonical_song]
    B --> C[work]
    C --> D[recording]
    D --> E[recording_asset]
    E --> F[audio_window]
    F --> G[audio_embedding]
    G --> H[audio_embedding_vector_192]

    I[model_registry] --> J[feature_set_registry]
    J --> G

    K[reference_set_registry] --> L[reference_set_member]
    D --> L

    M[query_embeddings.jsonl] --> N[SQL pgvector search]
    H --> N
    N --> O[retrieval_candidate]
    O --> P[match_decision]


为什么这次 live 测试要把 24 维 embedding pad 到 192 维

当前 schema v2 里提供了：


audio_embedding_vector_192
audio_embedding_vector_768


而这次本地 music20 样例 embedding 是 24 维 chroma 特征。

所以本次 live 测试采用的策略是：


逻辑维度：24


物理落盘维度：192


做法：后面补 0，写入 vector(192)


这样做的原因：


不需要临时改 schema
仍然可以验证 schema v2 + pgvector + retrieval 链路
对这批样例的余弦相似度排序不会产生方向性错误（所有向量都以同样方式补零）


这只是验证链路用法。

生产里应按真实 encoder 维度选择：


MERT / MuQ 之类高维 embedding：直接落合适物理表
如果后续维度更多，建议继续扩成 audio_embedding_vector_<dim> 分桶策略


本次实际落盘样例

以下内容来自 acr_test schema 的真实查询结果。


1. canonical_song

{"canonical_song_id":1,"biz_song_code":"100","title":"Song 100","primary_artist":"Artist 100","rights_status":"protected"}
{"canonical_song_id":2,"biz_song_code":"101","title":"Song 101","primary_artist":"Artist 101","rights_status":"protected"}


2. work

{"work_id":1,"canonical_song_id":1,"work_code":"work-100","work_title":"Song 100","composer":"Composer 100"}
{"work_id":2,"canonical_song_id":2,"work_code":"work-101","work_title":"Song 101","composer":"Composer 101"}


3. recording

{"recording_id":1,"work_id":1,"canonical_song_id":1,"recording_code":"rec-100","version_type":"master_reference","is_reference":true,"reference_priority":100}
{"recording_id":2,"work_id":2,"canonical_song_id":2,"recording_code":"rec-101","version_type":"master_reference","is_reference":true,"reference_priority":101}


4. recording_asset

{"asset_id":1,"recording_id":1,"asset_role":"reference_audio","storage_uri":"/workspace/downloads/100/type_11/93dfdeb0-7da5-42a8-9c71-cf12af57dd191650256918.wav","storage_scheme":"file","duration_sec":8.0,"ingest_status":"ready"}
{"asset_id":2,"recording_id":2,"asset_role":"reference_audio","storage_uri":"/workspace/downloads/101/type_11/83c0c07f-4f96-4ff4-998c-58db910f3cfa1650256915.wav","storage_scheme":"file","duration_sec":8.0,"ingest_status":"ready"}


5. audio_window

{"window_id":1,"asset_id":1,"recording_id":1,"work_id":1,"canonical_song_id":1,"window_index":0,"start_sec":0.0,"end_sec":8.0,"segment_role":"reference","segment_type":"full_clip"}
{"window_id":2,"asset_id":2,"recording_id":2,"work_id":2,"canonical_song_id":2,"window_index":0,"start_sec":0.0,"end_sec":8.0,"segment_role":"reference","segment_type":"full_clip"}


6. model_registry / feature_set_registry

{"model_id":1,"model_name":"local_chroma24","model_family":"chroma_baseline","model_version":"v1","output_embedding_dim":24,"default_window_sec":8.0}
{"feature_set_id":1,"model_id":1,"feature_name":"chroma24_songid_eval","embedding_dim":24,"distance_metric":"cosine","feature_schema_version":"v1"}


7. audio_embedding

{"embedding_id":1,"feature_set_id":1,"asset_id":1,"window_id":1,"recording_id":1,"canonical_song_id":1,"embedding_storage_mode":"pgvector_inline_192_padded","is_indexed":true}
{"embedding_id":2,"feature_set_id":1,"asset_id":2,"window_id":2,"recording_id":2,"canonical_song_id":2,"embedding_storage_mode":"pgvector_inline_192_padded","is_indexed":true}


8. reference_set_registry / retrieval_index_registry

{"reference_set_id":1,"set_name":"music20_live_reference","encoder_scope":"local_chroma24","status":"active"}
{"retrieval_index_id":1,"feature_set_id":1,"index_name":"music20_live_pgvector_hnsw","index_backend":"pgvector","index_type":"hnsw_cosine","row_count":20,"index_status":"active"}


9. retrieval_candidate / match_decision

{"retrieval_candidate_id":1,"query_id":"music20-q0000-t1-song100","source_lane":"semantic","candidate_level":"canonical_song","candidate_id":1,"raw_score":0.99998549,"normalized_score":0.90998694,"rank_no":1}
{"retrieval_candidate_id":2,"query_id":"music20-q0000-t1-song100","source_lane":"semantic","candidate_level":"canonical_song","candidate_id":17,"raw_score":0.9527432,"normalized_score":0.86746888,"rank_no":2}
{"match_decision_id":1,"query_id":"music20-q0000-t1-song100","canonical_song_id":1,"decision_status":"matched","decision_score":0.90998694}


本次 live 测试的表规模


表
行数


canonical_song
20


work
20


recording
20


recording_asset
20


audio_window
20


audio_embedding
20


retrieval_candidate
220


match_decision
22


说明：


20 条 reference song
22 条 query
每条 query 写入 top10 candidate，因此 22 * 10 = 220


本次测试链路与逻辑


A. schema / 数据完整性测试


连接 PostgreSQL
创建隔离 schema：acr_test

执行 acr_pg_schema_v2.sql

初始化：


model_registry
feature_set_registry
reference_set_registry
retrieval_index_registry


导入 20 条 reference 样例
验证表计数是否正确
主动插入三类错误 lineage：


recording.canonical_song_id 与 work.canonical_song_id 不一致

audio_window.recording_id 与 recording_asset.recording_id 不一致

audio_embedding 的 canonical_song_id 与父 audio_window 不一致


预期 PostgreSQL trigger 拒绝这些坏写入


B. live 检索评测测试


从 reference_embeddings.jsonl 读 20 条 reference embedding
写入 audio_embedding + audio_embedding_vector_192

从 query_embeddings.jsonl 读 22 条 query embedding
每条 query 用 SQL 执行 pgvector cosine 检索
在应用层做 song-level aggregation：


max_sim
top3_avg
vote
combined = 0.6 * max_sim + 0.3 * top3_avg + 0.1 * vote_factor


将 top10 候选落表到 retrieval_candidate

将 top1 决策落表到 match_decision

计算：


overall top1/top3/top10/mrr

by_query_type
confusion_focus


C. confusion test 口径

当前这次 live 样例里只实际包含：


type_1
type_7


因此：


type_7 可以作为 当前 live confusion check


type_8 / type_16 这次 live JSONL 没覆盖到，只能结合历史业务样本结果一起看


live pgvector 结果


1. overall


指标
值


query 数
22


top1
0.9091


top3
0.9545


top10
0.9545


MRR
0.9343


mean rank
1.8182


2. by query type


query_type
count
top1
top3
top10
解释


1
20
1.0
1.0
1.0
clean / near-clean


7
2
0.0
0.5
0.5
当前 live confusion 样例


8
0
N/A
N/A
N/A
本次 live JSONL 未覆盖


16
0
N/A
N/A
N/A
本次 live JSONL 未覆盖


3. 和现有 FAISS stand-in 的一致性


路径
overall top1
overall top3
type_1 top1
type_7 top1
type_7 top3


live PostgreSQL + pgvector
0.9091
0.9545
1.0
0.0
0.5


FAISS stand-in
0.9091
0.9545
1.0
0.0
0.5


结论：


当前 acr_test 上的 live pgvector 路径，已经和现有 stand-in 检索逻辑对齐。

问题不在“PostgreSQL 落盘导致召回变坏”，而在当前样例 embedding 对混淆类 query 本身就不够强。


本轮补充：完整 lineage trigger 负例覆盖

本轮重新执行 live 脚本后，live_pgvector_report.json 中的 lineage_negative_test 已从“单条 audio_window 验证”升级为“三类坏写入全部验证”：


case
结果
PostgreSQL 返回


recording_lineage_mismatch
拒绝成功
recording.canonical_song_id ... mismatches work.canonical_song_id ...


audio_window_lineage_mismatch
拒绝成功
Invalid asset_id=... or recording_id=... for audio_window


audio_embedding_lineage_mismatch
拒绝成功
audio_embedding lineage mismatch


这意味着：


当前 schema v2 的三条核心 lineage trigger，已经都有真实负例证据，而不只是“理论上存在”。


同时，本轮还补了两条机械验证证据：


py_compile 通过：live_pgvector_music20_eval.py


git diff --check 通过：本轮脚本、报告、文档变更无格式问题


混淆测试补充视图


1. 当前 live 样例视图


query_type
数据来源
top1
top3
结论


7
live_pgvector_report.json
0.0
0.5
已明显偏弱


2. 历史本地 20-song 小样本视图

来自：acr-engine/data/local_eval/music20_summary.json


query_type
top1
top3


1
1.0
1.0


7
0.45
0.65


8
0.4667
0.7333


16
0.4167
0.4167


说明：


这是本地小样本 chroma/FAISS sanity flow 的结果
它比当前 live JSONL 的 type_7 好，是因为样本构成不同
不能把这个结果直接当作生产效果，但可以当作“当前特征在小样本内并非完全不可用”的旁证


3. 历史业务语料 voice correctness 视图


query_type
文件
top1
top3
结论


7
voice_workspace20_type7_eval.json
0.0
0.05
极弱


8
voice_workspace20_type8_eval.json
0.0
0.0
极弱


16
voice_workspace20_type16_eval.json
0.0
0.0
极弱


结论：


只要 query 进入更真实、更混淆的业务样本，当前这条 baseline 仍然远远不够。

PostgreSQL 落库没问题，真正的问题还是 embedding lane 对 hard case 的判别力不足。


这次验证了什么，没验证什么


已验证


PostgreSQL 真实连通可用

vector 扩展可用
schema v2 可以真实 apply
main lineage trigger 可以真实拦截坏数据
样例数据链可以按 song -> work -> recording -> asset -> window -> embedding 落盘
live pgvector 检索和现有 stand-in 逻辑一致

retrieval_candidate / match_decision 可以真实承载在线结果


未验证


还没把 MERT / MuQ 真正接进这套 live 路径
这次 live 样例没有覆盖 type_8 / type_16 的 JSONL embedding
这次只验证了 20-song 级别，不代表 30w song 的索引性能
还没做多 recording / 多 version / cover lane 的聚合测试


推荐的下一步


路线 1：继续做 PostgreSQL 工程化


把 live_pgvector_music20_eval.py 泛化成：


可导入任意 manifest/reference set
可选择 encoder / feature set
可直接生成 retrieval_candidate / match_decision 报告


增加：


audio_embedding_vector_1024 / 其他常见维度表
bulk COPY / batched insert
HNSW 参数管理


路线 2：继续做混淆类效果验证


构造真正覆盖 type_8 / type_16 的 query embedding JSONL
用同一条 live script 重跑 PostgreSQL 评测
对比：


Chromaprint only
semantic only
fusion


输出 confusion bucket 报告


路线 3：切到 Phase-1 encoder-only 主线


保留当前 PostgreSQL 结构不变
将 local_chroma24 替换成：


MERT-v1-95M
MuQ


继续复用：


model_registry
feature_set_registry
reference_set_registry
retrieval_index_registry


重新测：


clean
type_7
type_8
type_16
业务 voice bucket


复现命令


1. live PostgreSQL + pgvector 测试

cd /workspace/acr-engine
/usr/local/miniconda3/bin/python scripts/live_pgvector_music20_eval.py \
  --dsn 'postgres://d2:d2pass@127.0.0.1:5432/d2' \
  --schema acr_test \
  --reset-schema \
  --output data/pgvector_eval/music20/live_pgvector_report.json


2. FAISS stand-in 对照测试

cd /workspace/acr-engine
/usr/local/miniconda3/bin/python scripts/evaluate_songid_pgvector_path.py \
  --reference-embeddings-jsonl data/pgvector_eval/music20/reference_embeddings.jsonl \
  --query-embeddings-jsonl data/pgvector_eval/music20/query_embeddings.jsonl \
  --output data/pgvector_eval/music20/songid_eval_report_fresh.json


一句话结论


PostgreSQL 这条路已经可以真实落 schema、落样例、落 candidate、落 decision，也能真实跑 pgvector 检索。

当前最大的短板不再是“怎么存”，而是 当前 baseline embedding 对混淆 query 的召回仍然明显不够。