TOAST è stato introdotto in PostgreSQL per gestire i dati delle righe che superano la dimensione della pagina di 8KB comprimendo colonne grandi in uno storage fisico separato. Quando la replica logica trasmette modifiche tramite il WAL, l'impostazione REPLICA IDENTITY determina quali valori di tuple precedenti sono inclusi. La REPLICA IDENTITY DEFAULT invia solo la chiave primaria, mentre REPLICA IDENTITY FULL invia l'intera immagine della riga precedente.
Quando una tabella contiene colonne JSONB o TEXT che superano ~2KB e sono compresse in TOAST, le operazioni di UPDATE che modificano solo colonne non TOAST potrebbero non recuperare i valori esterni TOAST per il record WAL. Il processo di decodifica logica salta i puntatori TOAST invariati per ridurre l'I/O, causando al subscriber di ricevere valori NULL o mancanti per questi campi grandi durante la risoluzione dei conflitti.
Passare a REPLICA IDENTITY FULL costringe PostgreSQL a includere l'intera vecchia tupla nel record WAL, recuperando esplicitamente tutti i valori TOAST dal storage esterno durante l'impegno. Sebbene questo garantisca la completezza dei dati per le operazioni di UPSERT, aumenta significativamente il volume WAL—spesso del 300-500% per tabelle JSONB ampie—perché ogni UPDATE deve registrare l'intera pre-immagine della riga.
Una piattaforma di trading finanziario aveva bisogno di replicare istantanee dell'order book da un cluster primario di PostgreSQL 15 a un data warehouse per la reportistica regolamentare. La tabella market_data memorizzava identificatori di strumenti e grandi payload JSONB (10-50KB) contenenti informazioni sul depth-of-book. La replica utilizzava pglogical con REPLICA IDENTITY DEFAULT (solo chiave primaria). Il processo ETL sul lato warehouse tentava di eseguire operazioni di UPSERT per mantenere una tabella dimensionale a lenta variazione, richiedendo i vecchi valori JSONB per calcolare le variazioni delta per il registro audit.
Durante periodi di trading ad alto volume, quando l'order book veniva aggiornato frequentemente, ma il payload JSONB rimaneva invariato, lo stream di replica logica inviava record di UPDATE contenenti solo la chiave primaria e i nuovi dati della tupla. I valori JSONB vecchi tostate non erano inclusi nel set di modifiche perché il comando di UPDATE toccava solo la colonna updated_at del timestamp. Il processo ETL non poteva accedere allo stato JSONB precedente all'aggiornamento, rendendo impossibile calcolare le esatte variazioni di prezzo per il registro audit, violando i requisiti di conformità a MiFID II.
Soluzione 1: Passare a REPLICA IDENTITY FULL Questo approccio costringerebbe il publisher a scrivere l'intera vecchia immagine della riga nel WAL per ogni UPDATE, inclusa l'intera contenuto JSONB dallo storage TOAST. I pro includevano la garanzia di completezza dei dati e un'implementazione semplice che non richiedeva modifiche allo schema. Tuttavia, i contro erano significativi: la generazione di WAL aumenterebbe di circa il 400% data la dimensione dei payload di 50KB, rischiando l'esaurimento dello spazio su disco sul primario e aumentando la latenza di rete verso il warehouse. Per una tabella che elaborava 10.000 aggiornamenti al secondo, questo era considerato troppo rischioso per la stabilità della produzione.
Soluzione 2: Journaling a livello applicativo con una tabella di storia separata
Il team ha considerato di creare un trigger sul primario che copiasse i vecchi valori JSONB in una separata tabella market_data_history prima dell'aggiornamento. I pro erano che la replica logica potesse replicare questa tabella di storia separatamente, evitando il problema dell'omissione di TOAST nella tabella principale, mantenendo piccola la footprint del WAL della tabella principale. I contro includevano un sovraccarico di scrittura doppia sul primario (aumentando la latenza delle transazioni), requisiti di storage aggiuntivi in crescita a un tasso di 2x, e complessità nella logica ETL per correlare i record storici con le modifiche della tabella principale utilizzando ID di transazione e timestamp.
Soluzione 3: Utilizzo di REPLICA IDENTITY con un indice di copertura che include un hash del JSONB
Questa strategia prevedeva la creazione di un indice funzionale su md5(jsonb_column::text) e l'inclusione di quell'hash in un indice REPLICA IDENTITY composito. I pro erano che le modifiche al contenuto JSONB sarebbero state rilevabili tramite la modifica dell'hash nel WAL senza inviare l'intero payload. I contro includevano l'impossibilità di recuperare il valore vecchio reale (solo il suo hash), che era insufficiente per il requisito normativo di mostrare l'esatto stato precedente all'aggiornamento, e il sovraccarico di manutenzione dell'indice su tabelle ad alta rotazione.
Il team ha scelto Soluzione 2 (Journaling a livello applicativo) ma con una modifica. Hanno utilizzato l'ottimizzazione dell'aggiornamento parziale JSONB di PostgreSQL disponibile nella versione 14+ e hanno implementato un trigger BEFORE UPDATE che archiviava solo i percorsi modificati (diff) piuttosto che l'intera vecchia riga. Questo ha ridotto la crescita della tabella di storia garantendo che tutti i dati necessari della pre-immagine fossero disponibili. Hanno mantenuto REPLICA IDENTITY DEFAULT sulla tabella principale per evitare l'ingrossamento del WAL, dirigendo l'ETL a unirsi contro la tabella di storia per la ricostruzione audit.
La dimensione dello stream di replica è rimasta stabile, evitando la pressione sullo storage primario. Il processo ETL ha ricostruito con successo percorsi audit completi unendo lo stato attuale della riga con i diffs archiviati dalla tabella di storia. La conformità regolamentare è stata raggiunta con solo un aumento del 15% nello storage primario (rispetto al 400% per REPLICA IDENTITY FULL) e un impatto minimo sul throughput delle transazioni.
Perché la decodifica logica di PostgreSQL omette i valori TOAST anche quando la colonna è modificata?
Molti candidati assumono che qualsiasi UPDATE recuperi automaticamente tutti i valori TOAST per il WAL. Tuttavia, PostgreSQL esegue "unTOASTing delle tuple" solo quando l'esecutore legge effettivamente il dato da modificare. Se un UPDATE modifica una colonna diversa (ad es., SET updated_at = NOW()) senza fare riferimento alla colonna JSONB nella sua lista di target o nella clausola WHERE, il puntatore TOAST rimane invariato e lo storage esterno non viene accesso. Di conseguenza, il record WAL contiene solo la tupla su disco con il suo puntatore, non i dati reali. Poiché la decodifica logica ricostruisce le tuple dal WAL senza accedere a heap o tabelle TOAST per le versioni vecchie, il valore omesso appare come NULL nello stream di cambiamento.
Come interagisce REPLICA IDENTITY FULL con gli aggiornamenti HOT (Heap-Only Tuple)?
I candidati spesso perdono che REPLICA IDENTITY FULL disabilita gli aggiornamenti HOT per una tabella. Gli aggiornamenti HOT consentono a PostgreSQL di concatenare versioni di riga all'interno della stessa pagina di dati senza aggiornare ogni voce dell'indice, a condizione che nessuna colonna indicizzata cambi. Quando REPLICA IDENTITY FULL è attivo, ogni UPDATE deve registrare l'intera immagine della vecchia riga per la replica, il che richiede al sistema di identificare univocamente la riga in base al suo contenuto completo. Questo rompe l'ottimizzazione HOT perché la replica logica necessita di dati completi di confronto delle tuple, costringendo gli aggiornamenti degli indici per ogni versione di riga anche quando si modificano colonne non indicizzate. Di conseguenza, le tabelle con questa impostazione sperimentano un aumento dell'ingrossamento degli indici e un maggiore I/O, un compromesso critico per le tabelle ad alta rotazione.
Qual è la differenza tra la compressione TOAST e la compressione WAL di PostgreSQL, e come interagiscono durante la replica logica?
Questa domanda separa le conoscenze approfondite sui sistemi da una comprensione superficiale. La compressione TOAST riduce la dimensione della riga utilizzando LZ4 o PGLZ prima di memorizzare colonne grandi in tabelle esterne. La compressione WAL (abilitata tramite wal_compression=lz4) comprime le immagini complete della pagina scritte nel WAL per l'efficienza del recupero in caso di crash. Tuttavia, quando si utilizza REPLICA IDENTITY FULL, i dati della tupla vecchia inviati alla decodifica logica vengono estratti prima che il record WAL venga compresso per lo storage. Di conseguenza, il decodificatore logico riceve dati TOAST non compressi (se recuperati), mentre il file WAL fisico potrebbe memorizzarlo compresso se fa parte di un'immagine di pagina completa, influenzando in modo diverso la larghezza di banda della rete rispetto all'I/O del disco.