iulie 6, 2020
Articles
Niciun comentariu

Cel mai bun mod de a completa o coloană nouă într-un tabel mare?

Avem un tabel de 2,2 GB în Postgres cu 7.801.611 rânduri în el. Adăugăm o coloană uuid / guid și mă întreb care este cel mai bun mod de a completa coloana respectivă (deoarece dorim să adăugăm o constrângere NOT NULL).

Dacă înțeleg corect Postgres, o actualizare este, din punct de vedere tehnic, o ștergere și inserare, deci aceasta este practic reconstituirea întregului tabel de 2,2 gb. De asemenea, avem un sclav care rulează, așa că nu vrem ca acesta să rămână în urmă.

Există vreo modalitate mai bună decât să scriem un script care să-l populeze încet în timp?

Comentarii

  • Ați rulat deja un ALTER TABLE .. ADD COLUMN ... sau trebuie să răspundeți și acelei părți?
  • Nu ați rulat orice modificare a tabelului încă, doar în faza de planificare. Am făcut acest lucru înainte adăugând coloana, completând-o, apoi adăugând constrângerea sau indexul. Cu toate acestea, acest tabel este semnificativ mai mare și sunt îngrijorat de încărcare, blocare, replicare etc. …

Răspuns

Depinde foarte mult de detaliile configurării și cerințelor dvs.

Rețineți că, de la Postgres 11, adăugarea doar a unei coloane cu o volatil DEFAULT declanșează încă o rescriere a tabelului . Din păcate, acesta este cazul dvs.

Dacă aveți spațiu liber suficient pe disc – cel puțin 110% din pg_size_pretty((pg_total_relation_size(tbl)) – și își poate permite un blocare partajare pentru ceva timp și o blocare exclusivă pentru un timp foarte scurt, apoi creați un tabel nou inclusiv coloana uuid folosind CREATE TABLE AS . De ce?

Codul de mai jos folosește o funcție din modulul suplimentar uuid-oss .

  • Blocați tabelul împotriva modificărilor concurente în modul SHARE (permițând în continuare citirile simultane). Încercările de a scrie pe tabel vor aștepta și vor eșua în cele din urmă. Vedeți mai jos.

  • Copiați întregul tabel în timp ce completați noua coloană din mers – eventual ordonând rânduri favorabil în timp ce vă aflați la el.
    Dacă urmează să reordonați rândurile, asigurați-vă că setați work_mem suficient de mare pentru a face sortarea în RAM sau la fel de mare după cum vă puteți permite (doar pentru sesiunea dvs., nu la nivel global).

  • Apoi adăugați constrângeri, chei străine, indici, declanșatori etc. masa. Când actualizați porțiuni mari ale unui tabel, este mult mai rapid să creați indici de la zero decât să adăugați rânduri iterativ. Sfaturi conexe din manual.

  • Când noul tabel este gata, renunțați la vechiul și redenumiți noul pentru a-l transforma în înlocuitor. Doar acest ultim pas dobândește o blocare exclusivă pe vechea tabelă pentru restul tranzacției – care ar trebui să fie foarte scurtă acum.
    De asemenea, este necesar să ștergeți orice obiect în funcție de tipul tabelului (vizualizări, funcții folosind tipul tabelei) în semnătură, …) și recreați-le după aceea.

  • Faceți totul într-o singură tranzacție pentru a evita stările incomplete. 

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;

Acesta ar trebui să fie cel mai rapid. Orice altă metodă de actualizare trebuie să rescrie tot tabelul, într-un mod mai scump. Ați parcurge această rută numai dacă nu aveți suficient spațiu liber pe disc sau nu vă puteți permite să blocați întregul tabel sau să generați erori pentru încercări de scriere simultane.

Ce se întâmplă cu scrierile simultane?

Altă tranzacție (în alte sesiuni) care încearcă să INSERT / UPDATE / DELETE în același tabel după ce tranzacția dvs. a preluat blocarea SHARE, va aștepta până când blocarea este eliberată sau va începe un timeout, oricare ar fi primul. Acestea vor eșuează în ambele sensuri, deoarece tabelul pe care încercau să-l scrie a fost șters de sub ele.

Noul tabel are un nou tabelul OID, dar tranzacția concurentă a rezolvat deja numele tabelei cu OID-ul tabelului anterior . Când blocarea este eliberată definitiv, încearcă să blocheze tabela ei înșiși înainte de a-i scrie și găsesc că ” s-a dus.Postgres va răspunde:

ERROR: could not open relation with OID 123456

Unde 123456 este OID-ul vechiului tabel. Trebuie să prindeți această excepție și să încercați din nou interogările în codul aplicației pentru a o evita.

Dacă nu vă puteți permite acest lucru, trebuie să păstrați tabelul dvs. original.

Păstrarea tabelului existent, alternativa 1

Actualizare la loc (posibil rularea actualizării pe segmente mici odată) înainte de a adăuga constrângerea NOT NULL . Adăugarea unei coloane noi cu valori NULL și fără NOT NULL constrângere este ieftină.
De la Postgres 9.2 puteți crea, de asemenea, o CHECK constrângere cu NOT VALID :

Constrângerea va continua să fie pus în aplicare împotriva inserțiilor sau actualizărilor ulterioare

