juli 6, 2020
Articles
Ingen kommentarer

Beste måten å fylle ut en ny kolonne i en stor tabell?

Vi har en 2,2 GB tabell i Postgres med 7 801 611 rader. Vi legger til en uuid / guid-kolonne i den, og jeg lurer på hva den beste måten å fylle ut den kolonnen er (da vi vil legge til en NOT NULL begrensning for den).

Hvis jeg forstår Postgres riktig, er en oppdatering teknisk sett en delete and insert, så dette bygger i utgangspunktet hele 2,2 GB-tabellen. Vi har også en slave som kjører, så vi vil ikke at den skal ligge etter.

Er det noe bedre enn å skrive et skript som sakte fyller det over tid?

Kommentarer

  • Har du allerede kjørt en ALTER TABLE .. ADD COLUMN ... eller skal den delen også besvares?
  • Har ikke løpt eventuelle tabellendringer ennå, bare i planleggingsfasen. Jeg har gjort dette før ved å legge til kolonnen, fylle den ut, deretter legge til begrensningen eller indeksen. Denne tabellen er imidlertid betydelig større, og jeg er bekymret for belastning, låsing, replikering osv …

Svar

Det kommer veldig an på detaljene i oppsettet og kravene.

Merk at siden Postgres 11 bare legges til en kolonne med en flyktig DEFAULT utløser fremdeles en omskriving av tabellen . Dessverre er dette ditt tilfelle.

