UPDATE sa subquery i LIMIT: Kada daemon samo mlati vazduh

Scenario: background daemon treba da izračuna izvedeni score trust_score po redu i da ga upiše. Tabela ima oko 2 miliona redova, daemon ide svakih 60 sekundi, svaka iteracija batch od 10 000. Posle nedelju dana posmatranja: score je postavljen samo na 240 000 redova. Daemon je posle prvog pokretanja praktično prestao da napreduje.

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 menja. U stvari: slomljeno.

Šta ne valja

Subquery bira uvek istih 10 000 redova (bez ORDER BY, ali PostgreSQL je dovoljno deterministički da u praksi vraća prvih 10 000 po heap poziciji). U prvom prolazu daemon updatuje tih 10 000. U drugom prolazu subquery bira iste ID-jeve — ali WHERE predikat trust_score IS NULL OR trust_score <> sub.ts je sada za svih 10 000 laž. Nula update-a.

Daemon nastavlja, 10 000 redova u sekundi kroz subquery, i ne ažurira ništa. Filter u WHERE UPDATE klauzule ne utiče na to šta subquery vraća — samo ih odbacuje nakon selekcije.

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

Popravka

Filter u subquery, ne posle:

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 redove bez trust_score-a), a WHERE ih sve prihvata. Daemon napreduje. 10 000 po minuti, predvidljivo, napredak merljiv.

Dva srodna anti-pattern-a

• ORDER BY RANDOM() LIMIT N kao „osiguranje napretka": radi, ali na velikim tabelama skupo.
• WHERE id > (last_seen_id) sa persistent cursor-om: ispravno rešenje za strogo uređenu obradu. Pro: determinističko, ponovljivo, monitorable. Con: traži state fajl ili malu tabelu za cursor.

Najčešće koristimo filter-in-subquery varijantu dok god jednostavan IS NULL predikat rešava. Čim uslov napretka postane kompleksniji, prelazimo na cursor.

Zašto se ne primeti u razvoju

Na dev bazi sa 1 000 redova i trust_score inicijalno NULL za svih 1 000: daemon u prvom pokretanju ažurira svih 1 000, u drugom prolazu svi već imaju score i daemon javlja „ništa za raditi". To je željeno ponašanje. U produkciji sa 2M redova, po batch-u se dira samo 10 000 — i uvek istih 10 000.

Anti-pattern je nevidljiv na dev veličinama. Otkriva se tek u produkciji kad neko vidi flat graf „Rows with trust_score != NULL" dve nedelje.

Operativno pravilo

Kod svakog batched UPDATE/DELETE/INSERT sa LIMIT: osiguraj da selekcija potiče iz skupa za obradu, a ne da WHERE predikat nateruje napredak.

Plus monitorable progress metrika po daemon-u svake minute:

• Redova u očekivanom stanju: X
• Redova već obrađenih: Y
• Rate/min: Z

Ako je Z za dve uzastopne iteracije 0, ili je sve završeno (X == 0) ili ti daemon mlati u prazno.