UPDATE sa subqueryjem i LIMIT-om: kad daemon vrti u prazno

Scenarij: pozadinski daemon treba izračunati izvedeni score trust_score po retku i upisati ga. Tablica ima oko 2 milijuna redaka, daemon se pokreće svakih 60 sekundi, svaka iteracija batch od 10 000. Nakon tjedan dana promatranja: score je postavljen samo na 240 000 redaka. Daemon je nakon prvog pokretanja praktički prestao napredovati.

Upit je izgledao ovako:

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);

Na prvi pogled čisto: batch-ograničeno, idempotentno, piše samo kad se nešto promijeni. U stvarnosti: slomljeno.

Što ne valja

Subquery uvijek bira istih 10 000 redaka (bez ORDER BY, ali PostgreSQL je dovoljno determinističan da u praksi vraća prvih 10 000 po poziciji u heapu). U prvom prolazu daemon ažurira točno tih 10 000. U drugom prolazu subquery vraća iste ID-jeve — ali WHERE predikat trust_score IS NULL OR trust_score <> sub.ts sada je za svih 10 000 neistinit. Nula update-a.

Daemon nastavlja raditi, kroz subquery prolazi 10 000 redaka u sekundi, a ne ažurira ništa. Filter u WHERE UPDATE klauzule nema utjecaja na to koje retke subquery vraća — samo ih nakon selekcije odbacuje.

To znači: subquery stalno proizvodi isti skup kandidata, a WHERE ih jednako stalno odbacuje.

Popravak

Premjesti filter u subquery, ne nakon nje:

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;

Sada subquery u svakoj iteraciji bira nove kandidate (sve retke koji još nemaju trust_score), a WHERE ih sve prihvaća. Daemon napreduje. 10 000 po minuti, predvidljivo, napredak mjerljiv.

Dva srodna anti-patterna

• ORDER BY RANDOM() LIMIT N kao "osiguranje napretka": radi, ali na velikim tablicama skupo jer PostgreSQL sortira cijelu tablicu.
• WHERE id > (last_seen_id) s perzistentnim kursorom: ispravno rješenje za strogo uređenu batch obradu. Za: determinističko, ponovljivo, mjerljivo. Protiv: traži state-datoteku ili malu tablicu za kursor.

Obično koristimo varijantu filter-in-subquery dok god je jednostavan IS NULL predikat dovoljan. Čim uvjet napretka postane složeniji (više zastavica, ovisi o stranom ključu), prelazimo na kursor.

Zašto se to ne uhvati u razvoju

Na dev bazi s 1 000 redaka i trust_score inicijalno NULL za sve: daemon u prvom pokretanju ažurira svih 1 000, u drugom javlja "ništa za raditi". To je željeno ponašanje. U produkciji s 2 M redaka po batch-u se dira samo 10 000 — i uvijek istih 10 000.

Anti-pattern je nevidljiv na dev veličini. Otkriva se tek u produkciji kad netko gleda monitor "redaka s trust_score != NULL" ravan dva tjedna.

Operativno pravilo

Kod svakog batched UPDATE/DELETE/INSERT s LIMIT: osiguraj da selekcija dolazi iz skupa za obradu, a ne da WHERE predikat pokušava forsirati napredak.

I mjerljiva metrika napretka po daemonu, emitirana svake minute:

• Redaka na čekanju (WHERE progress_condition): X
• Redaka gotovih: Y
• Rate/min: Z

Ako je Z 0 dvije iteracije zaredom, ili je sve gotovo (X == 0) ili tvoj daemon vrti u prazno.