02 数据库设计

适用读者：工程师与 AI Agent。本册描述 Podcast 目录服务的 PostgreSQL 16 数据库设计，可独立查阅与集成。 技术栈关联：drizzle-orm@0.45（schema 镜像 + 查询构建）、postgres@3.4（驱动）。 真实性约定：所有结论均给出 file_path:line 锚点。读不到的内容标注「未实现/待确认」，不臆造。

数据库引擎为 PostgreSQL 16，运行于本机 colima 虚拟机内的 podcast-pg 容器（docs/SERVICE-OPS.md:23）。 全库共 8 张正式表 + 17 个索引，承载 293,781 播客、22,867,469 剧集、354,508 分类关联（docs/SERVICE-OPS.md:23、scripts/etl/perf-stats.sql:2）。

路径约定：本册凡以 scripts/etl/... 或 src/... 开头的相对路径，均相对于仓库内 podcast-api/ 目录，即 /Volumes/External/develop/musicfab/podcast/podcast-api/。

1. 设计原则

数据库被切成三层，每层有截然不同的读写特征与生命周期。理解这三层的差异是理解全部 DDL 与索引的前提。

原则一：剧集是大表，一切索引围绕「先定位 show、再翻 show 内剧集」展开。 episodes 表 2286 万行，是 podcasts（29 万行）的约 78 倍。所有剧集查询都先经 podcast_uid 缩小到单个节目，再用 keyset 游标在节目内按发布时间翻页（scripts/etl/finalize.sql:98-99、src/lib/services/catalog.service.ts:571-575），避免任何跨节目的全表扫描。

原则二：目录只读，可在装载时预计算一切派生数据。 目录是一份只读快照，因此「按语言/分类的节目计数」这类聚合在装载时算一次、落进统计表，而非每个请求都对 35 万行的分类 JOIN 做 count(DISTINCT)（实测约 1.7s，scripts/etl/perf-stats.sql:2-3）。同理，节目卡片需要的「最新一集标题/时间」被反规范化进 podcasts 表（scripts/etl/finalize.sql:103-112）。

原则三：用户态可写，与目录解耦，重建目录不影响用户数据。 playback_progress / download_tasks 由 API 增量 upsert（src/lib/db/schema.ts:78-79、97），生命周期独立于目录快照。两表通过外键引用 episodes(episode_uid)，但目录重建采用先 DROP 后建（见 §7），重建前必须先处理这两张表的外键依赖。

原则四：快照式批量重建，而非增量更新。 全部目录数据走「JSONL → TSV → COPY 进 staging → finalize 去重建约束建索引 → ANALYZE」的批处理管线（scripts/etl/schema.sql:2-3），先无约束极速灌入、装载完再一次性建索引，比逐行 INSERT 维护索引快一个数量级。

2. ER 关系图

                         ┌──────────────────────────────────────┐
                         │              podcasts                 │  29 万行（核心目录·只读）
                         │  PK podcast_uid                       │
                         │     name / author / description       │
                         │     lang / tsv(生成列) / episode_count │
                         │     latest_episode_title (反规范化)    │
                         └───────┬───────────────────┬───────────┘
              podcast_uid (FK)   │ 1               1 │  podcast_uid (FK)
                                 │ N               N │
              ┌──────────────────▼──────┐   ┌────────▼─────────────────────┐
              │       episodes          │   │     podcast_categories       │  35 万行
              │  PK episode_uid         │   │  PK (podcast_uid, cat, sub)  │  (N—N 中间表)
              │  FK podcast_uid         │   │     cat / sub                │
              │     publish_date        │   └──────────────────────────────┘
              │     audio_url/duration  │              │
              └───────┬─────────────────┘              │ 概念上的 N—N：
        episode_uid   │ 2286 万行                       │ podcasts ←(cat,sub)→ 「分类维度」
        (FK)          │                                 ▼ (无独立 categories 实体表，
        ┌─────────────┼──────────────┐         分类是字符串 cat/sub，统计落入下方三表)
        ▼             ▼              ─┘
┌───────────────┐  ┌──────────────────┐
│playback_      │  │ download_tasks   │      用户态·可写（不随目录重建）
│  progress     │  │  PK task_id      │
│ PK(user_id,   │  │  FK episode_uid  │
│   episode_uid)│  │  user_id/status  │
│ FK episode_uid│  └──────────────────┘
└───────────────┘

预聚合统计表（只读·随目录重建，无外键，按值关联 podcasts.lang / podcast_categories.cat/sub）：
┌────────────────────┐  ┌──────────────────────────┐  ┌────────────────────────────────┐
│ language_stats     │  │ category_stats           │  │ category_sub_stats             │
│ PK lang            │  │ PK (lang, cat)           │  │ PK (lang, cat, sub)            │
│    podcast_count   │  │    podcast_count         │  │    podcast_count               │
└────────────────────┘  └──────────────────────────┘  └────────────────────────────────┘

