UPDATE mit Subquery und LIMIT: Wenn der Daemon im Leerlauf dreht

Das Szenario: Ein Background-Daemon soll einen abgeleiteten Score trust_score pro Row berechnen und schreiben. Die Tabelle hat rund 2 Millionen Rows, der Daemon läuft alle 60 Sekunden, in jeder Iteration ein Batch von 10 000. Nach einer Woche Beobachtung: Der Score war nur auf 240 000 Rows gesetzt. Der Daemon machte nach dem ersten Lauf praktisch keinen Fortschritt mehr.

Die Query sah so aus:

UPDATE job_advertisement ja
SET trust_score = sub.ts
FROM (
    SELECT id, compute_trust(id) AS ts
    FROM job_advertisement
    LIMIT 10000
) sub
WHERE ja.id = sub.id
  AND (ja.trust_score IS NULL OR ja.trust_score <> sub.ts);

Auf dem ersten Blick sauber: batch-begrenzt, idempotent, nur schreiben wenn sich etwas ändert. In Wirklichkeit: kaputt.

Was schief läuft

Die Subquery wählt immer dieselben 10 000 Rows (kein ORDER BY, aber PostgreSQL ist deterministisch genug, dass es in der Praxis die ersten 10 000 per Heap-Position liefert). Im ersten Durchlauf updatet der Daemon genau diese 10 000. Beim zweiten Durchlauf matcht die Subquery dieselben IDs erneut — aber das WHERE-Prädikat trust_score IS NULL OR trust_score <> sub.ts ist jetzt für alle 10 000 falsch. Null Updates.

Der Daemon läuft weiter, 10 000 Rows pro Sekunde durch die Subquery, und updatet nichts. Der Filter im WHERE der UPDATE-Klausel hat keinen Einfluss darauf, welche Rows die Subquery zurückliefert — er verwirft sie nur nach der Auswahl.

Das heißt: Die Subquery produziert permanent denselben Kandidaten-Set, und das WHERE wirft ihn genauso permanent weg.

Der Fix

Filter in die Subquery, nicht danach:

UPDATE job_advertisement ja
SET trust_score = sub.ts
FROM (
    SELECT id, compute_trust(id) AS ts
    FROM job_advertisement
    WHERE trust_score IS NULL
    ORDER BY id
    LIMIT 10000
) sub
WHERE ja.id = sub.id;

Jetzt selektiert die Subquery bei jeder Iteration neue Kandidaten (alle Rows, die noch keinen trust_score haben), und das WHERE nimmt sie alle. Der Daemon kommt voran. 10 000 pro Minute, vorhersagbar, Fortschritt messbar.

Zwei verwandte Anti-Patterns

• ORDER BY RANDOM() LIMIT N als „Fortschritts-Sicherung": funktioniert, aber auf großen Tabellen teuer, weil PostgreSQL die ganze Tabelle sortiert.
• WHERE id > (letzter_gesehener_id) mit einem persistenten Cursor: die richtige Lösung für strikt geordnete Stapel-Verarbeitung. Pro: deterministisch, wiederholbar, monitor-bar. Contra: erfordert eine State-Datei oder eine kleine Tabelle für den Cursor.

Wir nutzen in der Regel die Filter-in-Subquery-Variante, solange ein einfaches IS NULL-Prädikat reicht. Sobald die Progress-Bedingung komplexer wird (mehrere Flags, abhängig von Fremdschlüsseln), steigen wir auf einen Cursor um.

Warum das in der Entwicklung nicht auffällt

Auf einer Dev-Datenbank mit 1 000 Rows und trust_score initial NULL für alle 1 000: der Daemon updatet im ersten Lauf alle 1 000, in Durchlauf 2 sind alle trust_score schon gesetzt, und der Daemon meldet „nichts mehr zu tun". Das ist das gewünschte Verhalten. In Produktion mit 2 Mio Rows werden pro Batch nur 10 000 angefasst — und dieselben 10 000 immer wieder.

Das Anti-Pattern ist auf Dev-Größen unsichtbar. Es fällt erst in Produktion auf, wenn jemand die Monitor-Zahl für „Rows mit trust_score != NULL" über zwei Wochen flat sieht.

Operative Regel

Bei jedem batched UPDATE/DELETE/INSERT mit LIMIT: Stelle sicher, dass die Selektion aus dem Fortschritts-Set kommt, nicht das WHERE-Prädikat den Fortschritt erzwingt.

Und monitorbare Progress-Metrik pro Daemon in Prometheus oder ähnlich, jede Minute:

• Rows erwartungskonform (WHERE progress_condition): X
• Rows bereits erledigt: Y
• Rate/min: Z

Wenn Z für zwei Iterationen in Folge 0 ist, ist entweder alles fertig (X == 0) oder dein Daemon dreht im Leerlauf.