So lässt sich Change Data Capture in Postgres umsetzen

• Um Postgres-Änderungen in Echtzeit an andere Systeme zu übertragen, wird CDC (Change Data Capture) benötigt. Je nach Ansatz reichen die Optionen von einfachen Benachrichtigungen bis zu WAL-basierter Replikation, mit großen Unterschieden bei Zuverlässigkeit und Betriebsaufwand.
• Listen/Notify ist der leichtgewichtigste Einstieg, eignet sich wegen at-most-once-Zustellung, flüchtiger Benachrichtigungen und der Payload-Grenze von 8000 Byte aber eher als ergänzendes Signal als für geschäftskritisches CDC.
• Tabellen-Polling und Audit-Tabellen (Outbox Pattern) lassen sich allein mit Standardtabellen und Triggern umsetzen, erfordern aber, dass Lösch-Erkennung, Diffing, Commit-Reihenfolge, Write Amplification und Backpressure selbst gelöst werden.
• Logische Replikation (logical replication) ist ein leistungsfähiger Ansatz, der insert/update/delete aus dem WAL streamt, verlangt von der Anwendung aber die Verwaltung von Replication Slots, Ack, Neustarts und dem Umgang mit hohem Durchsatz.
• Sequin basiert auf der logischen Replikation von Postgres und leitet Änderungen an SQS, Kafka, Elasticsearch, Redis, HTTP-Endpunkte und mehr weiter, wodurch der Aufwand für die direkte Arbeit mit Replication Slots sinkt.

---

Wann Postgres CDC gebraucht wird

• Postgres ist stark darin, gespeicherte Daten zu verwalten. Wenn jedoch Workflows durch Tabellenänderungen ausgelöst oder Daten in Echtzeit in andere Datenspeicher, Systeme oder Services gestreamt werden sollen, muss die Datenbewegung separat entworfen werden.
• Change Data Capture (CDC) ist ein Verfahren, bei dem Änderungen in der Datenbank identifiziert und erfasst und dann in Echtzeit an Downstream-Systeme weitergegeben werden.
• Es gibt mehrere Möglichkeiten, Änderungen in Postgres zu erfassen, und sie unterscheiden sich in Implementierungsaufwand, Zuverlässigkeit und Betriebsaufwand.

Listen/Notify: das einfachste Pub-Sub

• Listen/Notify in Postgres ist eine Funktion für die Kommunikation zwischen Prozessen und arbeitet nach dem Publish-Subscribe-Muster.
• Eine Session kann auf einem bestimmten Channel mit listen lauschen, und Datenbankaktivitäten oder andere Sessions können auf diesem Channel notify senden.
• Für Change Capture kann das mit Triggern kombiniert werden.
  • Ein Beispiel-Trigger erzeugt bei after insert or update or delete ein JSON mit table, id und action des geänderten Datensatzes und ruft pg_notify('table_changes', payload::text) auf.
• Die Grenzen sind klar.
  • Es hat eine at-most-once-Zustellsemantik, und der Listener muss zum Zeitpunkt der Benachrichtigung verbunden sein.
  • Ein Listener erhält nur Benachrichtigungen ab dem Zeitpunkt des Abonnements, sodass selbst kurze Netzwerkunterbrechungen dazu führen können, dass Signale verloren gehen.
  • Die Payload-Größe ist auf 8000 Byte begrenzt; wird sie überschritten, schlägt der notify-Befehl fehl.
  • Zur Payload-Größe zählt auch der Channel-Name, und wie bei Postgres-Identifiern kann dieser maximal 64 Byte lang sein.
• Für grundlegende Änderungs-Erkennung oder zur Optimierung von Tabellen-Polling ist das nützlich, für komplexere CDC-Anforderungen aber oft nicht passend.

Tabellen-Polling: einfach, aber schwach bei Löschungen und Diffs

