PostgreSQL fillfactor

Precisei fazer uma prova simples para um cliente para mostrar como o parâmetro de storage FILLFACTOR afeta uma tabela e como verificar a sua eficiência. Toda vez que um UPDATE é executado no PostgreSQL, ele marca a tupla atualizada para remoção posterior e cria um novo registro. Porém, se no bloco onde o dado está gravado houver espaço sobrando, o PostgreSQL pode fazer a atualização dentro do próprio bloco, utilizando um recurso conhecido como “Heap Only Tuples”, ou HOT. Quando foi introduzido no PG 8.3, foi considerado um divisor de águas, aumentando muito a performance do banco e diminuindo o uso do Vacuum. Mas você pode dar uma ajuda para o HOT funcionar, deixando mais espaço livre para ele trabalhar em tabelas que sofrem muitas atualizações. Vejamos uma demonstração simples.

Primeiro vamos criar uma tabela nova e verificar as estatísticas e depois rodar um UPDATE:

tst=# CREATE TABLE abizi AS SELECT * FROM pg_class ;
SELECT 1009

tst=# SELECT n_tup_ins, n_tup_upd, n_tup_hot_upd, n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname = 'abizi';
 n_tup_ins | n_tup_upd | n_tup_hot_upd | n_live_tup | n_dead_tup
-----------+-----------+---------------+------------+------------
 1009      | 0         | 0             | 1009       | 0
(1 row)

tst=# UPDATE abizi SET relname = 'abizi';
UPDATE 1009

tst=# SELECT n_tup_ins, n_tup_upd, n_tup_hot_upd, n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname = 'abizi';
 n_tup_ins | n_tup_upd | n_tup_hot_upd | n_live_tup | n_dead_tup
-----------+-----------+---------------+------------+------------
 1009      | 1009      | 5             | 1009       | 1009
(1 row)

Note que após a atualização, os 1009 registros foram atualizados. Ao olhar o campo n_tup_hot_upd, ou seja número de tuplas que realizaram o HOT durante o UPDATE vemos que apenas 5 foram atualizados com o HOT.

Agora vamos criar novamente a tabela e antes de popular ela vamos ajustar o FILLFACTOR em 90:

tst=# DROP TABLE abizi;
DROP TABLE

tst=# CREATE TABLE abizi AS SELECT  * FROM pg_class WITH NO DATA;
SELECT 0
bbtmd=# ALTER TABLE abizi SET (fillfactor = 90);
ALTER TABLE

tst=# INSERT INTO abizi SELECT * FROM pg_class ;
INSERT 0 1012

tst=# UPDATE abizi SET relname = 'abizi';
UPDATE 1012

tst=# SELECT n_tup_ins, n_tup_upd, n_tup_hot_upd, n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname = 'abizi';
 n_tup_ins | n_tup_upd | n_tup_hot_upd | n_live_tup | n_dead_tup
-----------+-----------+---------------+------------+------------
      1012 |      1012 |           116 |       1012 |       1012

Note que agora o número de registros que usaram o HOT subiu para 116. Se fizermos a mesma coisa com um FILLFACTOR em 70:

tst=# DROP TABLE abizi;
DROP TABLE

tst=# CREATE TABLE abizi AS SELECT  * FROM pg_class WITH NO DATA;
SELECT 0

tst=# ALTER TABLE abizi SET (fillfactor = 70);
ALTER TABLE

tst=# INSERT INTO abizi SELECT * FROM pg_class ;
INSERT 0 1012

tst=# UPDATE abizi SET relname = 'abizi';
UPDATE 1012

tst=# SELECT n_tup_ins, n_tup_upd, n_tup_hot_upd, n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname = 'abizi';
 n_tup_ins | n_tup_upd | n_tup_hot_upd | n_live_tup | n_dead_tup
-----------+-----------+---------------+------------+------------
      1012 |      1012 |           449 |       1012 |       1012

Por fim com FILLFACTOR EM 50:

bbtmd=# DROP TABLE abizi;
DROP TABLE

tst=# CREATE TABLE abizi AS SELECT  * FROM pg_class WITH NO DATA;
SELECT 0

tst=# ALTER TABLE abizi SET (fillfactor = 50);
ALTER TABLE

tst=# INSERT INTO abizi SELECT * FROM pg_class ;
INSERT 0 1012

tst=# UPDATE abizi SET relname = 'abizi';
UPDATE 1012

tst=# SELECT n_tup_ins, n_tup_upd, n_tup_hot_upd, n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname = 'abizi';
 n_tup_ins | n_tup_upd | n_tup_hot_upd | n_live_tup | n_dead_tup
-----------+-----------+---------------+------------+------------
      1012 |      1012 |          1012 |       1012 |       1012
(1 row)

Note que aumentar o valor o FILLFACTOR significa também aumentar o volume de espaço em disco utilizado. No nosso exemplo, a tabela que com 100% de FILLFACTOR (o padrão) ocupava 208KB ocupou 424KB com 50%.

O negócio é acompanhar as estatísticas da sua base e verificar onde você tem tabelas com tamanho menor e alto volume de UPDATEs. Nestas tabelas, ajustar o FILLFACTOR, AUTOVACUUM e AUTOANALYZE é vital para um bom desempenho. Tabelas pequenas às vezes passam desapercebidas pelos olhos dos DBAs, mas podem ser grande fonte de dor de cabeça se forem utilizadas com frequência e não forem ajustadas de maneira correta.