Il modo migliore per cancellare milioni di righe per ID

Devo cancellare circa 2 milioni di righe dal mio database PG. Ho una lista di ID che devo cancellare. Comunque, ogni modo che provo a fare è prendere giorni.

Ho provato a metterli in un tavolo e a farlo in gruppi di 100. 4 giorni dopo, questo è ancora in esecuzione con solo 297268 righe cancellate. (Dovevo selezionare 100 ID da una tabella ID, eliminare dove IN quell’elenco, eliminare dalla tabella ids il 100 che ho selezionato).

Provai:

DELETE FROM tbl WHERE id IN (select * from ids) 

Anche questo sta durando per sempre. È difficile valutare quanto a lungo, dal momento che non riesco a vedere i progressi fino al completamento, ma la query era ancora in esecuzione dopo 2 giorni.

Sto solo cercando il modo più efficace per eliminare da una tabella quando conosco gli ID specifici da eliminare e ci sono milioni di ID.

Tutto dipende …

  • Elimina tutti gli indici (tranne quello sull’ID di cui hai bisogno per l’eliminazione)
    Ricreare in seguito (= molto più veloce di aggiornamenti incrementali agli indici)

  • Controlla se hai trigger che possono essere cancellati / disabilitati in modo sicuro temporaneamente

  • Le chiavi esterne fanno riferimento al tuo tavolo? Possono essere cancellati? Temporaneamente cancellato?

  • A seconda delle impostazioni del autovolume, potrebbe essere utile eseguire VACUUM ANALYZE prima dell’operazione.

  • Supponendo che non vi sia accesso simultaneo in scrittura alle tabelle coinvolte, potrebbe essere necessario bloccare le tabelle esclusivamente o questa route potrebbe non essere affatto per te.

  • Alcuni dei punti elencati nel relativo capitolo del manuale Popolamento di un database possono anche essere utili, a seconda della configurazione.

  • Se si eliminano ampie porzioni della tabella e il resto si inserisce nella RAM, il modo più semplice e veloce sarebbe questo:

 SET temp_buffers = '1000MB'; -- or whatever you can spare temporarily CREATE TEMP TABLE tmp AS SELECT t.* FROM tbl t LEFT JOIN del_list d USING (id) WHERE d.id IS NULL; -- copy surviving rows into temporary table TRUNCATE tbl; -- empty table - truncate is very fast for big tables INSERT INTO tbl SELECT * FROM tmp; -- insert back surviving rows. 

In questo modo non è necessario ricreare viste, chiavi esterne o altri oggetti dipendenti. Leggi le impostazioni di temp_buffers nel manuale . Questo metodo è veloce fintanto che la tabella si adatta alla memoria, o almeno alla maggior parte di essa. Tieni presente che puoi perdere dati se il tuo server si blocca durante questa operazione. È ansible avvolgere tutto in una transazione per renderlo più sicuro.

Esegui ANALYZE seguito. Oppure VACUUM ANALYZE se non si è passati al percorso troncato o VACUUM FULL ANALYZE se si desidera portarlo alla dimensione minima. Per i tavoli grandi considerate le alternative CLUSTER / pg_repack :

  • Ottimizza intervallo di query timestamp Postgres

Per le tabelle piccole, un semplice DELETE anziché TRUNCATE è spesso più veloce:

 DELETE FROM tbl t USING del_list d WHERE t.id = d.id; 

Leggi la sezione Note per TRUNCATE nel manuale . In particolare (come Pedro ha anche sottolineato nel suo commento ):

TRUNCATE non può essere utilizzato su una tabella che ha riferimenti a chiavi esterne da altre tabelle, a meno che tutte queste tabelle non vengano troncate nello stesso comando. […]

E:

TRUNCATE non genererà alcun trigger ON DELETE che potrebbe esistere per le tabelle.

Sappiamo che le prestazioni di aggiornamento / eliminazione di PostgreSQL non sono così potenti come Oracle. Quando dobbiamo eliminare milioni o decine di milioni di righe, è davvero difficile e richiede molto tempo.

Tuttavia, possiamo ancora farlo in dbs di produzione. La seguente è la mia idea:

Per prima cosa dovremmo creare una tabella di log con 2 colonne: id e flag ( id riferisce all’id che vuoi eliminare, flag può essere Y o null , con Y indica che il record è stato cancellato con successo).

Più tardi, creiamo una funzione. Facciamo l’operazione di eliminazione ogni 10.000 righe. Puoi vedere maggiori dettagli sul mio blog . Anche se è in cinese, puoi ancora ottenere le informazioni che desideri dal codice SQL lì.

Assicurati che la colonna id di entrambe le tabelle sia indicizzata, poiché verrà eseguita più rapidamente.

Puoi provare a copiare tutti i dati nella tabella tranne gli ID che desideri eliminare su una nuova tabella, quindi rinominare e poi scambiare le tabelle (a condizione che tu abbia abbastanza risorse per farlo).

Questo non è un consiglio esperto.

Due risposte possibili:

  1. La tabella potrebbe contenere molti vincoli o trigger quando si tenta di eliminare un record. Dovrà sostenere molti cicli del processore e il controllo da altre tabelle.

  2. Potrebbe essere necessario inserire questa dichiarazione all’interno di una transazione.

Il modo più semplice per farlo sarebbe quello di eliminare tutti i tuoi vincoli e quindi fare l’eliminazione.

Per prima cosa assicurati di avere un indice sui campi ID, sia nella tabella che vuoi eliminare sia nella tabella che stai utilizzando per gli ID di cancellazione.

100 alla volta sembra troppo piccolo. Prova con 1000 o 10000.

Non è necessario cancellare nulla dalla tabella ID di cancellazione. Aggiungere una nuova colonna per un numero di lotto e riempirla con 1000 per il lotto 1, 1000 per il lotto 2, ecc. E assicurarsi che la query di cancellazione includa il numero di lotto.

Se la tabella che stai cancellando è referenziata da some_other_table (e non vuoi rilasciare le chiavi esterne anche temporaneamente), assicurati di avere un indice sulla colonna di riferimento in some_other_table !

Ho avuto un problema simile e auto_explain usato auto_explain con auto_explain.log_nested_statements = true , che ha rivelato che l’ delete stava effettivamente facendo seq_scans su some_other_table :

  Query Text: SELECT 1 FROM ONLY "public"."some_other_table" x WHERE $1 OPERATOR(pg_catalog.=) "id" FOR KEY SHARE OF x LockRows (cost=[...]) -> Seq Scan on some_other_table x (cost=[...]) Filter: ($1 = id) 

Apparentemente sta provando a bloccare le file di riferimento nell’altra tabella (che non dovrebbe esistere, o l’eliminazione fallirà). Dopo aver creato gli indici sulle tabelle di riferimento, l’eliminazione era più veloce di ordini di grandezza.