Eine Postgres-Queue gesund halten

 (planetscale.com)
13 Punkte von GN⁺ 12 일 전 | 1 Kommentare | Auf WhatsApp teilen
  • Beim Betrieb einer Job-Queue in Postgres treten häufig chronische Probleme auf: die Ansammlung von Dead Tuples, daraus resultierender Table Bloat und Leistungsabfall. Der Artikel fasst Ursachen und Lösungen zusammen
  • Queue-Tabellen haben meist einen kurzen Lebenszyklus pro Zeile mit Einfügen–Lesen–Löschen; ihre Größe bleibt daher relativ konstant, aber der kumulative Durchsatz ist sehr hoch
  • Aufgrund der MVCC-Architektur von Postgres werden gelöschte Zeilen nicht sofort entfernt, sondern bleiben als Dead Tuples bestehen und müssen bereinigt werden; dafür ist Autovacuum zuständig
  • Wenn lang laufende Transaktionen oder überlappende Analyseabfragen den MVCC-Horizon festhalten, kann Autovacuum Dead Tuples nicht bereinigen, was die Queue-Performance verschlechtert
  • Als praktisches Mittel zur Lösung wird PlanetScales Traffic Control-Funktion (Teil der Insights-Erweiterung) vorgestellt, die Ressourcenbegrenzung nach Query-Klassen ermöglicht

Eigenschaften von Queue-Workloads

  • Das besondere Merkmal einer Queue-Tabelle ist, dass die meisten Zeilen temporär (transient) sind — sie werden eingefügt, einmal gelesen und wieder gelöscht
  • Die Tabellengröße bleibt fast konstant, aber der kumulative Durchsatz ist enorm
  • Ein wesentlicher Vorteil einer Job-Queue in Postgres ist, dass sich der Job-Status und andere DB-Logik innerhalb derselben Transaktion synchronisieren lassen
    • Wenn ein Job fehlschlägt, wird die gesamte Transaktion zurückgerollt
    • Nutzt man einen externen Queue-Dienst, wird die Synchronisierung mit dem Transaktionszustand der Anwendung deutlich komplexer

Beispiel für eine Queue-Tabelle und das Verhalten der Worker

  • Das im Artikel gezeigte Basisschema
CREATE TABLE jobs (  
  id BIGSERIAL PRIMARY KEY,  
  run_at TIMESTAMPTZ DEFAULT now(),  
  status TEXT DEFAULT 'pending',  
  payload JSONB  
);  
CREATE INDEX idx_jobs_fetch ON jobs (run_at) WHERE status = 'pending';
  • Ein Worker öffnet eine Transaktion und sperrt den ältesten pending Job mit FOR UPDATE SKIP LOCKED, um doppelte Verarbeitung zu vermeiden
  • Bei erfolgreicher Verarbeitung folgen DELETE und COMMIT; bei einem Fehler wird zurückgerollt, sodass die Zeile für einen anderen Worker wieder sichtbar wird
  • Diese Transaktion sollte so kurz wie möglich offen bleiben — je länger sie offen ist, desto stärker blockiert sie Vacuum (im Beispiel des Artikels basierend auf Submillisekunden-Workern)

Die Performance an sich ist nicht das Problem

  • Dass Postgres große Job-Queues verarbeiten kann, ist bereits dokumentiert; an der grundsätzlichen Leistungsfähigkeit scheitert es nicht
  • Das eigentliche Problem ist das Zusammenleben mit anderen konkurrierenden Workloads in derselben DB
  • Der Zustand einer Queue-Tabelle hängt nicht nur von ihren eigenen Einstellungen ab, sondern auch vom Verhalten aller Transaktionen auf derselben Postgres-Instanz
  • Der Artikel konzentriert sich auf konkurrierenden Query-Traffic auf der Primary (Einflüsse von Replikaten und Replication Slots werden separat betrachtet)

