UPDATE s subqueryjem in LIMIT: ko daemon teče v prazno

Scenarij: ozadinski daemon naj izračuna izpeljani score trust_score po vrstici in ga zapiše nazaj. Tabela ima okoli 2 milijona vrstic, daemon teče vsakih 60 sekund, vsaka iteracija batch po 10 000. Po tednu opazovanja: score je bil nastavljen le na 240 000 vrsticah. Daemon je po prvem zagonu praktično prenehal napredovati.

Poizvedba je bila taka:

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 čista: batch-omejena, idempotentna, piše le, če se kaj spremeni. V resnici: pokvarjena.

Kaj je narobe

Subquery vedno izbere istih 10 000 vrstic (brez ORDER BY, a PostgreSQL je dovolj determinističen, da v praksi vrne prvih 10 000 po poziciji v heapu). V prvem prehodu daemon posodobi natanko teh 10 000. V drugem prehodu subquery vrne iste ID-je — a WHERE predikat trust_score IS NULL OR trust_score <> sub.ts je zdaj za vseh 10 000 neresničen. Nič posodobitev.

Daemon teče naprej, skozi subquery potiska 10 000 vrstic na sekundo in posodablja nič. Filter v WHERE UPDATE-klavzule ne vpliva na to, katere vrstice vrne subquery — odvrže jih šele po izbiri.

To pomeni: subquery trajno proizvaja isti nabor kandidatov, WHERE pa ga prav tako trajno zavrača.

Popravek

Filter premakni v subquery, ne za njo:

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;

Zdaj subquery v vsaki iteraciji izbere sveže kandidate (vse vrstice, ki še nimajo trust_score), in WHERE jih vse sprejme. Daemon napreduje. 10 000 na minuto, predvidljivo, napredek merljiv.

Dva sorodna anti-vzorca

• ORDER BY RANDOM() LIMIT N kot "zavarovanje napredka": deluje, a na velikih tabelah drago, ker PostgreSQL sortira celotno tabelo.
• WHERE id > (last_seen_id) s perzistentnim kurzorjem: pravilna rešitev za strogo urejeno paketno obdelavo. Za: determinističen, ponovljiv, opazljiv. Proti: zahteva datoteko stanja ali majhno tabelo za kurzor.

Običajno uporabljamo različico filter-in-subquery, dokler zadošča preprost IS NULL predikat. Takoj ko pogoj napredka postane bolj zapleten (več zastavic, odvisno od tujih ključev), preidemo na kurzor.

Zakaj se to ne ujame v razvoju

Na razvojni bazi s 1 000 vrsticami in trust_score na začetku NULL za vse: daemon v prvem zagonu posodobi vseh 1 000, v drugem javlja "ničesar za narediti". To je želeno vedenje. V produkciji z 2 M vrsticami se na batch dotakne le 10 000 — in vedno istih 10 000.

Anti-vzorec je pri razvojni velikosti neviden. Pojavi se šele v produkciji, ko nekdo dva tedna gleda ploski monitor "vrstic z trust_score != NULL".

Operativno pravilo

Za vsak batched UPDATE/DELETE/INSERT z LIMIT: poskrbi, da izbira prihaja iz nabora napredka, in ne da WHERE predikat poskuša napredek vsiliti.

In merljiva metrika napredka po daemonu, emitirana vsako minuto:

• Vrstic na čakanju (WHERE progress_condition): X
• Vrstic končanih: Y
• Rate/min: Z

Če je Z 0 dve iteraciji zapored, je bodisi vse končano (X == 0) bodisi tvoj daemon teče v prazno.