PostgreSQL UPDATE 性能优化

测试环境

8C 16G
PG 14.16 (Ubuntu 14.16-1.pgdg22.04+1)) + PGVector 0.8.0
表物理占用大小：~50GB
Pkey idx (bigint btree): 155MB
Vector idx (hnsw): 23GB

并行化的批量更新

待优化的目标 SQL 语句：

1	UPDATE modeldata SET halfvector = vector WHERE halfvector IS NULL;

这条语句在较小的数据表上执行起来还是简单且有效的。不过对于生产环境上的巨量数据，此语句存在以下问题：

全表扫描且将导致全表加排它锁
执行过程中无法得知进度，也就无法预计完成时间
由于事务的 ACID 特性，不允许中断与增量更新
操作无法并行化执行，可能导致性能不佳

为解决以上问题，我最开始构建了以下 SQL 语句：

UPDATE modeldata
SET halfvector = vector
WHERE id IN (
    SELECT id
    FROM modeldata
    WHERE halfvector IS NULL
    ORDER BY RANDOM() -- 随机化抽样，避免多线程运行时条件竞争
    LIMIT 1000
);

不过性能不怎么理想，处理速度大概只有 10 rows/s。这是由于数据库的随机读写性能较差，为了提高 SQL 效率应该尽量让数据库进行顺序读写。

于是我又想办法将整个表进行分块。让每个块间无依赖关系，独立提交更新事务，从而允许所有事务并行执行。

我首先想到的是根据自增 ID 主键进行任务划分，得先找到 min_id 以及 max_id

1 2	SELECT MIN(id) as min_id FROM modeldata WHERE halfvector IS NULL; SELECT MAX(id) as max_id FROM modeldata WHERE halfvector IS NULL;

然后就能根据 id 范围和 batch_size （批处理的单个分块的大小）对更新任务进行分块了。如以下SQL语句：

1
2
3

UPDATE modeldata
SET halfvector = vector
WHERE id >= $startId AND id < $endId AND halfvector IS NULL;

删除的数据会导致id呈现出来间断的连续，因此分块的实际大小是小于等于 batch_size 的。不过由于更新操作的耗时一般占了单个事务运行总时长的 95% 以上，
较小的实际分块大小并不会对运行效率造成太大的影响。

分块后的并行处理的速度提高到了约 100 rows/s。这个速度对于生产环境的 3 百万条数据而言还是太慢了，完成更新大概需要 9 小时。还是得想办法继续优化。

Vacuum 垃圾回收

经过了一番调研，发现数据表很久没有执行过 vacuum 和 analyze 了。

虽然数据表已经开启了 autovacuum ，但是由于默认的最大允许消耗量太小，导致一直不能够完成清理。

vacuum 可以对表进行垃圾回收，使已删除的数据腾出空间以供重新利用。
analyze 会对表进行分析，随后，查询规划器使用这些统计数据来帮助确定最有效的查询执行计划。

1 2	-- 垃圾回收+分析 VACUUM (VERBOSE, ANALYZE) modeldata;

不过上述的SQL执行时会长时间卡在vacuuming indexes阶段。推测是向量索引太大（23GB）导致的，可以姑且先跳过索引垃圾回收。

1
2
3

VACUUM (VERBOSE, ANALYZE, INDEX_CLEANUP OFF) modeldata;
-- 查询进度 https://www.postgresql.org/docs/current/progress-reporting.html
SELECT * FROM pg_stat_progress_vacuum;

不过，为了提高 maintenance 相关操作（如vacuum, create index）的性能，可以调节相关参数：

-- 使用 ALTER SYSTEM SET 语句修改全局默认设置
ALTER SYSTEM SET maintenance_work_mem TO '10GB'; -- 最大内存用量
ALTER SYSTEM SET maintenance_io_concurrency TO 12; -- 最大并发异步IO数量
ALTER SYSTEM SET max_parallel_maintenance_workers TO 16; -- maintenance 操作允许的最大并行进程数
-- reload 使全局配置生效
SELECT pg_reload_conf();

按照PG文档中描述，maintenance_work_mem可以远大于work_mem，因为整个服务器很少概率会出现同时执行多个维护操作，所以可以放心地提高此参数大小。
由于此内存空间是独立于shared_buffers存在的，也应该避免设置过大出现OOM错误。

垃圾回收后可以减少未来写入操作所需要额外占用的物理空间。同时为HOT更新（详见下文）提供了条件。

查询/更新操作参数优化

因为 PG 的默认参数都是非常保守的，表CRUD相关的事务的参数也要根据运行环境调整。

ALTER SYSTEM SET max_worker_processes = 16; -- 最大总进程数
ALTER SYSTEM SET max_parallel_workers = 16; -- 普通查询允许的最大并行进程数
ALTER SYSTEM SET max_parallel_workers_per_gather= 8; -- 每个 gather 节点允许的最大并行进程数
ALTER SYSTEM SET shared_buffers = '4GB';  -- 共享缓冲区大小，推荐设置为1/4系统内存
ALTER SYSTEM SET effective_io_concurrency = 10; -- 单 session 最大异步IO数
ALTER SYSTEM SET work_mem = '24MB'; -- 最大单个查询操作内存用量
ALTER SYSTEM SET hash_mem_multiplier = 8.0; -- hash 操作最大内存用量倍率（n * work_mem）
ALTER SYSTEM SET temp_buffers = '24MB'; -- 最大单 session 的临时表大小
ALTER SYSTEM SET max_connections = 100; -- 最大连接数

还有若干优化WAL IO性能的参数。

-- WAL缓冲区大小，增大此参数可以减少WAL的磁盘IO
ALTER SYSTEM SET wal_buffers = '64MB';