Das eigentliche Problem: Bereinigung von Dead Tuples

  • Postgres verwendet MVCC und hält dadurch mehrere Versionen derselben Zeile — gelöschte Zeilen werden nicht sofort entfernt, sondern nach dem Markieren als gelöscht für neue Transaktionen unsichtbar
  • Solche zurückbleibenden Zeilen sind Dead Tuples und werden durch Vacuum-Operationen bereinigt
  • Dead Tuples erscheinen nicht im Ergebnis von SELECT, verursachen aber weiterhin Kosten
    • Sequential Scan: Der Executor liest Dead Tuples von Heap-Pages, prüft ihre Sichtbarkeit und verwirft sie dann
    • Index Scan (wie in der Queue mit ORDER BY run_at LIMIT 1): Der B-Tree-Index sammelt Verweise auf Dead Tuples an, sodass auch Einträge durchsucht werden, die auf nicht mehr sichtbare Zeilen zeigen
  • Jeder tote Indexeintrag verursacht zusätzliche I/O, die in der Anwendung unsichtbar bleibt, deren Kosten aber mit der Zahl der Dead Tuples deutlich steigen
  • Der Bereinigungszyklus wird durch autovacuum_naptime (Standard: 1 Minute) bestimmt; ob er ausgeführt wird, hängt von autovacuum_vacuum_threshold und autovacuum_vacuum_scale_factor ab

Interne Mechanismen von Dead Tuples

  • Drei Metadatenfelder einer Zeile sind zentral
    • ctid: physische Position des Tupels im Heap (page, offset)
    • xmin: Transaktions-ID (XID), die diese Zeile eingefügt hat
    • xmax: Transaktions-ID, die die Zeile gelöscht oder gesperrt hat; 0 bedeutet, dass keine Löschmarkierung gesetzt ist
  • Selbst wenn drei pending Zeilen abgefragt werden, kann es passieren, dass der Executor zuerst sechs zuvor gelöschte Dead Tuples scannt und nur drei Zeilen zurückgibt
  • Auch der Index erzeugt kumulierte verschwendete Arbeit, wenn Leaf-Entries auf Dead Tuples im Heap zeigen
  • Wenn die DB Dead Tuples schneller erzeugt, als sie sie bereinigen kann, gerät sie auf einen Pfad des Scheiterns
  • Ein gut abgestimmter Postgres-Cluster kann mehrere zehntausend Queue-Operationen pro Sekunde bewältigen

Wann Autovacuum wirkungslos wird

  • Hauptgründe, warum Autovacuum bei der Bereinigung von Dead Tuples scheitert
    • bestimmte Tabellensperren blockieren das Cleanup
    • ungeeignete Autovacuum-Konfiguration
    • am häufigsten: aktive Transaktionen verhindern die Rückgewinnung von Dead Tuples
  • Postgres vacuumt keine Dead Tuples, die für aktive Transaktionen noch sichtbar sein könnten
    • Die älteste aktive Transaktion setzt den Cutoff → den MVCC-Horizon
    • Bis diese Transaktion endet, bleiben alle Dead Tuples ab diesem Snapshot erhalten
  • Eine einzelne zweiminütige Transaktion kann den Horizon zwei Minuten lang festhalten
  • Dasselbe Fehlermuster entsteht auch durch überlappende Abfragen mittlerer Laufzeit
    • Beispiel: Drei Analyseabfragen mit je 40 Sekunden Laufzeit, im Abstand von 20 Sekunden versetzt gestartet — keine einzelne Query läuft ins Timeout, aber es ist immer mindestens eine aktiv, sodass der Horizon nicht vorankommt
  • Wenn man nach der Philosophie „Just use Postgres“ mehrere Workloads in einer DB zusammenlegt, liegt das Problem nicht in der schnellen Job-Verarbeitung selbst, sondern darin, dass überlappende langsame Queries die Bereinigung von Dead Tuples verzögern

Bestehende Werkzeuge und ihre Grenzen

  • Tuning-Optionen für Autovacuum: autovacuum_vacuum_cost_delay, autovacuum_vacuum_cost_limit
  • Timeouts zur Begrenzung lang laufender Queries
    • statement_timeout (Postgres 7.3): beendet einzelne SQL-Statements, die eine festgelegte Zeit überschreiten
    • idle_in_transaction_session_timeout (9.6): beendet Sessions, die innerhalb einer Transaktion zu lange untätig bleiben
    • transaction_timeout (17.0): beendet aktive oder inaktive Transaktionen, wenn sie die festgelegte Zeit überschreiten
  • Diese Timeouts zielen nur auf die Laufzeit einzelner Queries, begrenzen aber weder Parallelität noch Ausführungskosten und eignen sich deshalb nicht, um Workloads zu stoppen, die den MVCC-Horizon dauerhaft festhalten
  • Benötigt wird eine Unterscheidung nach Traffic-Klassen: Hochpriorisierte Workloads sollen unangetastet bleiben, während nur die Ressourcennutzung niedrig priorisierter Workloads gedrosselt wird

