怎么理解PostgreSQL创建数据表时的参数fillfactor

这篇文章主要讲解了"怎么理解PostgreSQL创建数据表时的参数fillfactor"，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习"怎么理解PostgreSQL创建数据表时的参数fillfactor"吧！

下面创建不同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 TABLETime: 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 TABLETime: 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 TABLETime: 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 1000000Time: 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 1000000Time: 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 1000000Time: 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 1000000Time: 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 1000000Time: 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 1000000Time: 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 1000000Time: 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 1000000Time: 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 1000000Time: 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 TABLETime: 191.706 ms[local]:5432 pg12@testdb=# drop table if exists t_fillfactor_70;DROP TABLETime: 35.313 ms[local]:5432 pg12@testdb=# drop table if exists t_fillfactor_50;DROP TABLETime: 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 TABLETime: 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 TABLETime: 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 TABLETime: 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 1000000Time: 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 1000000Time: 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 1000000Time: 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 testdbtransaction type: /home/pg12/script/update_100.sqlscaling factor: 1query mode: simplenumber of clients: 2number of threads: 1duration: 60 snumber of transactions actually processed: 691latency average = 174.136 mstps = 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 testdbtransaction type: /home/pg12/script/update_70.sqlscaling factor: 1query mode: simplenumber of clients: 2number of threads: 1duration: 60 snumber of transactions actually processed: 652latency average = 184.293 mstps = 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 testdbtransaction type: /home/pg12/script/update_50.sqlscaling factor: 1query mode: simplenumber of clients: 2number of threads: 1duration: 60 snumber of transactions actually processed: 627latency average = 191.700 mstps = 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

感谢各位的阅读，以上就是"怎么理解PostgreSQL创建数据表时的参数fillfactor"的内容了，经过本文的学习后，相信大家对怎么理解PostgreSQL创建数据表时的参数fillfactor这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是，小编将为大家推送更多相关知识点的文章，欢迎关注！