július 6, 2020
Articles
Nincs hozzászólás

A legjobb módszer egy új oszlop feltöltésére egy nagy táblázatban?

A Postgres-ben van egy 2,2 GB-os tábla, 7 801 611 sorral. Hozzáadunk hozzá uuid / guid oszlopot, és kíváncsi vagyok, mi a legjobb módja az oszlop feltöltésének (mivel NOT NULL korlátozást szeretnénk hozzáadni).

Ha jól értem a Postgres-t, akkor egy frissítés technikailag törlés és beillesztés, tehát ez alapvetően a teljes 2,2 gb-os tábla újjáépítését jelenti. Van egy rabszolgánk is, így nem akarjuk, hogy ez lemaradjon.

Van valami jobb, mint egy olyan szkriptet írni, amely az idő múlásával lassan feltölti?

Hozzászólások

  • Futtatott már ALTER TABLE .. ADD COLUMN ... -et, vagy erre a részre is válaszolni kell?
  • Még nem futott Bármilyen módosítás a táblában, csak a tervezés szakaszában. Korábban ezt tettem az oszlop hozzáadásával, feltöltésével, majd a korlátozás vagy index hozzáadásával. Ez a táblázat azonban lényegesen nagyobb, és aggódom a terhelés, a zárolás, a replikáció stb. miatt …

Válasz

Ez nagyban függ a beállítás részleteitől és a követelményektől.

Ne feledje, hogy a Postgres 11 óta csak oszlop hozzáadása illékony DEFAULT továbbra is tábla átírást vált ki . Sajnos ez a te eseted.

Ha van elegendő szabad hely a lemezen – a pg_size_pretty((pg_total_relation_size(tbl)) – és engedhet meg magának egy megosztási zárat egy ideig és egy kizárólagos zár rövid időre, majd hozzon létre egy új tábla beleértve a uuid oszlopot CREATE TABLE AS használatával. Miért?

Az alábbi kód egy függvényt használ a további uuid-oss modulból .

  • Zárolja a táblázatot az egyidejű változások ellen SHARE módban (továbbra is engedélyezi az egyidejű olvasásokat). A táblára írási kísérletek várakozni fognak, és végül kudarcot vallanak. Lásd alább.

  • Másolja át a teljes táblázatot, miközben menet közben tölti be az új oszlopot – esetleg sorokat kedvezően rendezhet, miközben ott van.
    If sorokat fog átrendezni, győződjön meg arról, hogy a work_mem beállítást elég magasra teszi a rendezéshez a RAM-ban, vagy olyan magasra ahogy megengedheti magának (csak a munkamenetéhez, nem globálisan).

  • Ezután adjon meg korlátozásokat, idegen kulcsokat, indexeket, indítókat stb. asztal. A táblázat nagy részeinek frissítésekor sokkal gyorsabb az indexek létrehozása a semmiből, mint a sorok iteratív hozzáadása. Kapcsolódó tanácsok a kézikönyvben.

  • Amikor elkészült az új tábla, dobja le a régit, és nevezze át az újat hogy csepp-helyettesítő legyen. Csak ez az utolsó lépés szerez exkluzív zárat a régi táblán a tranzakció hátralévő részére – aminek most nagyon rövidnek kell lennie.
    Azt is megköveteli, hogy töröljön minden objektumot a tábla típusától (nézetek, függvények a tábla típusától függően) az aláírásban …), és utána hozza létre újra.

  • Mindezt egy tranzakcióban végezze el a hiányos állapotok elkerülése érdekében. 

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;

Ennek a leggyorsabbnak kell lennie. A helyben történő frissítés bármely más módszerének át kell írnia az egész táblázatot is, csak drágábban. Csak akkor haladna ezen az úton, ha nem rendelkezik elegendő szabad területtel a lemezen, vagy ha nem engedheti meg magának, hogy lezárja az egész táblázatot, vagy hibákat generáljon egyidejű írási kísérletekhez.

Mi történik az egyidejű írásokkal?

Más tranzakció (más munkamenetekben) INSERT / UPDATE / DELETE próbálkozás ugyanabban a táblázatban, miután a tranzakció megadta a SHARE zárat, megvárja, amíg a zár feloldásra kerül, vagy bekapcsol egy időkorlát, amelyik előbb bekövetkezik. fail mindkét irányba, mivel a tábla, amelyhez írni akartak, törlődött alóluk.

Az új tábla új tábla OID, de az egyidejű tranzakció már megoldotta a tábla nevét az előző tábla OID értékére. Amikor a zár véglegesen feloldásra kerül, megpróbálják maguk lezárni az asztalt, mielőtt írnának rá, és rájönnek, hogy ” s elment.Postgres válaszol:

ERROR: could not open relation with OID 123456

Ahol 123456 a régi tábla OID-je. El kell fognia ezt a kivételt, és újra meg kell próbálnia a lekérdezéseket az alkalmazáskódban, hogy elkerülje azt.

Ha ezt nem engedheti meg magának, akkor meg kell tartania az eredeti táblázatot. >

A meglévő 1. alternatíva tábla megtartása

Frissítés a helyén (esetleg egyszerre futtassa a frissítést kis szegmenseken), mielőtt hozzáadná a NOT NULL korlátozást . Új NULL értékű oszlop hozzáadása NOT NULL korlátozás nélkül olcsó.
Mivel a Postgres 9.2 CHECK kényszert is létrehozhat a NOT VALID :

A korlátozás továbbra is fennáll kényszeríteni a későbbi beszúrásokkal vagy frissítésekkel

