É possível nunca executar o vácuo completo no PostgreSQL

Para entender as diferenças entre

• VACUUM
• VACUUM ANALYZE
• VACUUM FULL,

é necessário um conhecimento básico da arquitetura do PostgreSQL.

De vários9s ¹ nos é dito que:

• O PostgreSQL não usa o mecanismo de atualização IN-PLACE, portanto, de acordo com a maneira como o comando DELETE e UPDATE é projetado,

  • Sempre que operações DELETE são executadas, ele marca a tupla existente como DEAD em vez de remover fisicamente essas tuplas.
  • Da mesma forma, sempre que a operação UPDATE é executada, ela marca a tupla existente correspondente como DEAD e insere uma nova tupla (ou seja, operação UPDATE = DELETE + INSERT).

Isso leva ao que é chamado de "bloat" - ou seja, a tabela ficará maior, mesmo que o número de registros permaneça o mesmo por causa dessas tuplas mortas.

Do EnterpriseDB ² , temos:

• Quando um processo de vácuo é executado, o espaço ocupado por essas tuplas mortas é marcado como reutilizável por outras tuplas.

Isso é semelhante à exclusão de um arquivo no sistema de arquivos - o espaço é meramente marcado como reutilizável e nada é realmente excluído. Isso não importa - o importante a notar é que o espaço ocupado pela linha fica disponível novamente para o servidor PostgreSQL para aquela tabela!

A principal diferença entre VACUUMe VACUUM FULLé que com um VACUUM FULL, o espaço liberado também fica disponível para o sistema operacional! Isso pode ser útil se alguém desejar fazer um backup e estiver com pouco espaço em disco. A partir daqui :

• Vacuum full retira um bloqueio exclusivo e reconstrói a tabela para que não tenha blocos vazios (vamos fingir que o fator de preenchimento é 100% por enquanto).

Então, VACUUM FULLé análogo a desfragmentar um arquivo de disco (considere um arquivo de disco ≡ tabela PostgreSQL), do Percona ³ temos:

VACUUM FULL é a opção padrão disponível com uma instalação do PostgreSQL que nos permite RECONSTRUIR uma tabela. [ênfase minha]

No entanto, essa reconstrução exige um Access Exclusive Lock , que como o manual aponta:

• ACESSO EXCLUSIVO

  Conflitos com bloqueios de todos os modos (ACCESS SHARE, ROW SHARE, ROW EXCLUSIVE, SHARE UPDATE EXCLUSIVE, SHARE, SHARE ROW EXCLUSIVE, EXCLUSIVE e ACCESS EXCLUSIVE). Esta modalidade garante que o titular é a única transação que acessa a mesa de alguma forma . [ênfase minha]

Obviamente, e como você aponta, uma reescrita completa da tabela não é boa para a produção. O manual ainda diz:

• Normalmente, isso só deve ser usado quando uma quantidade significativa de espaço precisa ser recuperada de dentro da tabela.

Então, respondendo suas 2 perguntas:

P. 1)

o vácuo torna o espaço reutilizável para o novo inserto?

Sim, mas ainda mais importante , não é necessário um bloqueio de acesso exclusivo. A partir da documentação , descobrimos que:

Os trabalhadores do Autovacuum geralmente não bloqueiam outros comandos.

Autovacuum é um VACUUMsem o FULLe normalmente é configurado no postgresql.confarquivo.

VACUUMleva um bloqueio SHARE UPDATE EXCLUSIVE.

Novamente, do manual :

Este modo protege uma tabela contra alterações simultâneas de esquema e VACUUM executa.

Ou como explicado por um colaborador experiente do PostgreSQL aqui :

Autovacuum faz um bloqueio na tabela, mas é um bloqueio fraco que não interfere nas operações normais (SELECT, UPDATE, DELETE), mas interfere em coisas como adicionar índices ou truncar a tabela.

O blog do EnterpriseDB mencionado acima fornece "Dicas de práticas recomendadas VACUUM and ANALYZE" - pessoalmente, eu normalmente aconselharia um ANALYZEafter a VACUUMpara atualizar as estatísticas da tabela para o otimizador! Este artigo também pode ajudá-lo com decisões sobre quando você pode querer correr VACUUM.

P. 2)

A pergunta feita no assunto.

É possível nunca executar o vácuo completo no PostgreSQL

Sim, é possível nunca executá-lo, especialmente se o espaço em disco não for uma preocupação. No entanto, pode valer a pena, pois uma reescrita completa da tabela (juntamente com os índices) pode ter um impacto benéfico no desempenho da consulta - registros em ordem física podem levar a menos páginas que exigem varreduras!

Conclusão.

Esta é uma questão complexa e um artigo completo seria impossivelmente longo, mas eu recomendo que você leia as referências aqui (e também as páginas aqui , aqui , aqui (fator de preenchimento) e aqui ).

Claro, você não quer VACUUMinterromper seus sistemas de produção em horários de pico. Decidir sobre a melhor estratégia é o mesmo que praticamente tudo em TI - "It depends!".

Há espaço em disco e parâmetros de carga de E/S a serem levados em consideração e você terá que chegar a um compromisso que melhor se adapte a todas as partes interessadas.

^{1) o many9s é um excelente site para todas as coisas do PostgreSQL.
2) O EnterpriseDB também é um excelente site e a maior empresa PostgreSQL com um grande número de membros e colaboradores da equipe principal.
3) Percona, embora anteriormente uma empresa MySQL, agora também tem uma distribuição PostgreSQL, e eles patrocinam a promissora extensão pg_stat_monitor (veja também aqui ). Eles são altamente considerados.}

Para superusuários, não podemos bloquear o vácuo completo e apenas o vácuo na mesa, não tornando o espaço reutilizável, temos que usar o vácuo completo para remover fisicamente a tupla morta.