juli 6, 2020
Articles
Ingen kommentarer

Bedste måde at udfylde en ny kolonne i en stor tabel?

Vi har en 2,2 GB tabel i Postgres med 7.801.611 rækker i den. Vi tilføjer en uuid / guid-kolonne til den, og jeg spekulerer på, hvad den bedste måde at udfylde den kolonne er på (da vi vil tilføje en NOT NULL begrænsning til den).

Hvis jeg forstår Postgres korrekt, er en opdatering teknisk set en sletning og indsættelse, så dette grundlæggende genopbygger hele 2,2 GB-tabellen. Vi har også en slave, der kører, så vi vil ikke have den bagud.

Er der nogen måde bedre end at skrive et script, der langsomt udfylder det over tid?

Kommentarer

  • Har du allerede kørt en ALTER TABLE .. ADD COLUMN ... eller skal den del også besvares?
  • Har du ikke kørt eventuelle tabelændringer endnu, lige i planlægningsfasen. Jeg har gjort dette før ved at tilføje kolonnen, udfylde den og derefter tilføje begrænsningen eller indekset. Denne tabel er dog betydeligt større, og jeg er bekymret for belastning, låsning, replikering osv. …

Svar

Det afhænger meget af detaljerne i din opsætning og krav.

Bemærk, at siden Postgres 11 kun tilføjes en kolonne med en flygtig DEFAULT udløser stadig en tabelomskrivning . Desværre er dette din sag.

