Use FTS5 external content mode for blocks_fts to deduplicate data with blocks

[TCOTC]

FTS 改成 External Content Tables

https://www.sqlite.org/fts5.html#external_content_tables

AI 说可能降低索引开销、节省保存重复的原文，并且对查询性能影响不大，但我觉得实际效果不好说，得让 AI 改个 demo 出来试试才知道，先记个 issue。

[88250]

将 blocks_fts 改造为 FTS5 External Content Table，避免与 blocks 重复存储

背景

当前 blocks_fts 是标准 FTS5 虚表，与普通表 blocks 逐列完全重复（同为 21 列）。这意味着 content、fcontent、markdown、ial 等大体量文本列在两张表里各存了一份，FTS 在此之上再叠加倒排索引（blocks_fts_data / blocks_fts_idx / blocks_fts_docsize 等影子表）。这带来明显的存储浪费，也使双写一致性需靠人工维护。

方案

将 blocks_fts 改为 external-content 模式（content='blocks', content_rowid='rowid'）：FTS 虚表不再自行存储列值，仅维护倒排索引，原文由 blocks 表提供，查询时按 rowid 回表取值。

核心约束

external-content 模式下，FTS 行的 rowid 必须等于对应 blocks 行的 rowid，否则：

• snippet() / highlight() 回表 SELECT … FROM blocks WHERE rowid=? 会取错行
• DELETE FROM blocks_fts WHERE rowid=? 会静默失效，留下僵尸条目

FTS5 不会自动保证对齐（fts5StorageNewRowid 从私有 %_docsize 表分配），因此所有写路径需显式传 rowid。本方案保留应用层成对写入（不引入触发器，保持 SiYuan 零触发器约定），但全部改为基于 rowid。

改动清单

1. DDL — kernel/sql/database.go

CREATE VIRTUAL TABLE 追加 content='blocks', content_rowid='rowid'，列定义与顺序原样不动（保证 snippet 列索引 6~11、17 仍有效，SELECT * 列序不变）。

2. 重建命令 — kernel/sql/database.go

RebuildFTSIndex 改用 'rebuild' 命令：

stmt := "INSERT INTO blocks_fts(blocks_fts) VALUES('rebuild')"

原因：'rebuild' 走 SCAN 路径，用 blocks 的 rowid 作 FTS rowid，对齐天然保持。旧的 INSERT…SELECT FROM blocks 不含 rowid，会走 fts5StorageNewRowid 自分配，导致与 blocks rowid 脱钩。

3. 批量 INSERT — kernel/sql/upsert.go

insertBlocks0 改为三步：

1. INSERT INTO blocks(…)（不变）
2. SELECT id, ROWID FROM blocks WHERE id IN (…) 反查本批 rowid（按 id 用 map 对齐，不依赖返回顺序）
3. 用对齐的 rowid 拼 INSERT INTO blocks_fts(rowid, id, …)

新增 BlocksFTSInsert / BlocksFTSPlaceholder（首列 rowid）。

4. 单行 UPDATE — kernel/sql/block.go

updateRootContent / updateBlockContent / indexNode 三处统一改为：先查 rowid，再 UPDATE blocks_fts SET … WHERE rowid=?。新增 helper blockRowIDByBlockID。

5. 批量 UPDATE/DELETE — kernel/sql/database.go

• deleteBlocksByBoxTx / deleteByRootID / batchDeleteByRootIDs / batchDeleteByPathPrefix：改为 rowid IN (SELECT rowid FROM blocks WHERE …)，且先删 FTS 再删 blocks（否则子查询因 blocks 已删而查不到 rowid）
• batchUpdatePath / batchUpdateHPath：改为 rowid IN (SELECT rowid FROM blocks WHERE root_id=?)
• deleteBlocksByIDs：本就基于 rowid，无需改动

6. 版本号 — kernel/util/runtime.go

DatabaseVer bump，触发一次性全量重建（会丢失 block_embeddings 嵌入向量，需重新生成，这是升版本既有代价）。

不改动的部分

• 全部读路径（5 条 SELECT + GetDuplicatedRootIDs 诊断）——external-content 透明回表，行为不变
• operationQueue 去重逻辑
• 不引入触发器，保持零触发器约定
• asset_contents_fts / histories_fts 不在范围（无对应内容表，改动会丢数据）

