PostgreSQL Fillfactor 优化指南

PostgreSQL 数据库的物理结构概述

在没有非默认表空间的情况下，PostgreSQL 的每个数据库都存放在一个目录中，该目录以数据库 OID 命名。每个表对应的数据文件以 relfilenode_oid 命名。当表数据量较大时，PostgreSQL 会自动创建新的数据文件，以 relfilenode_iod.x 的形式命名，以应对文件系统的最大文件大小限制（可以通过 --with-segsize=SEGSIZE 参数修改数据文件大小）。

PostgreSQL 的数据文件以页面（数据块）为基本单位进行操作，页面大小默认为 8KB，可以通过编译时的 --with-blocksize=BLOCKSIZE 参数进行修改。数据库共享缓存中的空间也按页面划分，以确保缓存与数据文件的结构和内容一致。

每个堆表（heap file）由多个页面组成，PostgreSQL 的操作通常是追加式的。例如，insert 操作会向页面中添加新条目，update 操作会标记旧元组为已删除并添加新元组，delete 操作则会标记旧元组为已删除（如果存在索引，还需更新索引）。了解 MVCC 可更好地理解这些操作的原理。

Fillfactor 的重要性

Fillfactor 是 PostgreSQL 表存储参数中性能优化的关键之一。其值介于 10 到 100 之间，默认为 100。这意味着在页面中写入数据时，会尽可能利用空间。然而，这种默认设置在某些情况下可能导致性能问题。

例如，在执行 UPDATE 操作时，旧元组会被标记为已删除并写入新页面，这会影响索引的更新。频繁的 UPDATE 操作还会增加 CPU 和 IO 的负担，进而影响性能。

如何衡量 PostgreSQL 元组的大小

在更改 Fillfactor 之前，需先测量表行的大小。如果表行较大，减少 Fillfactor 的值可能并非最佳选择。测量工具可以通过以下 SQL 查询获取详细信息：

WITH cteTableInfo AS (    SELECT COUNT(1) AS ct,           SUM(length(t::text)) AS TextLength,           'public.table_name'::regclass AS TableName    FROM public.table_name AS t),cteRowSize AS (    SELECT ARRAY [pg_relation_size(TableName),                pg_relation_size(TableName, 'vm'),                pg_relation_size(TableName, 'fsm'),                pg_table_size(TableName),                pg_indexes_size(TableName),                pg_total_relation_size(TableName),                TextLength] AS val,           ARRAY ['Total Relation Size',                  'Visibility Map',                  'Free Space Map',                  'Table Included Toast Size',                  'Indexes Size',                  'Total Toast and Indexes Size',                  'Live Row Byte Size'] AS Name    FROM cteTableInfo, cteRowSizeUNION ALL SELECT ('------------------------------', NULL, NULL, NULL),UNION ALL SELECT ('TotalRows', ct, NULL, NULL) FROM cteTableInfo,UNION ALL SELECT ('LiveTuples', pg_stat_get_live_tuples(TableName), NULL, NULL) FROM cteTableInfo,UNION ALL SELECT ('DeadTuples', pg_stat_get_dead_tuples(TableName), NULL, NULL) FROM cteTableInfo;

通过以上查询，可以获取表的行大小、页面分布等详细信息，有助于决定是否需要调整 Fillfactor。

Fillfactor 的优化策略

在优化 Fillfactor 时，需综合考虑以下因素：

元组大小：如果元组较大，不建议减少 Fillfactor，默认值 100 更为稳妥。

频繁更新的表：需要更多的 CPU 和 IO 资源，可能影响性能。

表存储参数：可以通过 ALTER TABLE table_name SET (fillfactor = value) 更改 Fillfactor，但需注意以下事项：

Fillfactor 只影响新元组，不会自动修改现有元组。

在更改后，建议执行 VACUUM FULL 操作以重建表结构。

需要权衡数据文件大小和查询性能，避免因过度分配而导致全表扫描时的性能下降。

Fillfactor 的实际应用

需要根据具体场景进行 Fillfactor 调整。以下是常见优化步骤：

测量与分析：使用查询结果分析表行大小和页面分布，确认是否需要调整 Fillfactor。

逐步优化：如果表行较小且更新频繁，可以适当降低 Fillfactor。

监控与验证：在调整后，持续监控数据库性能，必要时进行优化。

参考文献

数据库系统概念笔记之存储和文件系统及 PostgreSQL 实现

pg 数据库表存放在哪里：超详细的 PG 数据存储结构

上一篇：PostgreSQL中的触发器

下一篇：流复制浅析 —— 物理流复制监控

发表评论

关于作者

喝酒易醉，品茶养心，人生如梦，品茶悟道，何以解忧？唯有杜康！

-- 愿君每日到此一游！