Início / dba / Perguntas / 337486
Accepted
Fran
Asked: 2024-03-05 20:24:25 +0800 CST

Revise o espaço liberado após DELETE no postgres

  • 772

Estou procurando ajuda prática para resolver problemas de espaço em disco em bancos de dados. Já visitei estas páginas:

No meu contexto ainda tenho algumas dúvidas: tenho um banco de dados com 100GB de espaço em disco. Uma tabela está relatando o uso de 73 GB de espaço de consulta:

select
    quote_ident(table_schema) || '.' || quote_ident(table_name),
    pg_size_pretty(pg_total_relation_size(quote_ident(table_schema) || '.' || quote_ident(table_name))) as disk_size
from
    information_schema.tables
where
    pg_total_relation_size(quote_ident(table_schema) || '.' || quote_ident(table_name)) > 100000
order by
    pg_total_relation_size(quote_ident(table_schema) || '.' || quote_ident(table_name)) desc;

Os desenvolvedores estão me dizendo que excluíram muitas linhas da tabela mencionada. Como posso verificar isso?

pg_total_relation_sizeretorna informações sobre o disco usado, VACUUM ANALYZEapenas o espaço livre disponível, mas não o espaço em disco. Assim pg_total_relation_sizeainda retornará o mesmo valor, certo?

No meu entendimento, se eu executar VACUUM ANALYZEele irá atualizar alguma referência no gerenciamento de espaço que o banco de dados pode usar para novas linhas. Existe alguma maneira prática de comparar o espaço em disco usado e o tamanho real da tabela? É possível com pgstattuple ou pg_freespacemap? Se sim, você poderia fornecer um exemplo de consulta?

Também encontrei algumas consultas verificando pg_catalog.pg_stat_all_tableso retorno da quantidade de n_dead_tupe n_live_tup. Depois de a, VACUUM ANALYZEeles seriam limpos, verificando assim o total de tuplas mortas após a VACUUM ANALYZEpara ver se o VACUUM FULLespaço livre não seria um bom indicador?

postgresql

2 respostas

  1. Best Answer

    Use a extensão pgstattuple. Ele mostrará quanto da tabela está ocupada por dados do usuário, lixo e espaço vazio:

    SELECT * FROM pgstattuple('schemaname.tablename');

    Isso executará uma varredura sequencial na mesa. Há também pgstattuple_approxalgo útil para tabelas grandes: ele verifica apenas parte da tabela e extrapola os números.

    Para verificar todas as tabelas em um banco de dados, você poderia

    SELECT t.oid::regclass AS table_name,
       p.*
FROM (SELECT oid
      FROM pg_class
      WHERE relkind IN ('r', 't', 'm')
      OFFSET 0) AS t
   CROSS JOIN LATERAL pgstattuple(t.oid) AS p;
    • 1

  2. Se o vácuo padrão (não COMPLETO) já tiver sido feito, você poderá ver quanto espaço livre contabilizado existe em uma tabela específica com algo como:

    select sum(avail) from pg_freespace('pgbench_accounts');

    Ou se você se preocupa com quantos blocos têm pelo menos x quantidade de espaço livre, você pode escrever uma consulta para fazer isso. É apenas SQL, tem toda a flexibilidade que o SQL possui.

    Se as tuplas foram excluídas, mas ainda não foram limpas por vácuo, então pgstattuple seria a maneira de avaliar isso. (Ou apenas execute o vácuo e use pg_freespace).

    • 1

relate perguntas