Limpeza e análise manual de tabelas

Se seu banco de dados for limpo pelo processo de autovacuum, é uma prática recomendada evitar a execução de verificações manuais em todo o banco de dados com muita frequência. Um operação de vacuum manual pode resultar em cargas de E/S ou picos de CPU desnecessários, e também pode falhar na remoção de tuplas inativas. Execute limpezas manuais tabela por tabela somente se for realmente necessário, como quando a proporção de tuplas ativas ou inativas está baixa ou quando houver períodos longos entre as limpezas automáticas. Além disso, você deve executar aspiradores manuais quando a atividade do usuário for mínima.

O autovacuum também mantém as estatísticas de uma tabela atualizadas. Quando você executa o comando ANALYZE manualmente, ele recria essas estatísticas em vez de atualizá-las. A recriação de estatísticas quando elas já estão atualizadas pelo processo normal de autovacuum pode resultar na utilização de recursos do sistema.

Recomendamos que você execute os comandos VACUUM e ANALYZE manualmente nos seguintes cenários:

Para reduzir o impacto de E/S ao executar os comandos VACUUM e ANALYZE na atividade simultânea do banco de dados, você pode usar o parâmetro vacuum_cost_delay. Em muitas situações, comandos de manutenção como VACUUM e ANALYZE não precisam ser concluídos rapidamente. No entanto, esses comandos não devem interferir na capacidade do sistema de realizar outras operações de banco de dados. Para evitar isso, você pode habilitar atrasos de vacuum baseados em custos usando o parâmetro vacuum_cost_delay. Por padrão, esse parâmetro é desabilitado para comandos VACUUM emitidos manualmente. Para habilitá-lo, defina-o com um valor diferente de zero.

Execução de operações de vacuum e limpeza em paralelo

A opção PARALLEL do comando VACUUM usa operadores paralelos para as fases de vacuum e limpeza do índice e está desabilitada por padrão. O número de operadores paralelos (o grau de paralelismo) é determinado pelo número de índices na tabela e pode ser especificado pelo usuário. Se você estiver executando operações VACUUM paralelas sem um argumento inteiro, o grau de paralelismo será calculado com base no número de índices na tabela.

Os parâmetros a seguir ajudam você a configurar a operação de vacuum paralela no Amazon RDS para PostgreSQL e no Aurora compatível com PostgreSQL:

O exemplo a seguir ilustra o comportamento do banco de dados quando você usa o VACUUM e ANALYZE manuais em um banco de dados.

Confira um exemplo de tabela em que o autovacuum foi desabilitado (apenas para fins ilustrativos; desabilitar o autovacuum não é recomendado):

create table t1 ( a int, b int, c int );
alter table t1 set (autovacuum_enabled=false);

apgl=> \d+ t1
Table "public.t1"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+---------+--------------+-------------
a | integer | | | | plain | |
b | integer | | | | plain | |
c | integer | | | | plain | |
Access method: heap
Options: autovacuum_enabled=false

Adicionar 1 milhão de linhas à tabela t1:

apgl=> select count(*) from t1;
count
1000000
(1 row)

Estatísticas da tabela t1:

select * from pg_stat_all_tables where relname='t1';
-[ RECORD 1 ]-------+--------
relid         | 914744
schemaname    | public
relname       | t1
seq_scan      | 0
seq_tup_read  | 0
idx_scan      |
idx_tup_fetch |
n_tup_ins     | 1000000
n_tup_upd     | 0
n_tup_del     | 0
n_tup_hot_upd | 0
n_live_tup    | 1000000
n_dead_tup    | 0
n_mod_since_analyze | 1000000
last_vacuum      |
last_autovacuum  |
last_analyze     |
last_autoanalyze |
vacuum_count     | 0
autovacuum_count | 0
analyze_count    | 0
autoanalyze_count | 0

Adicionar um índice:

create index i2 on t1 (b,a);

Execute o comando EXPLAIN (Plano 1):

Bitmap Heap Scan on t1 (cost=10521.17..14072.67 rows=5000 width=4)
Recheck Cond: (a = 5)
→ Bitmap Index Scan on i2 (cost=0.00..10519.92 rows=5000 width=0)
Index Cond: (a = 5)
(4 rows)

Execute o comando EXPLAIN ANALYZE (Plano 2):