• Der einfachste robuste Weg für Change Capture ist das direkte Polling einer Tabelle.
• Jede Tabelle braucht eine Spalte wie updated_at, die bei jeder Aktualisierung einer Zeile geändert wird; bei Bedarf lässt sich das per Trigger umsetzen.
• Die Kombination aus updated_at und id kann als Cursor dienen, wobei die Anwendungslogik den Cursor speichert und verwaltet.
• Mit zusätzlichem Notify-Subscription kann die Anwendung über eingefügte oder geänderte Datensätze informiert werden, um die Polling-Frequenz zu senken.
  • Da Postgres-Benachrichtigungen flüchtig sind, eignen sie sich hier nur als Optimierung über dem Polling.
• Es gibt drei wesentliche Nachteile.
  • Gelöschte Zeilen bleiben nicht in der Tabelle, daher ist Lösch-Erkennung nicht möglich.
  • Als Gegenmaßnahme kann ein Delete-Trigger id und nötige Spalten in einer separaten Tabelle wie deleted_contacts speichern, die die Anwendung dann pollt.
  • Es lässt sich erkennen, dass ein Datensatz aktualisiert wurde, aber nicht was sich geändert hat.
  • Da Datetime- und Sequence-Werte in Postgres nicht zwingend in Commit-Reihenfolge vorliegen, können beim Lesen eines Blocks anhand von updated_at noch nicht vollständig committete Zeilen übersehen werden.
• Für einfaches Change Tracking, bei dem Löschungen, Diffs und gelegentliche Auslassungen kein großes Problem sind, ist das eine vernünftige Wahl.

Audit-Tabelle: Änderungsprotokoll mit dem Outbox Pattern speichern

• Beim Ansatz mit Audit-Tabelle (audit table) werden Änderungen in einer separaten changelog-Tabelle gespeichert; das wird auch als Outbox Pattern bezeichnet.
• changelog kann Spalten enthalten, die die Änderung beschreiben.
  • action: ob es sich um insert, update oder delete handelt
  • old: das jsonb des Datensatzes vor der Änderung, bei insert leer
  • values: die geänderten Felder als jsonb, bei delete leer
  • inserted_at: der Zeitpunkt der Änderung
• Für die Umsetzung werden eine Trigger-Funktion benötigt, die bei jeder Änderung in changelog schreibt, sowie Trigger pro zu überwachender Tabelle.
• changelog kann auch wie eine Queue konsumiert werden.
  • Ein Application-Worker liest Änderungen aus der Tabelle.
  • Für eine grobe Exactly-Once-Verarbeitung lässt sich for update skip locked von Postgres verwenden.
  • Der Worker kann eine Transaktion öffnen, per order by timestamp limit 100 for update skip locked ein Batch sperren, es verarbeiten, die verarbeiteten Datensätze löschen und dann committen.
• Im Betrieb gibt es Nachteile.
  • Ein Schreibvorgang in einer einzelnen Tabelle erzeugt durch Write Amplification mehrere Schreibvorgänge in der Audit-Tabelle.
  • Typischerweise entstehen mindestens drei Writes: initiales Insert in die Audit-Tabelle, Update während der Verarbeitung und Delete nach Abschluss.
  • Eine Fan-out-Architektur über Worker muss passend zur Anwendung selbst entworfen werden.
  • Vor einem Deployment in Produktionsgröße müssen Trigger-Funktionen und Tabellendesign wahrscheinlich angepasst werden.
  • Auch Detailregeln wie Zeitlimits dafür, wie lange Worker Änderungen ausgecheckt halten dürfen, können relevant sein.
  • Wenn Worker Änderungen nicht erfolgreich verarbeiten, wächst die Audit-Tabelle weiter, sodass es an einem guten Backpressure-Management fehlt.

Foreign Data Wrapper: eher eine Option für Synchronisierung zwischen bestimmten Postgres-Systemen

• Foreign Data Wrapper (FDW) ist eine Funktion, mit der ein Postgres-System externe Datenquellen lesen und schreiben kann.
• Die am breitesten unterstützte FDW-Erweiterung ist postgres_fdw.
  • Damit lassen sich zwei Postgres-Datenbanken verbinden und Konstrukte erzeugen, die aus einer Datenbank heraus ähnlich wie Views auf Tabellen der anderen Datenbank wirken.
  • Intern agiert eine Postgres-Datenbank als Client und die andere als Server.
  • Bei einer Abfrage auf eine Foreign Table sendet die Client-Datenbank über das Postgres-Wire Protocol eine Anfrage an die Server-Datenbank.
