让 Postgres 慢 42,000 倍，因为我要离职了

[wbrobot]

转载英文文章，很有意思

让 PostgreSQL 慢 42,000 倍，因为我要离职了

原作者：Jacob Jackson
文章链接：ByteofDev
发布日期：2025 年 7 月 27 日

大家总是在想如何让 PostgreSQL 更快、更高效，但从来没有人考虑过如何让 PostgreSQL 变慢。当然，大多数人都在为追求速度而得到报酬，而我没有（不过，如果你想改变这一点，请告诉我）。在写一篇稍微更有用的指南时，我决定有人需要尝试创建一个优化到尽可能慢地处理查询的 PostgreSQL 配置。为什么？我说不上来，但这就是我思考的结果。

参数调整

我不能让这件事太简单。这是一个 PostgreSQL 调优挑战，而不是将 CPU 降频到一兆赫兹或删除索引的挑战，因此所有更改必须在 postgresql.conf 参数中进行。此外，数据库仍然需要能够在合理时间内处理至少一个事务——仅仅让 PostgreSQL 完全停止太简单了。这比看起来要困难，因为 PostgreSQL 尽力通过限制和最小化配置来防止做出如此愚蠢的决定。

为了衡量性能，我将使用 Benchbase 实现的 TPC-C 测试，配置 128 个仓库，使用 100 个连接，每个连接尝试以每秒 10,000 次事务的速率输出，全部由 PostgreSQL 19devel （截至 2025 年 7 月 14 日的最新版本）在 Linux 6.15.6 上运行，硬件为 Ryzen 7950x 、32GB 内存和 2TB SSD 。每次测试持续 120 秒，将执行两次：第一次用于缓存预热，第二次用于收集测量数据。

我使用默认的 postgresql.conf 进行了基准测试，仅对 shared_buffers、work_mem 和工作进程数量进行了基本调整。在该测试中，我获得了 7082 TPS 的良好性能。现在，让我们看看 PostgreSQL 能慢到什么程度。

缓存？不存在的……

PostgreSQL 能够高效响应读查询的一个方法是通过广泛的缓存。从磁盘读取数据很慢，因此每当 PostgreSQL 从磁盘读取一个数据块时，它会将该块缓存到内存中，以便下一个需要该块的查询可以从内存中读取。当然，我想强制所有查询使用最慢的读取方法，因此缓存越小越好。我可以通过 shared_buffers 参数自由控制缓冲区缓存和其他共享内存元素的大小。不幸的是，我不能简单地将它设置为 0 ，因为 PostgreSQL 还使用缓冲区缓存作为处理活动数据库页的区域。幸运的是，我仍然可以将其设置得很低。

首先，我尝试将基准测试中的 10GB 降到 8MB。

shared_buffers = 8MB

PostgreSQL 的运行速度已经只有最初的 1/7 。减少的缓冲区缓存迫使 PostgreSQL 在内存中保留更少的页面，这意味着无需访问操作系统即可满足的页面请求百分比从 99.90% 骤降至 70.52%，导致读取系统调用的数量增加了近 300 倍。

但我们可以做得更糟。70% 仍然太高，理论上可以进一步减少缓存大小。接下来，我尝试了 128kB 。

糟糕。128kB 的共享缓冲区最多只能存储 16 个数据库页面（不包括共享缓冲区中的其他内容），而 PostgreSQL 可能需要同时访问超过 16 个页面。经过一番尝试，我发现最低可能值大约是 2 MB 。现在 PostgreSQL 的 TPS 低于 500 。

shared_buffers = 2MB

让 PostgreSQL 尽可能多地执行后台工作

PostgreSQL 有许多除处理事务之外的计算密集型任务。我可以利用这一点。为了减少存储碎片，PostgreSQL 运行了一个自动清理（ autovacuum ）进程，找到空闲空间（例如删除操作产生的空间）并用其他元组填充这些空间。通常，只有在进行了特定数量的更改后才会运行此进程，以避免过多的性能损失，但我可以重新配置自动清理以最小化每次运行之间的时间间隔。

