Nejlepší způsob, jak naplnit nový sloupec ve velké tabulce?

• Co způsobuje zpomalení velkého INSERTU a explodování využití disku?

Níže uvedený kód používá funkci z dalšího uuid-oss modulu .

• Uzamkněte tabulku proti souběžným změnám v SHARE režimu (stále povolte souběžné čtení). Pokusy o zápis do tabulky počkají a nakonec selžou. Viz níže.

• Zkopírujte celou tabulku za současného vyplňování nového sloupce – případně můžete řádky řádně uspořádat, když na něm budete.
  Pokud chystáte změnit pořadí řádků, nezapomeňte nastavit work_mem dostatečně vysoko, aby bylo možné řadit v RAM nebo stejně vysoko jak si můžete dovolit (jen pro vaši relaci, ne globálně).

• Potom přidejte do nového omezení, cizí klíče, indexy, spouštěče atd. stůl. Při aktualizaci velkých částí tabulky je mnohem rychlejší vytváření indexů od nuly než opakované přidávání řádků. Související rady v příručce.

• Když je nová tabulka připravena, zrušte starou a přejmenujte novou aby to byla náhrada za drop-in. Pouze tento poslední krok získá exkluzivní zámek na staré tabulce pro zbytek transakce – což by nyní mělo být velmi krátké.
  Vyžaduje také odstranění libovolného objektu v závislosti na typu tabulky (pohledy, funkce využívající typ tabulky v podpisu, …) a poté je znovu vytvořte.

• Udělejte vše v jedné transakci, abyste předešli neúplným stavům.

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; 

Mělo by to být nejrychlejší. Jakýkoli jiný způsob aktualizace na místě musí přepsat také celou tabulku, jen dražším způsobem. Touto cestou byste šli, pouze pokud nemáte na disku dostatek volného místa nebo si nemůžete dovolit uzamknout celou tabulku nebo generovat chyby pro souběžné pokusy o zápis.

Co se stane se souběžnými zápisy?

Další transakce (v jiných relacích) se snaží INSERT / UPDATE / DELETE ve stejné tabulce poté, co vaše transakce uzamkne SHARE, počká, dokud se zámek neuvolní nebo nevyprší časový limit, podle toho, co nastane dříve. Budou fail v každém případě, protože tabulka, na kterou se pokoušeli zapsat, byla pod nimi odstraněna.

Nová tabulka má nový tabulka OID, ale souběžná transakce již vyřešila název tabulky na OID předchozí tabulky . Když je zámek konečně uvolněn, pokusí se tabulku uzamknout sami, než do ní zapíše a zjistí, že je “ je pryč.Postgres odpoví:

ERROR: could not open relation with OID 123456

Kde 123456 je OID staré tabulky. Tuto výjimku musíte zachytit a zkusit se jí v kódu aplikace pokusit, abyste se tomu vyhnuli.

Pokud si to nemůžete dovolit, musíte uchovat původní tabulku.

Zachování existující tabulky, alternativa 1

Aktualizace na místě (případně spuštění aktualizace na malých segmentech najednou) před přidáním omezení NOT NULL . Přidání nového sloupce s hodnotami NULL a bez omezení NOT NULL je levné.
Protože Postgres 9.2 můžete také vytvořit CHECK omezení s NOT VALID :

Omezení bude stále být vynuceno proti následným vložením nebo aktualizacím

To vám umožní aktualizovat řádky peu à peu – v několika samostatných transakcích . Tím se zabrání příliš dlouhému udržování zámků řádků a také to umožní opětovné použití mrtvých řádků. (Pokud není mezi nimi dostatek času na nastartování autovakuum, budete muset VACUUM spustit ručně.) Nakonec přidejte NOT NULL omezení a odstraňte NOT VALID CHECK omezení:

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; 

Související odpověď pojednávající o NOT VALID podrobněji:

• Zakázat všechna omezení a kontroly tabulky při obnově výpisu

Zachování stávající tabulky, alternativa 2

Připravte nový stav v dočasné tabulce , TRUNCATE originál a doplňte z dočasné tabulky. Vše v jedné transakci . stále musíte vzít SHARE zámek před pr připravit novou tabulku, aby se zabránilo ztrátě souběžných zápisů.

Podrobnosti v těchto souvisejících odpovědích na SO:

• Nejlepší způsob smazat miliony řádků podle ID
• Přidat nový sloupec bez zámku tabulky?

Komentáře

• Fantastická odpověď! Přesně informace, které jsem hledal. Dvě otázky 1. Máte nějaký nápad na snadný způsob, jak otestovat, jak dlouho by taková akce trvala? 2. Pokud to trvá řekněme 5 minut, co se stane s akcemi, které se během těchto 5 minut pokusí aktualizovat řádek v tabulce?
• @CollinPeters: 1. Lev ' času by šel do kopírování velké tabulky – a možná opětovného vytvoření indexů a omezení (to záleží). Puštění a přejmenování je levné. Chcete-li otestovat, můžete připravený skript SQL spustit bez LOCK až do DROP. Mohl jsem vyslovit jen divoké a zbytečné dohady. Pokud jde o 2., zvažte prosím dodatek k mé odpovědi.
• @ErwinBrandstetter Pokračujte v opětovném vytváření pohledů, takže pokud mám tucet zobrazení, které po přejmenování tabulky stále používají starou tabulku (oid). Existuje nějaký způsob, jak provést hlubokou výměnu namísto opětovného spuštění / obnovení celého zobrazení?
• @CodeFarmer: Pokud právě přejmenujete tabulku, budou zobrazení nadále pracovat s přejmenovanou tabulkou. Chcete-li, aby pohledy místo toho používaly novou tabulku, musíte je znovu vytvořit na základě nové tabulky. (Také proto, aby bylo možné smazat starou tabulku.) Žádné (praktické) řešení.
• od verze 9.2 postgres nevyvolá ' could not open relation with OID 123456