逻辑复制是 PostgreSQL 提供的一项多功能特性。我在POSETTE 演讲中详细讨论了该特性的理论背景。在演讲的最后，我强调了监控逻辑复制设置的必要性。如果您正在使用逻辑复制并且有设置监控功能，那么您会熟悉pg_stat_replication_slots。在某些情况下，此视图会显示大量的 spill_txns、 spill_count和 spill_bytes ，这表明与该复制槽对应的 WAL 发送器正在使用大量磁盘空间。这会增加 IO 子系统的负载，从而影响性能。这也意味着可用于用户数据和常规事务运行的磁盘空间减少。这表明logical_decoding_work_mem 的配置过低。这就是本博客的主题：如何确定 logical_decoding_work_mem的正确配置值。让我们首先讨论一下本 GUC 的目的。
重新排序缓冲区和 logical_decoding_work_mem
在解码 WAL 时，逻辑 WAL 发送器会将事务累积到内存中一个名为重排序缓冲区的数据结构中。对于 WAL 发送器遇到的每个事务，它都会维护一个该事务的更改队列。在读取每个 WAL 记录时，它会找到它所属的事务 ID，并将其添加到相应的更改队列中。一旦它看到事务的 COMMIT 记录，它就会解码相应队列中的所有更改并发送到下游。如果重排序缓冲区被尚未看到 COMMIT 记录的事务填满，它会将队列溢出到磁盘。我们看到此类磁盘溢出记录在 spill_txns、 spill_count 和 spill_bytes中。分配给重排序缓冲区的内存量由 logical_decoding_work_mem GUC 决定。如果 GUC 值较低，则会导致大量磁盘溢出；如果该值较高，则会浪费内存。服务器中的每个 WAL 发送器都会分配 logical_decoding_work_mem 大小的内存，因此维护重新排序缓冲区所消耗的总内存为 {WAL 发送器数量} * logical_decoding_work_mem ，最高可达 max_wal_senders * logical_decoding_work_mem。
最佳设置 logical_decoding_work_mem
显然，重新排序缓冲区应该能够保存所有并发事务的 WAL 记录，以避免磁盘溢出。可以有多少个并发事务？PostgreSQL 中的每个后端（客户端和工作器）都可能启动一个事务，并且在给定的后端中在给定时间只能有一个活动事务。因此，服务器中并发事务数的上限由以下变量决定： max_connections （决定服务器中客户端后端的最大数量）、 max_prepared_transactions （决定除客户端后端之外的准备好的事务的数量）、 max_worker_processes和 autovacuum_max_workers（它们共同决定除客户端后端之外可以执行事务的后端）。所有这些 GUC 的总和给出了服务器中可以同时运行的并发事务数的上限。假设已知每个事务平均生成的 WAL 数量，那么可能添加到重排序缓冲区的 WAL 总量等于 {系统中最大事务数} * {每个事务平均生成的 WAL 数量}。问题是如何求出这个平均值？
不同应用程序的事务和工作进程的事务可能具有不同的特性，因此会产生不同数量的 WAL。但它们都会在重排序缓冲区中竞争空间，并且它们都是单个 WAL 流的一部分，可以使用pg_waldump进行检查。我们可以通过几种方法利用此实用程序来估算 logical_decoding_work_mem。
- 计算给定 WAL 段集合中提交或中止的次数，并将 WAL 段的总大小除以该次数。WAL 段的总大小为 {WAL 段数量] * {每个 WAL 段的大小}。如果您发现事务溢出到磁盘，则并发事务生成的 WAL 总量高于 logical_decoding_work_mem 的值（默认值为 64MB），这相当于 4 个 WAL 段，每个 WAL 段的默认大小为 16MB。因此，您需要分析多个 WAL 段，而不仅仅是几个。
- pg_waldump 按事务报告 WAL 记录。可以通过从 pg_waldump 中抽取典型事务样本，估算每个典型事务的大小及其计数，从而获得更好的估算结果。
- 修改 pg_waldump，使其能够持续计数重组缓冲区中累积的 WAL 数量。算法如下：
- T = 0
- 读取一条 WAL 记录。如果该记录属于事务 x，则 Cx = Cx + WAL 记录大小，其中 Cx 表示事务 x 迄今为止的 WAL 记录总大小。如果 x 是新事务，则 Cx = WAL 记录大小
- T = T + Cx，其中 T 是读取该记录时在重排序缓冲区中累积的 WAL 记录的总大小。
- 当读取事务 x 的 COMMIT 或 ABORT WAL 记录时，T = T - Cx。
这样，T 跟踪了给定时间点重排序缓冲区中累积的 WAL 记录的大小。T 的最大值可用于估算 logical_decoding_work_mem。
- 如果您不熟悉 C 或 pg_waldump，则可以通过使用 python 等高级语言解析 pg_waldump 的输出来实现上述选项。
估算出重排序缓冲区中可能累积的最大 WAL 量后，再加上重排序缓冲区中其他数据结构的约 5% 开销，您就得到了 logical_decoding_work_mem的初步估算值。您可以通过设置 GUC 和监控pg_stat_replication_slots来进一步优化它。
但是，请记住，每个 WAL 发送器都会消耗 logical_decoding_work_mem大小的内存，这可能会影响常规服务器操作可用的总内存。您可以找到一个最佳值，既能为常规服务器操作留出足够的内存，又能减少磁盘溢出。选项 3 和 4 可以帮助您实现这一点。如果您绘制 T 与时间的曲线，您会发现 WAL 发送器在稳定状态下消耗的内存量，从而消除了逻辑解码内存使用量的任何波动。logical_decoding_work_mem 的 配置应考虑到这种稳定状态下的消耗量。
原文地址：https://ashutoshpg.blogspot.com/2025/06/avoiding-disk-spills-due-to-postgresqls.html
原文作者：Ashutosh Bapat