• FDW ist als Change-Capture-Ansatz unüblich und außerhalb sehr spezieller Szenarien kaum zu empfehlen.
• Wenn Änderungen aus einer Postgres-Datenbank direkt in eine andere geschrieben werden sollen, kann FDW aber passen.
  • Ein Beispiel ist die Trennung zwischen einer Datenbank für Buchhaltung und einer für die Anwendung.
  • Statt einer zwischengeschalteten Change-Capture-Stufe kann mit postgres_fdw direkt zwischen Datenbanken gespiegelt werden.
• Es ist auch möglich, einen eigenen FDW zu schreiben, der Änderungen an eine interne API per POST sendet.
  • Da in den Commit hinein auf die API geschrieben wird, kann die API Änderungen ablehnen und den Commit zurückrollen.
• FDW ist leistungsfähig, aber selten die beste CDC-Lösung, und das Schreiben eines eigenen FDW gehört fast zu den aufwendigsten Varianten der Änderungs-Erfassung.
  • Tools wie Supabase wrappers machen das einfacher, bleiben aber ein großer Aufwand.

Direkte logische Replikation: leistungsstarkes CDC auf WAL-Basis

• Postgres verfügt über ein Protokoll für Datenbankreplikation, und eine seiner Formen ist die logische Replikation (logical replication).
• Die logische Replikation basiert auf dem WAL (write-ahead log) von Postgres.
  • Jedes insert, update und delete in der Datenbank wird erfasst.
  • Änderungen werden an Subscriber gestreamt.
• Zunächst wird auf dem Primary ein Replication Slot angelegt.
  • Verwendet wird die Form pg_create_logical_replication_slot('<your_slot_name>', '<output_plugin>').
• Mit output_plugin wird festgelegt, welches Plugin die WAL-Änderungen dekodiert.
  • pgoutput ist das Standard-Plugin und liefert Ausgaben in dem binären Format, das Client-Server erwarten.
  • test_decoding ist ein einfaches Output-Plugin, das WAL-Änderungen menschenlesbar ausgibt.
  • Nicht in Postgres eingebaut, aber beliebt, ist wal2json; JSON ist für Anwendungen oft leichter als Einstieg zu verarbeiten als das binäre Postgres-Format.
• Nach dem Anlegen des Replication Slots kann mit dem Konsumieren begonnen werden.
  • Ein Replication Slot nutzt einen Bereich des Postgres-Protokolls, der sich von normalen Abfragen unterscheidet.
  • Viele Client-Bibliotheken bieten Hilfsfunktionen für die Arbeit mit Replication Slots.
  • Im psycopg2-Beispiel werden WAL-Nachrichten mit cursor.start_replication(...) und cursor.consume_stream(...) konsumiert und Ack mit cursor.send_feedback(flush_lsn=msg.wal_end) gesendet.
• Der Client muss empfangene WAL-Nachrichten acknowledgen; der Replication Slot funktioniert ähnlich wie Kafka mit einem Offset.
• Die logische Replikation ist ein robustes Verfahren, das eigens für CDC entwickelt wurde, aber sie ist komplex.
  • Replication Slots und das Replikationsprotokoll sind Entwickler:innen meist weniger vertraut als normale Tabellen und Abfragen.
  • Es wird eine Strategie benötigt, um bei Neustarts keine Nachrichten zu verlieren.
  • Das System muss darauf ausgelegt sein, große Mengen von Nachrichten aus Postgres zu verarbeiten.

Sequin: ein CDC-Tool, das logische Replikation kapselt

