fillfactor値によって全件更新した場合のページ数がどうなるかを確認してみた

はじめに

fillfactorはデータブロック中にどの程度の空き容量を確保するかを指定するためのパラメータです。
テーブルの場合はデフォルトで100で空き無しになります。インデックスの場合はデフォルトで90に設定されます。

今回はfillfactorの値を100, 80, 40と変えて、全件更新した場合のページ数がどのように変化するかを確認してみました。

fillfactor値によって全件更新した場合のページ数がどうなるかを確認してみた

まず準備としてfillfactorを100, 80, 40に設定した、テーブルtest_ff100、test_ff80、test_ff40を作成し、1000件のデータを挿入します。
※インデックスをつけていますが、今回は関係ないです。

また、Autovacuumが発生しないようにしています。

create table test_ff100 (
  id numeric,
  txt varchar(10)
) with (fillfactor = 100 );
alter table test_ff100 set (autovacuum_enabled = false);
※autovacuum_enabledの変更はcreate table文中でもできます。

insert into test_ff100(id, txt) select i, '0123456789' from generate_series(1, 1000) as i;
create unique index idx_test_ff100 on test_ff100(id) with (fillfactor = 100);

create table test_ff80 (
  id numeric,
  txt varchar(10)
) with (fillfactor = 80 );
alter table test_ff80 set (autovacuum_enabled = false);

insert into test_ff80(id, txt) select i, '0123456789' from generate_series(1, 1000) as i;
create unique index idx_test_ff80 on test_ff80(id) with (fillfactor = 80);

create table test_ff40 (
  id numeric,
  txt varchar(10)
) with (fillfactor = 40 );
alter table test_ff40 set (autovacuum_enabled = false);

insert into test_ff40(id, txt) select i, '0123456789' from generate_series(1, 1000) as i;
create unique index idx_test_ff40 on test_ff40(id) with (fillfactor = 40);

この時点のページ数は以下のようになります。
test_ff100が6ページに対して、test_ff40は14ページと空き領域が大きいため格納効率が悪くなっています。

select relname, relpages from pg_class where relname in ('test_ff40', 'test_ff80', 'test_ff100', 'idx_test_ff40', 'idx_test_ff80', 'idx_test_ff100') order by 1;
    relname     | relpages 
----------------+----------
 idx_test_ff100 |        5
 idx_test_ff40  |        9
 idx_test_ff80  |        6
 test_ff100     |        6
 test_ff40      |       14
 test_ff80      |        7
(6 rows)

ここから、以下の全件更新するUPDATE文を実行して、ページ数の推移を確認してみます。

update test_ff100 set txt = '0123456789';
update test_ff80 set txt = '0123456789';
update test_ff40 set txt = '0123456789';

analyze test_ff100;
analyze test_ff80;
analyze test_ff40;

3回更新した場合のページ数は以下のようになります。

■ 更新1回目
    relname     | relpages 
----------------+----------
 idx_test_ff100 |        9
 idx_test_ff40  |        9
 idx_test_ff80  |        9
 test_ff100     |       11
 test_ff40      |       14
 test_ff80      |       12
(6 rows)

■ 更新2回目
    relname     | relpages 
----------------+----------
 idx_test_ff100 |        9
 idx_test_ff40  |        9
 idx_test_ff80  |        9
 test_ff100     |       16
 test_ff40      |       14
 test_ff80      |       16
(6 rows)

■ 更新3回目
    relname     | relpages 
----------------+----------
 idx_test_ff100 |        9
 idx_test_ff40  |        9
 idx_test_ff80  |        9
 test_ff100     |       21
 test_ff40      |       14
 test_ff80      |       18
(6 rows)

test_ff40は最初に14ページでしたが、3回更新しても14ページで変わりありません。
一方、test_ff100は最初は6ページと最小でしたが、更新によって11, 16, 21と徐々にページ数が増えています。test_ff80はtest_ff40とtest_ff100の中間といったところです。
Autovacuumを有効にしていれば、test_ff100でもこれほどページ数は増加しなかったはずですが、Autovacuum文の負荷がかかりますので、やはりfillfactorは調整しておいたほうが良いです。

終わりに

実際にはこのような極端なケースは少ないと思いますが、fillfactorの設定によって使用されるサイズが大きく変わることに注意して設計しなければならないことが分かります。