TOAST is geïntroduceerd in PostgreSQL om rijgegevens die de paginagrootte van 8KB overschrijden te verwerken door grote kolommen in aparte fysieke opslag te comprimeren. Wanneer logische replicatie wijzigingen via de WAL streamt, bepaalt de instelling REPLICA IDENTITY welke oude tuplewaarden zijn inbegrepen. De standaard REPLICA IDENTITY DEFAULT verzendt alleen de primaire sleutel, terwijl REPLICA IDENTITY FULL de complete oude rijafbeelding verzendt.
Wanneer een tabel JSONB of TEXT kolommen bevat die groter zijn dan ~2KB en worden gecomprimeerd in TOAST, kunnen UPDATE-bewerkingen die alleen niet-TOAST-kolommen wijzigen, mogelijk de externe TOAST-waarden voor het WAL-record niet ophalen. Het logische decoderingsproces slaat onveranderde TOAST-pointers over om I/O te verminderen, waardoor de abonnee NULL of ontbrekende waarden voor deze grote velden ontvangt tijdens conflictoplossing.
Overschakelen naar REPLICA IDENTITY FULL dwingt PostgreSQL om de complete oude tuple op te nemen in het WAL-record, waarbij expliciet alle TOAST-waarden uit externe opslag worden opgehaald tijdens de committering. Hoewel dit datacompleetheid voor UPSERT-bewerkingen garandeert, verhoogt het het WAL-volume aanzienlijk—vaak met 300-500% voor brede JSONB-tabellen—omdat elke UPDATE de volledige pre-image van de rij moet loggen.
Een financiële handelsplatform moet orderboekmomentopnamen repliceren van een primaire PostgreSQL 15-cluster naar een datawarehouse voor regelgevingsrapportage. De market_data-tabel bevatte instrumentidentifiers en grote JSONB-ladingen (10-50KB) die informatie over de diepte van het boek bevatten. De replicatie gebruikte pglogical met REPLICA IDENTITY DEFAULT (alleen primaire sleutel). Het ETL-proces aan de zijde van het warehouse probeerde UPSERT-bewerkingen uit te voeren om een langzaam veranderende dimensietabel bij te houden, waarvoor de oude JSONB-waarden nodig waren om delta wijzigingen voor het auditlogboek te berekenen.
Tijdens handelsperiodes met hoge volumes, wanneer het orderboek vaak werd bijgewerkt maar de JSONB-lading onveranderd bleef, verzond de logische replicatiestroom UPDATE-records met alleen de primaire sleutel en de nieuwe tuple-gegevens. De TOAST-oude waarden van JSONB waren niet opgenomen in de wijzigingsset omdat de UPDATE-verklaring alleen de updated_at-timestampkolom raakte. Het ETL-proces kon de pre-update JSONB-status niet bereiken, waardoor het onmogelijk was om precieze prijsbewegingdeltas voor het auditlogboek te berekenen, in strijd met de vereisten van MiFID II-compliance.
Oplossing 1: Overschakelen naar REPLICA IDENTITY FULL Deze benadering zou de uitgever dwingen om de complete oude rijafbeelding naar het WAL te schrijven voor elke UPDATE, inclusief de volledige JSONB-inhoud uit de TOAST-opslag. De voordelen omvatten gegarandeerde datacompleetheid en eenvoudige implementatie zonder schemawijzigingen. De nadelen waren echter aanzienlijk: de WAL-generatie zou met ongeveer 400% toenemen gezien de 50KB-ladingen, wat het risico op uitputting van schijfruimte op de primaire en verhoogde netwerklatentie naar het warehouse met zich meebracht. Voor een tabel die 10.000 updates per seconde verwerkt, werd dit als te riskant voor de productieomstandigheden beoordeeld.
Oplossing 2: Journaling op applicatieniveau met een aparte historietabel
Het team overwoog om een trigger op de primaire te maken die de oude JSONB-waarden in een aparte market_data_history-tabel kopieerde voordat de update plaatsvond. De voordelen waren dat logische replicatie deze historietabel apart kon repliceren, waardoor het probleem van de omissie van TOAST op de hoofdtabel werd vermeden terwijl de WAL-voetafdruk van de hoofdtabel klein bleef. De nadelen omvatten dubbele schrijf-overhead op de primaire (wat de transactietijd verlengde), aanvullende opslagvereisten die met 2x groeiden, en complexiteit in de ETL-logica om historische records te correleren met wijzigingen in de hoofdtabel met behulp van transactie-ID's en tijdstempels.
Oplossing 3: Gebruik maken van REPLICA IDENTITY met een dekkende index inclusief een hash van de JSONB
Deze strategie omvatte het creëren van een functionele index op md5(jsonb_column::text) en het opnemen van die hash in een samengestelde REPLICA IDENTITY-index. De voordelen waren dat wijzigingen in de JSONB-inhoud detecteerbaar zouden zijn via de hashwijziging in het WAL zonder de volledige lading te verzenden. De nadelen omvatten het onvermogen om de werkelijke oude waarde op te halen (alleen de hash), wat onvoldoende was voor de wettelijke vereiste om de exacte pre-update status te tonen, en de overhead van indexonderhoud op tabellen met een hoge omloopsnelheid.
Het team koos voor Oplossing 2 (Journaling op applicatieniveau), maar met een wijziging. Ze maakten gebruik van PostgreSQL's JSONB-optimalisatie voor gedeeltelijke updates die beschikbaar is in versie 14+ en implementeerden een BEFORE UPDATE-trigger die alleen de gewijzigde paden (diff) opsloeg in plaats van de volledige oude rij. Dit verminderde de groei van de historietabel terwijl ervoor werd gezorgd dat alle nodige pre-image-gegevens beschikbaar waren. Ze hielden REPLICA IDENTITY DEFAULT op de hoofdtabel om WAL-bloat te voorkomen, waarbij het ETL-proces zich richtte op het aansluiten bij de historietabel voor audit reconstructie.
De replicatiestroomgrootte bleef stabiel, waardoor druk op de primaire opslag werd voorkomen. Het ETL-proces slaagde erin om complete auditsporen te reconstrueren door de huidige rijstatus te combineren met de opgeslagen diffs uit de historietabel. Regelgevende naleving werd bereikt met slechts een stijging van 15% in de primaire opslag (tegenover 400% voor REPLICA IDENTITY FULL) en minimale impact op de transactiedoorvoer.
Waarom slaat de logische decodering van PostgreSQL TOAST-waarden over, ook al is de kolom gewijzigd?
Veel kandidaten nemen aan dat elke UPDATE automatisch alle TOAST-waarden ophaalt voor de WAL. Echter, PostgreSQL voert "tuple unTOASTing" alleen uit wanneer de executor de datum daadwerkelijk leest om deze te wijzigen. Als een UPDATE een andere kolom wijzigt (bijv. SET updated_at = NOW()) zonder de JSONB-kolom in de doellijst of de WHERE-clausule te vermelden, blijft de TOAST-pointer onveranderd en wordt de externe opslag niet benaderd. Het WAL-record bevat daarom alleen de on-disk tuple met zijn pointer, niet de werkelijke gegevens. Aangezien de logische decodering tuples reconstrueert uit de WAL zonder heap- of TOAST-tabellen voor oude versies te raadplegen, verschijnt de weggelaten waarde als NULL in de wijzigingsstroom.
Hoe interageert REPLICA IDENTITY FULL met HOT (Heap-Only Tuple) updates?
Kandidaten missen vaak dat REPLICA IDENTITY FULL HOT-updates voor een tabel uitschakelt. HOT-updates stellen PostgreSQL in staat om rijversies binnen dezelfde gegevenspagina aan elkaar te koppelen zonder elke indexvermelding bij te werken, op voorwaarde dat er geen geïndexeerde kolom verandert. Wanneer REPLICA IDENTITY FULL actief is, moet elke UPDATE de gehele oude rijafbeelding voor replicatie loggen, wat vereist dat het systeem de rij uniek identificeert op basis van de volledige inhoud. Dit verstoort de HOT-optimalisatie omdat logische replicatie volledige tuplevergelijkingsgegevens nodig heeft, waardoor indexupdates voor elke rijversie nodig zijn, zelfs bij het wijzigen van niet-geïndexeerde kolommen. Dienovereenkomstig ervaren tabellen met deze instelling hogere indexbloat en verhoogde I/O, een kritieke afweging voor tabellen met een hoge omloopsnelheid.
Wat is het verschil tussen TOAST-compressie en WAL-compressie van PostgreSQL, en hoe interageren ze tijdens logische replicatie?
Deze vraag scheidt diepgaande systeemkennis van oppervlakkig begrip. TOAST-compressie reduceert de rijgrootte met behulp van LZ4- of PGLZ-compressie voordat grote kolommen in externe tabellen worden opgeslagen. WAL-compressie (geactiveerd via wal_compression=lz4) comprimeert volledige pagina-afbeeldingen die naar de WAL worden geschreven voor efficiëntie bij crashherstel. Echter, wanneer REPLICA IDENTITY FULL wordt gebruikt, worden de oude tuplegegevens die naar logische decodering worden verzonden, voordat het WAL-record wordt gecomprimeerd voor opslag. Daarom ontvangt de logische decoder niet-gecomprimeerde TOAST-gegevens (indien opgehaald), terwijl het fysieke WAL-bestand deze gecomprimeerd kan opslaan als het deel uitmaakt van een full-page afbeelding, wat de netwerkbandbreedte anders beïnvloedt in vergelijking met schijf-I/O.