Reduzir o inchaço em tabelas e índices com a extensão pg_repack

• Criação inicial da tabela de logs: uma tabela de logs é criada para registrar as alterações que ocorrem durante a cópia inicial dos dados, conforme mostrado no seguinte exemplo:

  postgres=>\dt+ repack.log_*
  List of relations
  -[ RECORD 1 ]-+----------
  Schema        | repack
  Name          | log_16490
  Type          | table
  Owner         | postgres
  Persistence   | permanent
  Access method | heap
  Size          | 65 MB
  Description   |

• Fase final de troca e descarte.

As recomendações a seguir se aplicam ao remover a sobrecarga das tabelas e dos índices usando a extensão pg_repack:

• Realize a recriação fora do horário comercial ou durante uma janela de manutenção para minimizar o impacto na performance de outras atividades do banco de dados.

• Monitore atentamente as sessões de bloqueio durante a atividade de recriação e garanta que não haja nenhuma atividade na tabela original que possa bloquear pg_repack, especificamente durante a fase final de troca e descarte, quando ela precisa de um bloqueio exclusivo na tabela original. Para ter mais informações, consulte Como identificar o que está bloqueando uma consulta.

  Ao ver uma sessão de bloqueio, é possível encerrá-la usando o comando a seguir após uma análise cuidadosa. Isso ajuda na continuação da pg_repack para concluir a recriação:

  SELECT pg_terminate_backend(pid);

• Ao aplicar as alterações acumuladas da tabela de logs de pg_repack's em sistemas com uma taxa de transação muito alta, o processo de aplicação pode não ser capaz de acompanhar a taxa de alterações. Nesses casos, pg_repack não conseguiria concluir o processo de aplicação. Para ter mais informações, consulte Monitorar a nova tabela durante a recriação. Se os índices estiverem muito sobrecarregados, uma solução alternativa será realizar uma recriação somente de índices. Isso também ajuda os ciclos de limpeza do índice do VACUUM a terminar mais rapidamente.

  É possível ignorar a fase de limpeza do índice usando o VACUUM manual do PostgreSQL versão 12, e ela é ignorada automaticamente durante o autovacuum de emergência do PostgreSQL versão 14. Isso ajuda o VACUUM a ser concluído mais rapidamente sem remover a sobrecarga do índice e serve apenas para situações de emergência, como evitar o VACUUM de encapsulamento. Para ter mais informações, consulte Evitar a sobrecarga nos índices no Guia do usuário do Amazon Aurora.

• A tabela deve ter PRIMARY KEY ou a restrição UNIQUE não nula.

• A versão da extensão deve ser a mesma para o cliente e para o servidor.

• Garanta que a instância do RDS tenha mais FreeStorageSpace do que o tamanho total da tabela sem a sobrecarga. Por exemplo, pense no tamanho total da tabela, incluindo TOAST e índices, como 2 TB, e a sobrecarga total na tabela, como 1 TB. O FreeStorageSpace necessário deve ser maior do que o valor exibido pelo seguinte cálculo:

  2TB (Table size) - 1TB (Table bloat) = 1TB

  É possível usar a consulta a seguir para conferir o tamanho total da tabela e usar pgstattuple para gerar a sobrecarga. Para ter mais informações, consulte Diagnosticar a sobrecarga na tabela e no índice no Guia do usuário do Amazon Aurora.

  SELECT pg_size_pretty(pg_total_relation_size('table_name')) AS total_table_size;

  Esse espaço é recuperado após a conclusão da atividade.

• Garanta que a instância do RDS tenha capacidade computacional e de E/S suficientes para lidar com a operação de recriação. Pense na possibilidade de aumentar a escala da classe de instância verticalmente para conseguir o equilíbrio ideal de performance.

• O tamanho do banco de dados é aumentado pelo tamanho total da tabela menos a sobrecarga, até a fase de troca e descarte da recriação. É possível monitorar a taxa de crescimento do tamanho do banco de dados, calcular a velocidade da recriação e estimar aproximadamente o tempo necessário para concluir a transferência inicial dos dados.

  Por exemplo, pense no tamanho total da tabela como 2 TB, o tamanho do banco de dados como 4 TB e a sobrecarga total na tabela como 1 TB. O valor do tamanho total do banco de dados exibido pelo cálculo no final da operação de recriação é o seguinte:

  2TB (Table size) + 4 TB (Database size) - 1TB (Table bloat) = 5TB

  É possível calcular aproximadamente a velocidade da operação de recriação criando uma amostra da taxa de crescimento em bytes entre dois pontos no tempo. Se a taxa de crescimento for de 1 GB por minuto, poderá levar mil minutos ou 16,6 horas aproximadamente para concluir a operação inicial de criação da tabela. Além da criação inicial da tabela, a pg_repack também precisa aplicar as alterações acumuladas. O tempo necessário depende da taxa de aplicação das alterações em andamento, além das alterações acumuladas.

  nota

  É possível usar a extensão pgstattuple para calcular a sobrecarga na tabela. Para ter mais informações, consulte pgstattuple.

• O número de linhas na tabela de logs pg_repack's, no esquema de recriação, representa o volume de alterações pendentes para serem aplicadas à nova tabela após o carregamento inicial.

  É possível conferir a tabela de logs pg_repack's em pg_stat_all_tables para monitorar as alterações aplicadas à nova tabela. pg_stat_all_tables.n_live_tup indica o número de registros pendentes a serem aplicados à nova tabela. Para ter mais informações, consulte pg_stat_all_tables.

  postgres=>SELECT relname,n_live_tup FROM pg_stat_all_tables WHERE schemaname = 'repack' AND relname ILIKE '%log%';
          
  -[ RECORD 1 ]---------
  relname    | log_16490
  n_live_tup | 2000000
          

• É possível usar a extensão pg_stat_statements para descobrir o tempo gasto em cada etapa da operação de recriação. Isso é útil na preparação para aplicar a mesma operação de recriação em um ambiente de produção. É possível ajustar a cláusula LIMIT para estender ainda mais a saída.

  postgres=>SELECT
       SUBSTR(query, 1, 100) query,
       round((round(total_exec_time::numeric, 6) / 1000 / 60),4) total_exec_time_in_minutes
   FROM
       pg_stat_statements
   WHERE
       query ILIKE '%repack%'
   ORDER BY
       total_exec_time DESC LIMIT 5;
          
   query                                                                 | total_exec_time_in_minutes
  -----------------------------------------------------------------------+----------------------------
   CREATE UNIQUE INDEX index_16493 ON repack.table_16490 USING btree (a) |                     6.8627
   INSERT INTO repack.table_16490 SELECT a FROM ONLY public.t1           |                     6.4150
   SELECT repack.repack_apply($1, $2, $3, $4, $5, $6)                    |                     0.5395
   SELECT repack.repack_drop($1, $2)                                     |                     0.0004
   SELECT repack.repack_swap($1)                                         |                     0.0004
  (5 rows)