Ez lehetővé teszi a peu à peu – több különálló tranzakcióban . Ez elkerüli a sorzárak túl sokáig tartó megtartását, és lehetővé teszi az elhalt sorok újrafelhasználását is. (Kézzel kell futtatnia a VACUUM alkalmazást, ha nincs elég idő az autovákuum beindításához.) Végül adja hozzá a NOT NULL kényszer és távolítsa el a NOT VALID CHECK kényszert: 

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;

Kapcsolódó válasz NOT VALID részletesebben:

A meglévő táblázat megtartása, 2. alternatíva

Készítse elő az új állapotot egy ideiglenes táblában , TRUNCATE az eredeti és újratöltés a temp táblából. Mindez a egy tranzakcióban . még mindig le kell vennie egy SHARE zárat előtt pr Az új tábla törlése az egyidejű írások elvesztésének megakadályozása érdekében.

A kapcsolódó válasz részletei a SO-n:

Megjegyzések

  • Fantasztikus válasz! Pontosan az az információ, amit kerestem. Két kérdés 1. Van elképzelése arról, hogyan lehetne kipróbálni, mennyi ideig tartana egy ilyen cselekedet? 2. Ha mondjuk 5 percet vesz igénybe, mi történik azokkal a műveletekkel, amelyek megpróbálják frissíteni az adott táblázat egy sorát az 5 perc alatt?
  • @CollinPeters: 1. Az oroszlán ' s részarány a nagy tábla másolására – és esetleg indexek és korlátozások újrateremtésére (ez attól függ) kerülne. A dobás és az átnevezés olcsó. A teszteléshez futtathatja az előkészített SQL parancsfájlt a LOCK nélkül, az DROP értékig. Csak vad és haszontalan találgatásokat tudtam kimondani. Ami a 2. pontot illeti, kérjük, vegye figyelembe a válaszom kiegészítését.
  • @ErwinBrandstetter Folytassa a nézetek újrateremtésével, tehát ha tucatnyi nézetem van, amely a táblák átnevezése után is használ régi táblákat (oid). Van-e valamilyen lehetőség mély cserére, nem pedig a teljes nézet frissítésének / létrehozásának újbóli futtatására?
  • @CodeFarmer: Ha csak átnevez egy táblát, akkor a nézetek továbbra is működnek az átnevezett táblával. Ahhoz, hogy a nézetek inkább az új táblázatot használják, az új tábla alapján újra kell létrehozniuk őket. (A régi tábla törlésének lehetővé tétele érdekében is.) Nincs (gyakorlati) megkerülési mód.
  • mivel a 9.2 postgres nem ' nem dobja meg could not open relation with OID 123456

Válasz

Nincs” legjobb “válaszom, de Van egy “legkevésbé rossz” válaszom, amely lehetővé teszi, hogy a dolgokat meglehetősen gyorsan elvégezhesse.

A táblázatom 2 mm-es sorokkal rendelkezett, és a frissítési teljesítmény csordultig állt, amikor megpróbáltam hozzáadni egy másodlagos időbélyeg oszlopot, amely alapértelmezés szerint az első . 

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

Miután 40 percig lógott, kipróbáltam ezt egy kis adagban, hogy képet kapjak, mennyi időbe telhet ez – az előrejelzés kb. 8 óra.

Az elfogadott válasz határozottan jobb – de ezt a táblázatot sokat használom az adatbázisomban. Van néhány tucat tábla, amelyre FKEY van; Szerettem volna elkerülni a KÜLFÖLDI KULCSOK váltását ennyi táblán És aztán vannak nézetek.

Egy kis keresés a dokumentumokban, esettanulmányokban és a StackOverflow-ban, és nekem volt egy „A-Ha!” Pillanatom. Nem az alapvető UPDATE, hanem az INDEX összes műveletén volt. A táblázatomban 12 index volt – néhány egyedi korlátozásra, néhány a lekérdezés-tervező felgyorsítására és néhány a teljes szöveges keresésre.

Minden FRISSÍTETT sor nem csak egy DELETE / INSERT-en dolgozott, hanem az egyes indexek megváltoztatásának és a megszorítások ellenőrzésének a költsége is.

Az én megoldásom az volt, hogy minden indexet eldobok és kényszer, frissítse a táblázatot, majd adja hozzá az összes indexet / korlátozást.

Körülbelül 3 percet vett igénybe egy SQL tranzakció megírása, amely a következőket tette:

  • BEGIN;
  • elvetett indexek / korlátok
  • táblázat frissítése
  • indexek / korlátozások újbóli hozzáadása
  • COMMIT;

A szkript futtatása 7 percet vett igénybe.

Az elfogadott válasz határozottan jobb és helyesebb … és gyakorlatilag kiküszöböli az állásidő szükségességét. Az én esetemben azonban lényegesen többre lett volna szükség ” A fejlesztő azon dolgozik, hogy használja ezt a megoldást, és volt egy 30 perces ablakunk az ütemezett leállásokról, amelyeket meg lehet valósítani. Megoldásunk 10-ben foglalkozott vele.

Hozzászólások

  • Nem is beszélve arról, hogy lehetővé teszi az egyidejű írást. Az egyidejű olvasás lassú lehet, index nélkül s mégis 🙂 Érdekes lenne összehasonlítani a többi lehetőséggel …

Vélemény, hozzászólás?

Az email címet nem tesszük közzé. A kötelező mezőket * karakterrel jelöltük