Acest lucru vă permite să actualizați rândurile peu à peu – în mai multe tranzacții separate . Acest lucru evită păstrarea blocărilor rândurilor prea mult timp și permite, de asemenea, reutilizarea rândurilor moarte. (Va trebui să rulați VACUUM manual dacă nu există suficient timp între care să înceapă autovacuum.) În cele din urmă, adăugați NOT NULL constrângere și eliminați NOT VALID CHECK constrângere: 

ALTER TABLE tbl ADD CONSTRAINT tbl_no_null CHECK (tbl_uuid IS NOT NULL) NOT VALID; -- update rows in multiple batches in separate transactions -- possibly run VACUUM between transactions ALTER TABLE tbl ALTER COLUMN tbl_uuid SET NOT NULL; ALTER TABLE tbl ALTER DROP CONSTRAINT tbl_no_null;

Răspuns asociat discutând NOT VALID mai detaliat:

Păstrarea tabelului existent, alternativa 2

Pregătiți noua stare într-un tabel temporar , TRUNCATE originalul și reumpleți din tabelul temp. Toate în o tranzacție . Dvs. încă trebuie să luați un SHARE blocare înainte relatii cu publicul pregătirea noului tabel pentru a preveni pierderea scrierilor simultane.

Detalii în acest răspuns asociat pe SO:

Comentarii

  • Răspuns fantastic! Exact informațiile pe care le căutam. Două întrebări 1. Aveți vreo idee despre o modalitate ușoară de a testa cât ar dura o acțiune ca aceasta? 2. Dacă durează 5 minute, ce se întâmplă cu acțiunile care încearcă să actualizeze un rând din acel tabel în acele 5 minute?
  • @CollinPeters: 1. Leul ' din timp ar merge în copierea tabelului mare – și, eventual, în recreerea indicilor și constrângerilor (asta depinde). Renunțarea și redenumirea sunt ieftine. Pentru a testa puteți rula scriptul SQL pregătit fără LOCK până la și excluzând DROP. Nu puteam rosti decât ghicituri sălbatice și inutile. În ceea ce privește 2., vă rugăm să luați în considerare addendum-ul la răspunsul meu.
  • @ErwinBrandstetter Continuați să recreați vizualizări, deci dacă am o duzină de vizualizări care folosesc încă tabelul vechi (oid) după redenumirea tabelului. Există vreo modalitate de a efectua o înlocuire profundă, mai degrabă decât de a rula din nou întreaga reîmprospătare / creare a vizualizărilor? Pentru a face vizualizări, utilizați în schimb tabelul nou , trebuie să le recreați pe baza noii tabele. (De asemenea, pentru a permite ștergerea vechiului tabel.) Niciun (practic) în jurul său.
  • deoarece 9.2 postgres nu ' nu aruncă could not open relation with OID 123456

Răspuns

Nu am„ cel mai bun ”răspuns, dar Am un răspuns „cel mai puțin rău” care ar putea să vă permită să faceți lucrurile destul de repede.

Tabelul meu avea 2MM rânduri și performanța actualizării a fost dificilă atunci când am încercat să adaug o coloană secundară de timestamp care a fost implicită 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 ;

După ce a atârnat timp de 40 de minute, am încercat acest lucru pe un lot mic pentru a-mi face o idee despre cât de mult ar putea dura acest lucru – prognoza era în jur 8 ore.

Răspunsul acceptat este cu siguranță mai bun – dar acest tabel este foarte utilizat în baza mea de date. Există câteva zeci de tabele care FKEY pe el; am vrut să evit comutarea cheilor străine pe atâtea tabele . Și apoi există puncte de vedere.

Un pic de căutare de documente, studii de caz și StackOverflow și am avut momentul „A-Ha!”. nu era pe actualizarea de bază, ci pe toate operațiunile INDEX. Tabelul meu avea 12 indexuri – câteva pentru constrângeri unice, câteva pentru accelerarea planificatorului de interogări și câteva pentru căutarea textului complet.

Fiecare rând care a fost ACTUALIZAT nu a funcționat doar pe un DELETE / INSERT, ci și cheltuielile generale pentru modificarea fiecărui index și verificarea constrângerilor.

Soluția mea a fost să renunț la fiecare index și constrângere, actualizați tabelul, apoi adăugați din nou toate indexurile / constrângerile.

A fost nevoie de aproximativ 3 minute pentru a scrie o tranzacție SQL care a făcut următoarele:

  • BEGIN;
  • indexuri / constanțe renunțate
  • tabel de actualizare
  • re-adăugați indexuri / constrângeri
  • COMMIT;

Scriptul a durat 7 minute pentru a fi rulat.

Răspunsul acceptat este cu siguranță mai bun și mai adecvat … și elimină practic necesitatea perioadelor de nefuncționare. În cazul meu, totuși, ar fi durat mult mai mult ” Dezvoltatorul „lucrează pentru a utiliza acea soluție și am avut o fereastră de 30 de minute programată de nefuncționare programată în care soluția noastră a abordat-o în 10.

Comentarii

>

  • Ca să nu mai vorbim, permite scrieri simultane. Citirile simultane pot fi lente, fără index Totuși 🙂 Ar fi interesant să-l comparăm cu celelalte opțiuni …

    • Lasă un răspuns

    Adresa ta de email nu va fi publicată. Câmpurile obligatorii sunt marcate cu *