Por que usar o VACUUM

--Por que usar o VACUUM.
--Este artigo explica de forma prática por que usar o VACUUM


--Criando a tabela temporaria chamada teste
DROP TABLE IF EXISTS teste;
CREATE TEMP TABLE teste
(
     id   SERIAL PRIMARY KEY,
     nome VARCHAR(10)
);

--Populando ...
INSERT INTO teste (nome) VALUES ('TESTE1');
INSERT INTO teste (nome) VALUES ('TESTE2');
INSERT INTO teste (nome) VALUES ('TESTE3');
INSERT INTO teste (nome) VALUES ('TESTE4');
INSERT INTO teste (nome) VALUES ('TESTE5');
INSERT INTO teste (nome) VALUES ('TESTE6');
INSERT INTO teste (nome) VALUES ('TESTE7');
INSERT INTO teste (nome) VALUES ('TESTE8');
INSERT INTO teste (nome) VALUES ('TESTE9');
INSERT INTO teste (nome) VALUES ('TESTE10');
INSERT INTO teste (nome) VALUES ('TESTE11');
INSERT INTO teste (nome) VALUES ('TESTE12');
INSERT INTO teste (nome) VALUES ('TESTE13');
INSERT INTO teste (nome) VALUES ('TESTE14');
INSERT INTO teste (nome) VALUES ('TESTE15');
INSERT INTO teste (nome) VALUES ('TESTE16');
INSERT INTO teste (nome) VALUES ('TESTE17');
INSERT INTO teste (nome) VALUES ('TESTE18');
INSERT INTO teste (nome) VALUES ('TESTE19');
INSERT INTO teste (nome) VALUES ('TESTE20');
INSERT INTO teste (nome) VALUES ('TESTE21');
INSERT INTO teste (nome) VALUES ('TESTE22');
INSERT INTO teste (nome) VALUES ('TESTE23');
INSERT INTO teste (nome) VALUES ('TESTE24');
INSERT INTO teste (nome) VALUES ('TESTE25');

--Fazendo uma simples consulta
SELECT * FROM teste; -- 25 tuplas na ordem de inserção

--Checando ultimo vacuum na tabela
     SELECT schemaname AS esquema
          , relname AS tabela
          , pg_stat_get_last_vacuum_time(relid) AS ultimo_vacuum
          , pg_stat_get_last_autovacuum_time(relid) AS ultimo_autovacuum
       FROM pg_stat_all_tables
       WHERE relname ILIKE 'teste'; -- null
      
--Checando quantas linhas mortas na tabela
     SELECT *
          , n_dead_tup AS linhas_mortas      
       FROM pg_stat_all_tables
      WHERE n_dead_tup > 0
        AND relname ILIKE 'teste'
   ORDER BY n_dead_tup DESC; -- nada

--Implentando o vacuum
VACUUM FULL VERBOSE teste;

--update 1
UPDATE teste SET nome = 'TESTE1 OK' WHERE id =1;

--Fazendo uma simples consulta
SELECT * FROM teste; -- 25 tuplas com id 1 por ultimo, pq ?


--Checando ultimo vacuum na tabela
     SELECT schemaname AS esquema
          , relname AS tabela
          , pg_stat_get_last_vacuum_time(relid) AS ultimo_vacuum
          , pg_stat_get_last_autovacuum_time(relid) AS ultimo_autovacuum
       FROM pg_stat_all_tables
       WHERE relname ILIKE 'teste'; -- vacuum registrado
      
--Checando quantas linhas mortas na tabela
     SELECT *
          , n_dead_tup AS linhas_mortas 
       FROM pg_stat_all_tables
      WHERE n_dead_tup > 0
        AND relname ILIKE 'teste'
   ORDER BY n_dead_tup DESC;  --1 linha morta

--update do id 1 a 10
UPDATE teste SET nome = 'TESTE1 OK' WHERE id =1;
UPDATE teste SET nome = 'TESTE2 OK' WHERE id =2;
UPDATE teste SET nome = 'TESTE3 OK' WHERE id =3;
UPDATE teste SET nome = 'TESTE4 OK' WHERE id =4;
UPDATE teste SET nome = 'TESTE5 OK' WHERE id =5;
UPDATE teste SET nome = 'TESTE6 OK' WHERE id =6;
UPDATE teste SET nome = 'TESTE7 OK' WHERE id =7;
UPDATE teste SET nome = 'TESTE7 OK' WHERE id =8;
UPDATE teste SET nome = 'TESTE7 OK' WHERE id =9;
UPDATE teste SET nome = 'TESTE7 OK' WHERE id =10;

--delete do id 22 a 25
DELETE FROM teste WHERE id >= 22;

--Listando...
SELECT * FROM teste; --fora da ordem natural, pq ?

--Checando ultimo vacuum na tabela
     SELECT schemaname AS esquema
          , relname AS tabela
          , pg_stat_get_last_vacuum_time(relid) AS ultimo_vacuum
          , pg_stat_get_last_autovacuum_time(relid) AS ultimo_autovacuum
       FROM pg_stat_all_tables
       WHERE relname ILIKE 'teste'; -- vacuum registrado
      
--Checando quantas linhas mortas na tabela
     SELECT *
          , n_dead_tup AS linhas_mortas      
       FROM pg_stat_all_tables
      WHERE n_dead_tup > 0
        AND relname ILIKE 'teste'
   ORDER BY n_dead_tup DESC;  --15 linhas mortas

--Dando um sumiço nos cadavers   :)
VACUUM FULL VERBOSE teste;

--Apenas listando na ordem dos ids
     SELECT *
       FROM teste
   ORDER BY 1 ASC; --agora com ordenacao pelo id

--Conclusão:
--Toda operação de update o Postgres inativa (mata) a(s) linha(s), porém o cadaver ainda tá lá, e dá um insert da(s) linha(s) novamente com as valores alterados, isso diminue o custo do update.
--Isso explica o motivo da ordem natural de inserção de linhas não aparecer, caso haja atualizações em numa consulta sem ordenação.
--Toda operação de delete o Postgres inativa (mata) a(s) linha(s), porém o cadaver ainda tá lá.
--A execução do VACUUM FULL dá um acesso exclusivo na tabela, e elimina as linhas mortas, dá um sumiço nos cadaveres, atualiza o índice da chave primaria e atualiza as estatisticas.
--A execução do VACUUM ANALYZE, não dá acesso exclusivo na tabela,  atualiza apenas as estatísticas, usado para traçar a melhor rota ou forma de acesso a tabela, por exemplo na execução de uma consulta, podendo ser INDEXADA(INDEXSCAN) ou SEQUENCIAL(SEQSCAN)
--A opção VERBOSE mostra os detalhes da operação.
--Recomenda-se caso a tabela tenha mais índices, além da chave primaria dá um REINDEX TABLE teste;