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.
# Éviter les problèmes de performances avec REPLICA IDENTITY FULL dans pour PostgreSQL
La réplication logique PostgreSQL nécessite que chaque table publiée possède *une identité de réplique* afin que l'abonné puisse localiser la bonne ligne à mettre à jour ou à supprimer. Par défaut, la clé primaire sert d'identité de réplique. Lorsqu'une table ne possède pas de clé primaire ou d'index unique approprié, vous pouvez définir l'identité de la réplique sur`FULL`, ce qui oblige PostgreSQL à utiliser la ligne entière comme clé.
Tout en `REPLICA IDENTITY FULL` résolvant le problème immédiat lié à la réplication de tables sans clés primaires, cela peut entraîner de sérieux problèmes de performances tant pour l'éditeur que pour l'abonné. Il est important de comprendre ces impacts pour quiconque utilise la réplication logique avec , y compris les fonctionnalités qui reposent sur la réplication logique en interne, telles que les déploiements. blue/green
## Pourquoi REPLICA IDENTITY FULL pose problème
### Augmentation du volume WAL sur l'éditeur
Le `REPLICA IDENTITY` paramètre contrôle les informations que PostgreSQL écrit dans le journal d'écriture anticipée (WAL) pour identifier les lignes mises à jour ou supprimées. Avec l'identité de réplique par défaut (clé primaire), seules les colonnes clés sont enregistrées comme l'ancienne identité de ligne. *Avec`FULL`, PostgreSQL enregistre les anciennes valeurs de chaque colonne pour chaque colonne et.* `UPDATE` `DELETE` Cela a plusieurs conséquences :
+ **La taille du WAL augmente de manière significative.** Pour les mises à jour, la taille de chaque enregistrement WAL double approximativement, car les anciennes et les nouvelles valeurs de chaque colonne sont enregistrées. Si la table contient de grandes valeurs stockées à l'aide de [TOAST](https://www.postgresql.org/docs/current/storage-toast.html), l'augmentation peut être beaucoup plus importante car les valeurs Toasted doivent être extraites et écrites dans le WAL même si elles n'ont pas été modifiées par la mise à jour.
+ **Utilisation plus élevée I/O et utilisation du processeur sur l'éditeur.** Les écritures WAL supplémentaires consomment davantage de I/O bande passante disque et de cycles CPU, en particulier pour les charges de travail intensives en écriture.
+ **Davantage de données envoyées aux abonnés.** L'éditeur doit transmettre des enregistrements WAL plus volumineux sur le réseau à chaque abonné, ce qui augmente la consommation de bande passante.
### Recherches de lignes lentes sur l'abonné
Lorsque l'abonné reçoit un enregistrement `UPDATE` ou un `DELETE` journal, il doit trouver la ligne correspondante dans sa copie locale de la table. Avec`REPLICA IDENTITY FULL`, l'abonné recherche une ligne qui correspond à *toutes les* valeurs de colonne de l'ancienne image de ligne.
La manière dont PostgreSQL effectue cette recherche varie selon la version majeure de PostgreSQL :
+ **Avant PostgreSQL 16** : si la table ne possède aucune clé primaire ni aucun index d'identité de réplique configuré de manière explicite, l'abonné effectue une analyse séquentielle de l'ensemble de la table pour chaque opération ou opération. `UPDATE` `DELETE` Sur de grandes tables, cela ralentit considérablement les performances d'application.
+ **PostgreSQL 16 et versions ultérieures** : l'abonné peut utiliser un btree ou un index de hachage pour les recherches de lignes, même si cet index n'est pas explicitement défini comme identité de réplique. Cependant, l'abonné n'évalue pas quel indice est le plus efficace. [À partir de la version 16](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=89e46da5e), PostgreSQL sélectionne [le premier index approprié](https://www.postgresql.org/docs/18/logical-replication-publication.html#LOGICAL-REPLICATION-PUBLICATION-REPLICA-IDENTITY) qu'il trouve, et l'utilisateur n'a aucun contrôle sur ce choix. Si l'index sélectionné présente une faible sélectivité (par exemple, un index sur une colonne booléenne ou une colonne d'état), la recherche de lignes peut être presque aussi lente qu'un scan séquentiel. Pour cette raison, le fait de s'appuyer sur une sélection d'index implicite avec `REPLICA IDENTITY FULL` n'est pas fiable et doit être considéré comme une solution de rechange, et non comme une configuration recommandée.
### Comment REPLICA IDENTITY FULL provoque un retard de réplication
Les deux problèmes décrits ci-dessus (WAL plus volumineux sur l'éditeur et recherches de lignes plus lentes sur l'abonné) se combinent pour provoquer un retard de réplication.
Par défaut, la réplication logique PostgreSQL utilise un *seul processus d'application* par abonnement pour recevoir les modifications de l'éditeur et les appliquer aux tables des abonnés. Le processus d'application traite les modifications en série, une ligne à la fois, dans l'ordre de validation. Cela signifie que le débit de l'abonné est limité par la rapidité avec laquelle il peut appliquer chaque modification individuelle.
Lorsqu'il `REPLICA IDENTITY FULL` est défini sur une table sans index approprié, chaque `UPDATE` ligne `DELETE` nécessite un scan séquentiel de l'ensemble de la table pour trouver la ligne correspondante. Si le tableau comporte des millions de lignes, chacune de ces opérations peut prendre quelques secondes ou plus. Il en résulte un problème en cascade :
1. **L'éditeur génère les modifications plus rapidement que l'abonné ne peut les appliquer.** La charge de travail d'écriture de l'éditeur se poursuit à un rythme normal, mais le préposé aux candidatures de l'abonné est bloqué en raison de scans séquentiels ou d'index mal sélectifs à chaque recherche de ligne.
1. **Le WAL s'accumule sur l'éditeur et peut épuiser l'espace de stockage.** PostgreSQL ne peut pas récupérer les segments WAL tant que l'abonné ne confirme pas qu'il les a appliqués. Au fur et à mesure que l'abonné prend du retard, l'éditeur accumule du WAL sur disque. Sur RDS pour PostgreSQL, cela semble augmenter. `OldestReplicationSlotLag` CloudWatch Dans les cas graves, cela peut consommer tout l'espace de stockage disponible et empêcher l'éditeur d'accepter des publications.
1. **Le décalage se renforce d'lui-même.** Au fur et à mesure que l'abonné prend du retard, le tableau de l'abonné continue de s'allonger à cause des encarts répliqués, ce qui ralentit encore davantage chaque analyse séquentielle. Sans intervention, le décalage augmente sans limite.
Ce problème est particulièrement grave pour les tables qui reçoivent fréquemment des `DELETE` opérations `UPDATE` ou des opérations. `INSERT`les opérations ne sont pas affectées car elles ne nécessitent pas de recherche de ligne sur l'abonné.
**Note**
À partir de PostgreSQL 16, le gestionnaire d'application peut utiliser l'application parallèle pour les transactions de streaming volumineuses, ce qui peut améliorer le débit. Cependant, le principal obstacle à la recherche de lignes en cas d'`REPLICA IDENTITY FULL`absence d'index demeure, car chaque ligne doit encore être scannée pour être localisée.
### Impact sur les blue/green déploiements
Blue/green les déploiements dans Amazon RDS utilisent la réplication logique en interne pour maintenir l'environnement écologique synchronisé avec l'environnement bleu en configurant un seul abonnement par base de données. Le *processus d’application* de la réplication logique dans un environnement vert est à thread unique. Un processus d'application unique reçoit toutes les modifications de l'environnement bleu et les applique une par une, dans l'ordre de validation. Il n'y a aucune application parallèle dans le chemin blue/green de réplication.
Cette conception à fil unique signifie que la capacité de l'environnement écologique à suivre le rythme de l'environnement bleu dépend entièrement de la rapidité avec laquelle un agent d'application peut traiter chaque modification individuelle. Lorsque les tables sont utilisées `REPLICA IDENTITY FULL` sans clé primaire ni index approprié, l'impact sur le programme d'application dépend de la version de PostgreSQL. Dans les versions antérieures à la version 16, chaque table `UPDATE` et `DELETE` chacune de ces tables obligent le responsable de l'application à effectuer une analyse séquentielle de l'ensemble de la table pour trouver la ligne correspondante. Dans les versions 16 et ultérieures, PostgreSQL utilisera [un index approprié](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=89e46da5e) s'il est disponible, mais s'il n'existe aucun index de qualification, le processus d'application revient toujours à un scan séquentiel. Pendant que le gestionnaire d'application recherche une ligne dans un grand tableau, toutes les autres modifications en attente dans toutes les tables sont mises en file d'attente et attendent.
Les conséquences pour les blue/green déploiements sont importantes :
+ **Le délai de réplication augmente continuellement.** Si l'environnement bleu génère du trafic d'écriture plus rapidement que le seul responsable de l'application ne peut le traiter, l'environnement vert prend de plus en plus de retard. L'outil d'application étant monophasé, il n'existe aucun moyen de paralléliser le rattrapage.
+ **Le basculement peut être bloqué.** Un blue/green passage au numérique nécessite que l'environnement vert soit entièrement synchronisé avec l'environnement bleu. Si le délai de réplication est trop élevé, le basculement ne peut pas être effectué dans le délai imparti.
+ **L'environnement vert ne rattrapera peut-être jamais son retard.** Pour les charges de travail exigeantes en écriture impliquant l'utilisation de grandes tables `REPLICA IDENTITY FULL` et l'absence d'index, le taux d'application peut être si lent que l'environnement écologique prend définitivement du retard, rendant le passage au numérique impossible sans résolution préalable de la configuration de la réplique d'identité.
+ **Le WAL s'accumule dans l'environnement bleu.** Lorsque l'environnement vert est en retard, l'environnement bleu conserve les segments WAL pour le slot de réplication. Cela augmente l'utilisation du stockage dans l'environnement bleu (de production) et peut affecter les performances de production.
Pour éviter ces problèmes, assurez-vous que toutes les tables disposent d'une clé primaire ou d'un index unique approprié explicitement configuré comme identité de réplique `ALTER TABLE ... REPLICA IDENTITY USING INDEX` *avant* de créer un blue/green déploiement. Ne vous fiez pas `REPLICA IDENTITY FULL` à la sélection d'index implicite dans PostgreSQL 16\+, car l'abonné pourrait choisir un index peu sélectif ou recourir à des analyses séquentielles. Testez le déploiement avec une charge de travail d'écriture représentative pour confirmer que l'environnement écologique peut suivre le rythme.
Pour plus d'informations sur les limites de blue/green déploiement, consultez[Limites et considérations relatives aux déploiements Amazon RDS ( blue/green )](blue-green-deployments-considerations.md). Pour connaître les bonnes pratiques, consultez [Meilleures pratiques de RDS pour PostgreSQL pour les déploiements blue/green](blue-green-deployments-best-practices.md#blue-green-deployments-best-practices-postgres).
## Comment identifier les tables à l'aide de REPLICA IDENTITY FULL
Exécutez la requête suivante pour trouver toutes les tables contenant `REPLICA IDENTITY FULL` :
```
SELECT n.nspname AS schema, c.relname AS table_name, c.relreplident
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
AND c.relreplident = 'f'
AND n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY n.nspname, c.relname;
```
Les valeurs des `relreplident` colonnes sont les suivantes :
+ `d`— par défaut (clé primaire)
+ `n`— rien
+ `f`— complet (ligne entière)
+ `i`— un indice spécifique
## Solutions de contournement et meilleures pratiques
### Ajoutez une clé primaire dans la mesure du possible
La solution la plus efficace consiste à ajouter une clé primaire aux tables qui en sont dépourvues. Lorsqu'une clé primaire existe, PostgreSQL l'utilise comme identité de réplique par défaut, ce qui permet de consulter efficacement les lignes de l'abonné et de minimiser la surcharge WAL pour l'éditeur.
```
ALTER TABLE my_table ADD COLUMN id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY;
```
**Important**
Cette instruction est `ACCESS EXCLUSIVE` verrouillée et réécrit la table dans son intégralité, car l'expression de valeur par défaut utilise celle `nextval()` qui est volatile. Toutes les lectures et écritures sur la table sont bloquées pendant la durée de la réécriture. Sur les grandes tables, cela peut entraîner des temps d'arrêt importants. Planifiez cette modification au cours d'une période de maintenance ou envisagez d'autres approches, telles que la création d'abord de la colonne comme nullable, puis le remplissage et l'ajout de la contrainte par étapes distinctes.
Si l'ajout d'une clé primaire n'est pas possible en raison des contraintes de l'application, pensez à ajouter un index unique sur un ensemble de `NOT NULL` colonnes et à le définir comme identité de réplique :
```
CREATE UNIQUE INDEX my_table_replica_idx ON my_table (col1, col2);
ALTER TABLE my_table REPLICA IDENTITY USING INDEX my_table_replica_idx;
```
**Note**
Pour éviter de bloquer les écritures lors de la création de l'index, utilisez la [https://www.postgresql.org/docs/current/sql-createindex.html#SQL-CREATEINDEX-CONCURRENTLY](https://www.postgresql.org/docs/current/sql-createindex.html#SQL-CREATEINDEX-CONCURRENTLY)clause : `CREATE UNIQUE INDEX CONCURRENTLY my_table_replica_idx ON my_table (col1, col2);`
**Note**
L'index utilisé pour l'identité de la réplique doit être unique, ne doit pas être partiel, ne doit pas être différable et ne doit inclure que des colonnes soumises à des `NOT NULL` contraintes.
### Ne vous fiez pas à la sélection d'index implicite (PostgreSQL 16\+)
À partir de PostgreSQL 16, le serveur d'application de l'abonné peut utiliser des index btree ou de hachage pour les recherches de lignes lorsque l'identité de la réplique est définie sur, même si ces index ne sont pas `FULL` explicitement configurés en tant qu'identité de réplique. Bien que cela empêche les scans séquentiels dans certains cas, le fait de s'appuyer sur ce comportement implicite constitue un contre-modèle pour les raisons suivantes :
+ **Vous ne contrôlez pas l'index choisi.** PostgreSQL sélectionne le premier index de qualification qu'il trouve dans l'ordre du catalogue, et non le plus sélectif ou le plus efficace. Si la table comporte plusieurs index de qualification, celui choisi peut être peu sélectif, ce qui entraîne de mauvaises performances de recherche.
+ **Le comportement est fragile.** L'ajout, la suppression ou la reconstruction d'index peuvent modifier l'index utilisé par l'utilisateur chargé de l'application, ce qui peut entraîner des régressions de performances inattendues lors de la réplication.
+ **Cela masque le problème sous-jacent.** Les tables dépourvues de clé primaire ou d'identité de réplique explicite présentent des risques inhérents à la réplication logique. Le fait de s'appuyer sur une sélection d'index implicite reporte le problème au lieu de le résoudre.
Configurez plutôt explicitement l'identité de la réplique pour chaque table répliquée :
+ **Meilleure option :** ajoutez une clé primaire. Il s'agit de la réplique d'identité la plus fiable et la plus efficace.
+ **Alternative :** `ALTER TABLE ... REPLICA IDENTITY USING INDEX` à utiliser pour désigner un index unique, non partiel et non différable spécifique avec uniquement des colonnes. `NOT NULL` Cela vous donne un contrôle explicite sur les colonnes utilisées pour l'identification des lignes.
Réservez `REPLICA IDENTITY FULL` uniquement pour les tables où aucune des deux options n'est réalisable, et comprenez que les performances dépendent de facteurs indépendants de votre volonté.
### Surveiller le retard de réplication
Lors de l'utilisation`REPLICA IDENTITY FULL`, surveillez de près le délai de réplication pour détecter les ralentissements appliqués par les abonnés avant qu'ils ne deviennent critiques.
**Sur l'éditeur**, vérifiez le décalage entre la position actuelle du WAL et ce que l'abonné a confirmé :
```
SELECT slot_name, confirmed_flush_lsn, pg_current_wal_lsn(),
(pg_current_wal_lsn() - confirmed_flush_lsn) AS lag_bytes
FROM pg_replication_slots
WHERE slot_type = 'logical';
```
Une `lag_bytes` valeur en constante augmentation indique que l'abonné est en retard. La `pg_stat_replication_slots` vue fournit des statistiques supplémentaires sur l'utilisation de chaque emplacement de réplication.
**En ce qui concerne l'abonné**, la `pg_stat_subscription` vue montre l'état de chaque candidat, y compris le dernier emplacement WAL reçu et signalé :
```
SELECT subname, received_lsn, latest_end_lsn,
last_msg_send_time, last_msg_receipt_time
FROM pg_stat_subscription;
```
**Note**
Dans PostgreSQL 16 et versions ultérieures, vous pouvez également `worker_type` choisir de faire la distinction entre le programme d'application principal et le programme d'application parallèle.
Un écart important entre `received_lsn` et`latest_end_lsn`, ou des horodatages périmés`last_msg_send_time`, peuvent indiquer que le postulant a du mal à suivre le rythme. La `pg_stat_subscription_stats` vue suit également les erreurs d'application et les conflits susceptibles de contribuer au décalage.
**Pour RDS pour PostgreSQL**, vous pouvez également surveiller la métrique, qui permet de suivre le décalage en octets `OldestReplicationSlotLag` CloudWatch du slot de réplication le plus en retard. Une valeur en hausse est un signe avant-coureur d'un retard de réplication.
**Vérification des tables susceptibles d'utiliser un index sous-optimal lors de l'application**
Sur l'abonné, vous pouvez identifier les tables dans lesquelles le gestionnaire d'application effectue un nombre excessif de lectures par tas, ce qui peut indiquer que la table ne dispose pas d'un index efficace pour les recherches de lignes lors de l'application. Exécutez la requête suivante sur l'abonné :
```
SELECT relname, heap_blks_read, heap_blks_hit,
idx_blks_read, idx_blks_hit,
heap_blks_read + heap_blks_hit AS total_heap_access
FROM pg_statio_user_tables
WHERE heap_blks_read > 0
ORDER BY heap_blks_read DESC
LIMIT 10;
```
Un tableau dont `heap_blks_read` la valeur relative est élevée `idx_blks_read` peut indiquer que le postulateur n'utilise pas un index efficace pour localiser les lignes `UPDATE` et les `DELETE` opérations. Il s'agit d'une source courante de retard de réplication en cours d'utilisation. `REPLICA IDENTITY FULL`
**Note**
Cette requête nécessite que le [https://www.postgresql.org/docs/current/runtime-config-statistics.html#GUC-TRACK-COUNTS](https://www.postgresql.org/docs/current/runtime-config-statistics.html#GUC-TRACK-COUNTS)paramètre soit activé sur l'abonné. Ce paramètre est activé par défaut.
### Évaluez si REPLICA IDENTITY FULL est nécessaire
Avant de procéder au réglage`REPLICA IDENTITY FULL`, demandez-vous si vous en avez vraiment besoin. Les raisons courantes de son utilisation incluent :
+ La table ne possède pas de clé primaire ni d'index unique.
+ Vous avez besoin de l'image avant complète des lignes pour les consommateurs de capture de données modifiées (CDC).
+ Les valeurs des colonnes Toasted doivent être incluses dans les événements de réplication pour les mises à jour qui ne modifient pas ces colonnes.
Si votre seule raison est l'absence de clé primaire, il est presque toujours préférable d'en ajouter une. Si vous avez besoin d'images avant complètes pour le CDC, demandez-vous si votre client CDC peut reconstruire des lignes complètes en conservant l'état en externe, ce qui évite le WAL et la surcharge d'abonnés de. `REPLICA IDENTITY FULL`
## Résumé des recommandations
| Scénario | Recommendation |
| --- | --- |
| La table possède une clé primaire | Utiliser l'identité de réplique par défaut (aucune action n'est requise) |
| La table possède un index NOT NULL unique | Définissez cet index comme identité de réplique avec ALTER TABLE ... REPLICA IDENTITY USING INDEX |
| La table n'a pas de clé appropriée (PostgreSQL 16\+) | Ajoutez une clé primaire ou un index unique. L'utilisation REPLICA IDENTITY FULL avec une sélection d'index implicite n'est pas fiable et ne doit être utilisée qu'en dernier recours |
| La table n'a pas de clé appropriée (avant PostgreSQL 16) | Ajoutez une clé primaire ou un index unique ; évitez REPLICA IDENTITY FULL si possible |
| Write-heavy charge de travail avec large/TOASTed colonnes | À éviter REPLICA IDENTITY FULL en raison de l'amplification du volume WAL |