PostgreSQL 性能优化：利用 HOT 更新和填充因子提升数据库效率

CAMPING

     在PostgreSQL 中有一个关于性能提升的小技巧，这个技巧并没有受到太多关注。Postgres 可以仅更新堆（即表本身），避免更新所有的索引。这种更新称为 HOT 更新，HOT表示“Heap Only Tuple（仅堆元组）”。了解 HOT 更新及其与页面填充因子的互动，对于在现有基础设施上实现性能提升，是一个非常有效的策略。接下来，我将讨论 HOT 更新以及如何在你的 PostgreSQL 更新中促进 HOT 更新的发生。

HOT 更新的介绍

    新版本的Postgresql能够执行HOT更新，这种更新方式特指当数据库需要更新某一行数据时，如果这个新的数据版本可以直接存放在原始数据所在的同一个数据页（Page）上，并且不需要移动到其他数据页，则可以执行 HOT 更新。这种机制的优点是不需要对整个数据表的物理存储结构进行大幅度调整，从而节省了数据移动和重新索引的开销。

    简单来说，如果更新后的数据行大小没有超过原数据页的剩余空间，并且更新的字段不涉及索引键（即不影响任何索引结构），那么 PostgreSQL 就可以在同一数据页内直接更新数据，而不必重新写入一个新的数据页或更新相关的索引。这种方法大大减少了磁盘 I/O 操作，提高了更新操作的效率。

HOT更新与索引

    在 PostgreSQL 数据库中，一个表创建了索引，当表中的某个行数据发生变更时，通常情况下不仅需要更新表中的数据，还需要同步更新相关的索引。这是因为索引中保存了指向具体数据行的引用，如果数据变动了，索引也必须被更新以保持数据的一致性和索引的有效性。

    然而，PostgreSQL 的 HOT（Heap Only Tuple）更新提供了一种优化机制。如果更新操作没有涉及到任何索引的键值（即更新的字段不是索引的一部分），那么 PostgreSQL 就可以只在数据页（Heap Page）内部进行更新，而不用去更新索引。这种情况下，因为索引未被修改，所以不需要进行额外的索引维护操作。

    简单来说，HOT 更新可以避免在某些更新操作中重建索引，从而减少数据库的磁盘和CPU使用，提高更新操作的性能。这种更新特别适用于索引列没有改变而只是其他非索引列变化的情况。

PG16中的HOT更新和BRIN索引

    在 PostgreSQL 16 版本之前，如果表中某个被索引的列发生更新，这通常会阻止执行 HOT（Heap Only Tuple）更新，因为索引必须随着数据行的变动而更新。然而，PostgreSQL 16 引入了对 HOT 更新的改进，尤其是与 BRIN（区块范围索引）相结合时的表现。

    BRIN 索引与传统的 B-tree 索引或其他类型的索引不同，它不直接存储指向具体数据行的引用，而是记录数据页的范围信息。这意味着，如果更新的列使用的是 BRIN 索引，并且更新操作没有导致数据跨越原有的区块范围，则 PostgreSQL 可以在不影响 BRIN 索引的情况下，在原数据页内进行 HOT 更新。这大大减少了更新操作对索引的影响，从而提高了性能。

    但是，需要注意的是，尽管 BRIN 索引在某些情况下可以支持 HOT 更新，如果更新的数据变动过大，超出了原有的区块范围，仍然可能会影响 BRIN 索引的效率和准确性。因此，在使用 BRIN 索引时，应当注意控制数据更新的幅度，确保数据变动在可接受的范围内，以维持索引的高效性。

填充因子（Fill Factor）

    在 PostgreSQL 中，一张表被分割为多个块或页面，每个页面可以存储固定数量的数据。当新的数据行插入到表中时，PostgreSQL 会尝试将其放入现有的页面中。如果没有足够的空间，就会分配一个新的页面。填充因子用来决定每个页面最初为将来的更新预留多少空间。

    PostgreSQL 的表默认填充因子为 100，这意味着默认情况下，PostgreSQL 会尽量将每个页面填满数据，不留空间给未来的数据更新。

    例如，假设一张表的填充因子设定为 70%。这样在 PostgreSQL 插入数据到页面时，会故意留下 30% 的空间空着，以便未来对现有数据行的更新。当表的填充因子更高（接近 100%）时，每个数据页面的空余空间更少，这可能导致数据行更新时空间不足，需要重新分配页面。

    通过降低填充因子，可以增加每个数据页面有足够空余空间来适应 HOT 更新的机会。但是，设置填充因子需要谨慎，因为过低的填充因子可能导致空间浪费以及增加磁盘 I/O，特别是在进行表扫描时。

配置填充因子

你可以在 CREATE TABLE 或 ALTER TABLE 语句中使用 FILLFACTOR 参数为表设置填充因子。该值的范围从 10 到 100，表示最初要填充的空间百分比。

-- 创建一个填充因子为 70% 的表
CREATE TABLE dbsec_test1 (    
id SERIAL PRIMARY KEY,    
names VARCHAR(100),    
age INT,    
dbsec_type VARCHAR(20)
) 
WITH (FILLFACTOR = 70);

也可以更改现有表的填充因子（alter table）：

-- 修改现有表的填充因子
ALTER TABLE dbsec_test1 SET (FILLFACTOR = 80);

HOT更新与填充因子

当填充因子设置得当时，HOT更新特别有效。当一张表的填充因子较低时，PostgreSQL 在插入数据行时会在每个数据页面留下更多空白空间。这些空白空间允许 PostgreSQL 在同一页面上进行更新，无需将整行数据移动到新的页面。在 PostgreSQL 中降低填充因子可以通过增加新版本数据行在数据页面上有空余空间的可能性，从而促进更多的 HOT 更新。

读写密集型场景与填充因子

    尽管可以降低填充因子，但并不是在所有情况下都适宜这么做：

• 读密集型工作负载：读密集型的表可能会从更高的填充因子中受益，以减少需要从磁盘读取的页面数。

• 写密集型工作负载：更新操作可能会从较低的填充因子中受益，以增加HOT更新的机会。

    对于读写均衡的数据库，将填充因子设置在 70、80 和 90 的范围内，可以合理利用 HOT 更新。有些情况下，可能会将填充因子设置到 50 甚至更低。

监控 HOT更新

    要识别 Postgres 中的 Heap Only Tuple (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 更新的发生。

    此外，值得一提的是，降低填充因子会增加你的测量膨胀。大多数膨胀查询会根据表统计数据估算行大小，并估计数据文件中有多少空间未被使用。在许多情况下，由于填充因子较低而产生的未使用空间将被视为膨胀。通常，这只是值得注意的一点，并不会引起膨胀警告或担忧，除非你的填充因子非常低。

    通过理解并适当调整填充因子，以及优化索引和更新策略，PostgreSQL 管理员可以显著提升数据库的写入效率和性能，尤其是在处理写密集型或频繁更新的场景中。此外，合理的填充因子设置还可以帮助减少磁盘 I/O 需求和提高查询效率，从而在读写均衡的环境中达到最优的性能平衡

总 结