Fillfactor 参数

介绍Fillfactor前先帮助大家回顾一下，Postgresql数据库的物理结构。  

在没有非默认表空间的情况下，Postgresql的每个数据库均存放在一个目录中，以数据库oid命名，该目录中存放每个表对应的数据文件，文件名以该数据表对应的relfilenode_oid命名。当表中的数据量足够大，导致表文件的大小大于1GB的时候，Postgresql会自动创建新的数据文件用于存放新插入的数据。新文件的名称为： relfilenode_iod.1, relfilenode_iod.2 等。使用该策略是为了防止在某些文件系统中，最大支持文件尺寸不能大于1GB的情形（--with-segsize=SEGSIZE 可修改数据文件大小）。   PostgreSQL将数据文件中的空间从逻辑上划分成一个个页面(数据块)。页面是数据库I/O的基本单位，即只能整页读写数据文件, 页面的大小默认是8K。PostgreSQL中在编译时可通过（--with-blocksize=BLOCKSIZE）来修改页面大小，更大的页面在一些特殊数据库应用中可能会很有用。   数据库共享缓存中的空间划分也是按页为基本单位, 一个页的大小与数据文件中页的大小一致, 这样便于整页读取数据文件,并放入到数据库Buffer中, 从Buffer写入数据文件也同理，保证了缓存与数据文件结构和内容上的一致性。   在PostgreSQL中，表文件中的tuple之间不进行关联，这样的表文件称为堆文件，每个堆文件都是由多个page组成，PostgreSQL中对数据进行操作一般都是通过追加数据来实现的，例如 insert 时向页中添加一条数据，update 将历史数据标记为删除,然后向页中添加新数据，delete 将历史数据标记为删除，假如存在索引，还涉及到索引的修改。这部分如果不太明白，可以了解一下PostgreSQL的MVCC。

  在了解了以上概念以及Postgresql数据库的物理结构后，我们可以更好地理解Fillfactor。  

1. Fillfactor是一个表存储参数，对于性能方面，这是最重要的参数之一。

  表的Fillfactor是介于10和100之间的百分比。默认值是100。   由于默认值为100，这意味着数据库会在页面中写入数据直到它没有剩余空间。因此在执行一个UPDATE时，旧元组将被标记为已删除，并且新元组将插入另一个页面。另外一个影响是，需要修改新记录的相关索引，因为索引必须指向新的位置。   还记得前面我们提到过的，对于一个UPDATE实际上有三个操作，首先标记已删除的元组，在新页面中写入新元组，以及索引修改。这不仅仅和UPDATE相关，实际上SELECT查询的性能也会因内部碎片而降低。   --查看表或者索引的Fillfactor值

SELECT
pc.relname AS ObjectName,pc.reloptions 
AS ObjectOptions
FROM pg_class AS pc
INNER JOIN pg_namespace AS pns
ON pns.oid = pc.relnamespace
WHERE pns.nspname = "public"
AND pc.relname = "table_name";     --替换为实际表名

如果设置了Fillfactor，你可以找到“{fillfactor = value}”，否则它只是NULL。

默认的Fillfactor是100，这个设置相对来说比较稳妥，但不是在所有情况下都是如此。

当表频繁更新时，你需要更多的CPU和IO用于不同的数据页操作这势必会影响性能。 解决方案是首先测量元组的大小，如果元组大小不是那么大，我们可以减少默认Fillfactor的值。  

2. 如何衡量PostgreSQL中元组的大小？

  在更改Fillfactor的默认值之前，我们应该测量表行的大小。如果表行较大，我们不应该更改Fillfactor的默认值。当我们进行性能优化时，知道page和堆表行的大小非常重要，否则我们需要处理高碎片并一次又一次地执行VACUUM FULL或VACUUM。   如果总行大小低于8kb，则可以尝试更改表存储参数。 使用下面的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
)
SELECT
unnest(name) AS Description
,unnest(val) AS Bytes
,pg_size_pretty(unnest(val)) AS BytesPretty
,unnest(val) / ct AS bytes_per_row
FROM cteTableInfo, cteRowSize
UNION 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;

3. 如何在PostgreSQL中更改Table Fillfactor的当前值？

ALTER TABLE table_name SET ( fillfactor = 50);
VACUUM FULL table_name;

  请注意： 更改Fillfactor的值后，只在特定表上执行VACUUM FULL。 更改Fillfactor的值后，它仅适用于新元组。 当我们减少Fillfactor的默认值时，每个页面都有一些空闲空间，UPDATE生成的新元组可以存储在同一页面上，索引页面就不需要任何修改，但是这也会导致数据文件比正常情况要更大，当做全表扫描时会导致访问更多的page，这些就需要运维人员进行性能测试，自行判断是否应该修改该参数。   参考文献：   http://www.postgres.cn/news/viewone/1/220                                                 数据库系统概念笔记之存储和文件系统及PostgreSQL实现 https://blog.csdn.net/weixin_39747511/article/details/112923862                 pg数据库表存放在哪里_超详细的PG数据存储结构