TOAST a été introduit dans PostgreSQL pour gérer les données de ligne dépassant la taille de page de 8 Ko en compressant les grandes colonnes dans un stockage physique séparé. Lorsque la réplication logique diffuse des modifications via le WAL, le paramètre REPLICA IDENTITY détermine quelles anciennes valeurs de tuple sont incluses. Le REPLICA IDENTITY DEFAULT par défaut n'envoie que la clé primaire, tandis que REPLICA IDENTITY FULL envoie l'image complète de l'ancienne ligne.
Lorsqu'une table contient des colonnes JSONB ou TEXT qui dépassent ~2 Ko et sont compressées dans TOAST, les opérations de MISE À JOUR qui modifient uniquement des colonnes non-TOAST peuvent ne pas récupérer les valeurs externes TOAST pour l’enregistrement WAL. Le processus de décodage logique omet les pointeurs TOAST inchangés pour réduire les E/S, ce qui amène le subscriber à recevoir des valeurs NULL ou manquantes pour ces grands champs lors de la résolution de conflits.
Le passage à REPLICA IDENTITY FULL oblige PostgreSQL à inclure le tuple ancien complet dans l’enregistrement WAL, récupérant explicitement toutes les valeurs TOAST depuis le stockage externe lors de l’engagement. Bien que cela garantisse l'intégralité des données pour les opérations UPSERT, cela augmente de manière significative le volume WAL—souvent de 300 à 500 % pour les grandes tables JSONB—car chaque MISE À JOUR doit enregistrer l'image préliminaire complète de la ligne.
Une plateforme de trading financier devait répliquer des instantanés du carnet de commandes d'un cluster PostgreSQL 15 principal vers un entrepôt de données pour des rapports réglementaires. La table market_data stockait des identifiants d'instruments et de grandes charges utiles JSONB (10-50 Ko) contenant des informations de profondeur de carnet. La réplication utilisait pglogical avec REPLICA IDENTITY DEFAULT (seulement la clé primaire). Le processus ETL côté entrepôt tentait d'effectuer des opérations UPSERT pour maintenir une table de dimension à évolution lente, nécessitant les anciennes valeurs JSONB pour calculer les changements delta pour le journal d'audit.
Lors des périodes de trading à fort volume, lorsque le carnet de commandes se mettait à jour fréquemment mais que la charge utile JSONB restait inchangée, le flux de réplication logique envoyait des enregistrements de MISE À JOUR contenant uniquement la clé primaire et les nouvelles données du tuple. Les anciennes valeurs JSONB TOAST n'étaient pas incluses dans l'ensemble des modifications car la déclaration MISE À JOUR touchait uniquement la colonne timestamp updated_at. Le processus ETL ne pouvait pas accéder à l'état JSONB avant la mise à jour, rendant impossible le calcul de deltas de mouvement de prix précis pour la trace d'audit, violant les exigences de conformité MiFID II.
Solution 1 : Passer à REPLICA IDENTITY FULL Cette approche obligerait le publisher à écrire l'image complète de l'ancienne ligne dans le WAL pour chaque MISE À JOUR, y compris tout le contenu JSONB depuis le stockage TOAST. Les avantages comprenaient l’intégrité des données garantie et une mise en œuvre simple ne nécessitant aucun changement de schéma. Cependant, les inconvénients étaient significatifs : la génération de WAL augmenterait d’environ 400 % compte tenu des charges utiles de 50 Ko, risquant l’épuisement de l’espace disque sur le principal et augmentant la latence réseau vers l’entrepôt. Pour une table traitant 10 000 mises à jour par seconde, cela était jugé trop risqué pour la stabilité de production.
Solution 2 : Journalisation au niveau de l'application avec une table d'historique séparée
L'équipe a envisagé de créer un déclencheur sur le principal qui copiait les anciennes valeurs JSONB dans une table séparée market_data_history avant la mise à jour. Les avantages étaient que la réplication logique pouvait répliquer cette table d'historique séparément, évitant le problème d'omission TOAST de la table principale tout en maintenant la charge WAL de la table principale dans de petites proportions. Les inconvénients incluaient une surcharge d'écriture double sur le principal (augmentant la latence des transactions), des exigences de stockage supplémentaires croissant à un rythme double et une complexité dans la logique ETL pour faire correspondre les enregistrements d'historique avec les modifications de la table principale à l'aide d’identifiants de transaction et d’horodatages.
Solution 3 : Utilisation de REPLICA IDENTITY avec un index couvrant incluant un hachage du JSONB
Cette stratégie consistait à créer un index fonctionnel sur md5(jsonb_column::text) et à inclure ce hachage dans un index composite REPLICA IDENTITY. Les avantages étaient que les changements dans le contenu JSONB seraient détectables via le changement de hachage dans le WAL sans envoyer la charge utile complète. Les inconvénients incluaient l'incapacité de récupérer la valeur ancienne réelle (seulement son hachage), ce qui était insuffisant pour la réglementation d'exiger de montrer l'état exact avant la mise à jour, et la surcharge de maintenance de l'index sur les tables à forte rotation.
L'équipe a choisi Solution 2 (Journalisation au niveau de l'application) mais avec une modification. Ils ont utilisé l'optimisation de mise à jour partielle JSONB de PostgreSQL disponible dans la version 14+ et ont mis en œuvre un déclencheur BEFORE UPDATE qui ne stockait que les chemins modifiés (diff) plutôt que la ligne complète. Cela a réduit la croissance de la table d'historique tout en garantissant que toutes les données nécessaires à l'image préliminaire étaient disponibles. Ils ont conservé REPLICA IDENTITY DEFAULT sur la table principale pour éviter le gonflement du WAL, dirigeant l’ETL à se joindre contre la table d'historique pour la reconstruction de l'audit.
La taille du flux de réplication est restée stable, empêchant la pression sur le stockage principal. Le processus ETL a réussi à reconstruire des pistes d'audit complètes en fusionnant l'état actuel de la ligne avec les diffs stockés de la table d'historique. La conformité réglementaire a été atteinte avec seulement une augmentation de 15 % de l’espace de stockage principal (contre 400 % pour REPLICA IDENTITY FULL) et un impact minimal sur le débit des transactions.
Pourquoi le décodage logique de PostgreSQL omet-il les valeurs TOAST même lorsque la colonne est modifiée ?
De nombreux candidats supposent que toute MISE À JOUR récupère automatiquement toutes les valeurs TOAST pour le WAL. Cependant, PostgreSQL ne réalise "l'unTOASTing de tuple" que lorsque l'exécuteur lit effectivement le datagramme pour le modifier. Si une MISE À JOUR modifie une autre colonne (par exemple, SET updated_at = NOW()) sans référencer la colonne JSONB dans sa liste cible ou clause WHERE, le pointeur TOAST reste inchangé et le stockage externe n'est pas accédé. L’enregistrement WAL contient donc uniquement le tuple sur disque avec son pointeur, pas les données réelles. Comme le décodage logique reconstruit des tuples à partir du WAL sans accéder aux tables heap ou TOAST pour d'anciennes versions, la valeur omise apparaît comme NULL dans le flux de modifications.
Comment REPLICA IDENTITY FULL interagit-elle avec les mises à jour HOT (Heap-Only Tuple) ?
Les candidats oublient souvent que REPLICA IDENTITY FULL désactive les mises à jour HOT pour une table. Les mises à jour HOT permettent à PostgreSQL d’enchaîner les versions de lignes au sein de la même page de données sans mettre à jour chaque entrée d'index, à condition qu'aucune colonne indexée ne change. Lorsque REPLICA IDENTITY FULL est actif, chaque MISE À JOUR doit enregistrer l'ancienne image de la ligne entière pour la réplication, ce qui nécessite au système d'identifier la ligne de manière unique par son contenu complet. Cela rompt l'optimisation HOT car la réplication logique nécessite des données de comparaison de tuple complètes, obligeant les mises à jour d’index pour chaque version de ligne même lorsque des colonnes non indexées sont modifiées. En conséquence, les tables avec ce paramètre connaissent une augmentation de l’encombrement des index et une augmentation des E/S, un compromis critique pour les tables à forte rotation.
Quelle est la différence entre la compression TOAST et la compression WAL de PostgreSQL, et comment interagissent-elles lors de la réplication logique ?
Cette question sépare la connaissance profonde des systèmes d'une compréhension superficielle. La compression TOAST réduit la taille des lignes en utilisant LZ4 ou PGLZ avant de stocker de grandes colonnes dans des tables externes. La compression WAL (activée via wal_compression=lz4) compresse les images des pages complètes écrites dans le WAL pour l'efficacité lors de la récupération après un crash. Cependant, lorsque REPLICA IDENTITY FULL est utilisé, les anciennes données de tuple envoyées au décodage logique sont extraites avant que l'enregistrement WAL ne soit compressé pour le stockage. Par conséquent, le décodeur logique reçoit des données TOAST non compressées (si récupérées), tandis que le fichier WAL physique pourrait les stocker compressées si cela fait partie d'une image de page complète, affectant ainsi différemment la bande passante réseau par rapport aux E/S disque.