postgres postgresql bloat vacuum

Vacuum

Quando comandos DML são executados, o MVCC faz com que versões novas das tuplas sejam criadas na tabela. Com o tempo, as transações atuais são finalizadas e algumas das versões de tuplas se tornam permanentemente inacessíveis para todas as transações atuais e futuras, chamadas de tuplas mortas. Ainda assim, essas versões ocupam espaço na tabela e esse espaço precisa ser recuperado. A relação entre espaço ocupado por tuplas mortas e espaço total é conhecido como inchaço (bloat, em inglês).

Todo espaço nos blocos passa por um ciclo de vida, nascendo na alocação do bloco quando a tabela está cheia, seguido pelo uso do espaço livre quando novas versões de tuplas são criadas e, com o tempo, versões de tuplas morrem. O último estágio desse ciclo é a reciclagem das tuplas mortas, liberando o espaço ocupado por elas em espaço livre, disponível para novos usos. Essa reciclagem é chamada de vacuum. 

alocação
de blocos ┌────────┐
      ╰┈┈>│ espaço │
    ╭┈┈┈┈>│ livre  │>┈╮
    ┆     └────────┘  ┆
    ┆vacuum           ┆ DML
    ┆                 ┆
 ┏━━━━━━━┓       ╔═══════╗
 ┃ tupla ┃  DML  ║ tupla ║
 ┃ morta ┃<┈┈┈┈┈<║ viva  ║
 ┗━━━━━━━┛       ╚═══════╝

O vacuum é uma operação sobre objetos inteiros, como tabelas e índices. Ele consiste em encontrar o identificador de transação mais antigo dentre as conexões atualmente ativas e caminhar pelo objeto marcando as tuplas inalcançáveis como espaço vazio. Portanto, é uma atividade vital para evitar o inchaço dos objetos e para manter a saúde do banco de dados.

Aproveitando o fato de que ele precisa caminhar periodicamente pelos objetos inteiros, o vacuum também se encarrega de outras atividades que fariam a mesma caminhada. A lista completa de atividades desempenhadas pelo vacuum é:

  1. Reciclagem de espaço ocupado por tuplas mortas.
  2. Atualização de estatísticas das tabelas.
  3. Atualização do mapa de visibilidade.
  4. Proteção contra wraparound de identificadores de transação.

O mapa de visibilidade é um mapa de bits indicando se cada página contém apenas tuplas visíveis para todas as transações atualmente ativas. Isso permite que o próprio vacuum economize retrabalho futuro e também permite consultas apenas sobre índices (index-only scans).

Identificadores de transação (xid) são números de 32 bits e, portanto, são consumidos após 4 Bi transações. Para evitar problemas quando o número retorna ao início é necessário resetar ele periodicamente e ajustar os valores em xmin das tuplas que são visíveis para todas as transações ativas.

A execução do vacuum pode ser acionada pelo comando VACUUM para um banco de dados inteiro: 

[[local]:5432] postgres@postgres=# VACUUM;

Ou para uma tabela específica: 

[[local]:5432] postgres@postgres=# VACUUM tabela;

A variante VACUUM ANALYZE ou VACUUM (ANALYZE) pode ser usada para fazer tanto a reciclagem de tuplas mortas quanto a atualização das estatísticas: 

[[local]:5432] postgres@postgres=# VACUUM ANALYZE tabela;

Também é possível executar apenas a atualização das estatísticas: 

[[local]:5432] postgres@postgres=# ANALYZE tabela;

A variante VACUUM VERBOSE ou VACUUM (VERBOSE) pode ser usada para mostrar mais detalhes da execução do vacuum: 

[[local]:5432] postgres@postgres=# VACUUM VERBOSE tabela;
INFO:  vacuuming "public.tabela"
INFO:  "tabela": removed 8000 row versions in 108 pages
INFO:  "tabela": found 8000 removable, 8000 nonremovable row versions in 108 out of 108 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 1213111
There were 4000 unused item identifiers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.

Autovacuum

Em função da importância das tarefas desempenhadas pelo vacuum, existe também um processo interno do PostgreSQL encarregado de detectar tabelas que precisem dessa atividade e de acionar o VACUUM, possivelmente com ANALYZE, nelas. O processo é o autovacuum launcher (lançador automático de vacuum) que vemos acompanhado do serviço.

Ele usa as estatísticas que vemos em pg_stat_{all,sys,user}_tables e as configurações da categoria "Autovacuum".

