Il modo migliore per popolare una nuova colonna in una tabella di grandi dimensioni?

Dipende molto dai dettagli della configurazione e dai requisiti.

Tieni presente che, a partire da Postgres 11, solo laggiunta di una colonna con un volatile DEFAULT attiva ancora la riscrittura della tabella . Purtroppo questo è il tuo caso.

Se hai spazio libero sufficiente su disco – almeno il 110% di pg_size_pretty((pg_total_relation_size(tbl)) e può permettersi un blocco di condivisione per un po di tempo e un blocco esclusivo per brevissimo tempo, quindi crea un nuova tabella inclusa la colonna uuid utilizzando CREATE TABLE AS . Perché?

• Che cosa causa il rallentamento di INSERT di grandi dimensioni e lesplosione dellutilizzo del disco?

Il codice seguente utilizza una funzione dal uuid-oss modulo aggiuntivo.

• Blocca la tabella contro modifiche simultanee in SHARE modalità (consentendo comunque letture simultanee). I tentativi di scrivere nella tabella attenderanno e alla fine falliranno. Vedi sotto.

• Copia lintera tabella mentre popola la nuova colonna al volo, possibilmente ordinando le righe favorevolmente mentre ci sei.
  If stai per riordinare le righe, assicurati di impostare work_mem abbastanza alto per eseguire lordinamento nella RAM o altrettanto alto come ti puoi permettere (solo per la tua sessione, non a livello globale).

• Quindi aggiungi vincoli, chiavi esterne, indici, trigger ecc. al nuovo tavolo. Quando si aggiornano grandi porzioni di una tabella, è molto più veloce creare indici da zero che aggiungere righe iterativamente. Consigli correlati nel manuale.

• Quando la nuova tabella è pronta, elimina la vecchia e rinomina la nuova per renderlo un sostituto immediato. Solo questultimo passaggio acquisisce un blocco esclusivo sulla vecchia tabella per il resto della transazione, che ora dovrebbe essere molto breve.
  Richiede inoltre leliminazione di qualsiasi oggetto a seconda del tipo di tabella (viste, funzioni che utilizzano il tipo di tabella nella firma, …) e ricrearli in seguito.

• Fai tutto in una transazione per evitare stati incompleti.

BEGIN; LOCK TABLE tbl IN SHARE MODE; SET LOCAL work_mem = "???? MB"; -- just for this transaction CREATE TABLE tbl_new AS SELECT uuid_generate_v1() AS tbl_uuid, <list of all columns in order> FROM tbl ORDER BY ??; -- optionally order rows favorably while being at it. ALTER TABLE tbl_new ALTER COLUMN tbl_uuid SET NOT NULL , ALTER COLUMN tbl_uuid SET DEFAULT uuid_generate_v1() , ADD CONSTRAINT tbl_uuid_uni UNIQUE(tbl_uuid); -- more constraints, indices, triggers? DROP TABLE tbl; ALTER TABLE tbl_new RENAME tbl; -- recreate views etc. if any COMMIT; 

Dovrebbe essere il più veloce. Qualsiasi altro metodo di aggiornamento in atto deve riscrivere anche lintera tabella, solo in un modo più costoso. Dovresti seguire questa strada solo se non hai abbastanza spazio libero su disco o non puoi permetterti di bloccare lintera tabella o generare errori per tentativi di scrittura simultanei.

Cosa succede alle scritture simultanee?

Altra transazione (in altre sessioni) che tenta di INSERT / UPDATE / DELETE nella stessa tabella dopo che la transazione ha acquisito il blocco SHARE, aspetterà fino a quando il blocco non viene rilasciato o si verifica un timeout, a seconda dellevento che si verifica per primo. >

Non ho una risposta” migliore “, ma Ho una risposta “meno negativa” che potrebbe consentirti di fare le cose in modo ragionevolmente veloce.

La mia tabella aveva 2 MM di righe e le prestazioni di aggiornamento stavano rallentando quando ho provato ad aggiungere una colonna timestamp secondaria che per impostazione predefinita era la prima .

ALTER TABLE mytable ADD new_timestamp TIMESTAMP ; UPDATE mytable SET new_timestamp = old_timestamp ; ALTER TABLE mytable ALTER new_timestamp SET NOT NULL ; 

Dopo che è rimasto in sospeso per 40 minuti, lho provato su un piccolo lotto per avere unidea di quanto tempo potrebbe richiedere: la previsione era intorno 8 ore.

La risposta accettata è decisamente migliore, ma questa tabella è ampiamente utilizzata nel mio database. Ci sono alcune dozzine di tabelle in cui FKEY su di essa; Volevo evitare di cambiare CHIAVI ESTERE su così tante tabelle . E poi ci sono le visualizzazioni.

Un po di ricerca in documenti, case study e StackOverflow, e ho avuto il momento “A-Ha!”. non era nel core UPDATE, ma in tutte le operazioni INDEX. La mia tabella aveva 12 indici: alcuni per vincoli univoci, alcuni per accelerare il pianificatore di query e alcuni per la ricerca a testo completo.

Ogni riga che è stata AGGIORNATA non stava solo lavorando su CANCELLAZIONE / INSERIMENTO, ma anche il sovraccarico di alterare ogni indice e controllare i vincoli.

La mia soluzione era eliminare ogni indice e vincolo, aggiorna la tabella, quindi aggiungi nuovamente tutti gli indici / vincoli.

Ci sono voluti circa 3 minuti per scrivere una transazione SQL che eseguisse le seguenti operazioni:

• BEGIN;
• eliminati indici / vincoli
• aggiorna tabella
• aggiungi nuovamente indici / vincoli
• COMMIT;

Lesecuzione dello script ha richiesto 7 minuti.

La risposta accettata è decisamente migliore e più appropriata … ed elimina virtualmente la necessità di tempi di inattività. Nel mio caso, però, ci sarebbe voluto molto di più ” Lo sviluppatore “lavorava per utilizzare quella soluzione e avevamo un periodo di inattività pianificato di 30 minuti in cui poteva essere realizzato. La nostra soluzione lo ha affrontato in 10.

Commenti

• Per non parlare del fatto che consente le scritture simultanee. Le letture simultanee potrebbero essere lente senza indexe s comunque 🙂 Sarebbe interessante fare un benchmark confrontandolo con le altre opzioni …