四月 17, 2024
摘要：在本教程中，您将学习如何在 PostgreSQL 中优化表的填充因子。
PostgreSQL 有一种方法可以只更新堆（表），而不必更新所有索引。这称为 HOT 更新，HOT 代表仅堆元组。
了解 HOT 更新及其与页面填充因子的关系，这可能是一个非常好的方法，用来提升现有基础架构的性能。让我们来回顾下 HOT 更新，以及如何在您的 PostgreSQL 更新中利用它们。
仅堆元组（HOT）更新
现代版本的 PostgreSQL 能够执行 HOT（仅堆元组）更新。当一个行的新版本可以存储在与原始版本相同的页面上时，将发生 HOT 更新，而无需将该行移动到新页面。
使用 HOT 更新时，如果更新后的行仍然可以与原始行位于同一数据页面上，则 Postgres 会在同一页面上添加新行，同时保留旧行数据，因为它可能仍被其他进程使用。Postgres 还会添加从旧行到新行的 HOT 链的链接，从而在发生 HOT 更新时，它可以找到新行。
HOT 更新和索引
因此，在没有 HOT 更新的 PostgreSQL 中，通常的工作方式是，如果您有一个带索引的表，在更新一行（元组）的时候，更新必须要应用到索引。对于 HOT 更新，如果您不更新索引键，Postgres 将跳过对索引的更新。
通过跳过索引更新的步骤，HOT 更新可减少更新操作所需的磁盘 I/O 和 CPU 处理，从而提高性能，尤其是对于索引较大或更新频繁的表。
HOT 更新和 BRIN 索引
在 PostgreSQL 16 之前，更新列上的任何索引都会阻止更新以 HOT 方式进行。PostgreSQL 16 中的版本更新让 HOT 更新更加可行，因为 BRIN（汇总）索引不包含对实际行的引用，只包含对页面的引用。这允许更新带有 BRIN 索引的列，并且仍然进行 HOT 更新。不过也要注意避免这些更新对列值进行过多更改，因为这可能会降低 BRIN 的有效性。
填充因子
在 PostgreSQL 中，一个表被划分为块或页面，每个块或页面都能够保存一定量的数据。当将一个新行插入到表中时，PostgreSQL 会尝试将其放入现有页面中。如果没有足够的空间，则会分配一个新页面。填充因子确定了每个页面中最初会为将来的更新保留多少空间。
Postgres 中表的默认填充因子是 100。这意味着默认情况下，Postgres 在最初存储数据时，会尝试将每个页面尽可能填充满，而不会为将来的更新预留任何空间。
例如，考虑一个填充因子为 70% 的表。当 PostgreSQL 将数据插入到页面时，它会将 30% 的页面留空，以适应将来对现有行的更新。当一个表具有较高的填充因子（接近 100%）时，每个数据页上的空闲空间就会减少。这可能会出现，对某一个行的更新需要比原始数据页面上的可用空间更多的空间。
通过降低填充因子，可以有更大的可能，让数据页面上有足够的可用空间，来对现有的行进行 HOT 更新。但要注意，需要对填充因子的设置保持平衡，因为将其设置得太低，会导致浪费空间并增加扫描表时的磁盘 I/O。
配置填充因子
您可以使用CREATE TABLE或ALTER TABLE语句中的FILLFACTOR参数，设置表的填充因子。该值的有效范围为 10 到 100，表示最初要填充的页面空间百分比。
-- Create a table with a fill factor of 70%
CREATE TABLE my_table (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
age INT
) WITH (FILLFACTOR = 70);
您可以更改一个现有表，以调整其填充因子：
-- Alter the fill factor of an existing table
ALTER TABLE my_table SET (FILLFACTOR = 80);
HOT 更新和填充因子
当填充因子设置得当时，HOT 更新特别有效。
当一个表的填充因子较低时，PostgreSQL 在插入行时会在每个数据页面上留出更多空闲空间。这些空闲空间允许 PostgreSQL 在同一页面上执行更新，而无需将整行移动到新页面。降低 PostgreSQL 中的填充因子，可以增加数据页面上为新版本的行提供可用空间的可能性，从而强制进行更多的 HOT 更新。
读取/写入繁重的工作负载和填充因子
尽管您可以降低填充因子，您不会想在每种情况下都这样做：
- 读取繁重的工作负载：读取繁重的表可能会受益于更高的填充因子，以减少需要从磁盘读取的页数。
- 写入繁重的工作负载：更新可能会受益于较低的填充因子，以增加 HOT 更新的机会。
在 70、80 和 90 范围内的填充因子设置，对于一个读/写数据库更好地利用 HOT 更新，是合理的。有些人可能会下调到 50 甚至更低，但这都是更新工作负载异常繁重情况下的特例。
监测 HOT 更新
要识别 PostgreSQL 中的仅堆元组（HOT）更新，可以查询系统视图pg_stat_user_tables或pg_stat_all_tables，获取关于表上执行更新的频率，以及其中有多少是 HOT 更新的信息。下面是一个这样的查询：
SELECT
relname AS table_name,
seq_scan AS sequential_scans,
idx_scan AS index_scans,
n_tup_ins AS inserts,
n_tup_upd AS updates,
n_tup_hot_upd AS hot_updates
FROM
pg_stat_user_tables
ORDER BY
hot_updates DESC;
在理想情况下，所有的更新都可能是 HOT 更新。开发人员应该查看他们的索引策略，和他们正在进行的更新，并尝试使这些更新符合 HOT 更新的条件 - 当它们符合 HOT 条件时，可以下调这些表上的填充因子，以进一步利用 HOT 更新。
还值得一提的是，降低填充因子会增加您监测出来的膨胀量。大多数膨胀的查询，根据表统计信息来估计行大小，并估计数据文件中未使用的空间量。在许多情况下，由于较低填充因子产生的未使用空间会被视为膨胀。一般来说，这只是需要注意的事情，不会引起膨胀的警告或担忧，除非你的填充因子非常非常低。
总结
- HOT 更新是指更新后的行可以与旧行放在同一页面上。
- HOT 更新可提高性能，因为它们减少了所需的 IOPS、WAL、索引更新和其他资源。
- 如果您尝试使用更多 HOT 更新，请考虑您的索引策略。如果更新的数据已被索引，则通常不能进行 HOT 更新，除非索引为 BRIN 类型。BRIN 索引支持 HOT 更新，是 PostgreSQL 16 中的一个新特性。
- 您可以通过降低页面填充因子，来增加 HOT 更新的机会。填充因子和性能可能需要有所权衡，因此通常最好采用一种监测和观察的方法来确定它。