-- 关闭事务同步提交，即不需要等待WAL写入磁盘，在有大量事务操作且不需要担心数据安全的场景下有帮助
SET LOCAL synchronous_commit TO OFF

不过生产环境的参数基本都已经配置好了，此处也就只列出一些常见的优化点备忘。

HOT 更新

HOT (Heap Only Tuple) updates，可以粗糙地翻译为：仅堆元组更新。为了避免引入太多 PG 的底层原理细节，本文这里就只列出大致技术原理和结果，详情可以阅读参考文献 1。

当在数据表中执行 update 操作时，由于 PG 的 MVCC 机制，并不会直接修改对应的目标元组，而是会在 Heap (堆)中创建一个新的元组，将旧元组标记为无效，最后更新相应的索引条目。
旧的、无效的元组的必须需要通过 vacuum 操作释放，而索引的更新也直接导致了 update 的效率较低。当索引相当大的时候（如上文提到的23GB），任何更新索引的操作也会变得非常耗时。
HOT 更新则是通过给旧元组添加一个到新元组的指针，来避免对索引的更新，而索引也还能够通过 HOT 链来找到最新的数据。

要触发 HOT 更新必须满足以下条件：

Block（物理块）中必须有空余的空间
待更新的字段必须不能有索引（否则索引也就必须更新）

第 2 条是很容易满足的，因为 halfvector 是空的且还未开始使用，可以在所有更新完成后再建立向量索引。

第 1 条可以通过设置表的 fillfactor 参数（默认为 100%）实现，以在 insert 数据的时候在块中预留一些空间。但是此参数只会在执行新的 insert 操作时生效，并不会影响原有的块。

而这时候 vacuum 的作用就体现出来了，VACUUM FULL 语句能够按照 fillfactor 完全在物理空间上清理并重建表（因为会加表锁，在生产环境不可行），标准 VACUUM 则可以清理死元组，并释放块中的空间。

因此，只要在更新操作前执行 VACUUM FULL 或 VACUUM，就可以让块中有空余的空间触发 HOT 更新。

物理顺序读写

为了尽量让数据库进行物理顺序读写操作，以得到极致的性能优化，我又尝试引入了 ctid 的概念。ctid 可以理解为 row 所在的物理地址，表现为一个块号与块地址的元组。

虽然自增 id 按照写入时的顺序是拥有一定的物理空间顺序的，但是经过了无数次 update/delete/vacuum 操作后，id 到物理地址的映射也就发生了很大的变化，变得有了一定的随机性。
如果直接使用顺序的 ctid，就不仅能省去逻辑 id 寻址的过程，还保证了物理空间的顺序性。

只讲原理多少有点抽象，先放出对应的SQL语句：

UPDATE modeldata SET halfvector = vector WHERE ctid = ANY(ARRAY(
    SELECT ctid FROM modeldata WHERE halfvector IS NULL LIMIT 1000
    FOR NO KEY UPDATE SKIP LOCKED
));

子查询中根据 WHERE 子句条件查询出目标 ctid，并使用 LIMIT 限制单次更新的大小。
子查询尾部的 FOR NO KEY UPDATE 给查询到的行加上更新排它锁（与非KEY值更新时的锁相同）。因为主查询语句也会加同样的锁，这个子句只是把加锁时间提前到了目标查询阶段。
SKIP LOCKED 子句会跳过所有已经被加锁的行，与 FOR NO KEY UPDATE 子句结合，避免了并发运行的事务查询到相同的行导致阻塞。
再看主查询的 WHERE 子句直接使用了子查询得到的 ctid。注意这里使用了 = ANY(ARRAY()) 而不是 IN（或者 =(ANY())）是因为 IN 需要使用 HashAggregate，性能与上文提到的 hash 操作的 work_mem 参数密切相关。
= ANY(ARRAY()) 一般对 work_mem 不敏感，通常性能较好。

这个SQL语句已经足够小而美了。不过为了减少全表扫描次数，且在任务目标大小不会变化的情况下，还可以将子查询的结果使用一个 materialized view 缓存起来。

1 2	CREATE MATERIALIZED VIEW outdated_data AS SELECT ctid AS id FROM modeldata WHERE halfvector IS NULL;

那么之后的并发 update 就可以写成：

1
2
3

UPDATE modeldata SET halfvector = vector WHERE ctid = ANY(ARRAY(
    SELECT id FROM outdated_data LIMIT $batch_size OFFSET $offset
));

记得在每次批处理开始前刷新一下视图：

1	REFRESH MATERIALIZED VIEW outdated_data;

此外，批处理块的大小（batch_size）也是有一定讲究的，太大太小都不行。
太小会导致单次事务准备和计划耗时占比高，而太大又可能导致超出 shared_buffer 或 work_mem。因此需要根据实际情况测试和调整。

最终在 HOT 更新这套组合拳下更新速度终于来到了平均 500 rows/s，峰值 1400 rows/s。
Emm，这个性能好像也不是很令人惊喜呢。但是这已经是在此条件下的我所能尽的最大努力了（悲

总结

虽然本文中提到了若干的SQL上的优化措施，但其实在本测试环境下 HOT 的优化对性能影响最大。这是因为更新这个巨大的向量索引非常耗时。

就比如，克隆的一个不带索引的表，对它执行任意的更新操作就可以轻松达到 10000 rows/s 的速度。

这就有一点讽刺了：怎么死磕 SQL 的细节优化，大概率也得不到一个数量级上的性能提升，到最后可能还是不如新建一个表再建索引来的快（

不论如何，这次调研也还算是有点收获吧 :D