lightdb建表参数fillfactor建议

fillfactor简介

一个表的填充因子，是一个10-100之间的百分数，100（完全填满）是默认值（功能相当于ORACLE的PCTFREE参数，默认是10）。insert操作会把表页只填写到指定的百分比，每个页面上剩余的空间被保留给该页的行更新。这就让update有机会把一行已更新版本放在与其原始版本相同的页面上，这比把它放在不同的页面上效率更高。

优点

当数据库更新时，索引也是需要进行维护的，特别是再高并发情况下，索引的维护代价会很大，并且可能造成索引的分裂，pg为了避免这个问题，引入了Heap-Only Tuple（堆内元组技术），他的总体思想是在旧行与新行之间建立一个链表，这样一来更新一行数据时，索引继续指向旧行，通过链表找到新行，不用重新维护索引，降低了开销，提升了更新效率。然而链表是不能跨数据块的，当更新的行数据与旧数据不在同一页时，指向该行数据的索引也被添加到索引页面中，所以如果设置了一个较小的fillfactor则提高了更新数据在一个数据块内完成的概率，能够更好的利用Heap-Only Tuple技术

fillfactor的存储情况

创建2两张测试表fillfactor=100和75，分别插入一些数据

###根据ctid查看数据分布情况
create table t_fillfactor_100(id int,c1 varchar(300),c2 varchar(300),constraint pk_100id primary key(id)) with(fillfactor=100);
insert into t_fillfactor_100 select generate_series(1,1000000),'AAAAAAAAAA','aaaaaaaaaa';
lightdb@csdb1=# select ctid,id from t_fillfactor_100 limit 1000;
  ctid   |  id  
---------+------
 (0,1)   |    1
 (0,2)   |    2
 (0,3)   |    3
 (0,4)   |    4
 (0,5)   |    5
 。。。
 (0,135) |  135
 (0,136) |  136
 (1,1)   |  137
 (1,2)   |  138
 (1,3)   |  139
 。。。
 (1,133) |  269
 (1,134) |  270
 (1,135) |  271
 (1,136) |  272
 (2,1)   |  273
 (2,2)   |  274
 (2,3)   |  275
 (2,4)   |  276
 (2,5)   |  277


create table t_fillfactor_75(id int,c1 varchar(300),c2 varchar(300),constraint pk_75id primary key(id)) with(fillfactor=75);
insert into t_fillfactor_75 select generate_series(1,1000000),'BBBBBBBBBB','bbbbbbbbbb';
 ###根据ctid查看数据分布情况
lightdb@csdb1=# select ctid,id from t_fillfactor_75 limit 1000;
  ctid   |  id  
---------+------
 (0,1)   |    1
 (0,2)   |    2
 (0,3)   |    3
 (0,4)   |    4
 (0,5)   |    5
 。。。。
 (0,100) |  100
 (0,101) |  101
 (0,102) |  102
 (1,1)   |  103
 (1,2)   |  104
 (1,3)   |  105
 。。。。
 (1,100) |  202
 (1,101) |  203
 (1,102) |  204
 (2,1)   |  205
 (2,2)   |  206
 (2,3)   |  207
 (2,4)   |  208
 (2,5)   |  209
ctid 代表数据的实际物理存储位置，比如(0,1)中的0代表块id，1代表在这个数据块上的第一条记录

根据上面的情况可以发现，在填充因子设置100的表，每个页面会存储136行数据，而填充因子设置75的表只有102，102/136刚好是页面填满的0.75

对比两张表的大小

lightdb@csdb1=# select pg_size_pretty(pg_relation_size('t_fillfactor_100'));
 pg_size_pretty 
----------------
 57 MB
(1 row)

lightdb@csdb1=# select pg_size_pretty(pg_relation_size('t_fillfactor_75'));
 pg_size_pretty 
----------------
 77 MB
(1 row)

配置较小的填充因子所占用的空间会更多

批量insert\update性能对比

创建2张测试表，fillfactor 分别为100、75、50，插入一些数据，使用\timing记录时间，记录插入后表的大小；

