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

下面创建不同fillfactor的数据表,执行update操作

[local]:5432pg12@testdb=#createtablet_fillfactor_100(idint,c1varchar(30),c2varchar(30),c3varchar(30))with(fillfactor=100);CREATETABLETime:2.462ms[local]:5432pg12@testdb=#createtablet_fillfactor_70(idint,c1varchar(30),c2varchar(30),c3varchar(30))with(fillfactor=70);CREATETABLETime:3.437ms[local]:5432pg12@testdb=#createtablet_fillfactor_50(idint,c1varchar(30),c2varchar(30),c3varchar(30))with(fillfactor=50);CREATETABLETime:28.553ms[local]:5432pg12@testdb=#insertintot_fillfactor_100(id,c1,c2,c3)selectx,'c1'||x,'c2'||x,'c3'||xfromgenerate_series(1,1000000)asx;INSERT01000000Time:3583.216ms(00:03.583)[local]:5432pg12@testdb=#insertintot_fillfactor_70(id,c1,c2,c3)selectx,'c1'||x,'c2'||x,'c3'||xfromgenerate_series(1,1000000)asx;INSERT01000000Time:6506.113ms(00:06.506)[local]:5432pg12@testdb=#insertintot_fillfactor_50(id,c1,c2,c3)selectx,'c1'||x,'c2'||x,'c3'||xfromgenerate_series(1,1000000)asx;INSERT01000000Time:3113.901ms(00:03.114)[local]:5432pg12@testdb=#updatet_fillfactor_100setc1=lpad('c1',30,'c1'),c2=lpad('c2',30,'c2'),c3=lpad('c3',30,'c3');UPDATE1000000Time:10641.794ms(00:10.642)[local]:5432pg12@testdb=#updatet_fillfactor_70setc1=lpad('c1',30,'c1'),c2=lpad('c2',30,'c2'),c3=lpad('c3',30,'c3');UPDATE1000000Time:8563.046ms(00:08.563)[local]:5432pg12@testdb=#updatet_fillfactor_50setc1=lpad('c1',30,'c1'),c2=lpad('c2',30,'c2'),c3=lpad('c3',30,'c3');UPDATE1000000Time:4036.735ms(00:04.037)

可以看到,在插入时,fillfactor较高的数据表耗时较短,而在update时(全量),fillfactor的则有较大的优势.但,经过多次update后,耗时并不明显,原因在于经过多次update,每个块的空闲空间跟fillfactor=100的设定已相差无几.

[local]:5432pg12@testdb=#updatet_fillfactor_100setc1=lpad('c1',30,'c1'),c2=lpad('c2',30,'c2'),c3=lpad('c3',30,'c3');UPDATE1000000Time:4276.404ms(00:04.276)[local]:5432pg12@testdb=#updatet_fillfactor_70setc1=lpad('c1',30,'c1'),c2=lpad('c2',30,'c2'),c3=lpad('c3',30,'c3');UPDATE1000000Time:3856.575ms(00:03.857)[local]:5432pg12@testdb=#updatet_fillfactor_50setc1=lpad('c1',30,'c1'),c2=lpad('c2',30,'c2'),c3=lpad('c3',30,'c3');UPDATE1000000Time:4364.962ms(00:04.365)[local]:5432pg12@testdb=#

重新创建表,使用pgbench进行测试