优点

• 省存储：FTS 影子表不再重复保存 content/fcontent/markdown/ial，siyuan.db 缩小，利好备份/同步快照
• 单一数据源：以 blocks 为真源，根除双写漂移（现有 index_fix.go 防御代码说明这是真实痛点）
• 写入更快：FTS 不再写入重复列值，只更新索引

代价

• 查询回表：snippet/列读取每条结果多一次 rowid 点查（O(1)，常规 LIMIT 搜索无感）
• 一次性迁移：全量重建会丢嵌入向量，需重新生成；用户经历一次重建耗时
• 改动面中等：6 处代码、4 个文件

验证

• ✅ gofmt / go build ./... / go vet 通过
• ✅ 全局搜索（主搜索、引用搜索、反链提及、文档内高亮、计数）正常
• ✅ 编辑后立即可搜、删除后不残留、移动/重命名路径正确
• ✅ 大小写/繁简切换触发 'rebuild' 重建正常
• ✅ 批量插入（大文档）无 rowid 错位

涉及文件

• kernel/sql/database.go（DDL、重建、批量 UPDATE/DELETE）
• kernel/sql/upsert.go（批量 INSERT、常量）
• kernel/sql/block.go（单行 UPDATE、rowID helper）
• kernel/util/runtime.go（版本号）

[TCOTC]

修改之后要测试性能变化

[88250]

SiYuan blocks_fts 改造为 FTS5 External Content Table — 性能基准报告

测试概述

• 测试对象:blocks_fts 从标准 FTS5 表改造为 external-content 表(content='blocks', content_rowid='rowid')
• 测试环境:Windows / Intel i7-8750H @ 2.20GHz / Go 1.26.1
• 基准脚本:kernel/sql/fts_bench_test.go(可复现)
• 运行方式:

  cd kernel
  go test -vet=off -tags=fts5 -run=^$ -bench=BenchmarkFTS -benchmem -benchtime=5x ./sql/
  go test -vet=off -tags=fts5 -run=TestFTSBenchStorage -v ./sql/

• 测试数据:5000 个 block,中英文混排(含中文段落、英文句子、代码片段),分两档体量

---

结果总览(两组场景对比)

指标	场景	标准 FTS	external-content	差异
批量 INSERT	小内容(~200B/块)	360 ms	302 ms	快 16%
	大内容(~2KB/块)	3,076 ms	2,361 ms	快 23%
FTS 索引重建	小内容	117 ms	90 ms	快 23%
	大内容	1,663 ms	1,322 ms	快 20%
查询·中文(50 条)	小内容	1.75 ms	1.91 ms	慢 9%
	大内容	10.3 ms	12.2 ms	慢 19%
查询·英文(50 条)	小内容	1.70 ms	1.81 ms	慢 6%
	大内容	14.1 ms	15.6 ms	慢 10%
数据库大小	小内容	9.54 MB	6.96 MB	省 27%
	大内容	133.7 MB	88.1 MB	省 34%

---

分项分析

1. 写入性能(INSERT)— 净收益

场景	收益幅度	机理
小内容(200B)	快 16%	省掉 FTS 影子表 %_content 的列写入;多出一次 rowid 反查点查(开销固定且小)
大内容(2KB)	快 23%	同上,但省下的是 KB 级大文本写入,收益被放大

趋势:内容越长,省下的影子表写入越大,INSERT 收益越显著。多出的 rowid 反查是 O(512 × log n) 索引扫描,开销与内容长度无关,在大内容下占比更小。

内存分配:external 模式 allocs/op 略高(小内容 35.8 万 vs 32.3 万),源于 rowid 反查的 map 构造。绝对值不大,GC 压力可忽略。

2. 索引重建(rebuild)— 净收益

场景	收益幅度	机理
小内容	快 23%	'rebuild' 命令只读 blocks 重新分词,不复制列值到影子表
大内容	快 20%	同上

重建开销的主要部分是分词(tokenization),这部分两种模式相同;external-content 省掉的是列值复制。因此收益幅度随内容增长趋于稳定(20-23%),不像 INSERT 那样持续放大。

3. 查询性能(MATCH + snippet)— 轻微代价

