juli 6, 2020
Articles
Inga kommentarer

Bästa sättet att fylla i en ny kolumn i en stor tabell?

Vi har en 2,2 GB-tabell i Postgres med 7 801 611 rader i den. Vi lägger till en uuid / guid-kolumn till den och jag undrar vad som är det bästa sättet att fylla i den kolumnen (eftersom vi vill lägga till en NOT NULL begränsning till den).

Om jag förstår Postgres korrekt är en uppdatering tekniskt en radera och infoga så det här bygger i princip upp hela 2,2 GB-tabellen. Vi har också en slav som kör så att vi inte vill att det släpar efter.

Finns det något bättre sätt än att skriva ett manus som långsamt fyller det över tiden?

Kommentarer

  • Har du redan kört en ALTER TABLE .. ADD COLUMN ... eller ska den delen också besvaras?
  • Har du inte kört eventuella tabelländringar ännu, bara i planeringsfasen. Jag har gjort det tidigare genom att lägga till kolumnen, fylla i den, sedan lägga till begränsningen eller indexet. Denna tabell är dock betydligt större och jag är orolig för belastningen, låsning, replikering, etc. …

Svar

Det beror väldigt mycket på detaljer om din installation och dina krav.

Observera att sedan Postgres 11 bara läggs till en kolumn med en flyktig DEFAULT utlöser fortfarande en omskrivning av tabellen . Tyvärr är detta ditt fall.

