Réduction du gonflement des tables et des index avec l’extension pg_repack

• Création initiale de la table de journal : une table de journal est créée pour enregistrer les modifications survenues lors de la copie initiale des données, comme indiqué dans l’exemple suivant :

  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   |

• Phase finale d’échange et de suppression.

Les recommandations suivantes s’appliquent lorsque vous supprimez le gonflement des tables et des index à l’aide de l’extension pg_repack :

• Effectuez le reconditionnement en dehors des heures ouvrables ou pendant une fenêtre de maintenance afin de minimiser son impact sur les performances des autres activités de base de données.

• Surveillez de près les sessions bloquantes pendant l’activité de reconstruction et assurez-vous qu’aucune activité sur la table d’origine ne risque de bloquer pg_repack, en particulier pendant la phase finale d’échange et de suppression lorsqu’un verrou exclusif de la table d’origine est nécessaire. Pour plus d’informations, consultez Identification de ce qui bloque une requête.

  Lorsque vous rencontrez une session bloquante, vous pouvez y mettre fin à l’aide de la commande suivante après un examen attentif. Cela permet la poursuite de pg_repack pour terminer la reconstruction :

  SELECT pg_terminate_backend(pid);

• Lors de l’application des modifications accumulées à partir de la table de journal pg_repack's sur des systèmes présentant un taux de transactions très élevé, le processus d’application risque de ne pas être en mesure de suivre le rythme des modifications. Dans de tels cas, pg_repack ne serait pas en mesure de terminer le processus d’application. Pour plus d’informations, consultez Surveillance de la nouvelle table lors du reconditionnement. Si les index sont très gonflés, une autre solution consiste à effectuer un reconditionnement des index uniquement. Cela permet également aux cycles de nettoyage des index du VACUUM de se terminer plus rapidement.

  Vous pouvez ignorer la phase de nettoyage de l’index à l’aide du VACUUM manuel de PostgreSQL version 12, et elle est automatiquement ignorée lors de l’autovacuum d’urgence à partir de PostgreSQL version 14. Cela permet au VACUUM de se terminer plus rapidement sans supprimer le gonflement de l’index et cette solution est uniquement destinée aux situations d’urgence, telles que la prévention du VACUUM de bouclage. Pour plus d’informations, consultez Prévention du gonflement dans les index dans le Guide de l’utilisateur Amazon Aurora.

• La table doit avoir une contrainte PRIMARY KEY ou UNIQUE non nulle.

• La version de l’extension doit être la même pour le client et le serveur.

• Assurez-vous que l’instance RDS a plus de FreeStorageSpace que la taille totale de la table sans le gonflement. Par exemple, considérez que la taille totale de la table, y compris TOAST et les index, est de 2 To, et que le gonflement total de la table est de 1 To. La valeur de FreeStorageSpace requise doit être supérieure à la valeur renvoyée par le calcul suivant :

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

  Vous pouvez utiliser la requête suivante pour vérifier la taille totale de la table et utiliser pgstattuple pour en déduire le gonflement. Pour plus d’informations, consultez Diagnostic du gonflement de la table et de l’index dans le Guide de l’utilisateur Amazon Aurora.

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

  Cet espace est récupéré une fois l’activité terminée.

• Assurez-vous que l’instance RDS dispose d’une capacité de calcul et d’E/S suffisante pour gérer l’opération de reconditionnement. Vous pouvez envisager d’augmenter verticalement la classe d’instance pour un équilibre optimal des performances.

• La taille de la base de données est augmentée de la taille totale de la table moins le gonflement, jusqu’à la phase d’échange et de suppression du reconditionnement. Vous pouvez surveiller le taux de croissance de la taille de la base de données, calculer la vitesse du reconditionnement et estimer approximativement le temps nécessaire pour terminer le transfert de données initial.

  Par exemple, considérez que la taille totale de la table est de 2 To, la taille de la base de données de 4 To et le gonflement total de la table de 1 To. La valeur de taille totale de la base de données renvoyée par le calcul à la fin de l’opération de reconditionnement est la suivante :

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

  Vous pouvez estimer approximativement la vitesse de l’opération de reconditionnement en échantillonnant le taux de croissance en octets entre deux points dans le temps. Si le taux de croissance est de 1 Go par minute, l’opération initiale de création de table peut prendre environ 1 000 minutes ou 16,6 heures. Outre la création initiale de la table, pg_repack doit également appliquer les modifications accumulées. Le temps nécessaire dépend du taux d’application des modifications en cours et des modifications accumulées.

  Note

  Vous pouvez utiliser l’extension pgstattuple pour calculer le gonflement dans la table. Pour plus d’informations, consultez pgstattuple.

• Le nombre de lignes de la table de journal pg_repack's, sous le schéma de reconditionnement, représente le volume de modifications en attente d’être appliquées à la nouvelle table après le chargement initial.

  Vous pouvez consulter la table de journal pg_repack's dans pg_stat_all_tables pour surveiller les modifications appliquées à la nouvelle table. pg_stat_all_tables.n_live_tup indique le nombre d’enregistrements en attente d’être appliqués à la nouvelle table. Pour plus d’informations, consultez 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
          

• Vous pouvez utiliser l’extension pg_stat_statements pour connaître le temps nécessaire à chaque étape de l’opération de reconditionnement. Cela est utile pour préparer l’application de la même opération de reconditionnement dans un environnement de production. Vous pouvez ajuster la clause LIMIT pour étendre davantage la sortie.

  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)