autovacuum_vacuum_insert_threshold = 1 # 仅需 1 次插入即可触发自动清理
autovacuum_vacuum_threshold = 0 # 触发清理所需的最小插入、更新或删除数量
autovacuum_vacuum_scale_factor = 0 # 计算阈值时考虑的未冻结表大小比例
autovacuum_vacuum_max_threshold = 1 # 触发清理所需的最大插入、更新或删除数量
autovacuum_naptime = 1 # 自动清理之间的最小延迟（秒）；遗憾的是，这不能低于 1 ，限制了我们
vacuum_cost_limit = 10000 # 查询成本限制，超过后清理会暂停；我希望清理永不停止，所以设为最大值
vacuum_cost_page_dirty = 0
vacuum_cost_page_hit = 0
vacuum_cost_page_miss = 0 # 这些设置在计算 `vacuum_cost_limit` 时最小化操作成本

我还重新配置了自动清理分析器，它收集统计信息以指导清理和查询计划（剧透：准确的统计信息不会阻止我搞乱查询计划）：

autovacuum_analyze_threshold = 0 # 与 autovacuum_vacuum_threshold 相同，但用于 ANALYZE
autovacuum_analyze_scale_factor = 0 # 与 autovacuum_vacuum_scale_factor 相同

我还尝试让清理过程本身尽可能慢：

maintenance_work_mem = 128kB # 为清理进程分配的内存量
log_autovacuum_min_duration = 0 # 自动清理操作需要运行的最短持续时间（毫秒）才会记录；我干脆记录一切
logging_collector = on # 启用日志记录
log_destination = stderr,jsonlog # 设置日志的输出格式/文件

需要注意的是，相反的方法也可能有效：如果完全禁用自动清理，页面将充满死元组，性能会逐渐下降。然而，由于这是一个插入密集型工作负载，仅运行 2 分钟，我认为这种方法不够低效。

PostgreSQL 现在的运行速度不到原来的 1/20 。我通过检查日志确认了性能下降的来源：

2025-07-20 09:10:20.455 EDT [25210] LOG:  automatic vacuum of table "benchbase.public.warehouse": index scans: 0
 pages: 0 removed, 222 remain, 222 scanned (100.00% of total), 0 eagerly scanned
 tuples: 0 removed, 354 remain, 226 are dead but not yet removable
 removable cutoff: 41662928, which was 523 XIDs old when operation ended
 frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
 visibility map: 0 pages set all-visible, 0 pages set all-frozen (0 were all-visible)
 index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed
 avg read rate: 116.252 MB/s, avg write rate: 4.824 MB/s
 buffer usage: 254 hits, 241 reads, 10 dirtied
 WAL usage: 2 records, 2 full page images, 16336 bytes, 1 buffers full
 system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.01 s
2025-07-20 09:10:20.773 EDT [25210] LOG:  automatic analyze of table "benchbase.public.warehouse"
 avg read rate: 8.332 MB/s, avg write rate: 0.717 MB/s
 buffer usage: 311 hits, 337 reads, 29 dirtied
 WAL usage: 36 records, 5 full page images, 42524 bytes, 4 buffers full
 system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.31 s
2025-07-20 09:10:20.933 EDT [25210] LOG:  automatic vacuum of table "benchbase.public.district": index scans: 0
 pages: 0 removed, 1677 remain, 1008 scanned (60.11% of total), 0 eagerly scanned
 tuples: 4 removed, 2047 remain, 557 are dead but not yet removable
 removable cutoff: 41662928, which was 686 XIDs old when operation ended
 frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
 visibility map: 0 pages set all-visible, 0 pages set all-frozen (0 were all-visible)
 index scan bypassed: 2 pages from table (0.12% of total) have 9 dead item identifiers
 avg read rate: 50.934 MB/s, avg write rate: 9.945 MB/s
 buffer usage: 1048 hits, 1009 reads, 197 dirtied
 WAL usage: 6 records, 1 full page images, 8707 bytes, 0 buffers full
 system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.15 s
2025-07-20 09:10:21.220 EDT [25210] LOG:  automatic analyze of table "benchbase.public.district"
 avg read rate: 47.235 MB/s, avg write rate: 1.330 MB/s
 buffer usage: 115 hits, 1705 reads, 48 dirtied
 WAL usage: 30 records, 1 full page images, 17003 bytes, 1 buffers full
 system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.28 s