Um VACUUM vai ser acionado para cada tabela que tiver sofrido alteração em uma quantidade mínima de tuplas desde a sua última execução. O parâmetro autovacuum_vacuum_scale_factor é a proporção da tabela que deve ter sido alterada (0.1, ou 10%, é o valor padrão) e o parâmetro autovacuum_vacuum_threshold é o número mínimo de tuplas (50 é o padrão).

Da mesma forma, um ANALYZE vai ser acionado para cada tabela que tiver sofrido alterações medidas da mesma forma, mas comparadas com os parâmetros autovacuum_analyze_scale_factor (0.2, ou 20%, é o valor padrão) e autovacuum_analyze_threshold (50 é o valor padrão).

Esses parâmetros podem ser configurados por tabela com ALTER TABLE, para tornar o vacuum mais agressivo nelas, o que é necessário em tabelas que sofrem muitas alterações, fazendo o ciclo de vida das tuplas girar uniformemente e não acumular indevidamente espaço livre ou tuplas mortas.

VACUUM FULL

O vacuum não retorna o espaço das tuplas mortas de volta ao sistema operacional. Isso exigiria uma compactação do objeto, operação muito mais onerosa por ter que ajustar ponteiros de outros objetos (índices apontam para tabelas) e manter locks mais estritos. Adicionalmente, a posterior alocação de blocos novos nas escritas é muito mais custosa que o uso de espaço disponível dentro do objeto. Portanto, o ideal é que todo objeto cujos dados sofrem alterações tenha algum espaço livre.

Contudo, em alguns casos pode ser necessário fazer essa compactação dos objetos, como quando uma tabela será tornada histórica e não receberá mais alterações, quando descobrimos que o autovacuum não pôde executar e o objeto se tornou quase completamente inchaço. O comando VACUUM FULL pode ser usado nesses casos.

Ele obtém um lock exclusivo no objeto, cria um novo objeto em disco, copia as tuplas vivas, apaga o objeto anterior e libera o lock. É uma operação bastante onerosa e com consequências negativas, então não deve ser usada cotidianamente.

Especificamente, o VACUUM FULL não toca no mapa de visibilidade ou nas estatísticas, então é fundamental executar um VACUUM ANALYZE logo em seguida. Ele também deixa o objeto sem espaço libre, então as novas escritas farão ele ser expandido sob demanda, o que torna elas mais lentas.

Por exemplo: 

[[local]:5432] postgres@postgres=# VACUUM FULL tabela;
[[local]:5432] postgres@postgres=# VACUUM ANALYZE tabela;

CLUSTER

O comando CLUSTER é similar ao VACUUM FULL no sentido de que ambos criam objetos novos com as tuplas vivas. Mas o CLUSTER também reordena as tuplas da tabela para seguirem a mesma ordem de um índice. Da mesma forma, é necessário executar um VACUUM ANALYZE na tabela logo em seguida.

Por exemplo: 

[[local]:5432] postgres@postgres=# CLUSTER tabela;
[[local]:5432] postgres@postgres=# VACUUM ANALYZE tabela;

Prática

  1. Crie uma tabela com dados artificiais: 
    [[local]:5432] postgres@postgres=# CREATE TABLE tabela AS
[[local]:5432] postgres@postgres-# SELECT id, 'número ' || id AS descricao
[[local]:5432] postgres@postgres-# FROM generate_series(1, 10000) AS id;
  2. Atualize as estatísticas da tabela e observe os campos n_live_tup, n_dead_tup, last_*analyze e last_*vacuum 
    [[local]:5432] postgres@postgres=# ANALYZE tabela;
[[local]:5432] postgres@postgres=# SELECT * FROM pg_stat_user_tables WHERE relname = 'tabela';
  3. Remova parte da tabela e observe as estatísticas novamente: 
    [[local]:5432] postgres@postgres=# DELETE FROM tabela WHERE id % 3 = 1;
[[local]:5432] postgres@postgres=# SELECT * FROM pg_stat_user_tables WHERE relname = 'tabela';
  4. Execute um VACUUM ANALYZE e observe novamente as estatísticas da tabela: 
    [[local]:5432] postgres@postgres=# VACUUM ANALYZE tabela;
[[local]:5432] postgres@postgres=# SELECT * FROM pg_stat_user_tables WHERE relname = 'tabela';