Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to understand the parameter fillfactor when PostgreSQL creates a data table". The content of the explanation in the article is simple and clear, and it is easy to learn and understand. below, please follow the editor's train of thought slowly and deeply, to study and learn "how to understand the parameter fillfactor when PostgreSQL creates a data table".

Let's create data tables for different fillfactor and perform the update operation

[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' | | XMagne C2' | | XMagne C3' | | x from generate_series (1m 1000000) as x INSERT 0 1000000Time: 3583.216 ms (00ms 03.583) [local]: 5432 pg12@testdb=# insert into t_fillfactor_70 (id,c1,c2,c3) select x as c1' | | x from generate_series (1m 1000000) as x INSERT 0 1000000Time: 6506.113 ms (00ms 06.506) [local]: 5432 pg12@testdb=# insert into t_fillfactor_50 (id,c1,c2,c3) select x as c1' | | x from generate_series (1Magne 1000000) as x INSERT 0 1000000Time: 3113.901 ms (00ms 03.114) [local]: 5432 pg12@testdb=# update t_fillfactor_100 set c1=lpad ('c1'), c2=lpad ('c2'), c3=lpad ('c3') UPDATE 1000000Time: 10641.794 ms (00local 10.642) [local]: 5432 pg12@testdb=# update t_fillfactor_70 set c1=lpad ('c1'), c2=lpad ('c2'), c3=lpad ('c3') UPDATE 1000000Time: 8563.046 ms (08.563) [local]: 5432 pg12@testdb=# update t_fillfactor_50 set c1=lpad ('c1'), c2=lpad ('c2'), c3=lpad ('c3)); UPDATE 1000000Time: 4036.735 ms (04.037)

It can be seen that when inserting, the data table with higher fillfactor takes less time, while at update (full volume), fillfactor has a greater advantage. However, after many times of update, the time is not obvious, because after many times of update, the free space of each block is almost the same as the setting of fillfactor=100.

[local]: 5432 pg12@testdb=# update t_fillfactor_100 set c1=lpad, c2=lpad, c3=lpad, UPDATE 1000000Time: 4276.404 ms, [local]: 5432 pg12@testdb=# update t_fillfactor_70 set c1=lpad, c2=lpad, c3=lpad. UPDATE 1000000Time: 3856.575 ms (03.857) [local]: 5432 pg12@testdb=# update t_fillfactor_50 set c1=lpad [local]: 5432 pg12@testdb=# [local]: 5432 pg12@testdb=# update t_fillfactor_50 set c1=lpad [local]: 5432 pg12@testdb=#

Recreate the table and test with pgbench

[local]: 5432 pg12@testdb=# drop table if exists, 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 varchar c1' | | XQuery c2' | xQuery c3' | x from generate_series (1Med 1000000) as x Insert into t_fillfactor_70 (id,c1,c2,c3) select xmeme c2' | | xmeme c3' | | x from generate_series (1meme 1000000) as xtincinsert into t_fillfactor_50 (id,c1,c2,c3) select xmeme c1' | | x from generate_series (1meme 1000000) as xtrap drop TABLETime: 191.706 ms [local]: 5432 pg12@testdb=# drop table if exists t_fillfactor_70 | | x recollection c2' | | x recorder c3' | | DROP TABLETime: 35.313 ms [local]: 5432 pg12@testdb=# drop table if exists tweak 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 journal c1' | | x from generate_series (1pm 1000000) as x INSERT 0 1000000Time: 2623.943 ms (00ms 02.624) [local]: 5432 pg12@testdb=# insert into t_fillfactor_70 (id,c1,c2,c3) select x as c1' | | x from generate_series (1Magne 1000000) as x INSERT 0 1000000Time: 2543.045 ms (00ms 02.543) [local]: 5432 pg12@testdb=# insert into t_fillfactor_50 (id,c1,c2,c3) select x local c1' | | x from generate_series (1m 1000000) as x insert 0 1000000Time: 2662.223 ms (00Vera 02.662) [local]: 5432 pg12@testdb=#

Use pgbench for testing

[pg12@localhost script] $cat update_100.sql\ set id random (1jie 1000000) begin;update t_fillfactor_100 set c1=lpad ('c1pl'), c2=lpad ('c2pl'), c3=lpad ('c3') where id=: id;end; [pg12@localhost script] $cat update_70.sql\ set id random (1JE1000000) begin Update t_fillfactor_70 set c1=lpad, c2=lpad, c3=lpad, where id=: id;end; [pg12@localhost script] $cat update_50.sql\ set id random (1m 1000000) begin;update t_fillfactor_50 set c1=lpad, c2=lpad, c2=lpad, c3=lpad, 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: 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] $

Use pgbench to test with random values for 60 seconds, with little difference.

Comparison of data table size:

[local]: 5432 pg12@testdb=# select pg_size_pretty (pg_relation_size ('taper fillfactor 100')); pg_size_pretty-58 MB (1 row) Time: 2.034 ms [local]: 5432 pg12@testdb=# select pg_size_pretty (pg_relation_size (' taper fillfactor 70')) Pg_size_pretty-82 MB (1 row) Time: 1.469 ms [local]: 5432 pg12@testdb=# select pg_size_pretty (pg_relation_size (1 row) Time: 2.531 ms [local]: 5432 pg12@testdb=#

58MB vs 82MB vs 117MB ≈ 100 vs 70 vs 50

Thank you for your reading, the above is the content of "how to understand the parameter fillfactor when PostgreSQL creates a data table". After the study of this article, I believe you have a deeper understanding of how to understand the parameter fillfactor when PostgreSQL creates a data table, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.