作者：Dylan Paulus 和 Carlota Soto
当处理大型 Postgres 表时，采用良好的 Postgres 维护实践变得越来越重要，以保持高性能。在所有可执行的 PostgreSQL 性能调优 中，有一个与 Postgres 存储相关的非常特殊方面应该放在首位：监控和减少表膨胀。
表膨胀是大型 PostgreSQL 表中的一个沉默杀手，尤其是在高事务量的生产数据库中。如果任其发展，它会逐渐降低您的查询速度并消耗磁盘空间，因此在扩展 Postgres 时，处理它至关重要。
在本文中，我们将通过介绍真空和自动真空过程以及讨论如何定期监控和减少膨胀来探讨如何实现这一目标。我们还将解释膨胀与 分区表 的关系，包括一些特殊建议。
在 关于此主题的先前文章 中，我们解释了表膨胀本质上是指 PostgreSQL 数据库中表消耗的超出实际存储其数据和索引所需空间的额外空间。这意味着，当您在 PostgreSQL 表上运行事务一段时间后，它可能开始变得膨胀（或膨胀），占用比预期更多的磁盘空间。
这是由于 PostgreSQL 管理数据修改的方式。PostgreSQL 使用一种称为多版本并发控制 (MVCC) 的机制，它确保当一个事务对表进行更改时，其他事务仍然可以读取表的前一个版本。PostgreSQL 不会覆盖旧数据，而是会保留该行的新的版本，并将旧的版本标记为“已删除”。随着时间的推移，随着事务的累积，这些已删除的 row（或已删除的 tuple）开始占用空间，从而导致表膨胀。
MVCC 系统有一些优点，但可以公平地说，它不是 Postgres 最出色的机制之一。它绝对是我们最不喜欢 Postgres 的东西之一，我们并不孤单.
因此，表膨胀主要以两种方式发生
当表包含大量已删除或未使用的 tuple 时
当表拥有一堆已分配的页面，而这些页面中的 tuple 严重碎片化时
感谢你的提问。
如果您正在扩展 PostgreSQL 或运行大型事务性 PostgreSQL 表，那么表膨胀应该引起您的注意，原因有很多。
表膨胀会导致性能下降。已删除的 tuple 与活动 tuple 存储在一起。在查询执行期间，PostgreSQL 必须筛选更多数据块，其中许多数据块都充满了已删除的 tuple，从而导致 I/O 增加和查询变慢。
索引也会膨胀，导致查找时间进一步变慢。索引本质上是一种数据结构，它根据一个或多个列的值提供对表中行的快速访问路径。当表中的行被更新或删除时，相关的索引值不会立即从索引中清除。与表的 MVCC 机制类似，这些过时的索引条目会被标记为无效，但不会立即删除。随着时间的推移，随着更多行被修改和删除，这些“已删除的”索引条目会累积起来，导致索引膨胀。
浪费磁盘空间。已删除的 tuple 和过多的页面分配也会导致您的磁盘使用率高于预期，这意味着更高的基础设施成本。
操作挑战。如果您的表膨胀，那么一切都会变慢，因为它本质上更大。例如，如果您使用复制，膨胀的表会导致复制延迟，因为复制过程需要处理比必要更多的数据；备份膨胀的表需要更长的时间，并且需要更多存储空间；从备份中还原可能会变得更加耗时；等等。
正如我们之前所说，表膨胀不幸的是，是 PostgreSQL 管理数据的自然结果。但也确实有一些工作负载模式会导致比其他模式更多的膨胀，例如：
大量的 UPDATE 和 DELETE 操作。经常更新的表会快速积累这些已删除的 row，因为在 Postgres 中更新 row 时，会创建一个 row 的新版本，并将旧版本标记为“已删除”。同样，当 row 被删除时，它们不会立即从表中删除。相反，它们会被标记为要删除，并稍后由自动真空过程删除（稍后将详细介绍自动真空）。
批量 INSERT 后跟 DELETE 操作。批处理操作，例如插入大量 row 后再删除其中许多 row，会导致膨胀快速累积。如果这些操作频繁发生，并且自动真空没有足够的时间或资源在这些批处理之间清理，那么这种情况尤其明显。
长时间运行的事务。长时间运行的事务可能会阻止已删除的 row 被真空处理。在事务完成之前，即使在该事务期间修改或删除的 row 不再需要，也不能对其进行真空处理。这会导致膨胀暂时累积。
在开始讨论如何减少膨胀之前，您需要了解您的表在膨胀方面的表现。PostgreSQL 中最流行、最有效的工具之一是 pgstattuple：
CREATE EXTENSION pgstattuple;
安装完成后，可以使用 pgstattuple 函数来获取特定表的概览
SELECT * FROM pgstattuple('your_table_name');
此查询将返回几个关键指标
table_len：表/索引的总大小。
tuple_count：表中的 tuple（row）数量。
tuple_len: 所有元组的总大小。
dead_tuple_count: 死元组的数量，会导致膨胀。
dead_tuple_len: 死元组占用的总大小。
有了这些指标，您可以按以下方式计算膨胀百分比：
(膨胀大小 / 表大小) * 100
其中
bloat_size = table_len - tuple_len
table_size = table_len
除了pgstattuple，您还可以使用其他方法来监控您的 Postgres 膨胀，例如：
pg_stat_user_tables 提供有关对用户表访问的统计信息，这可以帮助间接识别膨胀。如果您观察到seq_scan 正在增加，这可能表明由于膨胀导致表的索引性能不佳。
有几种自定义脚本和第三方工具利用系统视图和表来估计表的膨胀。例如，check_postgres 脚本提供了一个膨胀选项来测量和报告表和索引的膨胀。
一些图形用户界面工具和平台，例如 pgAdmin，提供了内置功能或集成来直观地监控表和索引的膨胀。
在监控膨胀时，请记住，并非所有死元组都意味着效率低下 - 一些死行是 PostgreSQL 的 MVCC 设计的自然结果。需要引起注意的是死行的过度积累。要确定对您的特定表而言“过度”意味着什么，需要一些观察。
现在，是时候谈谈如何尽可能地减少膨胀了。
Vacuum 是 PostgreSQL 中的一种维护操作，它执行以下操作：
清理死元组
更新数据统计信息以优化查询规划器
更新可见性映射以加快索引速度
如您所见，vacuum 对许多事情至关重要！在本文中，我们将重点关注第一点，即 vacuum 如何通过删除死元组来减少表膨胀。
要理解 vacuum，一个方便的类比是动态编程语言中的垃圾回收。这个概念是相似的，尽管有所不同 - 在 PostgreSQL 中，被 vacuum 的死元组不会释放回您的操作系统。表仍然拥有在页面中分配的内存，但删除死元组允许 PostgreSQL 重用该内存。
要手动运行 vacuum，执行 VACUUM 以 vacuum 当前数据库中的所有表和物化视图。或者，我们可以提供一个表名来仅对单个表运行 vacuum，VACUUM [table_name]。
但正如我们将在后面的文章中看到，PostgreSQL 带有一个默认启用的后台服务，该服务会在达到 vacuum_threshold 时自动运行 vacuum，称为 autovacuum。一旦您的表开始变大，配置 autovacuum 是有效管理膨胀的重要组成部分。
Vacuum 通过回收死元组解决了我们第一个导致表膨胀的原因。但是，第二个原因，碎片化页面怎么办？Vacuum 本身不会重新排序页面中的元组以对其进行碎片整理。它也不会删除表不再使用的页面。
您可以通过运行 VACUUM FULL 来完成，但这会导致更大的问题。 VACUUM FULL 不仅从死元组中回收空间，还压缩表并尝试减少其磁盘空间。它创建了表的副本，只复制活动元组，然后用新表替换旧表。此操作将回收的空间释放回操作系统。
VACUUM FULL 的主要问题是锁定。普通的 VACUUM 操作是非阻塞的；它不会在运行时锁定写入者（INSERT、UPDATE、DELETE 操作）。相反，VACUUM FULL 引入了 ACCESS EXCLUSIVE 锁，这会阻止任何人读取或写入表。在生产级应用程序中，这会导致中断！
要解决我们过多的页面问题，您可以考虑使用其他工具，例如 pg_repack。
运行 vacuum 不是免费的。引用官方 PostgreSQL 文档，“VACUUM 会导致 I/O 流量大幅增加，这可能会导致其他活动会话的性能下降。” 由于 VACUUM 可能很费 I/O，因此必须在数据库活动量低的时段或非高峰时间安排它，以最大限度地减少其对其他操作的影响。
实现正确的 vacuum 频率和类型是一个平衡行为。如果过于频繁地 vacuum，您可能会因过多的 I/O 操作而减慢系统速度。如果 vacuum 太不频繁，死元组的积累会降低性能并消耗磁盘空间。保持健康的 vacuum 频率需要许多 PostgreSQL 用户批判的平衡行为。
正如我们将在下一分钟看到，PostgreSQL 具有一个后台运行的 autovacuum 进程，试图自动清理死元组。但仍然必须监控并可能调整其设置，以确保它既不过于激进也不过于宽松。
PostgreSQL 带有一个名为 autovacuum 的后台服务，它会在达到 vacuum_threshold 时自动运行 vacuum。我们将在后面详细讨论此参数。
微调 autovacuum 的配置对于减少和维护表膨胀至关重要。从 PostgreSQL 8.3 开始，autovacuum 在所有 PostgreSQL 数据库中默认启用，但默认配置非常保守。之所以这样设置是为了支持旧系统。如果您有大型 PostgreSQL 表，并且频繁更新和删除，您很可能应该更改 autovacuum 的默认配置。
为此，您可以选择通过 postgresql.conf 文件全局更改整个 PostgreSQL 实例的 autovacuum 配置。可以通过执行 SQL SHOW config_file 或 psql -U postgres -c 'SHOW config_file' 从终端找到它。
或者，您可以更改每个表的 autovacuum 配置 - 这是推荐的方式。您数据库中的每个表都会有不同的死元组活动，因此会从不同的 autovacuum 设置中获益。
autovacuum 必须做出的关键决定之一是何时启动对特定表的 vacuum 过程。如前所述，该决定由 vacuum_threshold 指导，该阈值是使用表的特性和一些预设配置动态计算的。
计算 vacuum_threshold 的公式为
vacuum_threshold = autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * 元组数量
其中：
autovacuum_vacuum_threshold: 此配置值设置了在为表考虑 vacuum 之前必须更新或删除的最小元组数的基线。其默认值为 50，这意味着即使没有特定百分比的表发生变化，一旦修改了 50 个元组，就值得考虑 vacuum 操作。
autovacuum_vacuum_scale_factor: 这是一个乘数，它将 vacuum 的决定与表的大小联系起来。它确定触发 vacuum 所需的表（按元组计）的百分比。默认值为 0.2 或 20%，这意味着如果五分之一的表的元组是死的，它就成为 vacuum 的候选者。
如果您发现表的膨胀超过预期，则可能需要为大型表重新配置 autovacuum_vacuum_scale_factor。但要理解如何操作，值得深入了解一下 scale factor 的工作原理。关键是 autovacuum_vacuum_scale_factor 作为百分比，会随着表的大小进行缩放。例如，使用默认的 scale factor 0.2（20%），包含 100 行的表只需要 20 次更改（更新或删除）才能触发 autovacuum。这似乎相当合理。
但是，考虑一个包含一百万行的大得多的表。应用相同的 20% scale factor 意味着在 autovacuum 介入之前需要惊人的 200,000 次更改。在此期间，死元组（旧数据、已删除数据或已更新数据的残余）可能会大量积累。
为了解决这个问题，您可能需要考虑为大型 PostgreSQL 表降低 autovacuum_vacuum_scale_factor。将其设置为更小的十进制数，例如 0.01，这意味着在表发生 1% 的更改后就会启动 autovacuum。在我们的一百万行表的上下文中，这意味着在 10,000 次更改后，而不是 200,000 次更改后。
要更改 autovacuum_vacuum_scale_factor 以更改单个大型表，您需要运行类似于以下内容的代码：
ALTER TABLE [table_name] SET (autovacuum_vacuum_scale_factor = 0.01);
虽然您可能很想设置一个非常低的比例因子来保持表格的完整性，但请记住，真空操作也会消耗系统资源。正如我们之前所说，找到平衡至关重要。目标是在不因过于频繁地进行真空操作而对系统造成不必要的负担的情况下，最大程度地减少表格膨胀。
有时，即使在调整 autovacuum_vacuum_scale_factor 等参数之后，您也可能遇到自动真空无法跟上表格膨胀或速度比预期慢的问题。让我们深入了解一下造成这种情况的一些原因以及可能的解决方案。
PostgreSQL 包含一种机制来限制资源密集型操作（如真空和分析）的影响，以避免干扰正常的数据库活动。这种机制由一个成本限制计数器控制。当操作累积的“成本”达到预定义的限制时，相关进程将暂时停止，以让其他操作继续执行。
如果出现这种情况，您可以尝试以下解决方案：
关闭 autovacuum_vacuum_cost_delay。 通过将此参数为特定表格设置为 0，您可以有效地禁用此延迟，允许真空进程无中断地运行。
ALTER TABLE [table_name] SET (autovacuum_vacuum_cost_delay = 0);
增加成本限制。 如果关闭成本延迟过于激进，您可以改为增加 autovacuum_vacuum_cost_limit，以便在暂停之前让自动真空处理更多元组。默认值为 vacuum_cost_limit，即 200。
ALTER TABLE [table_name] SET (autovacuum_vacuum_cost_limit = 10000);
对于特别大的 PostgreSQL 表格，其中大量死亡元组，即使是经过优化的自动真空进程也可能滞后。在这种情况下，PostgreSQL 运行多个并发自动真空进程的能力就派上用场了。
默认情况下，PostgreSQL 会启动三个自动真空进程。但是，在有空闲内核的系统上，可以通过提高 autovacuum_max_workers 来增加此数量，以启动更多自动真空进程。
不幸的是，与之前讨论的参数不同，调整此参数并不像看起来那么简单。
更改不能使用 ALTER TABLE 命令进行。它需要在 postgresql.conf 文件中进行修改。
PostgreSQL 需要重新启动服务器才能使更改生效。
表格分区是保持大型 PostgreSQL 表格性能良好的绝佳方法，正如我们在此处所述。Timescale 可以帮助您 通过超表格自动执行此操作。
分区的一个不太为人所知的好处与表格膨胀有关。分区引入了一种更有效的方法来处理膨胀，尤其是在涉及时间序列或批量数据操作的情况下。分区本质上将一个表格分成更小、更易于管理的子表格（即使从用户的角度来看，它们仍然看起来像一个表格）。
当对分区表格执行操作时，只会影响相关的分区。例如，当在时间序列表格中插入特定月份的数据时，只会写入对应于该月份的分区。这种局部方法可以显着减少整个表格中死亡元组的生成。
在减少膨胀方面，使用分区的最大优势之一是删除的处理方式。使用分区允许您在不再需要数据时删除整个分区，而不是手动删除行，这会导致生成死亡元组。在减少膨胀方面，此操作非常快，因为它涉及从存储系统中删除物理文件。不会生成死亡元组。
此外，定期插入、更新和删除会导致表格碎片随着时间的推移而增加，但使用分区，每个分区都维护自己的物理存储，与单片表格相比，碎片级别更低。
就像任何事物一样，PostgreSQL 分区也有一些细微差别。在使用分区表格时，必须注意数据在分区之间的不平衡分布。合理组织的分区方案确保数据均匀分布在分区之间，但分区管理上的失误（例如，忘记创建新分区）会导致数据分布不均匀，这会带来一系列问题。（使用超表格作为您的分区方法的另一个好处是它们可以避免这些问题发生。）
分区表格中数据分布不均匀的示例
如果一个分区由于数据分布不均匀而成为热点，则会导致该特定分区内的频繁更新或删除。这会导致该分区中与其他分区相比，死亡元组的数量不成比例地增加，从而增加膨胀。
如果数据分布不均匀，某些分区可能会更频繁地进行真空操作，消耗更多资源，并可能影响性能。相反，人口稀少的地区可能不太频繁地进行真空操作，导致它们在较长时期内积累死亡元组。
数据分布不均匀也会导致类似于碎片的现象。虽然它可能不是传统意义上的膨胀（死亡元组占用空间），但分区中碎片化的可用空间可能会降低效率。
一个负载过重的分区可能会看到频繁的插入和删除，导致可用空间分散在整个分区中。这会让 PostgreSQL 更难有效地使用此空间来存放新数据，从而导致分区不必要地增长。
为了最大限度地减少与分区管理和膨胀相关的问题，请考虑使用 Timescale 的超表格。您将不再需要担心上面列出的数据分布不均匀的缺点。由于超表格会自动创建分区（在 Timescale 中称为“块”），我们不需要担心忘记创建分区。时间序列数据也有利于将自己相当均匀地分布。同样，设置数据保留策略 是一种自动删除旧块的绝佳方法，从而减小数据库的大小，而不会出现表格膨胀问题。
在通过微调自动真空来减少超表格中的膨胀方面，您可以针对每个块分别应用不同的自动真空考虑因素。块（分区）在后台是子表格，因此可以独立配置，允许您为更新更频繁的块（例如，最新的块）添加更激进的自动真空频率。
为此，请首先检索与超表格关联的所有块的列表。这将让您概述您可能想要配置的块。
SELECT show_chunks('[hypertable_name]');
确定需要配置调整的块后，您可以更改每个块的自动真空设置。该过程与更改 PostgreSQL 中单个表格的配置相同，即您可以应用我们在上一节中介绍的建议：
ALTER TABLE [chunk_name] SET (autovacuum_vacuum_cost_limit = 10000);
表格膨胀，这种经常被忽视的阻碍 PostgreSQL 性能的敌人，会偷偷摸摸地蚕食掉大型表格的效率。通过了解真空的工作原理以及微调自动真空参数，您可以控制住表格膨胀。