关系说明：

3. 全部 8 张表的完整 DDL 与字段说明

staging 表（podcasts_staging / episodes_staging / podcast_categories_staging，scripts/etl/schema.sql:9-41）是 ETL 临时表，无约束、装载完即 DROP（scripts/etl/run-etl.sh:34-35），不计入这 8 张正式表。

3.1 核心目录表

3.1.1 podcasts — 节目（show）

DDL（scripts/etl/finalize.sql:11-32，反规范化列追加于 103-104）：

CREATE TABLE podcasts (
    podcast_uid           TEXT PRIMARY KEY,
    name                  TEXT NOT NULL,
    author                TEXT,
    description           TEXT,
    lang                  TEXT,
    web                   TEXT,
    logo                  TEXT,
    color                 TEXT,
    detail_url            TEXT,
    episode_count         INT  NOT NULL DEFAULT 0,
    episode_count_claimed INT,
    status                TEXT,
    build_id              TEXT,
    tsv tsvector GENERATED ALWAYS AS (
        setweight(to_tsvector('simple', coalesce(name, '')),        'A') ||
        setweight(to_tsvector('simple', coalesce(author, '')),      'B') ||
        setweight(to_tsvector('simple', coalesce(description, '')), 'C')
    ) STORED
);
-- 装载后追加（finalize.sql:103-104）：
ALTER TABLE podcasts ADD COLUMN latest_episode_title TEXT;
ALTER TABLE podcasts ADD COLUMN latest_publish_date TIMESTAMPTZ;

去重策略：INSERT ... SELECT DISTINCT ON (podcast_uid) ... WHERE podcast_uid IS NOT NULL（finalize.sql:36-40）——同一 podcast_uid 重复行只保留首行，NULL uid 行丢弃。

3.1.2 episodes — 剧集

DDL（scripts/etl/finalize.sql:42-50）：

CREATE TABLE episodes (
    episode_uid   TEXT PRIMARY KEY,
    podcast_uid   TEXT NOT NULL REFERENCES podcasts(podcast_uid),
    name          TEXT NOT NULL,
    audio_url     TEXT,
    media_type    TEXT,
    publish_date  TIMESTAMPTZ,
    duration      INT
);

去重与孤儿过滤：DISTINCT ON (e.episode_uid) + WHERE e.episode_uid IS NOT NULL AND EXISTS(对应 podcast 存在)（finalize.sql:53-58）——剧集若所属节目缺失则整行丢弃并由 runner 用行数差计数。 无效剧集（缺 episodeUID）在 transform 阶段即跳过并计入 stats.episodesNoUid（transform.mjs:117-120）。

3.1.3 podcast_categories — 节目-分类关联（N—N 中间表）

DDL（scripts/etl/finalize.sql:60-65）：

CREATE TABLE podcast_categories (
    podcast_uid TEXT NOT NULL REFERENCES podcasts(podcast_uid),
    cat         TEXT NOT NULL,
    sub         TEXT NOT NULL DEFAULT '',
    PRIMARY KEY (podcast_uid, cat, sub)
);

去重：transform 阶段对同节目内 cat+sub 已去重（transform.mjs:134-139 的 seen Set）；finalize 再用 INSERT ... SELECT DISTINCT + EXISTS(podcast 存在) 兜底（finalize.sql:67-71）。共 354,508 关联行（perf-stats.sql:2）。

3.2 预聚合统计表

三张表均由 perf-stats.sql 用 CREATE TABLE ... AS SELECT 在装载时算一次，幂等（DROP TABLE IF EXISTS，perf-stats.sql:7/15/24）。无外键，按值关联目录。

3.2.1 language_stats — 按语言的节目计数

DDL（scripts/etl/perf-stats.sql:7-12）：

CREATE TABLE language_stats AS
SELECT coalesce(lang, '') AS lang, count(*)::int AS podcast_count
FROM podcasts GROUP BY 1;
ALTER TABLE language_stats ADD PRIMARY KEY (lang);

注：podcasts.lang 是单值，故各语言计数之和与总节目数精确相等（perf-stats.sql:6）。

3.2.2 category_stats — 按 (语言, 主分类) 的去重节目计数

DDL（scripts/etl/perf-stats.sql:15-21）：

CREATE TABLE category_stats AS
SELECT coalesce(p.lang, '') AS lang, c.cat, count(DISTINCT c.podcast_uid)::int AS podcast_count
FROM podcast_categories c JOIN podcasts p USING (podcast_uid)
GROUP BY 1, 2;
ALTER TABLE category_stats ADD PRIMARY KEY (lang, cat);