• Sequin ist ein CDC-Tool, das Postgres-Änderungen und Zeilen an Queues, Streams, Suchindizes, Caches, HTTP-Endpunkte und mehr weiterleitet.
• Zu den Zielen gehören SQS, Kafka, Elasticsearch, Redis und HTTP endpoints.
• Sequin verwendet intern die logische Replikation von Postgres, abstrahiert aber die Komplexität des Low-Level-Protokolls.
• insert, update und delete lassen sich vollständig erfassen; bei update und delete werden sowohl new- als auch old-Werte der Zeile mitgeschnitten.
• Sequin ist besonders dann eine Überlegung wert:
  • wenn Echtzeit-CDC benötigt wird
  • wenn direkt an Ziele wie SQS oder Webhooks ohne Zwischensystem gestreamt werden soll
  • wenn Funktionen wie Backfill historischer Daten und Änderungssfilter auf Basis von SQL-where-Klauseln gebraucht werden
  • wenn eine einfachere Alternative zur direkten Verwaltung von Replication Slots gesucht wird
  • wenn Exactly-Once-Garantien erforderlich sind
• Es gibt aber auch Nachteile.
  • Sequin ist keine interne Erweiterung von Postgres, sondern ein Third-Party-Tool, das neben der Datenbank läuft.
  • Dadurch ist es breit mit verschiedenen Postgres-Datenbanken kompatibel, aber wenn nicht Sequin Cloud genutzt wird, muss zusätzliche Infrastruktur selbst betrieben werden.

Auswahlkriterien

• In der Anfangsphase eignen sich Listen/Notify und Tabellen-Polling.
  • Listen/Notify ist gut für das Erfassen unkritischer Events, Prototyping und die Optimierung von Polling.
  • Polling ist für einfache Anwendungsfälle eine solide und geradlinige Lösung.
• Auf der nächsten Stufe kann die Audit-Tabelle eine Zwischenlösung sein.
  • Payloads mit new und old einer Zeile lassen sich erfassen.
  • Wenn sauber umgesetzt, kann ein Exactly-Once-System erreicht werden.
  • Beim Skalieren werden Write Amplification und fehlendes Backpressure zum Problem, und bei manueller Umsetzung können leicht Nachrichten verloren gehen.
• Für Skalierung ist die logische Replikation die robusteste Lösung.
  • Empfohlen wird allerdings eher ein Tool wie Sequin als das direkte Lesen aus dem Slot.
• FDW ist eine interessante Funktion, löst aber typische CDC-Anforderungen eher selten.

[GN⁺]

Hacker-News-Kommentare

