Perguntas[vacuum](dba)

Eu tenho uma tabela enorme que não consigo excluir as linhas, apenas atualizar as colunas que estão armazenando dados enormes em base64, que devo atualizar para null para tentar liberar espaço.

Então programei um script que é capaz de colocar todas as imagens em base64 esperando que o espaço seja liberado após o Vacuum!

As imagens estão definidas como null , vacuo executado, mas a tabela ainda está exatamente do mesmo tamanho, e tenho certeza que o espaço deve ser liberado imediatamente, então o que estou fazendo de errado?

O vácuo completo será capaz de liberar espaço de dados varchar enormes que atualizei para nulo? (Porque precisarei trancar a mesa se fizer isso e preciso ter certeza)

O tamanho do despejo diminuiu 10 vezes, portanto, espero um comportamento semelhante no tamanho do banco de dados.

A documentação oficial aqui me pareceu bastante enigmática. https://www.postgresql.org/docs/13/routine-vacuuming.html#AUTOVACUUM

Alguém pode oferecer uma explicação do que acontece sob o capô durante o vácuo automático ou o vácuo acionado manualmente?

Cometi um pequeno erro de cálculo e meu VACUUM FULL acabou enchendo os discos da instância. Consegui liberar espaço suficiente para reiniciar o postgres, mas temo que não seja suficiente para executar VACUUM FULL até a conclusão.

Existe alguma maneira de liberar o espaço usado pela execução VACUUM FULL com falha?

PS: Aqui estão as opções que eu useiVACUUM (FULL, VERBOSE, ANALYZE, INDEX_CLEANUP)

Eu recentemente tenho alguns DBs para manter. Acabei de falar sobre isso "Evitando falhas de wraparound de ID de transação". Eu verifiquei meus DBs e obtive os números:

Eu fiz vácuodb -a -v neste banco de dados e, em seguida, obtive os números:

Na minha opinião não é bom o suficiente? Devo me preocupar?

Se entendi corretamente o manual do PostgreSQL, devo usar o vácuo freeze para zerar os contadores? Eu sei que esse banco de dados crescerá de forma constante, geralmente em inserções, não tanto para exclusões/atualizações.

Obrigada.

Às vezes acho difícil entender a documentação do postgreSQL.