2025-07-20 09:10:21.543 EDT [25212] LOG:  automatic vacuum of table "benchbase.public.warehouse": index scans: 0
 pages: 0 removed, 222 remain, 222 scanned (100.00% of total), 0 eagerly scanned
 tuples: 0 removed, 503 remain, 375 are dead but not yet removable
 removable cutoff: 41662928, which was 845 XIDs old when operation ended
 frozen: 0 pages from table (0.00% of total) had 0 tuples frozen
 visibility map: 0 pages set all-visible, 0 pages set all-frozen (0 were all-visible)
 index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed
 avg read rate: 131.037 MB/s, avg write rate: 5.083 MB/s
 buffer usage: 268 hits, 232 reads, 9 dirtied
 WAL usage: 1 records, 0 full page images, 258 bytes, 0 buffers full
 system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.01 s
2025-07-20 09:10:21.813 EDT [25212] LOG:  automatic analyze of table "benchbase.public.warehouse"
 avg read rate: 10.244 MB/s, avg write rate: 0.851 MB/s
 buffer usage: 307 hits, 337 reads, 28 dirtied
 WAL usage: 33 records, 3 full page images, 30864 bytes, 2 buffers full
 system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.25 s
# ... 类似日志继续

PostgreSQL 现在每秒对热门表执行自动清理和分析操作，由于缓冲区缓存命中率已经很低，这迫使它从磁盘读取大量数据。更妙的是，这些操作几乎没有作用，因为每次运行之间变化很少。当然，293 TPS 仍然太快了。

将 PostgreSQL 变成布兰登·桑德森

布兰登·桑德森写了很多。你知道还有什么（将会）写很多？我的 PostgreSQL 实例，一旦我搞乱了 WAL 配置。在将更改提交到实际数据库文件之前，PostgreSQL 会将它们写入 WAL （预写日志），然后在检查点操作中提交这些更改。WAL 是高度可配置的，我可以利用这一点。首先，PostgreSQL 通常会将部分 WAL 保存在内存中，然后再刷新到磁盘。我不能让这种情况发生。

wal_writer_flush_after = 0 # 要求刷新的最小 WAL 数据量
wal_writer_delay = 1 # 刷新之间的最小延迟

我还希望 WAL 尽可能频繁地执行检查点。

min_wal_size = 32MB # 检查点后最小的 WAL 大小；我想尽可能多地执行检查点
max_wal_size = 32MB # WAL 最大大小，超过后将触发检查点。遗憾的是，我必须将两者都设为至少 32MB 以匹配 2 个 WAL 段
checkpoint_timeout = 30 # 检查点之间的最长时间（秒）； 30 秒是最小值
checkpoint_flush_after = 1 # 每 8kB 后将写入刷新到磁盘

当然，我还需要最大化 WAL 的写入。

wal_sync_method = open_datasync # 刷新到磁盘的方法；这应该是最慢的
wal_level = logical # 使 WAL 输出额外的复制信息。这些额外信息并非必需，但会损害性能
wal_log_hints = on # 强制 WAL 写出完整的修改页面
summarize_wal = on # 另一个用于备份的额外进程
track_wal_io_timing = on # 收集更多信息
checkpoint_completion_target = 0 # 完全不分散 I/O 负载

PostgreSQL 现在的事务处理速率降到了两位数，仅为原来的 1/70 。我可以通过查看日志确认这是由于 WAL 低效造成的：

2025-07-20 12:33:17.211 EDT [68697] LOG:  checkpoint complete: wrote 19 buffers (7.4%), wrote 2 SLRU buffers; 0 WAL file(s) added, 3 removed, 0 recycled; write=0.094 s, sync=0.042 s, total=0.207 s; sync files=57, longest=0.004 s, average=0.001 s; distance=31268 kB, estimate=31268 kB; lsn=1B7/3CDC1B80, redo lsn=1B7/3C11CD48
2025-07-20 12:33:17.458 EDT [68697] LOG:  checkpoints are occurring too frequently (0 seconds apart)
2025-07-20 12:33:17.458 EDT [68697] HINT:  Consider increasing the configuration parameter "max_wal_size".
2025-07-20 12:33:17.494 EDT [68697] LOG:  checkpoint starting: wal
2025-07-20 12:33:17.738 EDT [68697] LOG:  checkpoint complete: wrote 18 buffers (7.0%), wrote 1 SLRU buffers; 0 WAL file(s) added, 2 removed, 0 recycled; write=0.089 s, sync=0.047 s, total=0.280 s; sync files=50, longest=0.009 s, average=0.001 s; distance=34287 kB, estimate=34287 kB; lsn=1B7/3F1F7B18, redo lsn=1B7/3E298BA0
2025-07-20 12:33:17.923 EDT [68697] LOG:  checkpoints are occurring too frequently (0 seconds apart)
2025-07-20 12:33:17.923 EDT [68697] HINT:  Consider increasing the configuration parameter "max_wal_size".
2025-07-20 12:33:17.971 EDT [68697] LOG:  checkpoint starting: wal

