75 lines
6.0 KiB
Markdown
75 lines
6.0 KiB
Markdown
# 数据库迁移
|
||
|
||
本目录的 `.sql` 是**手工一次性迁移**（服务启动不做 DDL，结构由 `dbtool` 导出的 `db_dump.sql` 维护）。
|
||
命名规则 `日期-序号-主题.sql`，**按文件名顺序执行**。
|
||
|
||
- 全新库：直接用 `db_dump.sql` 建库（见根目录 `README.md`「数据库」一节），**不需要**跑这些迁移。
|
||
- 存量库升级：按下文顺序执行；大多语句幂等，但**数据变更类不可重放**（见各步说明）。
|
||
|
||
---
|
||
|
||
## 一、2026-09-21 系列（街拍主副图 / 重复检测 / 来源幂等）
|
||
|
||
| 顺序 | 文件 | 说明 |
|
||
| --- | --- | --- |
|
||
| 1 | `2026-09-21-01-street-main-detail.sql` | 草稿表与正式图片表加 `is_detail` / `parent_image_id`。全 `IF NOT EXISTS`，幂等。 |
|
||
| 2 | `2026-09-21-02-duplicate-review.sql` | 新建 `image_duplicates`，街拍正式表加 `source` / `source_url` 及部分唯一索引 `uq_ss_source`。幂等。 |
|
||
| 3 | `2026-09-21-03-ingest-source-idempotency.sql` | 两张**草稿表**加 `source` / `source_url` 及部分唯一索引。幂等。这些列会随 2026-09-22-05 删草稿表一并消失。 |
|
||
|
||
这三步属于更早的改造；对已经过它们的库重放无害。
|
||
|
||
---
|
||
|
||
## 二、2026-09-22 单表发布（草稿表 → 单表 + `status` + 公开只读视图）
|
||
|
||
**执行顺序：`01 → 01b → 03 → 04 → 搬迁脚本 → 05`**（与文件名排序一致：`01`、`01b`、`03`、`04`、`05`；搬迁脚本是一段独立命令，夹在 `04` 与 `05` 之间执行）
|
||
|
||
| 顺序 | 对象 | 前提 / 幂等 / 不可逆点 |
|
||
| --- | --- | --- |
|
||
| 1 | `2026-09-22-01-single-table-publish.sql` | 给 `brand_runways` / `street_snaps` 加 `status` 等列、建状态索引、建 4 个 `public_*` 视图。**幂等**，可重复执行（集成测试每次都会跑它）。 |
|
||
| 2 | `2026-09-22-01b-publish-existing-rows.sql` | 把存量正式行置 `published`。**只能执行一次**；且必须在新代码部署**之前**、搬迁脚本**之前**执行。理由：搬迁会把 pending 草稿连同旧 `created_at` 一起搬进来，若这条 UPDATE 可重放，待审内容会被误刷成已发布（本设计要防的就是这个泄漏）。 |
|
||
| 3 | `2026-09-22-03-entity-key-unique.sql` | 实体键部分唯一索引 `uq_br_entity` / `uq_ss_entity`（街拍为最终表达式 `(city, year, COALESCE(title,''))`）。**幂等**；建索引前先体检，有重复就报可读错误而非原生 23505。 |
|
||
| 4 | `2026-09-22-04-street-entity-key-title.sql` | 街拍索引的**幂等兜底**：`DROP INDEX IF EXISTS uq_ss_entity` 后按最终表达式重建。常态由 03 承载；本文件专治「库里已有同名旧 `(city, year)` 索引、03 因 `IF NOT EXISTS` 静默跳过」的存量库。 |
|
||
| 5 | 搬迁脚本 `<见下>` | 把草稿表数据搬进正式表。**一次性**；先 `-dry-run` + 守恒校验，**校验不过绝不进入第 6 步**。 |
|
||
| 6 | `2026-09-22-05-drop-draft-tables.sql` | 删除 4 张草稿表。**不可逆**；前提是第 5 步校验通过，且 `db/backups/` 有可用备份。⚠️ 编号排在最后（`05`）是刻意的：按文件名顺序执行的运维者不会在搬迁之前误删草稿表。 |
|
||
|
||
### 搬迁脚本的取回方式
|
||
|
||
`scripts/migrate_single_table/main.go` 是一次性工具，已随本次改造从工作树删除（它 `import` 了同批删除的
|
||
草稿模型，无法在改造后的代码树上编译）。如需重放，从**它被删除前的最后一个提交**取回：
|
||
|
||
```bash
|
||
# 取回脚本源码（c4bafc5 是删除提交 d68a570 的父提交，即脚本最后一次存在的版本）
|
||
git show c4bafc5:scripts/migrate_single_table/main.go
|
||
```
|
||
|
||
注意：脚本依赖草稿模型，必须在该提交（或更早）的代码树上运行，**不能**直接放进当前树。
|
||
真正需要重放时，建议 `git worktree add ../old c4bafc5` 切出旧树再跑。
|
||
|
||
### 索引口径的一个坑
|
||
|
||
`CREATE UNIQUE INDEX IF NOT EXISTS` 只按**索引名**判断存在性，不比对表达式。因此：
|
||
|
||
- 若库里已有同名但口径更窄的旧 `uq_ss_entity (city, year)`，03 会静默跳过、留下错误结构；
|
||
这时必须跑 04 纠正（04 的 `DROP` 正是为此）。
|
||
- 反过来，若只跑 03 而库里已存在正确的同名索引，跳过是无害的。
|
||
|
||
---
|
||
|
||
## 三、2026-10-12 系列（下线 pHash 去重 + 语义向量）
|
||
|
||
| 顺序 | 文件 | 前提 / 幂等 / 不可逆点 |
|
||
| --- | --- | --- |
|
||
| 1 | `2026-10-12-01-drop-dedup-add-embeddings.sql` | 下线 pHash 去重（DROP `phash`/`is_duplicate`/`dup_of` + 两个 HNSW 索引 + 死表 `image_duplicates`）；`image_embeddings` 的 `kind` 改 `varchar(8)`、`embedding` 改 `vector(768)`，补唯一索引并重建 HNSW。**不可逆**：DROP COLUMN / DROP TABLE 无回滚手段，执行前务必确认 `db/backups/` 有可用备份。`image_embeddings` 零数据，列类型变更无需 `USING`。 |
|
||
|
||
> 排序提示：本步必须排在「二、2026-09-22」系列**之后**执行——它 DROP 的列由那一系列的代码写入，先删列会让中间态代码报错。
|
||
|
||
---
|
||
|
||
## 四、2026-10-13 系列（性能）
|
||
|
||
| 顺序 | 文件 | 前提 / 幂等 / 不可逆点 |
|
||
| --- | --- | --- |
|
||
| 1 | `2026-10-13-01-image-list-indexes.sql` | 给 `brand_runway_images (runway_id, is_deleted, sort_order)` 与 `street_snap_images (snap_id, is_deleted, sort_order)` 补索引。原先两张图片表**只有主键 (id)**，「按图集列图片」的查询是 Seq Scan + Sort，图片表上万行时图集页会变慢。`IF NOT EXISTS` 幂等，可重复执行，无不可逆点。 |
|
||
| 2 | `2026-10-13-02-image-similar-pairs.sql` | 新建 `image_similar_pairs`（「疑似同款」预计算结果表）。原实现是在**打开图集页时**对每张图实时做向量近邻检索，116 张图的图集要发 116 次查询、每次约 20KB SQL；本机 PostgreSQL 跑在 WSL2 里、Windows 经 wslrelay 访问，该链路大 payload 吞吐约 0.47MB/s，合计要 5 秒。改为入库时（或 `dbtool similarbackfill`）算一次存表后，页面退化为一次小查询。纯新增表，`IF NOT EXISTS` 幂等，无不可逆点。 |
|