[local]:5432pg12@testdb=#droptableifexistst_fillfactor_100;t,c1varchar(30),c2varchar(30),c3varchar(30))with(fillfactor=70);createtablet_fillfactor_50(idint,c1varchar(30),c2varchar(30),c3varchar(30))with(fillfactor=50);insertintot_fillfactor_100(id,c1,c2,c3)selectx,'c1'||x,'c2'||x,'c3'||xfromgenerate_series(1,1000000)asx;insertintot_fillfactor_70(id,c1,c2,c3)selectx,'c1'||x,'c2'||x,'c3'||xfromgenerate_series(1,1000000)asx;insertintot_fillfactor_50(id,c1,c2,c3)selectx,'c1'||x,'c2'||x,'c3'||xfromgenerate_series(1,1000000)asx;DROPTABLETime:191.706ms[local]:5432pg12@testdb=#droptableifexistst_fillfactor_70;DROPTABLETime:35.313ms[local]:5432pg12@testdb=#droptableifexistst_fillfactor_50;DROPTABLETime:30.078ms[local]:5432pg12@testdb=#[local]:5432pg12@testdb=#createtablet_fillfactor_100(idint,c1varchar(30),c2varchar(30),c3varchar(30))with(fillfactor=100);CREATETABLETime:40.443ms[local]:5432pg12@testdb=#createtablet_fillfactor_70(idint,c1varchar(30),c2varchar(30),c3varchar(30))with(fillfactor=70);CREATETABLETime:1.334ms[local]:5432pg12@testdb=#createtablet_fillfactor_50(idint,c1varchar(30),c2varchar(30),c3varchar(30))with(fillfactor=50);CREATETABLETime:1.024ms[local]:5432pg12@testdb=#[local]:5432pg12@testdb=#insertintot_fillfactor_100(id,c1,c2,c3)selectx,'c1'||x,'c2'||x,'c3'||xfromgenerate_series(1,1000000)asx;INSERT01000000Time:2623.943ms(00:02.624)[local]:5432pg12@testdb=#insertintot_fillfactor_70(id,c1,c2,c3)selectx,'c1'||x,'c2'||x,'c3'||xfromgenerate_series(1,1000000)asx;INSERT01000000Time:2543.045ms(00:02.543)[local]:5432pg12@testdb=#insertintot_fillfactor_50(id,c1,c2,c3)selectx,'c1'||x,'c2'||x,'c3'||xfromgenerate_series(1,1000000)asx;INSERT01000000Time:2662.223ms(00:02.662)[local]:5432pg12@testdb=#

使用pgbench进行测试

[pg12@localhostscript]$catupdate_100.sql\setidrandom(1,1000000)begin;updatet_fillfactor_100setc1=lpad('c1',30,'c1'),c2=lpad('c2',30,'c2'),c3=lpad('c3',30,'c3')whereid=:id;end;[pg12@localhostscript]$catupdate_70.sql\setidrandom(1,1000000)begin;updatet_fillfactor_70setc1=lpad('c1',30,'c1'),c2=lpad('c2',30,'c2'),c3=lpad('c3',30,'c3')whereid=:id;end;[pg12@localhostscript]$catupdate_50.sql\setidrandom(1,1000000)begin;updatet_fillfactor_50setc1=lpad('c1',30,'c1'),c2=lpad('c2',30,'c2'),c3=lpad('c3',30,'c3')whereid=:id;end;[pg12@localhostscript]$pgbench-c2-C-f~/script/update_100.sql-j1-n-T60-Upg12testdbtransactiontype:/home/pg12/script/update_100.sqlscalingfactor:1querymode:simplenumberofclients:2numberofthreads:1duration:60snumberoftransactionsactuallyprocessed:691latencyaverage=174.136mstps=11.485277(includingconnectionsestablishing)tps=11.625959(excludingconnectionsestablishing)[pg12@localhostscript]$pgbench-c2-C-f~/script/update_70.sql-j1-n-T60-Upg12testdbtransactiontype:/home/pg12/script/update_70.sqlscalingfactor:1querymode:simplenumberofclients:2numberofthreads:1duration:60snumberoftransactionsactuallyprocessed:652latencyaverage=184.293mstps=10.852275(includingconnectionsestablishing)tps=10.981136(excludingconnectionsestablishing)[pg12@localhostscript]$pgbench-c2-C-f~/script/update_50.sql-j1-n-T60-Upg12testdbtransactiontype:/home/pg12/script/update_50.sqlscalingfactor:1querymode:simplenumberofclients:2numberofthreads:1duration:60snumberoftransactionsactuallyprocessed:627latencyaverage=191.700mstps=10.432967(includingconnectionsestablishing)tps=10.551899(excludingconnectionsestablishing)[pg12@localhostscript]$

使用pgbench使用随机值进行测试,时长为60s,结果差别不大.

数据表大小的比较：

[local]:5432pg12@testdb=#selectpg_size_pretty(pg_relation_size('t_fillfactor_100'));pg_size_pretty----------------58MB(1row)Time:2.034ms[local]:5432pg12@testdb=#selectpg_size_pretty(pg_relation_size('t_fillfactor_70'));pg_size_pretty----------------82MB(1row)Time:1.469ms[local]:5432pg12@testdb=#selectpg_size_pretty(pg_relation_size('t_fillfactor_50'));pg_size_pretty----------------117MB(1row)Time:2.531ms[local]:5432pg12@testdb=#

分别是58MB vs 82MB vs 117MB ≈ 100 vs 70 vs 50

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