Prestandaoptimering och frågeoptimering i PostgreSQL · Lektion

Skjuta upp index och begränsningar under inläsning

Ta bort och bygg om index och begränsningar kring massinläsningar för att kraftigt minska skrivförstärkningen.

Lektion 2 av 413 steg

Skjuta upp index och begränsningar under inläsning är en gratis lektion i Prestandaoptimering och frågeoptimering i PostgreSQL på CoddyKit. Detta är lektion 2 av 4. Du kan läsa vilka 3 lektioner som helst i den här lärvägen kostnadsfritt i sin helhet – därefter låser CoddyKit PRO upp alla lektioner, plus praktisk övning med en inbyggd kodredigerare och en AI-lärare dygnet runt. Den ingår i lärvägen för Prestandaoptimering och frågeoptimering i PostgreSQL, och Era framsteg synkroniseras mellan webben och CoddyKit-appen. Kursen i Prestandaoptimering och frågeoptimering i PostgreSQL innehåller totalt 4 lektioner.

Varför massinläsningar blir långsamma

När ni läser in miljontals rader i en tabell som redan har index och begränsningar betalar PostgreSQL en dold kostnad för varenda rad.

Varje index måste uppdateras (B-trädets siduppdelningar och WAL-skrivningar).
Varje främmande nyckel utlöser en sökning i den refererade tabellen.
Varje unik begränsning och kontrollbegränsning valideras per rad.

Detta arbete per rad kallas skrivförstärkning: en logisk INSERT blir många fysiska skrivningar. Den centrala optimeringen i den här lektionen är att skjuta upp arbetet — läs först in rådata och bygg sedan index och validera begränsningar en gång, i bulk.

Kostnaden för varje index

Att underhålla ett B-trädindex under en inläsning är inte kostnadsfritt. För varje infogad rad måste PostgreSQL gå genom trädet, hitta lövsidan, eventuellt dela den och logga ändringen till WAL.

Att bygga samma index efter att data har lästs in är mycket billigare: PostgreSQL sorterar alla nycklar på en gång och skriver täta, sekventiella sidor. En tabell med 5 index utför ungefär 6 gånger så mycket skrivarbete jämfört med att bara läsa in heap-tabellen.

Slutsatsen är: färre index under inläsningen = mindre skrivförstärkning.

Mönster: Ta bort, läs in, bygg om

Det klassiska ETL-mönstret för en tabell som ska ta emot en stor datamängd är:

Ta bort sekundära index.
Läs in data (COPY är snabbast).
Bygg om indexen i ett enda steg.

Nedan visas grundstrukturen. Observera att vi behåller primärnyckeln tills vidare och endast tar bort sekundära index som inte behövs under själva inläsningen.

DROP INDEX idx_orders_customer_id;
DROP INDEX idx_orders_created_at;

COPY orders FROM '/data/orders.csv' WITH (FORMAT csv, HEADER true);

CREATE INDEX idx_orders_customer_id ON orders (customer_id);
CREATE INDEX idx_orders_created_at ON orders (created_at);

COPY slår INSERT vid inläsning

När indexen är ur vägen spelar inläsningsmetoden roll. COPY strömmar rader i ett enda kommando med minimalt omkostnad per rad, medan tusentals enskilda INSERT-satser var och en medför kostnader för parsning, planering och rundresor.

För ETL-genomströmning bör ni föredra COPY (eller \copy från psql) framför radvisa infogningar. Om ni måste använda INSERT bör ni samla många rader i varje sats.

COPY staging_events (user_id, event_type, payload, created_at)
FROM '/data/events.csv'
WITH (FORMAT csv, HEADER true);

Skjuta upp validering av främmande nycklar

Främmande nycklar valideras rad för rad under en inläsning, vilket innebär en indexuppslagning i den överordnade tabellen varje gång. Detta kan undvikas genom att först göra begränsningen till NOT VALID, läsa in data och sedan validera allt i ett enda steg.

ADD CONSTRAINT ... NOT VALID lägger till FK:n utan att kontrollera befintliga rader. Nya rader kontrolleras fortfarande vid infogning, så om ni verkligen vill undvika arbete per rad måste ni ta bort och lägga till FK:n igen efter inläsningen, eller läsa in data innan FK:n läggs till.

