原文链接：https://dataegret.com/2022/02/vacuuming-update-heavy-tables/
原文作者：Alexei Kozlov
在我之前的文章中，我概述了一些在标准设置中可以很好地工作的通用参数。它们可能具有攻击性，但会允许其他进程不间断地工作。当涉及到 VACUUM 时，需要特别注意更新繁重的表，所以在这篇文章中，我将重点关注这一点。
幸运的是，Postgres 允许为每个表单独设置 VACUUM 参数，因此即使使用一般的、相对紧凑的配置，仍然可以更积极地配置单独的表。
什么是更新量大的表？
更新量大的表是包含数据的表，这些数据基于特定的业务需求，预计会非常频繁地更新以保持相关性。一个很好的例子是客户的银行账户余额、火车上的乘客数量、可用的剧院门票、在特定区域流通的可用出租车数量等。
现在让我们退后一步考虑，为什么频繁更新的表需要特别注意？
VACUUM 是如何工作的？
在 MVCC（多版本并发控制）模式中，PostgreSQL 保留以前版本的修改记录。这些版本仍然占用该表中的空间，尽管它们对事务不可见。
VACUUM（和 AUTOVACUUM）的目的是恢复这些过时记录所占用的空间，以便可以重新利用。
简而言之，VACUUM 进程将过期记录占用的空间标记为空闲并准备好重新利用。除此之外，还会发生其他支持过程：可见性映射正在更新，空闲空间映射等。
更新
要更新一条记录，PostgreSQL 会检查带有该记录的页面以搜索适当大小的可用空间。如果已识别空间，则将新记录输入到同一页面上。如果未找到该空间，则从上到下检查表以寻找适当大小的空白空间。
如果找到了空间——新记录就放在那里。如果不是，则在表底部输入数据。如果需要，将创建新的数据页面。
同时，对于先前版本的记录，系统列 xmax 设置为使先前的记录版本对新事务不可见。
“VACUUM-ing”
VACUUM 审查数据并检查其可见性。如果数据过期，则该数据占用的空间被标记为空闲。这不是在 VACUUM-ing 期间执行的唯一功能，但是对于我们的目的，最重要的部分是它将这个空间标记为可用。
对于普通表，VACUUM 设法处理数据，因此新记录或记录的新版本将被插入到可用的 “间隙” 中。
VACUUM 频繁更新的表
对于更新量大的表，由于时间限制，VACUUM 不会遍历表中的所有数据，因此会在表的末尾放置一条新记录（或更新版本的记录）。这会导致保留不必要的记录和典型的表膨胀。这不仅会影响有问题的表，还会影响 TOAST 和索引，因此每个额外的操作都会因为需要处理的数据量增加而减慢。
优化VACUUM频繁更新的表
由于上述过程，为了避免表膨胀，对于频繁更新的表，应该更积极地优化 VACUUM，以便 AUTOVACUUM 更频繁地审查这些表。
这可以通过为特定表设置参数来实现，执行以下命令：
ALTER TABLE table_name SET (autovacuum_vacuum_scale_factor = 0, autovacuum_vacuum_threshold = 1000);
此命令在更新或删除 1000 行后触发 AUTOVACUUM，无论表中总共有多少行。这些参数可以在建表阶段设置。
存储参数的完整列表可以在这里找到。由于参数列表如此广泛，因此可以根据需要微调 AUTOVACUUM 设置。
Fillfactor
我想在这里强调的另一个参数是fillfactor。它负责在创建新页之前调节具有数据的页的占用率。这样，在正常的数据插入过程中，表将继续以更快的速度增长，因为页上会有计划的间隙。
但是，在更新时，如果有足够的空间，将尝试将新版本的数据放在同一页上。由于在这种情况下预先分配了可用空间，因此将数据插入同一页的可能性显着增加。
与创建插入到不同页的空白空间或插入到新添加的页相比，这提高了性能。对于需要频繁更新的表，该参数的值可以设置在 100 以下。
推荐
对表参数的全面审计和偶尔检查以确定膨胀将允许您保持经常更新的表完好无损并优化您的数据库性能。
进一步阅读
对于频繁更新表，VACUUM 配置并不是唯一建议。膨胀需要被识别和消除，我建议您查看有关该过程的贴子。
此条目是作为对我之前贴子的评论的回应而创建的。如果您有任何无法找到答案的 PostgreSQL 问题 - 请在下方评论。
您有什么有趣的用例想分享吗？ - 我们很乐意在评论中看到它们。
评论
0 点赞