Agora, a seção VACCUM ( https://www.postgresql.org/docs/10/sql-vacuum.html ) diz que esta frase "O congelamento agressivo é sempre executado quando a tabela é reescrita".

Esta frase significa que VACUUM FREEZE faz o mesmo que FULL?

No trabalho, temos uma tabela de banco de dados que usamos para trabalhos enfileirados, então ela vê muito rendimento. Um problema que encontramos é que após um fim de semana sem nenhuma alteração de código, os índices nesta tabela são preenchidos com tuplas mortas. Quando executamos VACUUM VERBOSE ANALYZE, isso aparece como "600461 versões de linha morta não podem ser removidas ainda, xmin mais antigo: 902335252" (consulte [1]).

Quando procuramos o que estava impedindo o vácuo de limpá-los, ele apontou para uma consulta em execução LISTEN, usando o recurso pubsub do Postgres. Isso estava em execução há vários dias, o que eu acho que é uma maneira esperada de usar o LISTEN.

Portanto, uma maneira de resolver isso é garantir que nossos servidores de aplicativos sejam reiniciados regularmente, para que não possam ficar ouvindo por tanto tempo, ou reinicie qualquer LISTENs em execução por mais de um período de tempo. Dito isso, eu queria saber se havia uma maneira fácil de lidar com isso com o Postgres. Existe alguma maneira de configurar a transação para não bloquear a limpeza das tuplas mortas? Poderia algo estar errado no código do nosso aplicativo que faz com que LISTE tenha esse comportamento?

"Não tenha transações de longa duração" faz sentido para mim como um princípio geral, mas é bastante estranho para o LISTENqual se destina a ser usado como uma maneira de obter atualizações de streaming, então eu esperava que o Postgres pudesse ter uma boa solução.

=> SELECT *
-> FROM pg_stat_activity
-> WHERE backend_xmin = '902335252';
-[ RECORD 1 ]----+------------------------------
datid            | 16404
datname          | company_web_backend
pid              | 8936
leader_pid       |
usesysid         | 16388
usename          | company_web_backend
application_name |
client_addr      | 10.0.1.80
client_hostname  |
client_port      | 56654
backend_start    | 2021-07-24 01:21:28.270245+00
xact_start       | 2021-07-24 01:21:28.279008+00
query_start      | 2021-07-24 01:21:28.279008+00
state_change     | 2021-07-24 01:21:28.281313+00
wait_event_type  | Client
wait_event       | ClientWrite
state            | idle
backend_xid      |
backend_xmin     | 902335252
query            | LISTEN queued_jobs
backend_type     | client backend

[1]

 server=> VACUUM VERBOSE ANALYZE queued_jobs;
INFO:  vacuuming "public.queued_jobs"
INFO:  launched 2 parallel vacuum workers for index cleanup (planned: 2)
INFO:  "queued_jobs": found 0 removable, 5324589 nonremovable row versions in 553064 out of 8685508 pages
DETAIL:  600461 dead row versions cannot be removed yet, oldest xmin: 902335252
There were 9061227 unused item identifiers.
Skipped 3 pages due to buffer pins, 5619824 frozen pages.
0 pages are entirely empty.
CPU: user: 1.42 s, system: 1.25 s, elapsed: 3.10 s.
INFO:  vacuuming "pg_toast.pg_toast_37823"
INFO:  "pg_toast_37823": found 0 removable, 414848 nonremovable row versions in 79545 out of 16530493 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 902335252
There were 0 unused item identifiers.
Skipped 0 pages due to buffer pins, 14394456 frozen pages.
0 pages are entirely empty.
CPU: user: 0.46 s, system: 0.12 s, elapsed: 0.58 s.
INFO:  analyzing "public.queued_jobs"
INFO:  "queued_jobs": scanned 30000 of 8685508 pages, containing 311159 live rows and 2103 dead rows; 30000 rows in sample, 90085799 estimated total rows
VACUUM

Vamos criar uma tabela temporária (eu escolho uma tabela temporária porque o autovacuum não funciona para esse tipo de tabela):

CREATE TEMP TABLE test (
  id int PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  value int
);
INSERT INTO test (value) SELECT 0 FROM generate_series(0, 250);
SELECT ctid, * FROM test;

Veremos que a tabela consiste em duas páginas:

(0,1)   1   0
(0,2)   2   0
...
(0,226) 226 0
(1,1)   227 0
...
(1,25)  251 0

Agora, se atualizarmos uma linha

UPDATE test SET value = -1 WHERE id = 1;
SELECT ctid, * FROM test WHERE value <> 0;

veremos que a nova versão de linha foi inserida no final da tabela (na segunda página, que tem espaço livre suficiente para esta operação) e este é um comportamento padrão do PostgreSQL (a versão de linha antiga de (0,1) era marcado como morto)

(1,26)  1   -1

Vamos verificar o espaço livre das páginas:

CREATE EXTENSION pg_freespacemap;
SELECT * FROM pg_freespace('test');

Nós temos

0   0
1   0

ou seja, sem espaço livre; documentações dizem que o Free Space Map (FSM) é atualizado após a execução do vácuo. Agora, se atualizarmos outra linha da primeira página:

UPDATE test SET value = -1 WHERE id = 2;
SELECT ctid, * FROM test WHERE value <> 0;

vamos ver

(0,227) 2   -1
(1,26)  1   -1

A nova versão de linha não foi adicionada à 2ª página, mas foi adicionada à página atual, porque criamos espaço livre quando atualizamos o registro com id 1. Mas essa abordagem não corresponde ao meu entendimento de FSM. Aqui estão minhas perguntas:

1. Como o PostgreSQL soube no segundo UPDATE que havia espaço livre na primeira página se pg_freespace('test')retornasse zero? Eu pensei que esta função lhe dá o FSM que por sua vez é usado no caso de UPDATEs para decidir qual página tem espaço livre suficiente para armazenar a nova versão da linha.
2. Geralmente eu estava pensando que, mesmo que a primeira linha estivesse marcada como morta, ela ocuparia algum espaço na 1ª página e esse espaço será liberado somente após o vácuo. Assim, eu esperava que o segundo UPDATE também adicionasse a nova versão de linha à 2ª página.

Estamos enfrentando muitas lentidão devido ao inchaço do índice. Ao tentar otimizar o índice, recriá-lo parece gerar um índice muito menor:

reviews_hotel_id_connection_id_review_date_created_at_review_id: índice original reviews_hotel_id_connection_id_review_date_created_at_new: duplicata exata do índice original, recriado

 reviews | reviews_hotel_id_connection_id_review_date_created_at_review_id | 5296 kB  
 reviews | reviews_hotel_id_connection_id_review_date_created_at_new       | 2944 kB    

O novo índice tem apenas 55% do tamanho do índice original!

A execução VACUUM ANALYSE reviewsnão resulta em nenhuma redução de índice!

A execução VACUUM FULL reviewsfaz com que o índice original tenha exatamente o mesmo tamanho que o novo índice:

 reviews | reviews_hotel_id_connection_id_review_date_created_at_new       | 2944 kB 
 reviews | reviews_hotel_id_connection_id_review_date_created_at_review_id | 2944 kB

Embora VACUUM FULLbloqueie a tabela mesmo para leituras! É uma opção para otimizar índices sem causar tempo de inatividade?

Existe uma maneira de determinar antecipadamente quanto espaço em disco VACUUM FULLem uma tabela específica retornaria ao sistema operacional? Portanto, você pode decidir se vale a pena o custo de fazê-lo.

Bônus se houver uma consulta simples para fazer isso para cada tabela em um banco de dados/servidor (em vez de fazer cada uma separadamente).

Eu tenho um banco de dados Postgres(9.1) de 14,7 TB que precisa suportar gravações pesadas por longos períodos de tempo. Eu sei que preciso gerenciar de perto meus IDs de transação para evitar o bloqueio do banco de dados. Recentemente, notei que as consultas estavam diminuindo e vi que havia vários autovacuums em execução em nossas enormes tabelas somente leitura, com o subscrito "(para evitar quebra automática)". Parei o software em execução e executei um vacuumdb -F -acomando. No entanto, quando executo select current_query from pg_stat_activity, vejo que os processos de autovacuum ainda estão em execução, mesmo durante um vácuo manual. Eu tentei matar os autovacuums select pg_cancel_backend(pid)e eles morreram, mas imediatamente reiniciei novamente. Minhas perguntas:

1. O autovacuum deve continuar a ser executado durante um vácuo manual de db?
2. Como faço para efetivamente parar esses processos de autovacuum?
3. Por que esses autovacuums continuariam sendo executados em uma tabela somente leitura? O que há para aspirar?