Ytelse og spørringsoptimalisering i PostgreSQL · leksjon

Utsette indekser og begrensninger under lasting

Fjern og bygg opp indekser og begrensninger på nytt rundt masselasting for å redusere skriveforsterkning kraftig.

Leksjon 2 av 413 trinn

Utsette indekser og begrensninger under lasting er en gratis leksjon i Ytelse og spørringsoptimalisering i PostgreSQL på CoddyKit. Dette er leksjon 2 av 4. Du kan lese valgfritt 3 leksjoner fra denne læringsstien gratis i sin helhet – deretter låser CoddyKit PRO opp alle leksjoner, samt praktisk øving med en innebygd kodeeditor og en AI-veileder som er tilgjengelig døgnet rundt. Den er en del av læringsløpet i Ytelse og spørringsoptimalisering i PostgreSQL, og fremdriften din synkroniseres mellom nettet og CoddyKit-appen. Kurset i Ytelse og spørringsoptimalisering i PostgreSQL inneholder totalt 4 leksjoner.

Hvorfor bulkinnlastinger blir trege

Når De laster inn millioner av rader i en tabell som allerede har indekser og begrensninger, betaler PostgreSQL en skjult kostnad for hver eneste rad.

Hver indeks må oppdateres (B-tree-sidesplitter og WAL-skriving).
Hver fremmednøkkel utløser et oppslag i den refererte tabellen.
Hver unique- eller check-begrensning valideres per rad.

Dette arbeidet per rad kalles skriveforsterkning: én logisk INSERT blir til mange fysiske skrivinger. Kjerneoptimaliseringen i denne leksjonen er å utsette arbeidet — last inn rådataene først, og bygg deretter indekser og valider begrensninger én gang, som bulkoperasjoner.

Kostnaden for hver indeks

Det er ikke kostnadsfritt å vedlikeholde en B-tree-indeks under en lasting. For hver rad som settes inn, må PostgreSQL gå gjennom treet, finne løvsiden, eventuelt dele den og loggføre endringen i WAL.

Det er langt billigere å bygge den samme indeksen etter at dataene er på plass: PostgreSQL sorterer alle nøklene samtidig og skriver tette, sekvensielle sider. En tabell med 5 indekser som lastes rad for rad, utfører omtrent 6 ganger så mye skrivearbeid som lasting av bare heapen.

Poenget er: færre indekser under lasting = mindre forsterkning.

Mønster: Slett, last inn, bygg på nytt

Det klassiske ETL-mønsteret for en tabell som skal motta en stor datamengde, er:

Slett sekundærindeksene.
Last inn dataene (COPY er raskest).
Bygg på nytt indeksene i én operasjon.

Nedenfor ser De grunnstrukturen. Merk at vi beholder primærnøkkelen foreløpig og bare sletter sekundærindekser som ikke trengs under selve lastingen.

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 er bedre enn INSERT ved lasting

Når indeksene er fjernet, blir lastemetoden viktig. COPY strømmer rader i én kommando med minimalt ekstraarbeid per rad, mens tusenvis av individuelle INSERT-setninger hver pådrar seg kostnader for parsing, planlegging og rundturer.

For ETL-gjennomstrømming bør De foretrekke COPY (eller \copy fra psql) fremfor innsetting rad for rad. Hvis De må bruke INSERT, bør De samle mange rader i hver setning.

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

Utsettelse av validering av fremmednøkler

Fremmednøkler valideres rad for rad under lasting, med et indeksoppslag i foreldretabellen hver gang. Dette kan De unngå ved først å gjøre begrensningen NOT VALID, laste inn dataene og deretter validere samlet.

ADD CONSTRAINT ... NOT VALID legger til fremmednøkkelen uten å kontrollere eksisterende rader. Nye rader kontrolleres fortsatt ved innsetting, så for å hoppe over arbeid per rad må De slette og legge til fremmednøkkelen på nytt etter lastingen, eller laste inn dataene før De legger den til.

-- 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;

Hvorfor NOT VALID og deretter VALIDATE hjelper

Å legge til en fremmednøkkel på vanlig måte tar en ACCESS EXCLUSIVE-lås og skanner hele tabellen samtidig som skriving blokkeres. To-trinnsmetoden deler dette opp:

ADD ... NOT VALID er raskt og tar bare en kortvarig sterk lås for å registrere begrensningen.
VALIDATE CONSTRAINT skanner tabellen under en svakere SHARE UPDATE EXCLUSIVE-lås, slik at samtidige lese- og skriveoperasjoner kan fortsette.

For lasting betyr dette at De utfører den kostbare valideringen én gang, etter at alle dataene er på plass, i stedet for rad for rad.

DEFERRABLE-begrensninger i en transaksjon

PostgreSQL støtter også DEFERRABLE-begrensninger, som utsetter kontrollen til slutten av en transaksjon (COMMIT). Dette er forskjellig fra å slette en begrensning: kontrollen utføres fortsatt, bare senere.

Dette er nyttig når rader kommer i en rekkefølge som midlertidig bryter en fremmednøkkel eller unik begrensning (for eksempel når underordnede rader kommer før foreldrene i én transaksjon).

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;

Utsatt kontroll kontra slettet begrensning

Vær tydelig på avveiningen:

DEFERRABLE INITIALLY DEFERRED validerer fortsatt hver rad, bare ved COMMIT i stedet for ved INSERT. Det løser problemer med rekkefølge, men fjerner ikke valideringskostnaden.
Slett / legg til på nytt (eller NOT VALID + VALIDATE) fjerner arbeid per rad fullstendig og validerer på nytt i én effektiv skanning.