Om du har tillräckligt med ledigt utrymme på disken – minst 110% av pg_size_pretty((pg_total_relation_size(tbl)) – och har råd med ett delningslås under en tid och ett exklusivt lås under mycket kort tid, skapa sedan ett ny tabell inklusive kolumnen uuid med CREATE TABLE AS . Varför?

Koden nedan använder en -funktion från den extra uuid-oss -modulen .

  • Lås tabellen mot samtidiga förändringar i SHARE -läget (tillåter fortfarande samtidiga läsningar). Försök att skriva till bordet väntar och slutligen misslyckas. Se nedan.

  • Kopiera hela tabellen medan du fyller i den nya kolumnen i farten – eventuellt ordna rader med fördel medan du är vid den.
    Om du kommer att ordna om rader, se till att ställa work_mem tillräckligt högt för att göra sorteringen i RAM eller så högt som du har råd (bara för din session, inte globalt).

  • Sedan lägg till begränsningar, främmande nycklar, index, utlösare etc. till den nya tabell. När du uppdaterar stora delar av en tabell är det mycket snabbare att skapa index från grunden än att lägga till rader iterativt. Relaterade råd i handboken.

  • När den nya tabellen är klar, släpp den gamla och byt namn på den nya för att göra det till en drop-in ersättare. Endast det här sista steget förvärvar ett exklusivt lås på den gamla tabellen under resten av transaktionen – vilket borde vara väldigt kort nu.
    Det kräver också att du tar bort alla objekt beroende på tabelltyp (vyer, funktioner som använder tabelltypen i signaturen, …) och återskapa dem efteråt.

  • Gör allt i en transaktion för att undvika ofullständiga tillstånd. 

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;

Detta borde vara snabbast. Varje annan metod för att uppdatera på plats måste också skriva om hela bordet, bara på ett dyrare sätt. Du skulle bara gå den vägen om du inte har tillräckligt med ledigt utrymme på hårddisken eller inte har råd att låsa hela tabellen eller generera fel för samtidiga skrivförsök.

Vad händer med samtidiga skrivningar?

Annan transaktion (i andra sessioner) försöker INSERT / UPDATE / DELETE i samma tabell efter att din transaktion har tagit SHARE låset, väntar tills låset släpps eller en timeout börjar, beroende på vilket som kommer först. De kommer misslyckas på något sätt, eftersom tabellen de försökte skriva till har tagits bort under dem.

Den nya tabellen har en ny tabell OID, men samtidig transaktion har redan löst tabellnamnet till OID för föregående tabell . När låset äntligen släpps försöker de låsa bordet själva innan de skriver till det och upptäcker att det ” är borta.Postgres svarar:

ERROR: could not open relation with OID 123456

Där 123456 är OID för den gamla tabellen. Du måste fånga det undantaget och försöka igen frågor i din appkod för att undvika det.

Om du inte har råd att det ska hända måste du behålla din ursprungliga tabell.

Håll den befintliga tabellen, alternativ 1

Uppdateringen på plats (möjligen kör uppdateringen på små segment åt gången) innan du lägger till NOT NULL begränsningen . Att lägga till en ny kolumn med NULL-värden och utan NOT NULL begränsning är billigt.
Eftersom Postgres 9.2 Du kan också skapa en CHECK begränsning med NOT VALID :

Begränsningen kommer fortfarande att vara tvingas mot efterföljande inlägg eller uppdateringar

Det gör att du kan uppdatera rader peu à peu – i flera separata transaktioner . Detta undviker att hålla radlås för länge och det gör det också möjligt att återanvända döda rader. (Du måste köra VACUUM manuellt om det inte finns tillräckligt med tid däremellan för att autovakuum ska kunna sparka in.) Lägg till slut till NOT NULL begränsning och ta bort 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;

Relaterat svar som diskuterar NOT VALID mer detaljerat:

Behåll befintlig tabell, alternativ 2

Förbered det nya tillståndet i en tillfällig tabell , TRUNCATE originalet och fylla från temp-tabellen. Allt i en transaktion . Du behöver fortfarande ta ett SHARE lås före pr dela upp den nya tabellen för att förhindra att förlora samtidiga skrivningar.

Detaljer i dessa relaterade svar på SO:

Kommentarer

  • Fantastiskt svar! Exakt den information jag letade efter. Två frågor 1. Har du någon aning om ett enkelt sätt att testa hur lång tid en sådan åtgärd skulle ta? 2. Om det tar säg fem minuter, vad händer med åtgärder som försöker uppdatera en rad i den tabellen under dessa 5 minuter?
  • @CollinPeters: 1. Lejonet ' s andel av tiden skulle gå till att kopiera det stora bordet – och eventuellt återskapa index och begränsningar (det beror på). Att släppa och byta namn är billigt. För att testa kan du köra ditt förberedda SQL-skript utan LOCK till och med DROP. Jag kunde bara uttrycka vilda och värdelösa gissningar. När det gäller 2., överväga tillägget till mitt svar.
  • @ErwinBrandstetter Fortsätt med att återskapa vyer, så om jag har ett dussin av vyer som fortfarande använder gammal tabell (oid) efter tabellbyte. Finns det något sätt att utföra djupbyte snarare än att köra om hela uppdateringen / skapandet av vyn?
  • @CodeFarmer: Om du bara byter namn på en tabell fortsätter visningarna att arbeta med den namngivna tabellen. För att göra vyer använder du ny -tabellen istället, du måste återskapa dem baserat på den nya tabellen. (Även för att tillåta att den gamla tabellen raderas.) Ingen (praktisk) väg runt den.
  • eftersom 9.2 postgres ' t kastar could not open relation with OID 123456

Svar

Jag har inte ett” bästa ”svar, men Jag har ett ”minst dåligt” svar som kan låta dig få saker gjorda ganska snabbt.

Min tabell hade 2MM rader och uppdateringsprestandan tappade när jag försökte lägga till en sekundär tidsstämpelkolumn som standard som den första . 

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

Efter att det hängde i 40 minuter försökte jag detta på en liten sats för att få en uppfattning om hur lång tid det kunde ta – prognosen var runt 8 timmar.

Det accepterade svaret är definitivt bättre – men den här tabellen används i stor utsträckning i min databas. Det finns några dussin tabeller som FKEY på den; Jag ville undvika att byta utländska nycklar på så många tabeller Och sedan finns det åsikter.

Lite att söka i dokument, fallstudier och StackOverflow, och jag hade ”A-Ha!” Ögonblicket. var inte på kärnan UPDATE, men på alla INDEX-operationer. Mitt bord hade 12 index – några för unika begränsningar, några för att påskynda frågeplanern och några för fulltext-sökning.

Varje rad som UPPDATERADE arbetade inte bara med en DELETE / INSERT, utan också kostnaden för att ändra varje index och kontrollera begränsningar.

Min lösning var att släppa varje index och begränsning, uppdatera tabellen och lägg sedan till alla index / begränsningar igen.

Det tog ungefär 3 minuter att skriva en SQL-transaktion som gjorde följande:

  • BÖRJA;
  • tappade index / constaints
  • uppdateringstabell
  • lägg till index / begränsningar igen
  • COMMIT;

Skriptet tog 7 minuter att köra.

Det accepterade svaret är definitivt bättre och mer korrekt … och eliminerar praktiskt taget behovet av stillestånd. I mitt fall skulle det dock ha tagit betydligt mer ” Utvecklare ”arbetar för att använda den lösningen och vi hade ett 30-minutersfönster med schemalagd stillestånd som den kunde åstadkommas i. Vår lösning behandlade det i 10.

Kommentarer