本節介紹了PostgreSQL建立資料表時的引數fillfactor,該引數限制了插入資料時序預留多少空閒空間(比例),對於資料表來說預設值為100,索引是90,其解釋如下:
table
>The fillfactor for a table is a percentage between 10 and 100. 100 (complete packing) is the default. When a smaller fillfactor is specified, INSERT operations pack table pages only to the indicated percentage; the remaining space on each page is reserved for updating rows on that page. This gives UPDATE a chance to place the updated copy of a row on the same page as the original, which is more efficient than placing it on a different page. For a table whose entries are never updated, complete packing is the best choice, but in heavily updated tables smaller fillfactors are appropriate.

index
>The fillfactor for an index is a percentage that determines how full the index method will try to pack index pages. For B-trees, leaf pages are filled to this percentage during initial index build, and also when extending the index at the right (largest key values). If pages subsequently become completely full, they will be split, leading to gradual degradation in the index's efficiency. B-trees use a default fillfactor of 90, but any value from 10 to 100 can be selected. If the table is static then fillfactor 100 is best to minimize the index's physical size, but for heavily updated tables a smaller fillfactor is better to minimize the need for page splits. The other index methods use fillfactor in different but roughly analogous ways; the default fillfactor varies between methods.

下面建立不同fillfactor的資料表,執行update操作
```
[local]:5432 pg12@testdb=# create table t_fillfactor_100(id int,c1 varchar(30),c2 varchar(30),c3 varchar(30)) with (fillfactor=100);
CREATE TABLE
Time: 2.462 ms

[local]:5432 pg12@testdb=# create table t_fillfactor_70(id int,c1 varchar(30),c2 varchar(30),c3 varchar(30)) with (fillfactor=70);
CREATE TABLE
Time: 3.437 ms

[local]:5432 pg12@testdb=# create table t_fillfactor_50(id int,c1 varchar(30),c2 varchar(30),c3 varchar(30)) with (fillfactor=50);
CREATE TABLE
Time: 28.553 ms

[local]:5432 pg12@testdb=# insert into t_fillfactor_100(id,c1,c2,c3) select x,'c1'||x,'c2'||x,'c3'||x from generate_series(1,1000000) as x;
INSERT 0 1000000
Time: 3583.216 ms (00:03.583)

[local]:5432 pg12@testdb=# insert into t_fillfactor_70(id,c1,c2,c3) select x,'c1'||x,'c2'||x,'c3'||x from generate_series(1,1000000) as x;
INSERT 0 1000000
Time: 6506.113 ms (00:06.506)

[local]:5432 pg12@testdb=# insert into t_fillfactor_50(id,c1,c2,c3) select x,'c1'||x,'c2'||x,'c3'||x from generate_series(1,1000000) as x;
INSERT 0 1000000
Time: 3113.901 ms (00:03.114)

[local]:5432 pg12@testdb=# update t_fillfactor_100 set c1=lpad('c1',30,'c1'),c2=lpad('c2',30,'c2'),c3=lpad('c3',30,'c3');
UPDATE 1000000
Time: 10641.794 ms (00:10.642)

[local]:5432 pg12@testdb=# update t_fillfactor_70 set c1=lpad('c1',30,'c1'),c2=lpad('c2',30,'c2'),c3=lpad('c3',30,'c3');
UPDATE 1000000
Time: 8563.046 ms (00:08.563)

[local]:5432 pg12@testdb=# update t_fillfactor_50 set c1=lpad('c1',30,'c1'),c2=lpad('c2',30,'c2'),c3=lpad('c3',30,'c3');
UPDATE 1000000
Time: 4036.735 ms (00:04.037)
```
可以看到,在插入時,fillfactor較高的資料表耗時較短,而在update時(全量),fillfactor的則有較大的優勢.但,經過多次update後,耗時並不明顯,原因在於經過多次update,每個塊的空閒空間跟fillfactor=100的設定已相差無幾.
```
[local]:5432 pg12@testdb=# update t_fillfactor_100 set c1=lpad('c1',30,'c1'),c2=lpad('c2',30,'c2'),c3=lpad('c3',30,'c3');
UPDATE 1000000
Time: 4276.404 ms (00:04.276)

[local]:5432 pg12@testdb=# update t_fillfactor_70 set c1=lpad('c1',30,'c1'),c2=lpad('c2',30,'c2'),c3=lpad('c3',30,'c3');
UPDATE 1000000
Time: 3856.575 ms (00:03.857)

[local]:5432 pg12@testdb=# update t_fillfactor_50 set c1=lpad('c1',30,'c1'),c2=lpad('c2',30,'c2'),c3=lpad('c3',30,'c3');
UPDATE 1000000
Time: 4364.962 ms (00:04.365)
[local]:5432 pg12@testdb=# 
```
重新建立表,使用pgbench進行測試
```
[local]:5432 pg12@testdb=# drop table if exists t_fillfactor_100;
t,c1 varchar(30),c2 varchar(30),c3 varchar(30)) with (fillfactor=70);
create table t_fillfactor_50(id int,c1 varchar(30),c2 varchar(30),c3 varchar(30)) with (fillfactor=50);

insert into t_fillfactor_100(id,c1,c2,c3) select x,'c1'||x,'c2'||x,'c3'||x from generate_series(1,1000000) as x;
insert into t_fillfactor_70(id,c1,c2,c3) select x,'c1'||x,'c2'||x,'c3'||x from generate_series(1,1000000) as x;
insert into t_fillfactor_50(id,c1,c2,c3) select x,'c1'||x,'c2'||x,'c3'||x from generate_series(1,1000000) as x;
DROP TABLE
Time: 191.706 ms
[local]:5432 pg12@testdb=# drop table if exists t_fillfactor_70;
DROP TABLE
Time: 35.313 ms
[local]:5432 pg12@testdb=# drop table if exists t_fillfactor_50;
DROP TABLE
Time: 30.078 ms
[local]:5432 pg12@testdb=# 
[local]:5432 pg12@testdb=# create table t_fillfactor_100(id int,c1 varchar(30),c2 varchar(30),c3 varchar(30)) with (fillfactor=100);
CREATE TABLE
Time: 40.443 ms
[local]:5432 pg12@testdb=# create table t_fillfactor_70(id int,c1 varchar(30),c2 varchar(30),c3 varchar(30)) with (fillfactor=70);
CREATE TABLE
Time: 1.334 ms
[local]:5432 pg12@testdb=# create table t_fillfactor_50(id int,c1 varchar(30),c2 varchar(30),c3 varchar(30)) with (fillfactor=50);
CREATE TABLE
Time: 1.024 ms
[local]:5432 pg12@testdb=# 
[local]:5432 pg12@testdb=# insert into t_fillfactor_100(id,c1,c2,c3) select x,'c1'||x,'c2'||x,'c3'||x from generate_series(1,1000000) as x;
INSERT 0 1000000
Time: 2623.943 ms (00:02.624)
[local]:5432 pg12@testdb=# insert into t_fillfactor_70(id,c1,c2,c3) select x,'c1'||x,'c2'||x,'c3'||x from generate_series(1,1000000) as x;
INSERT 0 1000000
Time: 2543.045 ms (00:02.543)
[local]:5432 pg12@testdb=# insert into t_fillfactor_50(id,c1,c2,c3) select x,'c1'||x,'c2'||x,'c3'||x from generate_series(1,1000000) as x;
INSERT 0 1000000
Time: 2662.223 ms (00:02.662)
[local]:5432 pg12@testdb=# 
```
使用pgbench進行測試
```
[pg12@localhost script]$ cat update_100.sql 
\set id random(1,1000000)
begin;
update t_fillfactor_100 set c1=lpad('c1',30,'c1'),c2=lpad('c2',30,'c2'),c3=lpad('c3',30,'c3') where id= :id;
end;

[pg12@localhost script]$ cat update_70.sql 
\set id random(1,1000000)
begin;
update t_fillfactor_70 set c1=lpad('c1',30,'c1'),c2=lpad('c2',30,'c2'),c3=lpad('c3',30,'c3') where id= :id;
end;

[pg12@localhost script]$ cat update_50.sql 
\set id random(1,1000000)
begin;
update t_fillfactor_50 set c1=lpad('c1',30,'c1'),c2=lpad('c2',30,'c2'),c3=lpad('c3',30,'c3') where id= :id;
end;

[pg12@localhost script]$ pgbench -c 2 -C -f ~/script/update_100.sql -j 1 -n -T 60 -U pg12 testdb
transaction type: /home/pg12/script/update_100.sql
scaling factor: 1
query mode: simple
number of clients: 2
number of threads: 1
duration: 60 s
number of transactions actually processed: 691
latency average = 174.136 ms
tps = 11.485277 (including connections establishing)
tps = 11.625959 (excluding connections establishing)

[pg12@localhost script]$ pgbench -c 2 -C -f ~/script/update_70.sql -j 1 -n -T 60 -U pg12 testdb
transaction type: /home/pg12/script/update_70.sql
scaling factor: 1
query mode: simple
number of clients: 2
number of threads: 1
duration: 60 s
number of transactions actually processed: 652
latency average = 184.293 ms
tps = 10.852275 (including connections establishing)
tps = 10.981136 (excluding connections establishing)

[pg12@localhost script]$ pgbench -c 2 -C -f ~/script/update_50.sql -j 1 -n -T 60 -U pg12 testdb
transaction type: /home/pg12/script/update_50.sql
scaling factor: 1
query mode: simple
number of clients: 2
number of threads: 1
duration: 60 s
number of transactions actually processed: 627
latency average = 191.700 ms
tps = 10.432967 (including connections establishing)
tps = 10.551899 (excluding connections establishing)
[pg12@localhost script]$ 
```
使用pgbench使用隨機值進行測試,時長為60s,結果差別不大.

資料表大小的比較：
```
[local]:5432 pg12@testdb=# select pg_size_pretty(pg_relation_size('t_fillfactor_100'));
 pg_size_pretty 
----------------
 58 MB
(1 row)

Time: 2.034 ms
[local]:5432 pg12@testdb=# select pg_size_pretty(pg_relation_size('t_fillfactor_70'));
 pg_size_pretty 
----------------
 82 MB
(1 row)

Time: 1.469 ms
[local]:5432 pg12@testdb=# select pg_size_pretty(pg_relation_size('t_fillfactor_50'));
 pg_size_pretty 
----------------
 117 MB
(1 row)

Time: 2.531 ms
[local]:5432 pg12@testdb=# 
```
分別是58MB vs 82MB vs 117MB ≈ 100 vs 70 vs 50

**參考資料**
[difference-between-table-fillfactor-and-index-fillfactor](https://dba.stackexchange.com/questions/15306/difference-between-table-fillfactor-and-index-fillfactor/15307
)

PostgreSQL DBA(86) - Table Parameter(fillfactor)

相關文章