De beste manier om een nieuwe kolom in een grote tabel te vullen?

• Wat zorgt ervoor dat grote INSERT langzamer gaat en het schijfgebruik explodeert?

De onderstaande code gebruikt een functie van de aanvullende uuid-oss module .

• Vergrendel de tabel tegen gelijktijdige wijzigingen in de SHARE modus (nog steeds gelijktijdige lezingen toestaan). Pogingen om naar de tabel te schrijven zullen wachten en mislukken uiteindelijk. Zie hieronder.

• Kopieer de hele tabel terwijl u de nieuwe kolom direct vult – mogelijk de rijen gunstig ordenen terwijl u bezig bent.
  If je gaat de rijen opnieuw ordenen, zorg ervoor dat je work_mem hoog genoeg instelt om in RAM te sorteren of zo hoog zoals u zich kunt veroorloven (alleen voor uw sessie, niet wereldwijd).

• Voeg vervolgens beperkingen, externe sleutels, indices, triggers enz. toe aan de nieuwe tafel. Bij het bijwerken van grote delen van een tabel is het veel sneller om vanaf het begin indexen te maken dan iteratief rijen toe te voegen. Gerelateerd advies in de handleiding.

• Als de nieuwe tabel klaar is, laat je de oude vallen en hernoem je de nieuwe om er een drop-in vervanging van te maken. Alleen deze laatste stap krijgt een exclusieve vergrendeling op de oude tafel voor de rest van de transactie – wat nu erg kort zou moeten zijn.
  Het vereist ook dat u elk object verwijdert, afhankelijk van het tafeltype (views, functies die het tafeltype gebruiken). in de handtekening, …) en maak ze daarna opnieuw aan.

• Doe het allemaal in één transactie om onvolledige staten te vermijden.

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; 

Dit zou het snelst moeten zijn. Elke andere methode van updaten moet ook de hele tabel herschrijven, alleen op een duurdere manier. Je zou die weg alleen gaan als je niet genoeg vrije schijfruimte hebt of je kunt het je niet veroorloven om de hele tabel te vergrendelen of fouten te genereren voor gelijktijdige schrijfpogingen.

Wat gebeurt er met gelijktijdige schrijfacties?

Andere transactie (in andere sessies) die probeert INSERT / UPDATE / DELETE in dezelfde tabel nadat uw transactie de SHARE -vergrendeling heeft aangenomen, wacht totdat de vergrendeling wordt vrijgegeven of een time-out wordt geactiveerd, afhankelijk van wat het eerst komt. Ze zullen mislukken hoe dan ook, aangezien de tabel waarnaar ze probeerden te schrijven onder hen is verwijderd.

De nieuwe tabel heeft een nieuwe tabel-OID, maar gelijktijdige transacties hebben de tabelnaam al omgezet naar de OID van de vorige tabel . Wanneer de vergrendeling eindelijk wordt vrijgegeven, proberen ze de tabel zelf te vergrendelen voordat ze ernaar schrijven en ontdekken dat deze ” is weg.Postgres zal antwoorden:

ERROR: could not open relation with OID 123456

Waarbij 123456 de GID is van de oude tabel. U moet die uitzondering opvangen en opnieuw zoeken in uw app-code om deze te vermijden.

Als u dat niet kunt betalen, moet u uw oorspronkelijke tabel behouden .

De bestaande tabel behouden, alternatief 1

Update op zijn plaats (mogelijk wordt de update op kleine segmenten tegelijk uitgevoerd) voordat je de NOT NULL beperking toevoegt . Het toevoegen van een nieuwe kolom met NULL-waarden en zonder NOT NULL beperking is goedkoop.
Sinds Postgres 9.2 je kunt ook een CHECK beperking maken met NOT VALID :

De beperking blijft worden afgedwongen tegen volgende invoegingen of updates

Hiermee kunt u rijen peu à peu – in meerdere afzonderlijke transacties . Hierdoor wordt voorkomen dat rijvergrendelingen te lang worden bewaard en kunnen dode rijen ook worden hergebruikt. (U “zult VACUUM handmatig moeten uitvoeren als er niet genoeg tijd tussen zit om autovacuum in werking te laten treden.) Voeg ten slotte de NOT NULL beperking en verwijder de NOT VALID CHECK beperking:

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; 

Gerelateerd antwoord bespreken NOT VALID in meer detail:

• Schakel alle beperkingen en tabelcontroles uit tijdens het herstellen van een dump

De bestaande tabel behouden, alternatief 2

Bereid de nieuwe staat voor in een tijdelijke tabel , TRUNCATE het origineel en vul bij vanuit de temp-tabel. Allemaal in één transactie . U moet nog steeds een SHARE lock voor pr eparing van de nieuwe tabel om verlies van gelijktijdige schrijfbewerkingen te voorkomen.

Details in dit gerelateerde antwoord op SO:

• De beste manier om miljoenen rijen verwijderen op ID
• Nieuwe kolom toevoegen zonder tabelvergrendeling?

Reacties

• Fantastisch antwoord! Precies de info die ik zocht. Twee vragen 1. Heeft u enig idee hoe u op een eenvoudige manier kunt testen hoe lang een dergelijke actie zou duren? 2. Als het zeggen 5 minuten duurt, wat gebeurt er dan met acties die proberen een rij in die tabel bij te werken gedurende die 5 minuten?
• @CollinPeters: 1. De leeuw ' s aandeel van de tijd zou gaan in het kopiëren van de grote tabel – en mogelijk het opnieuw creëren van indices en beperkingen (dat hangt ervan af). Verwijderen en hernoemen is goedkoop. Om te testen kunt u uw voorbereide SQL-script uitvoeren zonder de LOCK tot en met de DROP. Ik kon alleen maar wilde en nutteloze gissingen uiten. Wat betreft 2., overweeg alstublieft het addendum bij mijn antwoord.
• @ErwinBrandstetter Ga door met het opnieuw maken van weergaven, dus als ik een dozijn weergaven heb die nog steeds de oude tabel (oid) gebruiken na het hernoemen van de tabel. Is er een manier om een diepe vervanging uit te voeren in plaats van het vernieuwen / creëren van de hele weergave opnieuw uit te voeren?
• @CodeFarmer: Als je de naam van een tabel alleen wijzigt, blijven views werken met de hernoemde tabel. Om views in plaats daarvan de new tabel te laten gebruiken, moet u ze opnieuw maken op basis van de nieuwe tabel. (Ook om toe te staan dat de oude tabel wordt verwijderd.) Er is geen (praktische) manier omheen.
• sinds 9.2 postgres niet ' gooit could not open relation with OID 123456