Les traductions sont fournies par des outils de traduction automatique. En cas de conflit entre le contenu d'une traduction et celui de la version originale en anglais, la version anglaise prévaudra.

Gestion de la fonction autovacuum avec de grands index

Dans le cadre de son fonctionnement, la fonction autovacuum effectue plusieurs phases de mise à vide lorsqu'elle s'exécute sur une table. Avant que la table ne soit nettoyée, tous ses index sont d'abord vidés. Lorsque vous supprimez plusieurs grands index, cette phase consomme beaucoup de temps et de ressources. Par conséquent, il est recommandé de contrôler le nombre d'index d'une table et d'éliminer les index inutilisés.

Pour ce processus, vérifiez d'abord la taille globale de l'index. Déterminez ensuite s'il existe des index potentiellement inutilisés qui peuvent être supprimés comme le montrent les exemples suivants.

Pour vérifier la taille de la table et de ses index

postgres=> select pg_size_pretty(pg_relation_size('pgbench_accounts'));
pg_size_pretty
6404 MB
(1 row)

postgres=> select pg_size_pretty(pg_indexes_size('pgbench_accounts'));
pg_size_pretty
11 GB
(1 row)

Dans cet exemple, la taille des index est supérieure à celle de la table. Cette différence peut entraîner des problèmes de performances car les index sont surchargés ou inutilisés, ce qui a une incidence sur la fonction autovacuum ainsi que sur les opérations d'insertion.

Pour vérifier la présence d'index inutilisés

À l'aide de la vue pg_stat_user_indexes, vous pouvez vérifier la fréquence d'utilisation d'un index avec la colonne idx_scan. Dans l'exemple suivant, les index non utilisés ont la valeur idx_scan définie sur 0.

postgres=> select * from pg_stat_user_indexes where relname = 'pgbench_accounts' order by idx_scan desc;
    
relid  | indexrelid | schemaname | relname          | indexrelname          | idx_scan | idx_tup_read | idx_tup_fetch
-------+------------+------------+------------------+-----------------------+----------+--------------+---------------
16433  | 16454      | public     | pgbench_accounts | index_f               | 6        | 6            | 0
16433  | 16450      | public     | pgbench_accounts | index_b               | 3        | 199999       | 0
16433  | 16447      | public     | pgbench_accounts | pgbench_accounts_pkey | 0        | 0            | 0
16433  | 16452      | public     | pgbench_accounts | index_d               | 0        | 0            | 0
16433  | 16453      | public     | pgbench_accounts | index_e               | 0        | 0            | 0
16433  | 16451      | public     | pgbench_accounts | index_c               | 0        | 0            | 0
16433  | 16449      | public     | pgbench_accounts | index_a               | 0        | 0            | 0
(7 rows)        
    

postgres=> select schemaname, relname, indexrelname, idx_scan from pg_stat_user_indexes where relname = 'pgbench_accounts' order by idx_scan desc;
    
schemaname  | relname          | indexrelname          | idx_scan
------------+------------------+-----------------------+----------
public      | pgbench_accounts | index_f               | 6
public      | pgbench_accounts | index_b               | 3
public      | pgbench_accounts | pgbench_accounts_pkey | 0
public      | pgbench_accounts | index_d               | 0
public      | pgbench_accounts | index_e               | 0
public      | pgbench_accounts | index_c               | 0
public      | pgbench_accounts | index_a               | 0
(7 rows)        
    

Pour vérifier quand les statistiques ont été réinitialisées pour la dernière fois pour une base de données, utilisez pg_stat_database

postgres=> select datname, stats_reset from pg_stat_database where datname = 'postgres';
    
datname   | stats_reset
----------+-------------------------------
postgres  | 2022-11-17 08:58:11.427224+00
(1 row)       
    

Mise à vide d'une table le plus rapidement possible

RDSpour Postgre SQL 12 et supérieur

Si vous avez trop d'index dans une grande table, votre instance de base de données est peut-être proche de l'ID de transaction wraparound (XID), c'est-à-dire lorsque le XID compteur est ramené à zéro. Si elle n'est pas vérifiée, cette situation peut entraîner une perte de données. Toutefois, vous pouvez rapidement vider la table sans nettoyer les index. Dans RDS Postgre SQL 12 et versions ultérieures, vous pouvez utiliser la INDEX_CLEANUPclause VACUUM with.

postgres=> VACUUM (INDEX_CLEANUP FALSE, VERBOSE TRUE) pgbench_accounts;
        
INFO: vacuuming "public.pgbench_accounts"
INFO: table "pgbench_accounts": found 0 removable, 8 nonremovable row versions in 1 out of 819673 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 7517
Skipped 0 pages due to buffer pins, 0 frozen pages.
CPU: user: 0.01 s, system: 0.00 s, elapsed: 0.01 s.
        

Si une session Autovacuum est déjà en cours, vous devez y mettre fin pour commencer le manuelVACUUM. Pour plus d'informations sur le gel manuel du processus vacuum, consultez Réalisation d'un gel manuel du processus vacuum.

RDSpour Postgre SQL 11 et versions antérieures

Cependant, dans RDS les versions SQL 11 et antérieures de Postgre, le seul moyen de permettre au vide de se terminer plus rapidement est de réduire le nombre d'index sur une table. La suppression d'un index peut affecter les plans de requête. Nous vous recommandons de supprimer d'abord les index inutilisés, puis de supprimer les index lorsque XID Wraparound est très proche. Une fois le processus vacuum terminé, vous pouvez recréer ces index.