For maksimal gjennomstrømming ved svært store lastinger lønner det seg å slette og bygge på nytt. For korrekthet når innsettingsrekkefølgen er komplisert, er deferrable det riktige verktøyet.

Optimalisering av indeksbyggingen

Å bygge indekser på nytt etter en lasting er i seg selv en sorteringstung operasjon. To innstillinger gjør dette mye raskere for økten som utfører lastingen:

maintenance_work_mem — mer minne betyr færre eksterne sorteringssammenslåinger ved bygging av indekser.
max_parallel_maintenance_workers — lar én enkelt CREATE INDEX bruke flere CPU-er.

Øk disse innstillingene for lastingsøkten, og bygg deretter indeksene.

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

For en stor inkrementell lasting i en eksisterende tabell er en robust rekkefølge på operasjonene:

Slett sekundærindekser.
Slett eller deaktiver kostbare fremmednøkler.
Øk maintenance_work_mem.
Last inn via COPY.
Bygg indeksene på nytt.
Legg til fremmednøklene på nytt og VALIDATE.
Kjør ANALYZE slik at planleggeren får fersk statistikk.

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;

Ikke glem ANALYZE

Etter en stor lasting er tabellstatistikken som planleggeren baserer seg på, utdatert — den kan fortsatt tro at tabellen er liten. Det fører til dårlige planer (sekvensielle skanninger der en indeks ville vært bedre, eller feil rekkefølge på join-operasjoner).

Kjør alltid ANALYZE (eller VACUUM ANALYZE) på nylig lastede tabeller før De kjører spørringer mot dem. Å bygge indekser på nytt oppdaterer ikke planleggerens statistikk; det er bare ANALYZE som gjør det.

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

Hurtigsjekk

Test forståelsen Deres av avveiningene knyttet til gjennomstrømming.

Besvar den i den interaktive leksjonen

Oppsummering

Viktige poenger om å utsette indekser og begrensninger under masselasting:

Aktive indekser og begrensninger fører til skriveforsterkning — én INSERT blir til mange fysiske skrivinger.
Det beste mønsteret er slett, last inn, bygg på nytt: fjern sekundærindekser og fremmednøkler, last inn med COPY, og opprett dem deretter på nytt i én operasjon.
ADD CONSTRAINT ... NOT VALID etterfulgt av VALIDATE CONSTRAINT flytter kontrollen av fremmednøkler ut av banen som kjøres per rad, og inn i én samlet skanning under en svakere lås.
DEFERRABLE INITIALLY DEFERRED utsetter bare kontrollene til COMMIT — det løser problemer med innsettingsrekkefølgen, men eliminerer ikke valideringskostnaden.
Øk maintenance_work_mem og max_parallel_maintenance_workers for å gjøre gjenoppbyggingen raskere.
Avslutt alltid med ANALYZE slik at planleggeren ser de nye dataene.

Gratis å komme i gang

Lær deg SQL med en AI-veileder – gratis

Skriv og kjør ekte kode i nettleseren, få umiddelbar hjelp fra en AI-veileder som er tilgjengelig døgnet rundt, og fortsett der du slapp – på nettet eller i appen.

Kurs: 22
Leksjoner: 88

Start læringen gratis Se hele kurset

Ofte stilte spørsmål

Er leksjonen «Utsette indekser og begrensninger under lasting» gratis?

Ja – du kan lese valgfritt 3 av leksjonene i læringsstien Ytelse og spørringsoptimalisering i PostgreSQL, inkludert «Utsette indekser og begrensninger under lasting», gratis i sin helhet her på nettet. Deretter låser CoddyKit PRO opp alle leksjoner, samt interaktiv øving med en innebygd kodeeditor og en AI-veileder som er tilgjengelig døgnet rundt. Kurset i Ytelse og spørringsoptimalisering i PostgreSQL inneholder totalt 4 leksjoner.

Hva lærer jeg i «Utsette indekser og begrensninger under lasting»?

Fjern og bygg opp indekser og begrensninger på nytt rundt masselasting for å redusere skriveforsterkning kraftig. Du øver på Ytelse og spørringsoptimalisering i PostgreSQL med praktisk kode som du kjører direkte i nettleseren, mens en AI-veileder som er tilgjengelig døgnet rundt, svarer på spørsmålene dine mens du jobber deg gjennom leksjonen.

Trenger jeg erfaring for å begynne med Ytelse og spørringsoptimalisering i PostgreSQL?

Ingen tidligere erfaring er nødvendig. Ytelse og spørringsoptimalisering i PostgreSQL på CoddyKit er lagt opp for både nybegynnere og viderekomne, så De kan begynne her eller helt fra start og lære i Deres eget tempo. Dette er leksjon 2 av 4.

Hvor lang tid tar leksjonen «Utsette indekser og begrensninger under lasting»?

De fleste CoddyKit-leksjoner tar omtrent 5–10 minutter. Hver leksjon er kort og interaktiv, slik at De gjør jevne fremskritt og kan fortsette akkurat der De slapp – både på nettet og i appen.

Kan jeg skrive og kjøre kode i denne Ytelse og spørringsoptimalisering i PostgreSQL-leksjonen?

Ja. Alle Ytelse og spørringsoptimalisering i PostgreSQL-leksjoner har en innebygd kodeeditor, slik at De kan skrive og kjøre ekte kode direkte i nettleseren og få umiddelbar tilbakemelding fra AI – uten lokal konfigurering.

Alle leksjonene i dette kurset

Gjennomstrømming for COPY kontra INSERT med flere rader
Utsette indekser og begrensninger under lasting
Justere WAL og sjekkpunkter for inntak
Upserts i stor skala med ON CONFLICT

← Tilbake til Ytelse og spørringsoptimalisering i PostgreSQL