场景	代价幅度	机理
小内容	慢 6-9%	每条结果按 rowid 回 blocks 取列值(回表点查)
大内容	慢 10-19%	同上,但回表读取的是 2KB 大列,绝对开销更高

绝对值:即便大内容场景,单次搜索(50 条结果)也仅慢 2-3 ms。人对 100ms 以内的差异无感知,实际使用中查询代价不可察觉。

趋势:内容越长,回表读取的列值越大,查询的相对代价越高。这是 external-content 模式的固有代价(用"查询回表"换"存储/写入收益")。

4. 存储体积 — 确定性收益

场景	节省幅度	节省绝对值	机理
小内容	省 27%	2.6 MB	消除 FTS 影子表 %_content 里 21 列的重复存储
大内容	省 34%	45.6 MB	同上,大文本列(content/fcontent/markdown/ial)的重复存储占比更高

这是最确定、最显著的收益,直接利好:

• 备份体积下降
• dejavu 同步快照缩小(加密/传输/存储均受益)
• WAL/checkpoint I/O 减少
• page cache 命中率提升(同样缓存覆盖更多有效数据)

---

净影响结论

维度	小内容场景	大内容场景	评估
写入	快 16%	快 23%	✅ 净收益
重建	快 23%	快 20%	✅ 净收益
存储	省 27%	省 34%	✅✅ 显著收益
同步/备份	正比于存储	正比于存储	✅✅ 确定性收益
查询	慢 6-9%(<0.2ms)	慢 10-19%(<3ms)	⚠️ 轻微代价,无感

综合判断:

1. 写入、重建、存储三个维度均为正向收益,且随内容体量增长而扩大
2. 查询是唯一的代价,但绝对值极小(小内容 <0.2ms,大内容 <3ms),用户不可察觉
3. 存储收益尤其突出:真实笔记库(长文档为主,数百 MB 到数 GB)可节省约三分之一的数据库体积
4. 对 SiYuan 这类"搜索结果有限(LIMIT ≤ 50)、写入与重建是常规操作、笔记多为长文档"的负载特征,external-content 改造是净正面优化,且内容越长收益越大

对真实用户的意义:大内容场景(贴近真实长笔记)下,省 34% 数据库体积 + 写入快 23%,是用户能实际感知到的改进(更快的大文档导入、更小的同步体积),而查询慢 19%(绝对值 2ms)完全无感。

---

复现说明

基准脚本 kernel/sql/fts_bench_test.go 已就绪。调整 genBlock 中正文段落的重复次数(当前 k < 10 约 2KB,改为 k < 1 约 200B)可在大小内容场景间切换,复现上述两组数据。

注:运行需带 -tags=fts5(内核构建要求,FTS5 是可选编译)。-vet=off 用于跳过 queue*.go 里既有的、与本改动无关的 vet 警告。

[88250]

ok 了，你评审一下，我先关了。

[TCOTC]

#18067

[TCOTC]

让 AI 用我的笔记库测试，总体表现是不错的：

---

重复策略： rebuild 预热 1 + 重复 3 次（中位数）；搜索 / 局部更新 预热 2 + 重复 50 次（中位数）；VACUUM 仍单次。

数据： D:\Datafile\SiYuan\temp\siyuan.db，194,986 块

---

总体指标

指标	standard	external	变化
rebuild (ms, 中位数×3)	6504.43	4074.84	−37.4%
rebuild 后大小 (MB)	471.74	312.55	−33.7%
VACUUM (ms)	7593.18	5000.21	−34.1%
VACUUM 后大小 (MB)	463.02	303.30	−34.5%
局部更新 64 块 (ms, 中位数×50)	4.43	3.76	−15.1%

搜索（ms，中位数×50，命中 ≤ 50）

关键词	standard	external	变化
笔记	1.06	1.05	−0.8%
思源	1.00	0.53	−46.8%
code	5.34	5.77	+7.9%
the	2.10	2.11	+0.4%

---

变化列 = (external − standard) / standard；负值表示 external 更快或更省空间。

简要结论： external 在 rebuild、体积、VACUUM 上约省 33–37%；搜索整体接近，「思源」一词 external 明显更快（可能与分词/缓存有关，单次关键词波动需结合多次跑看）。

---

test files.zip