Database Traffic Control™

  • Eine von PlanetScale entwickelte Funktion als Teil der Insights-Erweiterung, verfügbar nur für PlanetScale Postgres
  • Sie dient der fein granularen Steuerung von individueller Query-Performance und Ressourcennutzung
  • Mit einem Resource Budget lassen sich Zielabfragen mit Ressourcenlimits versehen — bei Überschreitung können sie blockiert werden
  • Die Lösungsstrategie besteht darin, Anzahl und Häufigkeit überlappender langsamer Queries zu begrenzen, damit Autovacuum genug Spielraum hat, Dead Tuples mit angemessener Geschwindigkeit zu bereinigen
  • Blockierte Queries werden nicht dauerhaft abgewiesen, sondern sollen erneut versucht werden; Retry-Logik in der Anwendung ist daher Pflicht
  • Es geht also darum, bei gleichem Gesamtvolumen die Ausführungsgeschwindigkeit zu glätten

Demo-Aufbau und Hintergrund

  • Der Anstoß für diesen Artikel war der Blogpost von Brandur Leach aus dem Jahr 2015: "Postgres Job Queues & Failure By MVCC"
    • Darin wurde ein kritischer Fehlermodus von Job-Queues auf Postgres-Basis dokumentiert
    • Enthalten war auch eine Testbench, die praktisch zeigt, wie nicht geschlossene Transaktionen den MVCC-Horizon festhalten und Cleanup verhindern
  • Die ursprüngliche Testbench ist als brandur/que-degradation-test veröffentlicht

Reproduktion des Problems (mit Postgres 18)

  • Der ursprüngliche Test basierte auf Ruby + Que gem v0.x + Postgres 9.4
  • Um das Verhalten auf SQL-Ebene isoliert zu prüfen, wurde er vom Autor in TypeScript + Bun neu geschrieben
  • Beibehalten wurden dasselbe rekursive CTE-Muster, dasselbe Schema, dieselbe Producer-Rate, Work-Dauer, Worker-Anzahl und dasselbe Long-Runner-Muster wie bei Que
  • Ausgeführt wurde das Ganze auf einem PlanetScale PS-5-Cluster (ab 5 US-Dollar pro Monat)
  • Ergebnis: sichtbare, aber beherrschbare Performanceverschlechterung
    • Im Originaltest wurde die DB innerhalb von 15 Minuten in eine Death Spiral getrieben; auf PS-5 blieb die Worker-Queue 15 Minuten lang nahe 0
    • Allerdings nahmen die Dead Tuples linear zu, was darauf hindeutet, dass das gleiche Problem bei längerer Laufzeit wieder auftreten würde
    • Verbesserungen bei der B-Tree-Bereinigung (Bottom-up Deletion bei Version Churn, scan-basierte Entfernung toter Index-Tupel usw.) haben das Problem abgemildert, aber nicht beseitigt

Verbesserungsversuch: SKIP LOCKED + Batch-Verarbeitung

  • Zwei moderne Verbesserungen, die es 2015 noch nicht gab
    1. FOR UPDATE SKIP LOCKED — ersetzt die rekursive CTE vollständig durch ein einzelnes SELECT und überspringt Zeilen, die bereits von anderen Workern gesperrt sind
    2. Batch Processing (10 Jobs pro Transaktion) — verarbeitet 10 Jobs mit einer einzigen Sperrakquisition und verteilt so die Kosten des Index-Scans
  • Bei unveränderten Bedingungen: 8 Worker, Producer mit 50 Jobs/s, 10 ms Arbeit, Start des Long-Runners nach 45 Sekunden
  • Wichtige Ergebnisse
Kennzahl original (rekursive CTE) enhanced (SKIP LOCKED + Batch)
Baseline-Lock-Zeit 2–3 ms 1.3–3.0 ms
Lock-Zeit am Ende (typisch) 10–34 ms 9–29 ms
Schlimmste Spitze 84.5 ms (33k Dead Tuples) 180 ms (24k Dead Tuples)
Queue-Tiefe 0–100 (oszillierend) 0 (meistens)
Dead Tuples am Ende 42,400 42,450
Durchsatz ~89/s ~50/s
  • Die Degradationskurve ist nahezu identisch — beide Ansätze scannen denselben B-Tree-Index und stoßen auf dieselben Dead Tuples
  • Der Unterschied beim Durchsatz liegt nicht an der Sperrstrategie, sondern am Testdesign (CTE-Worker holen Jobs schneller als der Producer sie erzeugt, Batch-Worker leeren die Queue und gehen dann in Backoff-Sleep)
  • Fazit: Ein Queue-Design, das vor 10 Jahren die DB in 15 Minuten zerstören konnte, hält heute deutlich länger durch, aber das Grundproblem bleibt bestehen — bei 500 Jobs/s zeigt es sich wieder schneller