explain (analyze,buffers,costs off) select a from t1 where b = 5;
QUERY PLAN
Bitmap Heap Scan on t1 (actual time=0.023..0.024 rows=1 loops=1)
Recheck Cond: (b = 5)
Heap Blocks: exact=1
Buffers: shared hit=4
→ Bitmap Index Scan on i2 (actual time=0.016..0.016 rows=1 loops=1)
Index Cond: (b = 5)
Buffers: shared hit=3
Planning Time: 0.054 ms
Execution Time: 0.076 ms
(9 rows)

Os comandos EXPLAIN e EXPLAIN ANALYZE exibem planos diferentes, porque o autovacuum foi desabilitado na tabela e o comando ANALYZE não foi executado manualmente. Agora vamos atualizar um valor na tabela e regenerar o plano EXPLAIN ANALYZE:

update t1 set a=8 where b=5;
explain (analyze,buffers,costs off) select a from t1 where b = 5;

O comando EXPLAIN ANALYZE (Plano 3) agora exibe:

apgl=> explain (analyze,buffers,costs off) select a from t1 where b = 5;
QUERY PLAN
Bitmap Heap Scan on t1 (actual time=0.075..0.076 rows=1 loops=1)
Recheck Cond: (b = 5)
Heap Blocks: exact=1
Buffers: shared hit=5
→ Bitmap Index Scan on i2 (actual time=0.017..0.017 rows=2 loops=1)
Index Cond: (b = 5)
Buffers: shared hit=3
Planning Time: 0.053 ms
Execution Time: 0.125 ms

Se você comparar os custos entre o Plano 2 e o Plano 3, verá as diferenças no tempo de planejamento e execução, porque ainda não coletamos estatísticas.

Agora vamos executar um ANALYZE manual na tabela, verificar as estatísticas e regenerar o plano:

apgl=> analyze t1
apgl→ ;
ANALYZE
Time: 212.223 ms

apgl=> select * from pg_stat_all_tables where relname='t1';
-[ RECORD 1 ]-------+------------------------------
relid          | 914744
schemaname     | public
relname        | t1
seq_scan       | 3
seq_tup_read   | 1000000
idx_scan       | 3
idx_tup_fetch  | 3
n_tup_ins      | 1000000
n_tup_upd      | 1
n_tup_del      | 0
n_tup_hot_upd  | 0
n_live_tup     | 1000000
n_dead_tup     | 1
n_mod_since_analyze | 0
last_vacuum       |
last_autovacuum   |
last_analyze      | 2023-04-15 11:39:02.075089+00
last_autoanalyze  |
vacuum_count      | 0
autovacuum_count  | 0
analyze_count     | 1
autoanalyze_count | 0

Time: 148.347 ms

Execute o comando EXPLAIN ANALYZE (Plano 4):

apgl=> explain (analyze,buffers,costs off) select a from t1 where b = 5;
QUERY PLAN
Index Only Scan using i2 on t1 (actual time=0.022..0.023 rows=1 loops=1)
Index Cond: (b = 5)
Heap Fetches: 1
Buffers: shared hit=4
Planning Time: 0.056 ms
Execution Time: 0.068 ms
(6 rows)

Time: 138.462 ms

Se comparar todos os resultados do plano depois de analisar manualmente a tabela e coletar estatísticas, você observará que o Plano 4 do otimizador é melhor do que os outros e que também diminui o tempo de execução da consulta. Este exemplo mostra como é importante executar atividades de manutenção no banco de dados.

Reescrita de toda uma tabela com VACUUM FULL

A execução do comando VACUUM com o parâmetro FULL regrava todo o conteúdo de uma tabela em um novo arquivo de disco sem espaço extra e retorna espaço não utilizado para o sistema operacional. Essa operação é muito mais lenta e requer um bloqueio ACCESS EXCLUSIVE em cada tabela. Também requer espaço extra em disco, pois grava uma nova cópia da tabela e não libera a cópia antiga até que a operação seja concluída.

O VACUUM FULL pode ser útil nos seguintes casos:

Recomendamos que você use VACUUM FULL quando tiver tabelas de chaves não primárias, se seu banco de dados puder tolerar tempo de inatividade.

Como o VACUUM FULL requer mais bloqueio do que outras operações, é mais caro executá-lo em bancos de dados cruciais. Para substituir esse método, você pode usar a extensão pg_repack , descrita na próxima seção. Essa opção é semelhante ao VACUUM FULL, mas requer bloqueio mínimo e é compatível com o Amazon RDS para PostgreSQL e o Aurora compatível com PostgreSQL.