Risposta.

La cancellazione di massa di decine di milioni di righe è una delle operazioni tipiche e più pericolose, soprattutto in database ad alto carico. Storicamente molti scrivevano semplicemente DELETE FROM, il che portava a blocchi della tabella e al sovraccarico del registro delle transazioni. Il problema principale: la transazione diventa troppo grande, i processi che la gestiscono si bloccano e le conseguenze del rollback possono essere difficili da prevedere.

Soluzione — implementare la cancellazione "a pacchetti" (batch), elaborando un piccolo numero di righe in un ciclo con transazioni brevi, per minimizzare i blocchi e l'influenza sul sistema:

Esempio di codice (SQL Server):

WHILE 1=1
BEGIN
   DELETE TOP (10000)
   FROM YourHugeTable
   WHERE CreatedAt < DATEADD(year,-2,GETDATE());

   IF @@ROWCOUNT = 0 BREAK;
   WAITFOR DELAY '00:00:01'; -- piccola pausa per ridurre il carico
END

Caratteristiche chiave:

Si minimizzano le dimensioni dei blocchi e le registrazioni nel registro delle transazioni.
L'elaborazione avviene in piccole porzioni: il sistema rimane reattivo.
Può essere combinato con la visualizzazione dei progressi o una logica di monitoraggio esterna.

Domande ingannevoli.

Se si utilizza TRUNCATE invece di DELETE, è sempre più veloce e sicuro?

No. TRUNCATE è molto più veloce, ma :

TRUNCATE non può essere applicato se una chiave esterna fa riferimento alla tabella.
TRUNCATE non attiva i trigger.
TRUNCATE elimina completamente tutte le righe, non in base a una condizione.

È importante utilizzare indici sul campo filtro in un DELETE di massa?

Sì, avere un indice appropriato sulla colonna filtro (ad esempio, CreatedAt) accelera la ricerca delle righe da eliminare e riduce il carico sulla tabella. Senza indice, la query colpirà l'intera tabella, anche se in ogni porzione vengono eliminate solo poche righe.

CREATE INDEX idx_createdat ON YourHugeTable(CreatedAt);

Cosa succede se si eseguono più thread di massa DELETE contemporaneamente?

Questo porterà a una competizione per i blocchi: si verificheranno escalation dei blocchi, aumento dei tempi di attesa e probabilità di deadlock. La cancellazione di massa da una tabella dovrebbe avvenire in un solo processo, oppure con una suddivisione dei range ben ponderata.

Errori tipici e anti-pattern

Cancellazione di massa in un'unica transazione (blocca la tabella, sovraccarica il registro delle transazioni).
Mancanza di controllo del progresso e gestione del tempo di esecuzione.
Mancanza di indici — l'intera tabella viene scansionata ogni volta.

Esempio pratico

Caso negativo

Il DBA ha deciso di svuotare una tabella di 60 milioni di righe con una singola query DELETE FROM Log WHERE dt < '2021-01-01'. Il server è quasi "bloccato", gli altri processi hanno iniziato ad attendere l'esecuzione, il file del registro è aumentato drasticamente, il ripristino è diventato lungo.

Pro:

Facile da implementare.

Contro:

Notevole calo delle prestazioni dell'intero server, possibilità di perdita di dati in caso di crash, lungo tempo di ripristino.

Caso positivo

La cancellazione è stata suddivisa in pacchetti da 10.000 righe, il processo è controllato, dopo ogni porzione c'è una pausa. Il server funziona in modo stabile, gli altri compiti vengono completati, l'amministratore monitora i progressi.