Lösung mit Traffic Control

  • Steuerungsmöglichkeiten durch Resource Budget
    • Server share & burst limit: Anteil an Server-Ressourcen und Verbrauchsgeschwindigkeit
    • Per-query limit: ausführbare Query-Zeit in Sekunden, gemessen an der Servernutzung
    • Maximum concurrent workers: Verhältnis zur Zahl verfügbarer Worker-Prozesse
  • Die Zielabfragen werden meist über Metadaten in SQLCommenter-Tags angegeben (z. B. action=analytics)
  • Statt eines Long-Runners, der von idle_in_transaction_session_timeout erfasst würde, wird Degradation durch ein realistischeres Szenario erzeugt: aktive, sich überlappende Analyseabfragen (ein Fall, der durch Session-Timeouts nicht abgefangen wird)
  • Das Maximum concurrent workers für Queries mit action=analytics wird auf 1 Worker (max_worker_processes 25 %) begrenzt — dadurch läuft immer nur eine Analyseabfrage gleichzeitig
  • Um innerhalb eines 15-Minuten-Fensters eine Death Spiral zu provozieren, wird der Producer auf 800 Jobs/s erhöht
  • Von EC2 aus werden zwei Läufe des „enhanced“-Workloads gegen dieselbe PlanetScale-DB durchgeführt
    • 800 Jobs/s
    • drei Analyseabfragen mit je 120 Sekunden Laufzeit, gleichzeitig gestartet und versetzt wiederholt, damit sie sich ständig überlappen
    • Laufzeit 15 Minuten
  • Vergleich der Ergebnisse
Kennzahl Traffic Control deaktiviert Traffic Control aktiviert
Queue-Backlog 155,000 Jobs 0 Jobs
Lock-Zeit 300 ms+ 2 ms
Dead Tuples am Ende 383,000 0–23,000 (zyklisch)
Analytics-Queries 3 gleichzeitig, überlappend jeweils 1, 2 in Retry
VACUUM-Effektivität Blockiert (Horizon festgehalten) Normal (Bereinigungsfenster zwischen Queries vorhanden)
Ergebnis Death Spiral Vollständig stabil
  • Traffic Control begrenzt die Parallelität bestimmter Workloads direkt und ermöglicht damit eine Form der Steuerung, die mit Autovacuum-Tuning oder Timeouts nicht erreichbar ist
  • Analyse-Reports laufen weiterhin im Rahmen der verfügbaren Kapazität und schließen in 15 Minuten 15 Durchläufe ab, während die Queue durchgehend gesund bleibt

Fazit

  • Das MVCC-Dead-Tuple-Problem von Postgres-basierten Queues ist kein Relikt aus dem Jahr 2015
  • Modernes Postgres bietet durch B-Tree-Verbesserungen und SKIP LOCKED deutlich mehr Spielraum, aber der zugrunde liegende Mechanismus ist unverändert
    • Wenn VACUUM Dead Tuples nicht bereinigen kann, sammeln sie sich an
    • Wenn lang laufende oder überlappende Transaktionen den MVCC-Horizon festhalten, kann VACUUM nicht aufräumen
  • In Umgebungen, in denen man mit „Just use Postgres“ Queue, Analytics und Applikationslogik in eine einzige DB legt, ist das kein theoretisches Risiko, sondern normaler Betriebsalltag
  • Die gefährliche Form ist kein spektakulärer Crash, sondern ein leiser degradierender Gleichgewichtszustand — Lock-Zeiten steigen langsam, Jobs werden träger, aber kein Alarm schlägt an
  • Die Timeout-Werkzeuge von Postgres können weder Workload-Klassen unterscheiden noch Parallelität begrenzen
  • Wenn eine Queue zusammen mit anderen Workloads betrieben wird, ist die wirksamste Maßnahme, sicherzustellen, dass VACUUM mithalten kann — und Traffic Control vereinfacht genau das

Verwandte Beiträge

