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.