是的，通常 WAL 检查点不应该（查看笔记）每 487 毫秒发生一次。但我还没完。

实质上删除索引

还记得引言中我说我们不能动索引吗？其实我们不需要。PostgreSQL 在计算查询计划时，会区别对待磁盘的随机访问和顺序访问，因为在硬盘上随机访问页面通常比顺序访问慢。查询带索引的表通常需要随机访问页面，而表扫描通常涉及顺序访问，这意味着调整随机页面的相对成本应该可以防止使用任何索引。

random_page_cost = 1e300 # 设置访问随机页面的成本
cpu_index_tuple_cost = 1e300 # 设置处理索引中一个元组的成本

仅需更改这两个参数，就能在几乎所有情况下禁用索引。我最终不得不将 shared_buffers 值调回到 8MB，以避免表扫描出现错误，但显然这对性能帮助不大。

PostgreSQL 现在每秒处理不到一笔事务，比默认调优慢了 7000 多倍，而这一切都没有更改 postgresql.conf 之外的任何内容。不过，我还有最后一个绝招。

将 I/O 强制到一个线程

我无法让 PostgreSQL 单线程运行，因为 100 个连接中的每一个都有自己的进程。然而，借助 PostgreSQL 18 中的新选项，我仍然可以使 I/O 单线程运行。PostgreSQL 18 引入了一个新参数 io_method，用于控制线程是同步发出 I/O 系统调用（io_method = sync）、异步请求工作线程发出系统调用（io_method = worker），还是使用新的 io_uring Linux API （io_method = io_uring）。结合 io_workers 参数（设置使用 io_method=worker 时的最大工作线程数），我可以强制所有 I/O 进入一个工作线程。

io_method = worker
io_workers = 1

好了，PostgreSQL 现在的 TPS 远低于 0.1 ，比我们开始时慢了 42,000 倍。如果排除因死锁未完成的事务，情况更糟（更好？）：在 100 个连接和 120 秒内，只有 11 笔事务成功完成。

最终感想

几个小时和 32 个参数调整后，我成功地“杀死”了一个 PostgreSQL 数据库。谁能想到仅通过修改 postgresql.conf 就能对 PostgreSQL 性能造成如此大的破坏？我以为我能将 TPS 降到个位数，但没想到 PostgreSQL 会让我做到这一步。如果你想自己尝试重现这个结果，以下是从默认值更改的参数：

shared_buffers = 8MB
autovacuum_vacuum_insert_threshold = 1
autovacuum_vacuum_threshold = 0
autovacuum_vacuum_scale_factor = 0
autovacuum_vacuum_max_threshold = 1
autovacuum_naptime = 1
vacuum_cost_limit = 10000
vacuum_cost_page_dirty = 0
vacuum_cost_page_hit = 0
vacuum_cost_page_miss = 0
autovacuum_analyze_threshold = 0
autovacuum_analyze_scale_factor = 0
maintenance_work_mem = 128kB
log_autovacuum_min_duration = 0
logging_collector = on
log_destination = stderr,jsonlog
wal_writer_flush_after = 0
wal_writer_delay = 1
min_wal_size = 32MB
max_wal_size = 32MB
checkpoint_timeout = 30
checkpoint_flush_after = 1
wal_sync_method = open_datasync
wal_level = logical
wal_log_hints = on
summarize_wal = on
track_wal_io_timing = on
checkpoint_completion_target = 0
random_page_cost = 1e300
cpu_index_tuple_cost = 1e300
io_method = worker
io_workers = 1

你可以通过安装 BenchBase PostgreSQL 并使用示例 TPC-C 配置进行基准测试，配置为 120 秒运行时间，120 秒预热，128 个仓库，100 个连接，最大吞吐量为 50k TPS 。你也可以尝试进一步降低性能。我专注于我认为对 PostgreSQL 性能影响最大的参数，大多数参数未经过测试。

好了，在写这篇文章的过程中，我的下背部开始疼痛，所以我想是时候出去走走了。

[myderr]

其实不用这么麻烦，我发挥正常水平写 sql 就行了