3.2.3 category_sub_stats — 按 (语言, 主分类, 子分类) 的去重节目计数

DDL（scripts/etl/perf-stats.sql:24-31）：

CREATE TABLE category_sub_stats AS
SELECT coalesce(p.lang, '') AS lang, c.cat, c.sub, count(DISTINCT c.podcast_uid)::int AS podcast_count
FROM podcast_categories c JOIN podcasts p USING (podcast_uid)
WHERE c.sub <> ''
GROUP BY 1, 2, 3;
ALTER TABLE category_sub_stats ADD PRIMARY KEY (lang, cat, sub);

注：WHERE c.sub <> '' 排除「仅主分类」的关联行——它们不携带子分类维度（perf-stats.sql:23/29）。

3.3 用户态表

3.3.1 playback_progress — 播放进度

DDL（scripts/etl/finalize.sql:74-82）：

CREATE TABLE playback_progress (
    user_id          TEXT NOT NULL,
    episode_uid      TEXT NOT NULL REFERENCES episodes(episode_uid),
    position_seconds INT  NOT NULL,
    duration_seconds INT,
    completed        BOOLEAN NOT NULL DEFAULT false,
    updated_at       TIMESTAMPTZ NOT NULL DEFAULT now(),
    PRIMARY KEY (user_id, episode_uid)
);

PK (user_id, episode_uid) 使「每用户每剧集一条进度」并支持 upsert（schema.ts:93）。

3.3.2 download_tasks — 下载任务与配额

DDL（scripts/etl/finalize.sql:84-95）：

CREATE TABLE download_tasks (
    task_id      TEXT PRIMARY KEY,
    user_id      TEXT NOT NULL,
    episode_uid  TEXT NOT NULL REFERENCES episodes(episode_uid),
    format       TEXT NOT NULL,
    quality      TEXT NOT NULL,
    status       TEXT NOT NULL DEFAULT 'pending',
    file_size_bytes BIGINT,
    created_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
    completed_at TIMESTAMPTZ
);

注：偏差 D-6——任务创建后立即完成（schema.ts:97），无异步队列。

4. 全部 17 索引清单

17 个索引 = 8 张表的 8 个主键（PRIMARY KEY 自动建唯一索引）+ 9 个显式 CREATE INDEX。 8 个主键：podcasts(podcast_uid)、episodes(episode_uid)、podcast_categories(podcast_uid,cat,sub)、playback_progress(user_id,episode_uid)、download_tasks(task_id)、language_stats(lang)、category_stats(lang,cat)、category_sub_stats(lang,cat,sub)。

已被废弃的过渡索引（建后又 DROP，不计入 17）：idx_podcasts_episode_count、idx_podcasts_latest_pub（finalize.sql:113/119 建、perf-stats.sql:43-44 删）——它们的用途被 #15/#17 的 keyset 复合索引覆盖。

4.1 重点：keyset 复合索引（#10/#15/#16/#17）

每个 shows 列表排序方式都配一个「(排序键, 唯一兜底键)」的覆盖索引，使任意一页都是一次索引范围扫描，而非 OFFSET 跳行（perf-stats.sql:33-34）。

• 原理：OFFSET N 必须先扫描并丢弃前 N 行，深翻页代价随页码线性增长；keyset 改用 WHERE (sortkey, uid) < (last_sortkey, last_uid) 直接定位到上次游标位置，恒定代价。
• 唯一兜底键：排序键（如 episode_count、name、latest_publish_date）可能重复，追加唯一的 podcast_uid 作第二排序键，保证全序、游标无歧义。
• #10 是剧集表的核心：(podcast_uid, publish_date DESC, episode_uid DESC)，先按 podcast_uid 定位到单节目，再在节目内按发布时间倒序 keyset 翻页，对 2286 万行剧集表至关重要（src/lib/services/catalog.service.ts:571-575/619）。NULL 发布时间的剧集落入尾部，退化为仅按 episode_uid 翻页（catalog.service.ts:619）。
• 建索引时序：idx_episodes_podcast_pub 必须先于反规范化 UPDATE 建好，使 DISTINCT ON 取最新一集时直接走索引（finalize.sql:101-102）。

4.2 重点：tsvector GIN 全文检索（#12）

idx_podcasts_tsv 是建在生成列 tsv 上的 GIN 索引（finalize.sql:116）。 查询侧用 websearch_to_tsquery('simple', q)，以 tsv @@ tsquery 命中、ts_rank(tsv, tsquery) 排序（src/lib/services/catalog.service.ts:371-377）。因 ts_rank 无稳定 keyset，全文检索分页退化为 offset 分页（catalog.service.ts:31/380）。

4.3 重点：pg_trgm 三元组索引（#13）

