UPDATE con subquery e LIMIT: quando il daemon gira a vuoto

Lo scenario: un daemon in background deve calcolare uno score derivato trust_score per riga e scriverlo. La tabella ha circa 2 milioni di righe, il daemon gira ogni 60 secondi, ogni iterazione un batch da 10 000. Dopo una settimana di osservazione: lo score era impostato solo su 240 000 righe. Il daemon dopo la prima esecuzione non faceva praticamente più nessun progresso.

La query era così:

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

A prima vista pulita: limitata per batch, idempotente, scrive solo se qualcosa cambia. In realtà: rotta.

Cosa va storto

La subquery seleziona sempre le stesse 10 000 righe (nessun ORDER BY, ma PostgreSQL è abbastanza deterministico da restituire in pratica le prime 10 000 per posizione nell'heap). Al primo passaggio il daemon aggiorna esattamente quelle 10 000. Al secondo passaggio la subquery restituisce gli stessi ID — ma il predicato WHERE trust_score IS NULL OR trust_score <> sub.ts è ora falso per tutte 10 000. Zero update.

Il daemon continua a girare, fa passare 10 000 righe al secondo attraverso la subquery, e non aggiorna niente. Il filtro nel WHERE dell'UPDATE non ha alcun effetto su quali righe la subquery produce — le scarta solo dopo la selezione.

Significa: la subquery produce permanentemente lo stesso set di candidati, e il WHERE lo scarta altrettanto permanentemente.

Il fix

Sposta il filtro dentro la subquery, non dopo:

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;

Ora la subquery seleziona a ogni iterazione candidati nuovi (tutte le righe che non hanno ancora un trust_score), e il WHERE li accetta tutti. Il daemon avanza. 10 000 al minuto, prevedibile, progresso misurabile.

Due anti-pattern correlati

• ORDER BY RANDOM() LIMIT N come "assicurazione di progresso": funziona, ma su tabelle grandi è costoso perché PostgreSQL ordina l'intera tabella.
• WHERE id > (last_seen_id) con cursor persistente: la soluzione corretta per un'elaborazione strettamente ordinata. Pro: deterministico, ripetibile, monitorabile. Contro: richiede un file di stato o una piccola tabella per il cursore.

Usiamo di solito la variante filter-in-subquery finché un semplice predicato IS NULL basta. Appena la condizione di progresso diventa più complessa (più flag, dipendenze da foreign key), passiamo al cursore.

Perché non emerge in sviluppo

Su un database di dev con 1 000 righe e trust_score inizialmente NULL per tutte: il daemon aggiorna tutte le 1 000 al primo giro, al secondo segnala "niente da fare". È il comportamento desiderato. In produzione con 2 M righe vengono toccate solo 10 000 per batch — e sempre le stesse 10 000.

L'anti-pattern è invisibile alla scala dev. Emerge solo in produzione, quando qualcuno guarda il monitor "righe con trust_score != NULL" piatto per due settimane.

Regola operativa

Per ogni UPDATE/DELETE/INSERT batched con LIMIT: assicurati che la selezione provenga dal set di progresso, non lasciare che sia il predicato WHERE a forzare il progresso.

E una metrica di progresso monitorabile per daemon, emessa ogni minuto:

• Righe attese (WHERE progress_condition): X
• Righe già fatte: Y
• Rate/min: Z

Se Z è 0 per due iterazioni di fila, o è tutto finito (X == 0) oppure il tuo daemon gira a vuoto.