• Trigger + History-Tabellen (Audit-Tabellen) sind in 98 % der Fälle die richtige Antwort. Wenn man sie noch nicht nutzt, sollte man heute damit anfangen. Diese Technik ist seit über 30 Jahren bewährt.
  Ein einfaches Beispiel für eine generische Implementierung gibt es unter https://gist.github.com/slotrans/353952c4f383596e6fe8777db5d.... Dabei wird auf Platzeffizienz verzichtet und stattdessen eine „einfache Implementierung“ gewählt.
  Wenn man unveränderliche Daten speichern kann, ist das wirklich gut; aber in einer Datenbank gibt es vermutlich sehr viele veränderliche Daten, und wahrscheinlich vergisst man täglich vieles. Nicht vergessen: History-Tabellen verwenden.
  Siehe auch: https://github.com/matthiasn/talk-transcripts/blob/master/Hi...
  History-Tracking-Bibliotheken oder -Techniken auf Anwendungsebene wie Papertrail sollte man besser nicht verwenden. Sie sind langsam und fehleranfällig und erfassen keine DB-Änderungen, die am App-Stack vorbeigehen. Auch der Versuch, updated-Zeitstempel in der App zu setzen, ist grundsätzlich falsch, weil jeder Webserver eine andere Uhr hat. Man muss die DB-Uhr verwenden; sie ist die einzige richtige Uhr.

  • Aus Gründen der Konsistenz sollte man die Zeit nicht auf dem Client erzeugen, sondern die DB-Uhr verwenden, indem man Aufrufe wie now() in die Query einbaut.
    Dieser Zeitstempel allein reicht für die Synchronisierung jedoch nicht aus. Denn der Zeitstempel wird nicht zum Zeitpunkt des Transaktions-Commits erzeugt, sondern zum Startzeitpunkt der Transaktion.
    Wenn man eine Tabelle pollt und nach aktuellen Zeitstempeln filtert, kann man Teile von Transaktionen verpassen, deren Commit-Reihenfolge durcheinandergerät. Man kann zwar einen Pufferbereich einrichten, der einige Minuten weiter in die Vergangenheit zurückreicht, und Duplikate entfernen; in PostgreSQL ist die Dauer von Transaktionen aber unbegrenzt, und zu weit in die Vergangenheit zu schauen ist sehr verschwenderisch. Wenn Genauigkeit und Effizienz wichtig sind, ist dieser Ansatz nicht passend.
  • Estuary (https://estuary.dev, ich bin CTO) erstellt ohne zusätzliche Konfiguration der Produktions-DB ein Echtzeit-Data-Lake-Änderungslog sämtlicher Datenbankänderungen im Cloud Storage.
    Mit Log Sequence Number, DB-Zeit und REPLICA IDENTITY FULL sind sogar Vorher-/Nachher-Zustände von Änderungen enthalten. Wenn man anschließend Collections etwa in Snowflake materialisiert, erhält man standardmäßig synchronisierte Tabellen, die den Updates der Quell-DB folgen.
    Auf Basis desselben zugrunde liegenden Data Lake kann man außerdem die vollständige Tabellenhistorie für Audit-Zwecke transformieren oder materialisieren, ohne erneut Capture oder einen WAL-Reader an die Quell-DB anzuhängen.
  • Wenn man in Triggern Session-Variablen referenziert, konnte man zusätzliche Informationen wie Kommentare zum Änderungsgrund in die Historie aufnehmen. Ich habe das nur in einem kleinen privaten Projekt ausprobiert, aber bisher funktioniert es gut.
  • Ich habe das Beispiel nach SQLite portiert und die Funktionsweise demonstriert: https://chat.openai.com/share/b5113cb1-10df-4a38-adde-5ec0e7...
    Eine SQLite-Variante, die ein ähnliches Muster nicht mit JSON, sondern spaltenbasiert umsetzt, habe ich ebenfalls separat beschrieben: https://simonwillison.net/2023/Apr/15/sqlite-history/
  • Dieser Ansatz ist gut, und tatsächlich bauen wir auch den Activity Feed unserer App so. Allerdings löst er nicht das Problem, „Änderungen nach außen zu pushen“. Wenn man natürlich auf WAL-Änderungen der Audit-Tabelle hört, bekommt man beide Vorteile.

• Der Artikel fasst mehrere Ansätze, die mit den Bordmitteln von Postgres möglich sind, knapp und gut zusammen.
  Im Abschnitt „Änderungen in Audit-Tabellen erfassen“ haben wir bei meinem früheren Arbeitgeber das Temporal-Tables-Pattern erfolgreich eingesetzt. Anders als bei anderen wichtigen relationalen DBMS ist es in Postgres selbst nicht eingebaut, aber es gibt ein einfaches Pattern, das man per SQL-Funktion nutzen kann: https://github.com/nearform/temporal_tables
  Damit kann man den Zustand einer Tabelle zu einem bestimmten Zeitpunkt ansehen und Fragen beantworten wie: „Welche Einstellungen hatte dieser Nutzer am 12. August?“, „Wie viele unbearbeitete Records gab es gestern Abend um 23:55 Uhr?“ oder „Zeig mir den Unterschied zwischen den aktuellen Feature-Flags und denen von vor einer Woche.“

• Ich habe früher einmal bei einem Unternehmen mit einem sehr großen monolithischen SQL Server beraten. Es war zwar kein Postgres, aber selbst wenn es Postgres gewesen wäre, wäre es ähnlich gewesen.
  Über Jahrzehnte hinweg wurde er für alle möglichen Zwecke im Unternehmen genutzt, und faktisch speicherten alle Anwendungen und Geschäftsprozesse des gesamten Unternehmens ihre Daten in dieser Datenbank.
  Das Problem war, dass es viele Anwendungen gab, die diese DB abfragten, und enorm viele Prozesse und Prozeduren, die Daten einfügten oder änderten. Wenn sich vorgelagerte Einfüge- oder Änderungsprozesse änderten oder neu hinzukamen, wurden dadurch Invarianten auf Anwendungsebene verletzt. Auch normale Prozesse verhielten sich anders, wenn schlechte Daten vorhanden waren.
  Die Ursachen nachzuverfolgen war sehr schwierig, weil die Dinge, in die man hineinschaute, meist vor zehn Jahren geschrieben worden waren und die damaligen Mitarbeitenden das Unternehmen bereits verlassen hatten.
  Ich frage mich, ob man Änderungen in einer Postgres-Datenbank in irgendeiner Form als DAG erfassen könnte, sodass man erkennen kann, welcher Prozess Daten einfügt, ändert oder löscht und wie er sich historisch verhalten hat, außerdem wie mehrere Anwendungen diese Daten abfragen und wie sich Query-Statistiken im Zeitverlauf verändern.
  Ich weiß nicht recht, ob es dafür Vorbilder gibt oder mit welchem Ansatz man ein solches Tool bauen könnte. Ich hatte früher schon einmal überlegt, etwas Ähnliches zu bauen, aber es wirkt wie ein Bereich, in dem man für gute Entscheidungen ein Verständnis auf dem Niveau von Postgres-Core-Engineers braucht.

  • Die logische Replikation von Postgres enthält alle Änderungsanweisungen, also Informationen zu Inserts, Updates und Deletes, um denselben Zustand in einer anderen Datenbank logisch wiederherzustellen.
    Man bekommt nicht für jede Änderung Herkunftsdaten auf Client-Ebene.
    Es gibt aber Umwege. Der logische Replikationsstream kann auch Informationsmeldungen der Funktion pg_logical_emit_message enthalten, sodass der Client selbst Metadaten einfügen kann. Vielleicht lässt sich konfigurieren, dass zu Beginn jeder Transaktion eine Client-Kennung ausgegeben wird.
  • Ich weiß nicht, wie man Queries behandeln sollte, aber für Inserts und Updates gibt es Spalten, die die Event-Herkunft (last updated by) verfolgen. Das könnte ein Antipattern sein, daher wäre eine robustere Lösung wünschenswert.
  • Technisch enthält Log-Replikation alle Aktionen aller Akteure, und wenn man Trigger vorsichtig einsetzt, lässt sich auch mit einer DDL/DML-Erfassungstabelle alles nachverfolgen. Wenn einem DCL Sorgen macht, kann man das ebenfalls einbeziehen.
    Dieser Ansatz funktioniert bei fast allen SQL-artigen Lösungen, die WAL oder Trigger verwenden.
    In SQL Server habe ich den Trigger-Ansatz mehrfach eingesetzt, aber wenn man alle Queries loggt, wird es tendenziell langsam. Einen Einfügemechanismus zu entwerfen, der den Betrieb nicht blockiert, ist nicht perfekt, und Sampling kann nötig sein.
  • Schon allein, wenn jede Anwendung ihren eigenen DB-Benutzer hat, erhält man ziemlich viele Informationen.
  • Es gab die Idee, alle Skripte und Programme durchzugehen, die Queries an die DB schicken, und jeder Query einen eindeutigen ID-Kommentar hinzuzufügen, der auf das jeweilige Skript oder Programm verweist. Wenn dieser Kommentar und die ID im Query-Log landen, könnte man die Herkunft nachverfolgen.

• Wenn man den Weg über „Audit-Tabellen“ gehen will, kann man einfach pgaudit verwenden. Das ist eine praxiserprobte Erweiterung und bei Nutzung von AWS auch auf RDS verfügbar.
  https://github.com/pgaudit/pgaudit/blob/master/README.md
  https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appen...

  • Es gibt auch die Erweiterung temporal_tables, die auf HN bereits diskutiert wurde.
    https://github.com/arkhipov/temporal_tables
    https://news.ycombinator.com/item?id=26748096

• Das muss man nicht unbedingt tun. Wenn man das will, bedeutet es, die Relationen in Postgres in Verträge zu verwandeln. Kein Dienst kann dann noch internen Zustand persistieren.
  Wenn man sich wirklich konsequent dem Domain-driven Design verschreibt, könnte es möglich sein, aber besser ist es, ein leichtgewichtiges und zugleich praktisches eventbasiertes System zu verwenden.

  • Die Relationen der Datenbank sind, ob man will oder nicht, bereits Verträge.
    Irgendetwas Eventbasiertes ist 1000-mal komplexer.

• Das Polling einer updated_at-Spalte ist in der einfachsten Form nicht robust. Es gibt nämlich keine Garantie, dass Transaktionen in dieser Reihenfolge committen.

  • Ich bin der Autor. Guter Hinweis. Zum Beispiel startet Transaktion A, der Before-Trigger wird ausgeführt und updated_at von Row 1 wird auf 2023-09-22 12:00:01 gesetzt.
    Kurz darauf startet Transaktion B, updated_at von Row 2 wird auf 2023-09-22 12:00:02 gesetzt, und B committet zuerst.
    Die Polling-Query läuft, sieht Row 2 als neueste Änderung und aktualisiert den Cursor auf 2023-09-22 12:00:02; wenn A danach committet, wird Row 1 verpasst.
    Eine einfache Möglichkeit, dieses Problem zu vermeiden, ist, nicht nahezu in Echtzeit zu pollen. Die Reihenfolge wird letztlich konsistent.
    Ein robusterer Vorschlag könnte sein, eine Sequenz zu verwenden, etwa eine Spalte updated_at_idx, die bei jeder Änderung einer Zeile erhöht wird.
  • Das wusste ich bisher nicht. Gilt das auch, wenn man einen Trigger zum Aktualisieren der Spalte verwendet?
    Ich frage mich, ob bei einem Before-Trigger, der now() setzt, die updated_at-Timestamps zweier Zeilen von der Commit-Reihenfolge der Transaktionen abweichen können. updated_at und Commit-Timestamp müssen nicht identisch sein, aber updated_at sollte die Commit-Reihenfolge auf Milli- oder Mikrosekundenebene korrekt abbilden.
  • Für Polling verwenden wir statt updated_at eine Spalte _txid, die der Trigger auf die aktuelle Transaktions-ID setzt. Beim späteren Polling prüfen wir mit txid_current(), welche Transaktionen committed wurden und welche noch nicht.
    Das ist etwas heikel und sehr anfällig für Randfallfehler, läuft aber seit Jahren gut in Produktion.

• Der Artikel ist hervorragend.
  Wenn man Elixir und Postgres verwendet: Ich habe mit einem ähnlichen Ansatz eine kleine Library gebaut, die WAL-Änderungen mithört: https://github.com/cpursley/walex

• Diese Ansätze sind alle nicht besonders toll; persönlich halte ich Polling für am praktikabelsten.
  Es wäre gut, wenn Postgres in diesem Bereich innovativer würde.

  • Es gab Versuche, verschiedene Arten von Temporalität als First-Class-Features in den SQL-Standard aufzunehmen.
    Solange das nicht im SQL-Standard landet, dürfte es schwer sein, im Kernel-Space relationaler DBMS echte Dynamik zu erzeugen. Die Optionen sind zahlreich und komplex, und erfolgreiche Lösungen im User-Space sind auch nicht unbedingt übermäßig teuer in Sachen Performance.
    Nebenbei: Forschende in diesem Bereich tendieren meist zum Ansatz mit Audit-Tabellen. Denn so bleiben innerhalb der Datenbank konsistente ACID-Eigenschaften erhalten, und Postgres bleibt der Single Point of Failure, statt zusätzliche Proxys oder Polling-Jobs einzuführen.
  • Ist ein Polling-Intervall von 1 Sekunde praktikabel?

• In der Datenwelt gibt es eine große Lücke. Statt einen Datenspeicher nach Ergebnissen zu fragen, wäre es schön, wenn Query-Ergebnisse inkrementell gepusht würden.
  Ich mache viel Echtzeit- und Streaming-Analytics; man kann Stream Processing nutzen und manches auch innerhalb des Datenspeichers über materialisierte Views abbilden. Aber sobald die Daten in einer DB oder einem Data Lake liegen, landet man downstream praktisch wieder beim Polling, wenn man Änderungen sehen will.
  Wenn man auf bestimmte Situationen in den Daten reagieren oder eine Oberfläche ohne Page-Refresh aktualisieren will, gibt es kaum saubere Lösungen. Auch die Lösungen in diesem Artikel wirken eher wie Workarounds als wie First-Class-Features.
  Wenn man einen Report bauen will, der sich ohne Page-Refresh in Echtzeit aktualisiert, läuft es meist darauf hinaus, die Daten aus der DB zu laden und Änderungen dann über Kafka und WebSocket in die GUI zu streamen. Am Ende betreibt man eine merkwürdige Lambda-Architektur, bei der ein Teil der Analyse im Code und ein Teil in der DB passiert.
  Innovation gibt es in diesem Bereich durchaus. KSQL und Kafka Streams können Änderungen ausgeben, Materialize hat Subscriptions, und ClickHouse hat Live Views. Allerdings sind viele Features neu oder noch im Preview-Stadium und passen nicht ganz. Ich habe sie alle ausprobiert, habe aber das Gefühl, dass sie zu viel Arbeit auf die Entwickler abwälzen.
  Schön wäre eine Library, mit der man über eine Option wie [select * from orders with suscribe] direkt einen Change Feed bekommen kann. Das ist ein ausreichend wichtiger Bereich, hat aber bisher zu wenig Aufmerksamkeit bekommen.

  • https://github.com/pipelinedb/pipelinedb
  • MongoDB hat schon seit Langem ChangeStreams.

• Es gibt eine große Falle bei der Replikation, die der Artikel nicht behandelt, und deshalb nutze ich keine Replikation.
  Postgres versucht sehr stark zu garantieren, dass Konsumenten eines Replication Slots keine Daten verpassen. Wenn ein Konsument also keine Daten aus dem Slot konsumiert, bewahrt Postgres die verpassten Daten freundlicherweise weiter auf — bis irgendwann die Platte voll ist und die DB umkippt. Das ist mir beim Prototyping bei zwei verschiedenen SaaS-DBs passiert; zur Wiederherstellung blieb nur ein Support-Ticket.
  Wenn ein Konsument eines Replication Slots aufhört zu lesen, muss zwingend ein Alarm auslösen.
  Ein weiterer Grund ist, dass der Codepfad zum Abrufen des initialen Snapshots einer Tabelle völlig anders ist als der Codepfad zum Lesen von Änderungen. Das Lesen eines Replication Slots so zu initialisieren, dass man keine einzige Änderung verpasst, ist nicht trivial.
  Leider ist Replikation aus Sicht von Change Capture die am wenigsten hackige Lösung.
  Ich verwende Polling, speichere aber statt updated_at die txid.

  • Man kann ein Größenlimit setzen, sodass Slots ab einer bestimmten Größe als ungültig markiert werden, statt weiter Speicherplatz festzuhalten: https://www.postgresql.org/docs/current/runtime-config-repli...
    Mich würde interessieren, welches Verhalten du dir eher wünschen würdest.
    Wenn man mit großen Datenvolumina arbeitet, will man initiale Snapshots und das Lesen von Änderungen unterschiedlich behandeln. Schließlich muss man Dinge wie parallele Initialisierung oder Initialisierung auf Basis physischer Backups ermöglichen. Ich verstehe aber, dass eine Funktion nützlich sein könnte, die nach dem Erstellen eines Slots bestehende Daten selektiv streamt.
    Der Teil, das Lesen eines Replication Slots so zu initialisieren, dass keine Änderungen verpasst werden, sollte eigentlich nicht schwierig sein; mich würde interessieren, wo es bei dir hakte.
  • Ein Trick, um das erste Problem anzugehen, besteht darin, Logical-Decoding-Messages an sich selbst zu senden. Dadurch kann man die vorgehaltenen WALs niedrig halten.
    Wenn man nicht alle Änderungen braucht, sind auch temporäre Replication Slots nützlich, die sich selbst aufräumen, sobald die Verbindung abbricht. Es gibt außerdem eine Konfiguration, mit der man ein Maximum für vorgehaltene WALs setzt, damit der Server nicht abstürzt.
  • Ich bin schon in diese Falle getappt. Sie ist wirklich subtil. Wenn man den Konsumenten entfernt, fühlt es sich so an, als sollte das keinerlei Auswirkungen auf die Primär-DB haben; tatsächlich entsteht aber eine Zeitbombe.
    Es wäre hilfreich, wenn du genauer erklären könntest, wie du txid statt updated_at verwendest.