Réduction du ballonnement 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   |

• swap-and-dropPhase finale.

Les recommandations suivantes s'appliquent lorsque vous supprimez le bloat des tables et des index à l'aide de l'pg_repackextension :

• Effectuez le reconditionnement en dehors des heures ouvrables ou pendant une période 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 bloquerpg_repack, en particulier pendant la swap-and-drop phase finale lorsqu'un verrouillage exclusif de la table d'origine est nécessaire. Pour plus d'informations, consultez la section Identification de ce qui bloque une requête.

  Lorsque vous voyez une session bloquante, vous pouvez y mettre fin à l'aide de la commande suivante après mûre réflexion. Cela permet de continuer pg_repack à terminer la reconstruction :

  SELECT pg_terminate_backend(pid);

• Lors de l'application des modifications accumulées à partir de la table des pg_repack's journaux 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 il ne serait pas en mesure de terminer le processus de candidature. Pour de plus amples informations, veuillez consulter Surveillance de la nouvelle table lors du reconditionnement. Si les index sont très volumineux, une autre solution consiste à effectuer un reconditionnement des index uniquement. Cela permet également aux cycles VACUUM de nettoyage des index de se terminer plus rapidement.

  Vous pouvez ignorer la phase de nettoyage de l'index à l'aide VACUUM du manuel depuis Postgre SQL version 12, et elle est automatiquement ignorée lors de l'aspiration automatique d'urgence depuis Postgre version 14. SQL Cela permet de VACUUM terminer plus rapidement sans supprimer le gonflement de l'index et n'est destiné qu'aux situations d'urgence telles que la prévention de l'VACUUMencapsulation. Pour plus d'informations, consultez la section Éviter le gonflement des index dans le guide de l'utilisateur Amazon Aurora.

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

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

• Assurez-vous que la taille de l'RDSinstance est FreeStorageSpace supérieure à la taille totale de la table sans le gonflement. Par exemple, considérez que la taille totale de la table, index TOAST et index inclus, est de 2 To, et que le volume total de la table est de 1 To. La valeur requise FreeStorageSpace 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 l'utiliser pgstattuple pour en déduire le gonflement. Pour plus d'informations, consultez la section Diagnostic du gonflement des tables et des 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'RDSinstance dispose d'une capacité de calcul et d'E/S suffisante pour gérer l'opération de reconditionnement. Vous pouvez envisager d'augmenter 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 surchargement, jusqu'à la swap-and-drop phase de 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 la charge totale 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, vous pg_repack devez également appliquer les modifications accumulées. Le temps nécessaire dépend du taux d'application des modifications en cours et des modifications cumulées.

  Note

  Vous pouvez utiliser pgstattuple l'extension pour calculer le gonflement dans le tableau. Pour plus d'informations, consultez pgstattuple.

• Le nombre de lignes de la table de pg_repack's journal, 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 des pg_repack's journaux pg_stat_all_tables pour surveiller les modifications appliquées à la nouvelle table. pg_stat_all_tables.n_live_tupindique 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'pg_stat_statementsextension 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 LIMIT clause 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)