Início / dba / Perguntas / 187044
Accepted
Hassan Baig
Asked: 2017-09-28 04:36:08 +0800 CST

Espaço em disco não liberado após a limpeza de linhas da tabela PG

  • 772

Eu entendo que o espaço não é liberado abruptamente (a menos que um faça TRUNCATE). Mas este parece-me anormal.

Qual poderia ser o motivo de uma tabela postgresql (9.3.10) não liberar espaço após ter 70% de seus registros removidos?

Eu tenho uma tabela chamada user_sessions_session. Como o nome indica, ele armazena dados de sessões para cada usuário de um aplicativo da web.

Seu tamanho original era:

              Table               |  Size   | External Size 
----------------------------------+---------+---------------
 user_sessions_session            | 15 GB   | 13 GB

Depois disso, excluí todas as sessões de usuário com mais de 3 meses atrás. Essa foi a maioria das linhas na tabela. Isso foi há 3 dias. Acabei de verificar o tamanho da tabela novamente, eis o que vejo:

              Table               |  Size   | External Size 
----------------------------------+---------+---------------
 user_sessions_session            | 15 GB   | 13 GB

Além disso, select * from pg_stat_activity where query like 'autovacuum:%';revela que nenhuma aspiração está acontecendo neste momento.

Btw eu tive esse mesmo problema com esta mesma tabela de sessões antes - não é um caso isolado.

Caso seja importante, aqui está o SQL que usei para obter os tamanhos das tabelas (user_sessions_session apareceu como número 1 na lista):

SELECT                                                         
   relname as "Table",
   pg_size_pretty(pg_total_relation_size(relid)) As "Size",
   pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) as "External Size"
   FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC;
postgresql postgresql-9.3

1 respostas

  1. Best Answer

    Dos documentos,

    No PostgreSQL, um UPDATE ou DELETE de uma linha não remove imediatamente a versão antiga da linha. Essa abordagem é necessária para obter os benefícios do controle de simultaneidade multiversão (MVCC, consulte o Capítulo 13): a versão da linha não deve ser excluída enquanto ainda estiver potencialmente visível para outras transações. Mas, eventualmente, uma versão de linha desatualizada ou excluída não interessa mais a nenhuma transação. O espaço que ocupa deve então ser recuperado para reutilização por novas linhas, para evitar o crescimento ilimitado dos requisitos de espaço em disco. Isso é feito executando VACUUM.

    De forma alguma. Nunca. Não há exceções. Sempre que você exclui uma linha, você a marca essencialmente como inativa para seu instantâneo. Outros instantâneos iniciados por transações anteriores ainda podem vê-lo. Isso significa que ainda está no disco.

    As linhas não são armazenadas em seu próprio arquivo no disco. Para excluir essa linha, mesmo quando quiser, é necessário reescrever os arquivos armazenados no disco sem essa linha. DELETEnão faz isso e, em circunstâncias normais , tudo bem, porque as linhas mortas que não são visíveis para uma transação podem ser recuperadas internamente por um VACUUM. Após serem marcados para reutilização, novos dados podem ser armazenados no heap.

    Então, essencialmente, apenas as operações que internamente fazem com que o heap da tabela seja reescrito recuperam espaço. Essas operações são:

    • CLUSTER
    • VACUUM FULL
    • TRUNCATE
    • ALTER TABLE(formulários que reescrevem a tabela)

    Todas essas operações requerem um ACCESS EXCLUSIVEbloqueio. E, TRUNCATEé uma marreta que até viola o MVCC .

    @EvanCarroll: e, tecnicamente, posso realmente ficar sem espaço em disco mesmo depois de remover uma tonelada de linhas aqui? – Hassan Baig 1 min atrás

    Sim, realmente e tecnicamente e tudo mais, mas vamos fazer uma ressalva especial ao acima. Se você fizer DELETEum monte de linhas e confirmar, elas serão marcadas como inativas. Quando você VACUUM, o espaço de heap da tabela é recuperado. A partir desse ponto, para ficar sem espaço em disco, você deve

    • Esgote esse espaço recuperado no heap dessa tabela porque o PostgreSQL apenas armazenará coisas nesse espaço.
    • Ou esgote a pilha de uma mesa diferente.

    Mas sim, coisas MUITO ruins podem acontecer quando você esgota seu espaço em disco. E eles acontecerão com todos os fornecedores de banco de dados MVCC. Simplesmente, não fique sem espaço em disco .

    • 18

relate perguntas