数据库fillfactor
postgresql-fillfactor
fillfactor
fillfactor是在创建表的时候指定的参数，该参数是限制数据插入一页时预留的空闲空间比例，对于数据库表的默认值是100，索引默认值是90
table（默认100）
一个表的填充因子是一个10-100质检的百分数。100（完全填满）是默认值。设置较小的填充因子，insert操作会把表页面只填满到指定的百分比，剩余的空间留给页面上行的更新。这就让update有机会把一行的已更新版本放到在与原始版本相同的页面上，这比把它放在一个不同的页面上效率更高。对于不经常更新的表来说，设置为100是最好的选择，如果更新频繁设置较小的值更合适。这个参数对toast表不生效。
index（fillfactor默认90）
索引的填充因子是一个百分数，它决定索引方法将尝试填充索引页面的充满程度。对于B-tree，在初始的索引构建过程中，叶子页面会被填充至该百分数，B-tree默认的填充因子是90，可以设置为10-100的任何整数值。如果表是静态的，那么填充因子100是最好的，这样索引占用空间最小。对于更新频繁的表，设置较小的值有利于最小化页面分裂。
验证
--创建表test_fill_1设置fillfactor=100
abase=# create table test_fill_1(n_id int,c_xm varchar(300)) with (fillfactor=100);
CREATE TABLE
--创建表test_fill_2设置fillfactor=80
abase=# create table test_fill_2(n_id int,c_xm varchar(300)) with (fillfactor=80);
CREATE TABLE
--添加主键
abase=# alter table test_fill_1 add primary key(n_id);
ALTER TABLE
abase=# alter table test_fill_2 add primary key(n_id);
ALTER TABLE
--初始化数据
abase=# insert into test_fill_1 select generate_series(1,1000000),'zhangsan'||generate_series(1,1000000);
INSERT 0 1000000
Time: 7067.047 ms
abase=# insert into test_fill_2 select generate_series(1,1000000),'zhangsan'||generate_series(1,1000000);
INSERT 0 1000000
Time: 6849.234 ms
--表分析
postgres=# vacuum analyze test_fill_1;
VACUUM
postgres=# vacuum analyze test_fill_2;
--查看表的页数
abase=# select relpages,reltuples from pg_class where relname = 'test_fill_1';
relpages | reltuples
----------+-----------
6369 | 1e+06
(1 row)
abase=# select relpages,reltuples from pg_class where relname = 'test_fill_2';
relpages | reltuples
----------+-----------
7999 | 1e+06
(1 row)
--查看表结构
abase=# \d+ test_fill_1;
Table "public.test_fill_1"
Column | Type | Modifiers | Storage | Stats target | Description
--------+------------------------+-----------+----------+--------------+-------------
n_id | integer | | plain | |
c_xm | character varying(300) | | extended | |
Options: fillfactor=100
abase=# \d+ test_fill_2;
Table "public.test_fill_2"
Column | Type | Modifiers | Storage | Stats target | Description
--------+------------------------+-----------+----------+--------------+-------------
n_id | integer | | plain | |
c_xm | character varying(300) | | extended | |
Options: fillfactor=80
--查看表大小，fillfactor越大占用的空间越小
abase=# select pg_size_pretty(pg_relation_size('test_fill_1'));
pg_size_pretty
----------------
50 MB
(1 row)
Time: 1.251 ms
abase=# select pg_size_pretty(pg_relation_size('test_fill_2'));
pg_size_pretty
----------------
62 MB
(1 row)
Time: 0.995 ms
--查看主键大小
abase=# select pg_size_pretty(pg_relation_size('test_fill_1_pkey'));
pg_size_pretty
----------------
21 MB
(1 row)
abase=# select pg_size_pretty(pg_relation_size('test_fill_2_pkey'));
pg_size_pretty
----------------
21 MB
(1 row)
初始化数据耗时差别不大，fillfactor=80略快。设置了fillfactor=80的表占用空间更大。索引方面占用空间一样。
更新数据
--1.更新test_fill_1
abase=# select ctid,* from test_fill_1 where n_id =1;
ctid | n_id | c_xm
-------+------+-----------
(0,1) | 1 | zhangsan1
(1 row)
abase=# update test_fill_1 set c_xm='李四' where n_id = 1;
UPDATE 1
--更新test_fill_1 fillfactor为100，更新后，数据插入到了最后一页ctid为(6368,74)
abase=# select ctid,* from test_fill_1 where n_id =1;
ctid | n_id | c_xm
-----------+------+------
(6368,74) | 1 | 李四
(1 row)
--2.更新test_fill_2
abase=# select ctid,* from test_fill_2 where n_id =1;
ctid | n_id | c_xm
-------+------+-----------
(0,1) | 1 | zhangsan1
(1 row)
Time: 1.392 ms
abase=# update test_fill_2 set c_xm='李四' where n_id = 1;;
UPDATE 1
--test_fill_2表的fillfactor为80，还剩余20%的空间可以利用，更新后数据是在第一页插入了这条数据，ctid为 (0,149)还在第一页
abase=# select ctid,* from test_fill_2 where n_id =1;
ctid | n_id | c_xm
---------+------+------
(0,149) | 1 | 李四
(1 row)
设置了fillfactor=100后，更新数据会在最后一页插入一条数据
而设置fillfactor=80，数据会在当前页插入一条数据
更新效率
--为了看出明显的效果，先将autovacuum关闭掉
--更新test_fill_1的所有数据‘
更新的效率来看较小的fillfactor更新更快
abase=# update test_fill_1 set c_xm = c_xm||'x';
UPDATE 1000000
Time: 13035.901 ms
--更新test_fill_2的所有数据
abase=# update test_fill_2 set c_xm = c_xm||'x';
UPDATE 1000000
Time: 10162.411 ms
--再次全部更新
abase=# update test_fill_1 set c_xm = c_xm||'y';
UPDATE 1000000
Time: 11741.058 ms
abase=# update test_fill_2 set c_xm = c_xm||'y';
UPDATE 1000000
Time: 10838.738 ms
abase=# update test_fill_1 set c_xm = c_xm||'z';
UPDATE 1000000
Time: 14763.844 ms
abase=# update test_fill_2 set c_xm = c_xm||'z';
UPDATE 1000000
Time: 9392.977 ms
--经过三次全部更新来看，设置fillfactor=80时，更新的速度在10s左右。
--多次更新后，可以看到fillfactor=80的页在更新时，还是会使用前面的页的旧行。
abase=# select ctid,*from test_fill_1 limit 100;
ctid | n_id | c_xm
------------+--------+-----------------------
(6369,155) | 1 | zhangsan1xyzxxy
(6369,156) | 2 | zhangsan2xyzxxy
(6369,157) | 3 | zhangsan3xyzxxy
(6369,158) | 4 | zhangsan4xyzxxy
(6369,159) | 5 | zhangsan5xyzxxy
(6369,160) | 6 | zhangsan6xyzxxy
(6369,161) | 7 | zhangsan7xyzxxy
(6369,162) | 8 | zhangsan8xyzxxy
(6369,163) | 9 | zhangsan9xyzxxy
(6369,164) | 10 | zhangsan10xyzxxy
(6369,165) | 11 | zhangsan11xyzxxy
(6369,166) | 12 | zhangsan12xyzxxy
(6369,167) | 13 | zhangsan13xyzxxy
abase=# select ctid,*from test_fill_2 limit 100;
ctid | n_id | c_xm
---------+------+---------------------
(0,158) | 1 | zhangsan1xyzxxy
(0,159) | 2 | zhangsan2xyzxxy
(0,160) | 3 | zhangsan3xyzxxy
(0,161) | 4 | zhangsan4xyzxxy
(0,162) | 5 | zhangsan5xyzxxy
(0,163) | 6 | zhangsan6xyzxxy
(0,164) | 7 | zhangsan7xyzxxy
(0,165) | 8 | zhangsan8xyzxxy
(0,166) | 9 | zhangsan9xyzxxy
(0,167) | 10 | zhangsan10xyzxxy
(0,168) | 11 | zhangsan11xyzxxy
(0,169) | 12 | zhangsan12xyzxxy
(0,170) | 13 | zhangsan13xyzxxy
(0,171) | 14 | zhangsan14xyzxxy
(0,172) | 15 | zhangsan15xyzxxy
--更新小范围数据，test_fill_2效果很明显
abase=# update test_fill_1 set c_xm = c_xm||'xx' where n_id>1000 and n_id <2000;
UPDATE 999
Time: 28.306 ms
abase=# update test_fill_2 set c_xm = c_xm||'xx' where n_id>1000 and n_id <2000;
UPDATE 999
Time: 13.577 ms
在update（全量）的时候fillfactor=80的效率更高，少量数据更新的时候低fillfactor效果更明显。
再次update以后，可以看到fillfactor=80的页在更新时，还是会使用更新到前面的页的旧行。（没有autovacuum的情况下仍然更新可以使用标记为删除的行）
更新后索引大小
--test_fill_1表的所以更大
postgres=# select pg_size_pretty(pg_relation_size('test_fill_1_pkey'));
pg_size_pretty
----------------
66 MB
(1 row)
Time: 0.870 ms
postgres=# select pg_size_pretty(pg_relation_size('test_fill_2_pkey'));
pg_size_pretty
----------------
43 MB
(1 row)
Time: 0.782 ms
可以看到设置了fillfactor=80表的索引比fillfactor=100的索引要小，而fillfactor=100膨胀的更快，这是为什么呢？请往下看
Heap-Only Tuples(hot)
在PG中因为多版本功能的原因，当更新一行时，实际上旧行并未被删除，只是插入一条新行。如果这个表上有索引，而更新的字段不是索引的键值时，由于新行的物理位置发生了变化，因此仍然需要更新索引，这将导致性能下降。为了解决这个问题PostgreSQL自8.3版本后，引入了一个名为Heap-Only Tuple的新技术，简称HOT。使用HOT技术之后，如果更新后的新行与旧行在同一个数据块内，旧行会有一个指针，指向新行，这样就不必更新索引了，当从索引访问到数据行时，会根据这个指针找到新行。
HOT详细说明见下图，图中表上有一个索引，其中“索引项n”指向数据块的第3行。
更新第三行后，因为有用HOT技术，所以索引项仍然指向原先的旧数据（第3行），而第3行旧数据中有一个指针指向新数据（第6行），如下图：
注意，如果在原先的数据块中无法放下新行，就不能使用HOT技术了，即HOT技术中的行间指针只能在一个数据块内，不能跨数据块。所以为了使用HOT技术，应该在数据块中留出较多的空闲空间，方法是把表的填充因子（fillfactor）设置为一个合适的值。
Fillfactor参数的意思是插入数据时，块数据的空间占用率达到这个比率后，就不在插入数据了，默认值为100，表示块中不留存空间，数据全部填满数据块。
当有空闲空间的时候，更新可能会走hot更新，数据是在一页内变动的，索引不会变化。所以前面fillfactor=80在更新后索引要比fillfactor=100小。
总结
1.初始化数据耗时差别不大，设置了fillfactor=80的表占用空间更大，初始化时索引方面占用空间一样。
2.设置了fillfactor=100后，更新数据会在最后一页插入一条数据，而设置fillfactor=80，数据会在当前页空闲空间插入一条数据
3.在更新较频繁的表设置合适的fillfactor可以提高更新效率，因为有hot技术，减小索引膨胀
4.在update（全量）的时候fillfactor=80的效率更高，少量数据更新的时候低fillfactor效果更明显。
5.多次update以后，可以看到fillfactor=80时，还是会使用前面的页的旧行。（如果autovacuum没有及时清理的情况下更新可以使用标记为删除的行）