-- Add the FK without scanning existing rows
ALTER TABLE orders
  ADD CONSTRAINT fk_orders_customer
  FOREIGN KEY (customer_id) REFERENCES customers (id)
  NOT VALID;

-- Later, validate all rows in one bulk pass
ALTER TABLE orders VALIDATE CONSTRAINT fk_orders_customer;

Varför NOT VALID följt av VALIDATE hjälper

Att lägga till en främmande nyckel på normalt sätt tar ett ACCESS EXCLUSIVE-lås och skannar hela tabellen samtidigt som skrivningar blockeras. Tvåstegsmetoden delar upp detta:

ADD ... NOT VALID går snabbt och tar endast ett kortvarigt starkt lås för att registrera begränsningen.
VALIDATE CONSTRAINT skannar tabellen under ett svagare SHARE UPDATE EXCLUSIVE-lås, vilket tillåter samtidiga läsningar och skrivningar.

För inläsningar innebär detta att den kostsamma valideringen utförs en gång, efter att all data finns på plats, i stället för rad för rad.

DEFERRABLE-begränsningar i en transaktion

PostgreSQL stöder även DEFERRABLE-begränsningar, som skjuter upp kontrollen till slutet av en transaktion (COMMIT). Detta skiljer sig från att ta bort en begränsning: kontrollen utförs fortfarande, men senare.

Det är användbart när rader kommer i en ordning som tillfälligt bryter mot en FK- eller unikhetsbegränsning, till exempel när rader för barn kommer före föräldrar i en och samma transaktion.

ALTER TABLE order_items
  ADD CONSTRAINT fk_items_order
  FOREIGN KEY (order_id) REFERENCES orders (id)
  DEFERRABLE INITIALLY DEFERRED;

BEGIN;
  -- insert children and parents in any order;
  -- FK is checked only at COMMIT
COMMIT;

Uppskjuten kontroll jämfört med borttagen begränsning

Var tydlig med avvägningen:

DEFERRABLE INITIALLY DEFERRED validerar fortfarande varje rad, men gör det vid COMMIT i stället för vid INSERT. Det löser problem med ordningen, men tar inte bort valideringskostnaden.
Ta bort/lägg till igen (eller NOT VALID + VALIDATE) eliminerar arbetet per rad och validerar i stället allt i en effektiv skanning.

För maximal genomströmning vid mycket stora inläsningar är det bäst att ta bort och bygga om. För korrekthet när infogningsordningen är komplicerad är deferrable rätt verktyg.

Justera ombyggnaden av index

Att bygga om index efter en inläsning är i sig en sorteringstung operation. Två inställningar gör den mycket snabbare för sessionen som kör inläsningen:

maintenance_work_mem — mer minne innebär färre sammanslagningar av externa sorteringar när index byggs.
max_parallel_maintenance_workers — gör det möjligt för ett enda CREATE INDEX att använda flera CPU-kärnor.

Höj dessa värden för inläsningssessionen och bygg sedan indexen.

SET maintenance_work_mem = '2GB';
SET max_parallel_maintenance_workers = 4;

CREATE INDEX idx_orders_customer_id ON orders (customer_id);
CREATE INDEX idx_orders_created_at ON orders (created_at);

En komplett ETL-sekvens

För en stor inkrementell inläsning i en befintlig tabell är en robust ordning följande:

Ta bort sekundära index.
Ta bort eller inaktivera kostsamma främmande nycklar.
Höj maintenance_work_mem.
Läs in via COPY.
Bygg om indexen.
Lägg till FK:er igen och kör VALIDATE.
Kör ANALYZE så att planeraren får färsk statistik.

ALTER TABLE orders DROP CONSTRAINT fk_orders_customer;
DROP INDEX idx_orders_created_at;

SET maintenance_work_mem = '1GB';
COPY orders FROM '/data/orders.csv' WITH (FORMAT csv, HEADER true);

CREATE INDEX idx_orders_created_at ON orders (created_at);
ALTER TABLE orders
  ADD CONSTRAINT fk_orders_customer
  FOREIGN KEY (customer_id) REFERENCES customers (id);

ANALYZE orders;

Glöm inte ANALYZE

Efter en stor inläsning är tabellstatistiken som planeraren förlitar sig på inaktuell — den kan fortfarande tro att tabellen är liten. Det leder till dåliga planer, till exempel sekventiella skanningar när ett index hade varit bättre eller felaktiga join-ordningar.

