Medir e resolver o aumento de tabelas e índices

O aumento excessivo de tabelas e índices ocorre quando a operação de limpeza não acompanha a taxa de linguagem de manipulação de dados (DML) em uma tabela. Algum aumento de tabelas e índices é inevitável e geralmente aceitável porque as transações podem reutilizar esse espaço. O bloat grave causa problemas de desempenho, como tempos de consulta mais lentos, aumento de E/S, uso menos eficiente de buffer e cache compartilhados e aumento dos custos de armazenamento.

A melhor maneira de estimar o aumento é usar o módulo pgstattuple. No entanto, usar pgstattuple é menos eficiente quando a extensão precisa ler todas as páginas de uma relação. Se não for possível usar o pgstattuple, use outros scripts para estimar o aumento, embora essa abordagem seja menos precisa.

Ler a saída do pgstattuple

Quando você usa pgstattuple para informar as características de armazenamento físico de uma tabela ou índice, a saída tem o seguinte formato:

> SELECT * FROM pgstattuple('large_table');
-[ RECORD 1 ]------+-----------
table_len          | 7431888896
tuple_count        | 66666666
tuple_len          | 2796296271
tuple_percent      | 37.63
dead_tuple_count   | 0
dead_tuple_len     | 0
dead_tuple_percent | 0
free_space         | 3873153652
free_percent       | 52.12

A seguir, descrevemos a saída:

table_len (7431888896)

O tamanho total da tabela em bytes. Ele inclui todas as tuplas ativas, tuplas inativas e espaço livre. Nesse caso, large_table ocupa aproximadamente 7.088 GB.

tuple_count (66666666)

Representa o número de tuplas ativas (linhas visíveis e ativas) na tabela. A tabela contém aproximadamente 66 milhões de linhas ativas.

tuple_len (2796296271)

O comprimento total em bytes de todas as tuplas ativas combinadas, que é de aproximadamente 2,6 GB.

tuple_percent (37.63)

A porcentagem do table_len ocupada por tuplas ativas. O cálculo é feito da seguinte forma:

(tuple_len/table_len) ∗ 100 = (2796296271/7431888896) ∗ 100 ≈ 37.63%

Esse resultado indica que os dados ativos usam menos de 40% do espaço em disco da tabela.

dead_tuple_count (0)

O número de tuplas mortas (linhas que as operações de limpeza ainda não removeram após atualizações ou exclusões). Um valor de 0 significa que a tabela está limpa e não tem tuplas inativas aguardando recuperação. Isso geralmente sugere que VACUUM ou autovacuum é executado com eficiência na tabela.

dead_tuple_len (0)

O comprimento total em bytes de todas as tuplas mortas. De acordo com dead_tuple_count, um valor de 0 significa que as linhas inativas não consomem espaço.

dead_tuple_percent (0)

A porcentagem do table_len ocupada por tuplas inativas. O cálculo é feito da seguinte forma:

(dead_tuple_len/table_len) ∗ 100 = (0/7431888896) ∗ 100 = 0%

free_space (3873153652)

Esse valor representa a quantidade de espaço livre nas páginas de dados da tabela. É possível reutilizar esse espaço para novas inserções ou atualizações sem precisar expandir a tabela. Isso indica uma quantidade significativa de espaço livre, aproximadamente 3,7 GB.

free_percent (52.12)

Esse valor representa a porcentagem do table_len que é espaço livre. O cálculo é feito da seguinte forma:

(free_space/table_len) ∗ 100 = (3873153652/7431888896) ∗ 100 ≈ 52.12%

Estimar o aumento usando consultas

É possível estimar o aumento usando consultas em tabelas do catálogo do sistema, mas esse método é menos preciso do que usar extensões como pgstattuple. Se você usar scripts como Verificação de bloat da tabela do PostgreSQL, as estatísticas da tabela precisam estar atualizadas. Caso contrário, a estimativa será imprecisa.

Ao comparar a saída de uma consulta com o módulo pgstattuple, as estimativas são comparáveis, mas não são medidas exatas de inflacionamento. O módulo ainda é a medição mais precisa de bloat porque a consulta SQL depende do mecanismo de estatísticas cumulativas do PostgreSQL, enquanto o módulo lê as páginas de dados reais.

O exemplo a seguir compara a saída de dois métodos diferentes para analisar o aumento de tabelas no PostgreSQL para uma tabela chamada large_table.

> SELECT * FROM pgstattuple('large_table');
-[ RECORD 1 ]------+-----------
table_len          | 2475892736
tuple_count        | 22000000
tuple_len          | 922925931
tuple_percent      | 37.28
dead_tuple_count   | 0
dead_tuple_len     | 0
dead_tuple_percent | 0
free_space         | 1293696900
free_percent       | 52.25

Vs.

-[ RECORD 1 ]+------------
databasename | postgres
schemaname   | public
tablename    | large_table
can_estimate | t
est_rows     | 21965400
pct_bloat    | 54
mb_bloat     | 1269.24
table_mb     | 2361.195

Excesso de endereços

É possível gerenciar o aumento de tamanho das seguintes maneiras, cada uma com vantagens e desvantagens que exigem um planejamento cuidadoso:

• Faça uma limpeza completa. Essa operação requer um bloqueio completo da tabela durante a duração do vácuo e exige tempo de inatividade.
• Mova os dados para uma nova tabela. Para usar esse método, é necessário interromper as mudanças na tabela para garantir que elas não sejam transferidas para a nova relação.
• Use pg_squeeze. Essa extensão reduz o excesso de informações on-line. A extensão cria uma tabela de sombra, insere todos os dados e mantém as alterações da tabela usando um slot de replicação até a última etapa, que é a troca da tabela inchada pela tabela não inchada recém-criada. Em seguida, ele solicita um bloqueio breve da tabela para mover a tabela não inchada e substituir a tabela inchada. Esse método exige o dobro do espaço do objeto durante a operação, mas é o menos intrusivo para a operação normal.