Hvis du har tilstrækkelig ledig plads på disken – mindst 110% af pg_size_pretty((pg_total_relation_size(tbl)) – og har råd til en delelås i nogen tid og en eksklusiv lås i meget kort tid, og opret derefter en ny tabel inklusive uuid kolonne ved hjælp af CREATE TABLE AS . Hvorfor?

Nedenstående kode bruger en -funktion fra det ekstra uuid-oss -modul .

  • Lås tabellen mod samtidige ændringer i SHARE -tilstand (tillader stadig samtidig læsning). Forsøg på at skrive til bordet venter og mislykkes til sidst. Se nedenfor.

  • Kopier hele tabellen, mens du udfylder den nye kolonne med det samme – muligvis bestiller du rækker, mens du er ved den.
    Hvis du skal omarrangere rækker, skal du sørge for at indstille work_mem højt nok til at udføre sorteringen i RAM eller så højt som du har råd (kun til din session, ikke globalt).

  • Derefter tilføj begrænsninger, fremmednøgler, indekser, udløsere osv. til det nye bord. Når du opdaterer store dele af en tabel, er det meget hurtigere at oprette indekser fra bunden end at tilføje rækker iterativt. Relateret rådgivning i manualen.

  • Når den nye tabel er klar, skal du slippe den gamle og omdøbe den nye at gøre det til en drop-in erstatning. Kun dette sidste trin erhverver en eksklusiv lås på den gamle tabel for resten af transaktionen – som skulle være meget kort nu.
    Det kræver også, at du sletter ethvert objekt afhængigt af bordtypen (visninger, funktioner ved hjælp af tabeltypen i signaturen, …) og genskab dem bagefter.

  • Gør det hele i en transaktion for at undgå ufuldstændige tilstande. 

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;

Dette skal være hurtigst. Enhver anden metode til opdatering på plads skal også omskrive hele bordet, bare på en dyrere måde. Du vil kun gå den rute, hvis du ikke har nok ledig plads på disken eller ikke har råd til at låse hele bordet eller generere fejl til samtidige skriveforsøg.

Hvad sker der med samtidige skrivninger?

Anden transaktion (i andre sessioner) forsøger at INSERT / UPDATE / DELETE i den samme tabel, efter at din transaktion har taget SHARE låsen, venter, indtil låsen frigøres, eller en timeout starter, alt efter hvad der kommer først. De vil mislykkes på nogen måde, da tabellen, de forsøgte at skrive til, er blevet slettet under dem.

Den nye tabel har en ny tabel OID, men samtidig transaktion har allerede løst tabelnavnet til OID for forrige tabel . Når låsen endelig frigøres, prøver de at låse tabellen selv, før de skriver til den og finder ud af, at den ” er væk.Postgres vil svare:

ERROR: could not open relation with OID 123456

Hvor 123456 er OID for den gamle tabel. Du er nødt til at fange denne undtagelse og prøve forespørgsler igen i din appkode for at undgå den.

Hvis du ikke har råd til, at det sker, skal du beholde din originale tabel.

Hold den eksisterende tabel, alternativ 1

Opdatering på plads (muligvis kører opdateringen på små segmenter ad gangen), før du tilføjer NOT NULL begrænsningen . Tilføjelse af en ny kolonne med NULL-værdier og uden NOT NULL begrænsning er billig.
Da Postgres 9.2 kan du også oprette en CHECK begrænsning med NOT VALID :

Begrænsningen vil stadig være håndhæves mod efterfølgende indsatser eller opdateringer

Det giver dig mulighed for at opdatere rækker peu à peu – i flere separate transaktioner . Dette undgår at holde række låse for længe, og det gør det også muligt at genbruge døde rækker. (Du skal køre VACUUM manuelt, hvis der ikke er nok tid imellem til, at autovakuum kan sparke ind.) Til sidst tilføj NOT NULL begrænsning og fjern NOT VALID CHECK begrænsning: 

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;

Relateret svar, der diskuterer NOT VALID mere detaljeret:

Behold den eksisterende tabel, alternativ 2

Forbered den nye tilstand i en midlertidig tabel , TRUNCATE originalen og genopfyld fra temp-tabellen. Alt i en transaktion . Du skal stadig tage en SHARE lås før pr udjævning af den nye tabel for at forhindre tab af samtidige skriv.

Detaljer i dette relaterede svar på SO:

Kommentarer

  • Fantastisk svar! Præcis den information, jeg ledte efter. To spørgsmål 1. Har du nogen idé om en nem måde at teste, hvor lang tid en handling som denne vil tage? 2. Hvis det tager sig 5 minutter, hvad sker der med handlinger, der forsøger at opdatere en række i denne tabel i løbet af disse 5 minutter?
  • @CollinPeters: 1. Løven ' s andel af tiden ville gå i at kopiere det store bord – og muligvis genskabe indekser og begrænsninger (det afhænger af). At slippe og omdøbe er billigt. For at teste kan du køre dit forberedte SQL-script uden LOCK op til og eksklusive DROP. Jeg kunne kun fremsætte vilde og ubrugelige gæt. Hvad angår 2., bedes du overveje tillægget til mit svar.
  • @ErwinBrandstetter Fortsæt med at genskabe synspunkter, så hvis jeg har et dusin synspunkter, der stadig bruger den gamle tabel (oid) efter omdøbning af tabel. Er der nogen måde at udføre dyb erstatning i stedet for at køre hele opdateringen / oprettelsen af visningen?
  • @CodeFarmer: Hvis du bare omdøber en tabel, fortsætter visningerne med den omdøbte tabel. For at få visninger skal du bruge tabellen ny i stedet for, du skal genskabe dem baseret på den nye tabel. (Også for at tillade at den gamle tabel slettes.) Ingen (praktisk) vej rundt den.
  • da 9.2 postgres ' t kast could not open relation with OID 123456

Svar

Jeg har ikke et” bedste “svar, men Jeg har et “mindst dårligt” svar, der muligvis giver dig mulighed for at få tingene gjort rimeligt hurtigt.

Min tabel havde 2MM rækker, og opdateringsydelsen blev tøffende, da jeg forsøgte at tilføje en sekundær tidsstempelkolonne, der som standard var den første . 

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

Efter at det hang i 40 minutter, prøvede jeg dette på et lille parti for at få en idé om, hvor lang tid det kunne tage – prognosen var omkring 8 timer.

Det accepterede svar er bestemt bedre – men denne tabel bruges meget i min database. Der er et par dusin tabeller, der FKEY på den; Jeg ville undgå at skifte UDENLANDSKE NØGLER på så mange tabeller Og så er der synspunkter.

Lidt at søge i dokumenter, casestudier og StackOverflow, og jeg havde øjeblikket “A-Ha!”. var ikke på kernen UPDATE, men på alle INDEX-operationer. Min tabel havde 12 indekser – et par til unikke begrænsninger, et par til at fremskynde forespørgselsplanlæggeren og et par til fuldtekstsøgning.

Hver række, der blev OPDATERET, arbejdede ikke bare med en DELETE / INSERT, men også omkostningerne ved at ændre hvert indeks og kontrollere begrænsninger.

Min løsning var at droppe hvert indeks og begrænsning, opdater tabellen, og tilføj derefter alle indekser / begrænsninger igen.

Det tog cirka 3 minutter at skrive en SQL-transaktion, der gjorde følgende:

  • BEGIN;
  • droppede indekser / konstanter
  • opdateringstabel
  • tilføj indekser / begrænsninger igen
  • COMMIT;

Scriptet tog 7 minutter at køre.

Det accepterede svar er bestemt bedre og mere korrekt … og eliminerer stort set behovet for nedetid. I mit tilfælde ville det dog have taget betydeligt mere ” Udvikler “arbejder på at bruge den løsning, og vi havde et 30 minutters vindue med planlagt nedetid, som det kunne opnås i. Vores løsning behandlede det i 10.

Kommentarer

  • For ikke at nævne det tillader samtidig skrivning. Samtidige læsninger kan være langsomme uden indeks s dog 🙂 Ville være interessant at sammenligne det med de andre muligheder …

Skriv et svar

Din e-mailadresse vil ikke blive publiceret. Krævede felter er markeret med *

Deep Theme Powered by WordPress