Kör alltid ANALYZE (eller VACUUM ANALYZE) på nyligen inlästa tabeller innan ni kör frågor mot dem. Att bygga om index uppdaterar inte planerstatistiken; det är endast ANALYZE som gör det.

ANALYZE orders;
-- or to also reclaim space and freeze:
VACUUM ANALYZE orders;

Snabbtest

Testa er förståelse av avvägningarna kring genomströmning.

Besvara frågan i den interaktiva lektionen

Sammanfattning

Viktiga slutsatser om att skjuta upp index och begränsningar vid massinläsningar:

Aktiva index och begränsningar orsakar skrivförstärkning — en INSERT blir många fysiska skrivningar.
Det vinnande mönstret är ta bort, läs in, bygg om: ta bort sekundära index och FK:er, läs in med COPY och återskapa dem sedan i ett enda steg.
ADD CONSTRAINT ... NOT VALID följt av VALIDATE CONSTRAINT flyttar FK-kontrollen från vägen för radvisa infogningar till en enda masskanning under ett lättare lås.
DEFERRABLE INITIALLY DEFERRED skjuter endast upp kontrollerna till COMMIT — det löser problem med infogningsordningen men eliminerar inte valideringskostnaden.
Höj maintenance_work_mem och max_parallel_maintenance_workers för att snabba upp ombyggnaden.
Avsluta alltid med ANALYZE så att planeraren ser de nya uppgifterna.

Gratis att börja

Lär dig SQL med en AI-lärare – gratis

Skriv och kör riktig kod i webbläsaren, få omedelbar hjälp av en AI-lärare dygnet runt och fortsätt där du slutade – på webben eller i appen.

Kurser: 22
Lektioner: 88

Börja lära dig gratis Visa hela kursen

Vanliga frågor

Är lektionen ”Skjuta upp index och begränsningar under inläsning” gratis?

Ja – du kan läsa vilka 3 lektioner som helst i lärvägen Prestandaoptimering och frågeoptimering i PostgreSQL, inklusive ”Skjuta upp index och begränsningar under inläsning”, kostnadsfritt i sin helhet här på webben. Därefter låser CoddyKit PRO upp alla lektioner, plus interaktiv övning med en inbyggd kodredigerare och en AI-lärare dygnet runt. Kursen i Prestandaoptimering och frågeoptimering i PostgreSQL innehåller totalt 4 lektioner.

Vad lär jag mig i ”Skjuta upp index och begränsningar under inläsning”?

Ta bort och bygg om index och begränsningar kring massinläsningar för att kraftigt minska skrivförstärkningen. Ni övar på Prestandaoptimering och frågeoptimering i PostgreSQL med praktisk kod som körs direkt i webbläsaren, medan en AI-handledare som är tillgänglig dygnet runt svarar på Era frågor under lektionen.

Behöver jag någon erfarenhet för att börja lära mig Prestandaoptimering och frågeoptimering i PostgreSQL?

Du behöver inga förkunskaper. Utbildningen i Prestandaoptimering och frågeoptimering i PostgreSQL på CoddyKit är upplagd för allt från nybörjare till avancerade elever, så att du kan börja här eller från början och gå fram i din egen takt. Detta är lektion 2 av 4.

Hur lång tid tar lektionen ”Skjuta upp index och begränsningar under inläsning”?

De flesta CoddyKit-lektioner tar cirka 5–10 minuter. Varje lektion är kort och interaktiv, så att du gör stadiga framsteg och kan fortsätta precis där du slutade – på webben eller i appen.

Kan jag skriva och köra kod i den här Prestandaoptimering och frågeoptimering i PostgreSQL-lektionen?

Ja. Varje Prestandaoptimering och frågeoptimering i PostgreSQL-lektion innehåller en inbyggd kodredigerare, så att du kan skriva och köra riktig kod direkt i webbläsaren och få omedelbar AI-feedback – utan lokal installation.

Alla lektioner i den här kursen

Genomströmning för COPY kontra flerrads-INSERT
Skjuta upp index och begränsningar under inläsning
Finjustera WAL och checkpoints för inläsning
Upserts i stor skala med ON CONFLICT

← Tillbaka till Prestandaoptimering och frågeoptimering i PostgreSQL