Hollis Varden：关于 PostgreSQL 18 的十三项小型测量
内容提要
作者在 PostgreSQL 18.6 上完成 13 项小型测量，涵盖索引、写入与查询性能：部分索引只存储符合条件的行，体积远小于全表索引；BRIN 索引在有序数据上体积极小，查询速度接近 B-tree；INCLUDE 索引因无法去重而明显变大；仅索引扫描仍需检查未标记可见的页面，VACUUM 后堆读取降为零；外键列需手动建索引，否则级联删除极慢；\copy 批量导入远快于逐行 INSERT；UNLOGGED 表写入更快但不写 WAL；关闭 synchronous_commit 可提升提交吞吐；UPDATE 产生新行版本，VACUUM 不归还空间但可复用；排序超过 work_mem 会落盘变慢，并行查询以两个 worker 收益最大。
延伸解读
索引选择需权衡体积与查询模式
部分索引仅存储符合条件的行，体积可缩小至全表索引的5%左右，但仅当查询条件匹配索引的WHERE子句时才会被使用。BRIN索引在数据按索引列顺序插入时体积极小（24 kB vs 44.9 MB），查询速度接近B-tree，但文章未测试乱序数据，此时BRIN可能失效。INCLUDE索引因无法利用B-tree去重而显著增大，例如从14.2 MB增至45.0 MB，适合需要覆盖查询但可接受空间代价的场景。
写入性能优化与持久性取舍
批量导入时\copy远快于逐行INSERT（0.039秒 vs 2.1秒/20万行）。UNLOGGED表写入更快且不写WAL，但崩溃后数据清空且不被复制。关闭synchronous_commit可提升提交吞吐（28,101 TPS升至64,478 TPS），但崩溃可能丢失最后几个提交，不过不会损坏数据。这些优化需根据数据持久性要求谨慎选择。
维护与并行配置的实际影响
外键列不会自动创建索引，缺失时级联删除极慢（3.6秒 vs 0.013秒）。UPDATE产生新行版本，VACUUM不归还空间给操作系统但可复用，因此表大小不会持续增长。仅索引扫描仍需检查未标记可见的页面，VACUUM后堆读取降为零。排序超过work_mem会落盘变慢，但超过所需内存后无额外收益；并行查询以两个worker收益最大，增至4个反而变慢。
Q&A
PostgreSQL 的部分索引和全表索引在体积上差别大吗？
差别很大。例如在 customer_id 上建索引，全表索引大小为 14.2 MB，而加上 WHERE status = 'open' 的部分索引只有 0.7 MB，因为部分索引只存储符合条件的行。
BRIN 索引在什么情况下体积小且查询速度接近 B-tree？
当数据按索引列有序插入时，BRIN 索引体积极小（例如 24 kB），查询速度与 B-tree 接近（0.63 ms vs 0.55 ms）。但文章未测试乱序数据的情况。
为什么 INCLUDE 索引会比普通索引大很多？
因为 INCLUDE 索引无法利用 B-tree 的去重优化（自 PostgreSQL 13 起，普通 B-tree 会对重复键去重），导致索引体积显著增大。例如 (customer_id) 索引为 14.2 MB，而 INCLUDE (amount) 后变为 45.0 MB。
仅索引扫描（Index Only Scan）为什么还会读取堆表？VACUUM 后有什么变化？
仅索引扫描需要检查那些未被 VACUUM 标记为全部可见的页面，因此仍会访问堆表。在 VACUUM 后，堆读取降为零，扫描时间从 7.1 ms 降至 4.4 ms。
外键列没有索引会导致级联删除变慢吗？
是的。在测试中，删除 200 个父行（级联删除 20,000 个子行）时，没有索引耗时 3.6 秒，而有索引仅需 0.013 秒。PostgreSQL 不会自动为外键列创建索引，需要手动添加。
批量导入数据时，使用 \copy 和逐行 INSERT 性能差距有多大？
差距显著。导入 200,000 行时，\copy 耗时 0.039 秒，每语句 1,000 行的 INSERT 耗时 0.133 秒，而逐行 INSERT 耗时 2.1 秒。
关闭 synchronous_commit 能提升多少提交吞吐量？有什么风险？
在测试中，关闭 synchronous_commit 后事务吞吐从 28,101 TPS 提升到 64,478 TPS。风险是崩溃时可能丢失最后几个提交，但不会导致数据损坏。
UPDATE 后表体积会变大吗？VACUUM 能回收空间吗？
UPDATE 会写入新的行版本，导致表体积增大（从 202.5 MB 增至 404.7 MB）。VACUUM 不会将空间归还给操作系统，但可以允许后续 UPDATE 重用这些空间，因此第二次全表 UPDATE 后体积保持不变。
排序时 work_mem 设置多少合适？超过需要会更快吗？
work_mem 不足时排序会落盘变慢（4MB 时耗时 433 ms，使用外部归并排序）。将 work_mem 提高到 64MB 后，排序在内存中完成，耗时降至 297 ms；继续提高到 256MB 并无进一步提升。因此，work_mem 只需满足排序需求即可，过大反而可能因每个查询的每个排序/哈希操作都占用而浪费内存。
并行查询中，增加 worker 数量总能提升性能吗？
不一定。在测试的 LIKE 过滤和 GROUP BY 查询中，2 个 worker 带来了大部分性能提升（从 151 ms 降至 54 ms），而 4 个 worker 反而更慢（44 ms）。因此，需要根据实际负载调整并行度。