lightdb@csdb1=# create table t_fillfactor_100(id int,c1 varchar(300),c2 varchar(300),constraint pk_100id primary key(id)) with(fillfactor=100);
CREATE TABLE
Time: 4.333 ms
lightdb@csdb1=# create table t_fillfactor_75(id int,c1 varchar(300),c2 varchar(300),constraint pk_75id primary key(id)) with(fillfactor=75);
CREATE TABLE
Time: 14.952 ms
lightdb@csdb1=# create table t_fillfactor_50(id int,c1 varchar(300),c2 varchar(300),constraint pk_50id primary key(id)) with(fillfactor=50);
CREATE TABLE
Time: 13.097 ms




lightdb@csdb1=# insert into t_fillfactor_100 select generate_series(1,1000000),'AAAAAAAAAA','aaaaaaaaaa';
INSERT 0 1000000
Time: 1803.724 ms (00:01.804)
lightdb@csdb1=# insert into t_fillfactor_75 select generate_series(1,1000000),'BBBBBBBBBB','bbbbbbbbbb';
INSERT 0 1000000
Time: 1862.422 ms (00:01.862)
lightdb@csdb1=# insert into t_fillfactor_50 select generate_series(1,1000000),'CCCCCCCCCC','cccccccccc';
INSERT 0 1000000
Time: 3136.870 ms (00:03.137)

记录表大小

lightdb@csdb1=# select pg_size_pretty(pg_relation_size('t_fillfactor_100'));
 pg_size_pretty 
----------------
 57 MB
(1 row)

Time: 0.426 ms
lightdb@csdb1=# select pg_size_pretty(pg_relation_size('t_fillfactor_75'));
 pg_size_pretty 
----------------
 77 MB
(1 row)

Time: 0.544 ms
lightdb@csdb1=# select pg_size_pretty(pg_relation_size('t_fillfactor_50'));
 pg_size_pretty 
----------------
 117 MB
(1 row)

Time: 0.391 ms

记录更新耗时情况
lightdb@csdb1=# update t_fillfactor_100 set c1 ='XXXXXXXXXX',c2='xxxxxxxxxx';
UPDATE 1000000
Time: 3815.667 ms (00:03.816)
lightdb@csdb1=# update t_fillfactor_75 set c1 ='YYYYYYYYYY',c2='yyyyyyyyyy';
UPDATE 1000000
Time: 3337.989 ms (00:03.338)
lightdb@csdb1=# update t_fillfactor_50 set c1 ='ZZZZZZZZZZ',c2='zzzzzzzzzz';
UPDATE 1000000
Time: 1535.088 ms (00:01.535)

再次记录表大小
lightdb@csdb1=# select pg_size_pretty(pg_relation_size('t_fillfactor_100'));
 pg_size_pretty 
----------------
 115 MB
(1 row)

Time: 0.544 ms
lightdb@csdb1=# select pg_size_pretty(pg_relation_size('t_fillfactor_75'));
 pg_size_pretty 
----------------
 128 MB
(1 row)

Time: 0.556 ms
lightdb@csdb1=# select pg_size_pretty(pg_relation_size('t_fillfactor_50'));
 pg_size_pretty 
----------------
 117 MB
(1 row)

Time: 0.827 ms

	fillfactor=100	fillfactor=75	fillfactor=50
insert耗时	1803.724 ms	1862.422 ms	3136.870 ms
insert后表大小	57 MB	77 MB	117 MB
update耗时	3815.667 ms	3337.989 ms	1535.088 ms
update后表大小	115 MB	128 MB	117 MB

从表格中，可以分析出，fillfactor会降低insert的性能,但是update性能将会得到提升

建议：

根据需求选择合适的fillfactor,在建表时不要使用默认值

附知识点：

PostgreSQL中数据操作永远是Append操作,具体含义如下:

1. insert 时向页中添加一条数据
2. update 将历史数据标记为无效,然后向页中添加新数据
3. delete 将历史数据标记为无效

因为这个特性,所以需要定期对数据库vacuum,否则会导致数据库膨胀,建议打开autovacuum.