1 Kommentare

 
GN⁺ 12 일 전
Hacker-News-Kommentare

  • Postgres hat nach wie vor das Problem des Vacuum-Horizonts. Dabei blockieren lange laufende Abfragen das Vacuum auf sich schnell ändernden Tabellen. Dieses Problem ist seit 2015 gut bekannt. Im Standard-Postgres gibt es kein gutes Werkzeug, um das zu lösen, aber die Custom-Version der Firma des Autors hat eine Funktion dafür. Unterm Strich ist es weiterhin nicht klug, lange OLAP-artige Jobs und schnelle Queue-artige Jobs auf derselben Postgres-Instanz zu mischen. Je nach Anforderungen kann die Nutzung einer Message Queue wie 0MQ oder RMQ die einfachere Lösung sein

    • Solange die Datenbank Cache oder Buffer Pool nicht fein granular steuern kann, ist das Mischen solcher unterschiedlichen Lasten immer eine schlechte Wahl. Es gibt nämlich keine Möglichkeit zu verhindern, dass analytische Tabellen den gesamten Cache verschmutzen

  • Der Artikel war in Ordnung, aber es gibt ein paar Punkte anzumerken.

    1. Die Erklärung des MVCC-Horizonts wirkt widersprüchlich. Wenn Transaktionen zu unterschiedlichen Zeitpunkten starten, haben sie unterschiedliche Snapshots, und sobald die erste Transaktion endet, sollte Vacuum weiterlaufen können
    2. Das Performance-Problem der Abfrage SELECT * FROM jobs WHERE status='pending' ORDER BY run_at LIMIT 1 FOR UPDATE SKIP LOCKED; existiert tatsächlich, lässt sich aber durch das Hinzufügen einer monoton steigenden Spalte mit Index abmildern. Dann müssen tote Tupel nicht berücksichtigt werden, und es wird nur Platz verschwendet, während die Leseleistung weniger stark leidet. Wie man bei gleichzeitigem Schreiben Monotonie garantiert, hängt allerdings vom Applikationsdesign ab
    3. Kurz gesagt lautet die Lehre: „Verwende in Postgres keine extrem langen Transaktionen zusammen mit einer extrem hohen Transaktionsfrequenz“
    • Auch mit zusätzlichem Index muss Postgres tote Tupel weiterhin aufbewahren, bis sie vollständig bereinigt werden können. Es wird vielleicht etwas schneller, aber am Ende läuft die Platte voll, und auch das Vacuum anderer Tabellen kann behindert werden

  • Hier ist der Autor. Fragt gern alles, was euch interessiert

    • Es sieht so aus, als würde die Queue-Implementierung im Blog die Transaktion offen halten, während ein Job läuft. Ich verstehe den Rat, sie möglichst kurz zu halten, frage mich aber, ob man die lange Transaktion selbst nicht vermeiden könnte, indem man die Statusspalte auf „processing“ aktualisiert
    • Mich würde interessieren, ob ihr getestet habt, den Fillfactor der Queue-Tabelle auf unter 100 zu setzen. Mit HOT-Updates könnte man bei Statusänderungen vermeiden, neue Indexeinträge anzulegen, und stattdessen toten Platz wiederverwenden, was den Zeitpunkt des Problems hinauszögern könnte
    • Mich würde interessieren, worin sich diese Lösung von der Nutzung von pg_squeeze unterscheidet. Wir haben in unserem Queue-System dasselbe Problem und testen gerade pg_squeeze, was ziemlich gut zu funktionieren scheint

  • Das wirkt wie Werbung, aber ich hätte mir zumindest etwas Erklärung zur technischen Lösungsweise gewünscht

  • Postgres kann wirklich sehr viel. Viele Leute greifen zu Kafka oder SQS, aber tatsächlich gibt es viele Aufgaben, die sich auch mit Graphile Worker ausreichend bewältigen lassen

    • Die Philosophie „Lasst uns alles mit Postgres machen“ ist bei kleiner oder mittlerer Größenordnung gut, um die Einfachheit zu bewahren, aber mit wachsender Größe ist es besser, einer SQL-Datenbank so wenig Arbeit wie möglich zu geben. In den meisten Systemen ist sie nämlich der Flaschenhals
    • SQS ist sehr einfach und lässt sich in einer AWS-Umgebung leicht integrieren. Kafka ist deutlich komplexer, aber eine hervorragende Wahl, wenn man die benötigten Funktionen wirklich braucht. Es bringt allerdings eine große operative Last mit sich, und viele Projekte wurden nur für den Lebenslauf damit gebaut. SQS wird dagegen als pragmatisches Werkzeug eingesetzt. Wenn man AWS aber verlassen will, kann die Abhängigkeit zum Problem werden

  • In Postgres wird das Problem beim Aktualisieren von Zeilen deutlich gravierender. Wenn man nur Inserts und Deletes verwendet, hält es recht lange durch

    • In Postgres ist UPDATE im Grunde eine Kombination aus INSERT und DELETE