Hvis du har tilstrekkelig ledig plass på disken – minst 110% av pg_size_pretty((pg_total_relation_size(tbl)) – og har råd til en delelås i noen tid og en eksklusiv lås i veldig kort tid, og opprett deretter en ny tabell inkludert uuid kolonne ved hjelp av CREATE TABLE AS . Hvorfor?

Koden nedenfor bruker en -funksjon fra den ekstra uuid-oss -modulen .

  • Lås tabellen mot samtidige endringer i SHARE -modus (fremdeles tillater samtidig lesing). Forsøk på å skrive til bordet vil vente og til slutt mislykkes. Se nedenfor.

  • Kopier hele tabellen mens du fyller ut den nye kolonnen med en gang – eventuelt bestiller du rader gunstig mens du er i den.
    Hvis du kommer til å omorganisere rader, sørg for å sette work_mem høyt nok til å gjøre sorteringen i RAM eller så høyt som du har råd (bare for økten din, ikke globalt).

  • Deretter legg til begrensninger, utenlandske nøkler, indekser, utløsere etc. til den nye bord. Når du oppdaterer store deler av en tabell, er det mye raskere å lage indekser fra bunnen av enn å legge til rader iterativt. Beslektede råd i håndboken.

  • Når den nye tabellen er klar, kan du slippe den gamle og gi den nye navnet for å gjøre det til en innbytter. Bare dette siste trinnet får en eksklusiv lås på den gamle tabellen for resten av transaksjonen – som skal være veldig kort nå.
    Det krever også at du sletter et hvilket som helst objekt avhengig av tabelltypen (visninger, funksjoner ved bruk av tabelltypen i signaturen, …) og gjenskape dem etterpå.

  • Gjør alt i en transaksjon for å unngå ufullstendige tilstander. 

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 raskest. Enhver annen metode for oppdatering på plass må også omskrive hele bordet, bare på en dyrere måte. Du vil bare gå den veien hvis du ikke har nok ledig plass på disken eller ikke har råd til å låse hele tabellen eller generere feil for samtidig skriveforsøk.

Hva skjer med samtidige skrivinger?

Andre transaksjoner (i andre økter) som prøver å INSERT / UPDATE / DELETE i samme tabell etter at transaksjonen din har tatt SHARE låsen, venter til låsen frigjøres eller en tidsavbrudd sparker inn, det som kommer først. De vil mislykkes uansett, siden tabellen de prøvde å skrive til er slettet under dem.

Den nye tabellen har en ny tabell OID, men samtidig transaksjon har allerede løst tabellnavnet til OID for forrige tabell . Når låsen endelig frigjøres, prøver de å låse tabellen selv før de skriver til den og finner ut at den » er borte.Postgres vil svare:

ERROR: could not open relation with OID 123456

Hvor 123456 er OID for den gamle tabellen. Du må fange det unntaket og prøve spørsmål i appkoden din for å unngå det.

Hvis du ikke har råd til at det skal skje, må du beholde den opprinnelige tabellen.

Holde den eksisterende tabellen, alternativ 1

Oppdatering på plass (muligens kjører oppdateringen på små segmenter om gangen) før du legger til NOT NULL begrensningen . Å legge til en ny kolonne med NULL-verdier og uten NOT NULL begrensning er billig.
Siden Postgres 9.2 kan du også opprette en CHECK begrensning med NOT VALID :

Begrensningen vil fortsatt håndheves mot påfølgende innlegg eller oppdateringer

Som lar deg oppdatere rader peu à peu – i flere separate transaksjoner . Dette unngår å holde radlåser for lenge, og det gjør det også mulig å bruke døde rader på nytt. (Du må kjøre VACUUM manuelt hvis det ikke er nok tid i mellom til at autovakuum kan sparke inn.) Til slutt, legg til NOT NULL begrensning og fjern NOT VALID CHECK begrensningen: 

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;

Beslektet svar som diskuterer NOT VALID mer detaljert:

Behold den eksisterende tabellen, alternativ 2

Forbered den nye tilstanden i en midlertidig tabell , TRUNCATE originalen og påfyll fra temp-tabellen. Alt i en transaksjon . Du trenger fortsatt å ta en SHARE lås før pr utjevning av den nye tabellen for å forhindre tap av samtidige skriv.

Detaljer i dette relaterte svaret på SO:

Kommentarer

  • Fantastisk svar! Nøyaktig informasjonen jeg lette etter. To spørsmål 1. Har du noen ide om en enkel måte å teste hvor lang tid en handling som denne vil ta? 2. Hvis det tar si 5 minutter, hva skjer med handlinger som prøver å oppdatere en rad i den tabellen i løpet av de 5 minuttene?
  • @CollinPeters: 1. Løven ' s andel av tiden ville gå til å kopiere det store bordet – og muligens gjenskape indekser og begrensninger (det avhenger). Å slippe og gi nytt navn er billig. For å teste kan du kjøre det forberedte SQL-skriptet uten LOCK til og med DROP. Jeg kunne bare uttale ville og unyttige gjetninger. Når det gjelder 2., vær så snill å vurdere tillegget til svaret mitt.
  • @ErwinBrandstetter Fortsett med å gjenskape visninger, så hvis jeg har et dusin visninger som fremdeles bruker gammel tabell (oid) etter å gi nytt navn til bord. Er det noen måte å utføre dyp erstatning i stedet for å kjøre hele visningsoppdateringen / -opprettingen på nytt?
  • @CodeFarmer: Hvis du bare gir nytt navn til en tabell, fortsetter visninger å jobbe med den navngitte tabellen. For å lage visninger, bruk ny tabellen i stedet, må du gjenskape dem basert på den nye tabellen. (Også for å la den gamle tabellen slettes.) Ingen (praktisk) vei rundt den.
  • siden 9.2 postgres ' t kaster could not open relation with OID 123456

Svar

Jeg har ikke et» beste «svar, men Jeg har et «minst dårlig» svar som kan la deg få ting gjort ganske raskt.

Tabellen min hadde 2MM rader, og oppdateringsytelsen ble tøff når jeg prøvde å legge til en sekundær tidsstempelkolonne som var standard til 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 ;

Etter at den hang i 40 minutter, prøvde jeg dette på et lite parti for å få en ide om hvor lang tid dette kunne ta – prognosen var rundt 8 timer.

Det aksepterte svaret er definitivt bedre – men denne tabellen er mye brukt i databasen min. Det er noen titalls tabeller som FKEY på den. Jeg ønsket å unngå å bytte UTENLANDSKE TASTER på så mange bord . Og så er det synspunkter.

Litt å søke i dokumenter, case-studier og StackOverflow, og jeg hadde «A-Ha!» Øyeblikket. var ikke på kjernen UPDATE, men på alle INDEX-operasjonene. Tabellen min hadde 12 indekser – noen få for unike begrensninger, noen få for å få fart på søkeplanleggeren og noen få for fulltekstsøk.

Hver rad som ble OPPDATERT, jobbet ikke bare med en DELETE / INSERT, men også overhead for å endre hver indeks og kontrollere begrensninger.

Min løsning var å slippe hver indeks og begrensning, oppdater tabellen, og legg deretter til alle indeksene / begrensningene igjen.

Det tok omtrent 3 minutter å skrive en SQL-transaksjon som gjorde følgende:

  • BEGIN;
  • droppede indekser / constaints
  • oppdateringstabell
  • legg til på nytt indekser / begrensninger
  • COMMIT;

Skriptet tok 7 minutter å kjøre.

Det aksepterte svaret er definitivt bedre og mer riktig … og eliminerer praktisk talt behovet for nedetid. I mitt tilfelle ville det imidlertid tatt betydelig mer » Utvikler «jobbet for å bruke den løsningen, og vi hadde et 30-minutters vindu med planlagt nedetid som den kunne oppnås i. Vår løsning adresserte det i 10.

Kommentarer

  • For ikke å nevne at det tillater samtidig skriving. Samtidige lesinger kan være langsomme uten indeks s skjønt 🙂 Ville være interessant å sammenligne det med de andre alternativene …

Legg igjen en kommentar

Din e-postadresse vil ikke bli publisert. Obligatoriske felt er merket med *

Deep Theme Powered by WordPress