启用 pg_trgm 扩展（schema.sql:5），在 name 上建 gin_trgm_ops GIN 索引（finalize.sql:117），支持基于三元组的模糊匹配与相似度查询（容错拼写、子串匹配），与 #12 的精确全文检索互补。

5. tsvector 生成列

podcasts.tsv 是数据库侧的 GENERATED ALWAYS AS (...) STORED 列（finalize.sql:27-31、schema.ts:34-36），由三个字段加权拼接而成：

tsv tsvector GENERATED ALWAYS AS (
    setweight(to_tsvector('simple', coalesce(name, '')),        'A') ||
    setweight(to_tsvector('simple', coalesce(author, '')),      'B') ||
    setweight(to_tsvector('simple', coalesce(description, '')), 'C')
) STORED

权重经 ts_rank 转化为相关度得分，使「名称命中」的节目排在「仅简介命中」之上。

为什么用 simple 配置而非语言 stemmer：目录混合多语言（lang 跨多种语言），若用某一语言的词干分析（如 english）会对其他语言做错误词干化（finalize.sql:25-26、schema.ts:33）。simple 配置不做词干、不去停用词，仅按空白与标点切词、转小写，对混合语料是安全的最小公约数。查询侧相应使用 websearch_to_tsquery('simple', ...) 与之对齐（catalog.service.ts:349/371）。

约束：tsv 由数据库自动维护，API 永不写入，只能经 raw sql 片段引用（schema.ts:13-15 注释）。

6. ETL 管线

四步串行管线（scripts/etl/run-etl.sh:12-29），输入 6 个 shard 的 JSONL，产出三张正式表。

dataset/shard0..5/podcasts.jsonl
        │  ① transform.mjs（流式逐行解析，脏行跳过计数，不中断批次）
        ▼
podcasts.tsv / episodes.tsv / categories.tsv（PostgreSQL COPY text 格式）
        │  ② schema.sql 建无约束 staging 表
        ▼
② COPY → *_staging（极速灌入，无索引无约束）
        │  ③ finalize.sql：DISTINCT ON 去重 + 加 PK/FK 约束 + 反规范化最新一集 + 建索引 + ANALYZE
        ▼
podcasts / episodes / podcast_categories（带约束与索引）
        │  ④ perf-stats.sql：预聚合三张统计表 + 建 3 个 keyset 复合索引 + ANALYZE
        ▼
language_stats / category_stats / category_sub_stats + keyset 索引
        │  ⑤ 回显行数 → DROP staging
        ▼
完成

6.1 各步骤与真实行数

脏行容错（原则一致性铁律）：transform 永不因单行异常中断批次（transform.mjs:2-3 注释），三类异常各自计数——dirtyLines（行解析失败或缺 uid，transform.mjs:89/95）、episodesNoUid（剧集缺 uid，transform.mjs:117-120）。本次全量入库 0 脏行，但容错逻辑常驻。

reload 旁路：reload.sh 跳过 ① transform，直接用已建好的 TSV 走 ②③④⑤，约 20 分钟（reload.sh:2、docs/SERVICE-OPS.md:135）——用于数据卷丢失后从 TSV 快速重建。

7. 数据一致性保证与重建/原子切换

7.1 装载期一致性保证

7.2 重建与原子切换（现状与约束）

当前 finalize.sql 采用 就地 DROP + 重建：开头 DROP TABLE IF EXISTS playback_progress, download_tasks, podcast_categories, episodes, podcasts，按 FK 依赖逆序 DROP（先用户态/关联、后核心表，finalize.sql:5-9），再重建。

由此带来两点必须注意：

1. 目录重建会连带 DROP 用户态表：finalize.sql:5-6 显式先 DROP playback_progress 与 download_tasks。这是因为它们经 FK 引用 episodes，不先 DROP 会因外键依赖无法 DROP episodes。因此当前重建流程不保留用户态数据——重建前如需保留 playback_progress / download_tasks，须自行备份后再恢复（代码中未实现自动备份/恢复，属待确认的运维约束）。

2. 非真正原子切换：DROP 到重建完成之间存在表不可用窗口，不是「新表建好再原子改名替换旧表」的零停机方案。代码中未实现双表 + ALTER TABLE RENAME 式的原子切换。生产做法是离线窗口执行 reload/run-etl（docs/SERVICE-OPS.md:135），重建期间服务暂不可查目录。

3. 幂等性：finalize.sql（DROP IF EXISTS 开头）与 perf-stats.sql（每表/每索引 DROP IF EXISTS，perf-stats.sql:7/15/24/35-44）均可重复执行，重跑结果一致。

小结：当前是「快照式就地重建 + ANALYZE」，一致性靠去重/外键/NULL 兜底/越界保护在装载期一次性保证；尚未实现零停机原子切换与用户态自动保留，这是后续可演进方向。