Ydelsesoptimering og optimering af forespørgsler i PostgreSQL · Lektion

Udskydelse af indeks og constraints under indlæsning

Fjern og genopbyg indeks og constraints omkring masseindlæsninger for at reducere write amplification markant.

Lektion 2 af 413 trin

Udskydelse af indeks og constraints under indlæsning er en gratis Ydelsesoptimering og optimering af forespørgsler i PostgreSQL-lektion på CoddyKit. Dette er lektion 2 af 4. Du kan læse alle 3 lektioner i dette læringsspor gratis i deres fulde længde — derefter låser CoddyKit PRO alle lektioner op samt praktiske øvelser med en indbygget kodeeditor og en AI-underviser døgnet rundt. Den er en del af læringsforløbet i Ydelsesoptimering og optimering af forespørgsler i PostgreSQL, og dine fremskridt synkroniseres på tværs af nettet og CoddyKit-appen. Ydelsesoptimering og optimering af forespørgsler i PostgreSQL-kurset indeholder 4 lektioner i alt.

Hvorfor masseindlæsninger bliver langsomme

Når du indlæser millioner af rækker i en tabel, der allerede har indekser og begrænsninger, betaler PostgreSQL en skjult afgift for hver eneste række.

Hvert indeks skal opdateres (B-træ-sidesplit, WAL-skrivninger).
Hver fremmednøgle udløser et opslag i den refererede tabel.
Hver unik begrænsning eller kontrolbegrænsning valideres pr. række.

Dette arbejde pr. række kaldes skriveforstærkning: én logisk INSERT bliver til mange fysiske skrivninger. Den centrale optimering i denne lektion er at udskyde arbejdet — indlæs rådataene først, og opbyg derefter indekserne og validér begrænsningerne én gang samlet.

Omkostningen pr. indeks

Det er ikke gratis at vedligeholde et B-træindeks under en indlæsning. For hver række, der indsættes, skal PostgreSQL gennemløbe træet, finde blad-siden, eventuelt opdele den og logge ændringen til WAL.

Det er langt billigere at opbygge det samme indeks efter, at dataene er til stede: PostgreSQL sorterer alle nøgler på én gang og skriver tætte, sekventielle sider. En tabel med 5 indekser, der indlæses række for række, udfører omtrent 6 gange så meget skrivearbejde som en indlæsning kun af heapen.

Konklusionen er: færre indekser under indlæsningen = mindre forstærkning.

Mønster: Slet, indlæs, genopbyg

Det klassiske ETL-mønster for en tabel, der skal modtage en stor indlæsning, er:

Slet de sekundære indekser.
Indlæs dataene (COPY er hurtigst).
Genopbyg indekserne i én gennemgang.

Nedenfor ses grundstrukturen. Bemærk, at vi beholder primærnøglen indtil videre og kun sletter sekundære indekser, der ikke er nødvendige under selve indlæ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 ved indlæsning

Når indekserne er af vejen, har indlæsningsmetoden betydning. COPY streamer rækker i én kommando med et minimalt overhead pr. række, mens tusindvis af individuelle INSERT-sætninger hver især betaler omkostninger til fortolkning, planlægning og rundture.

Ved ETL-gennemløb bør du foretrække COPY (eller \copy fra psql) frem for indsættelser én række ad gangen. Hvis du er nødt til at bruge INSERT, så saml mange rækker i hver sætning.

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

Udskydelse af validering af fremmednøgler

Fremmednøgler valideres række for række under en indlæsning, og der udføres et indeksopslag i den overordnede tabel hver gang. Du kan undgå dette ved først at gøre begrænsningen til NOT VALID, derefter indlæse dataene og til sidst validere samlet.

ADD CONSTRAINT ... NOT VALID tilføjer fremmednøglen uden at kontrollere eksisterende rækker. Nye rækker kontrolleres stadig ved indsættelse, så hvis du helt vil undgå arbejdet række for række, skal du slette og tilføje fremmednøglen igen efter indlæsningen eller indlæse dataene, før du tilføjer den.

-- 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 efterfulgt af VALIDATE hjælper

Hvis du tilføjer en fremmednøgle på normal vis, kræver det en ACCESS EXCLUSIVE-lås og en gennemgang af hele tabellen, mens skrivninger blokeres. To-trinsmetoden opdeler dette:

ADD ... NOT VALID er hurtigt og kræver kun en kortvarig, stærk lås for at registrere begrænsningen.
VALIDATE CONSTRAINT gennemgår tabellen under en svagere SHARE UPDATE EXCLUSIVE-lås, så samtidige læsninger og skrivninger kan fortsætte.

For indlæsninger betyder det, at du udfører den dyre validering én gang, efter at alle data er til stede, i stedet for for hver række.

DEFERRABLE-begrænsninger i en transaktion

PostgreSQL understøtter også DEFERRABLE-begrænsninger, som udskyder kontrollen til slutningen af en transaktion (COMMIT). Det er anderledes end at slette en begrænsning: Kontrollen udføres stadig, bare senere.

Det er nyttigt, når rækker ankommer i en rækkefølge, der midlertidigt overtræder en fremmednøgle eller en unik begrænsning (for eksempel når underordnede rækker kommer før overordnede rækker i én 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;

Udskudt kontrol kontra slettet begrænsning

Vær opmærksom på afvejningen:

DEFERRABLE INITIALLY DEFERRED validerer stadig hver række, blot ved COMMIT i stedet for ved INSERT. Det løser problemer med rækkefølgen, men fjerner ikke valideringsomkostningen.
Sletning/gen-tilføjelse (eller NOT VALID + VALIDATE) fjerner arbejdet række for række helt og validerer igen i én effektiv gennemgang.

Ved maksimal gennemløbshastighed for enorme indlæsninger vinder sletning og genopbygning. Når korrekthed ved en vanskelig indsættelsesrækkefølge er vigtig, er deferrable det rette værktøj.

Optimering af indeksgenopbygningen

Genopbygning af indekser efter en indlæsning er i sig selv en sorteringstung operation. To indstillinger gør den meget hurtigere for den session, der kører indlæsningen:

maintenance_work_mem — mere hukommelse betyder færre eksterne sorteringssammenfletninger ved opbygning af indekser.
max_parallel_maintenance_workers — lader en enkelt CREATE INDEX bruge flere CPU'er.

Hæv disse indstillinger for indlæsningssessionen, og opbyg derefter indekserne.

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 komplet ETL-sekvens

Hvis vi samler det hele for en stor trinvis indlæsning i en eksisterende tabel, er en robust rækkefølge:

Slet sekundære indekser.
Slet eller deaktivér dyre fremmednøgler.
Hæv maintenance_work_mem.
Indlæs via COPY.
Genopbyg indekserne.
Tilføj fremmednøglerne igen, og VALIDATE.
Kør ANALYZE, så forespørgselsplanlæggeren får friske statistikker.

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;

Glem ikke ANALYZE

Efter en stor indlæsning er tabelstatistikkerne, som forespørgselsplanlæggeren baserer sig på, forældede — den tror måske stadig, at tabellen er lille. Det fører til dårlige planer (sekventielle gennemgange, hvor et indeks ville være bedre, eller forkerte join-rækkefølger).

Kør altid ANALYZE (eller VACUUM ANALYZE) på nyligt indlæste tabeller, før du kører forespørgsler mod dem. Genopbygning af indekser opdaterer ikke statistikkerne for forespørgselsplanlæggeren; det gør kun ANALYZE.

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

Hurtig kontrol

Test din forståelse af afvejningerne ved gennemløbshastighed.

Besvar den i den interaktive lektion

Opsummering

Vigtige pointer om at udskyde indekser og begrænsninger under masseindlæsninger:

Aktive indekser og begrænsninger medfører skriveforstærkning — én INSERT bliver til mange fysiske skrivninger.
Det bedste mønster er slet, indlæs, genopbyg: fjern sekundære indekser og fremmednøgler, indlæs med COPY, og genskab dem derefter i én gennemgang.
ADD CONSTRAINT ... NOT VALID efterfulgt af VALIDATE CONSTRAINT flytter kontrollen af fremmednøgler væk fra stien række for række og ind i én samlet gennemgang under en mindre omfattende lås.
DEFERRABLE INITIALLY DEFERRED udskyder kun kontrollerne til COMMIT — det løser problemer med indsættelsesrækkefølgen, men fjerner ikke valideringsomkostningen.
Hæv maintenance_work_mem og max_parallel_maintenance_workers for at gøre genopbygningen hurtigere.
Afslut altid med ANALYZE, så forespørgselsplanlæggeren kan se de nye data.

Gratis at komme i gang

Lær SQL med en AI-underviser — gratis

Skriv og kør rigtig kode i din browser, få øjeblikkelig hjælp fra en AI-underviser døgnet rundt, og fortsæt, hvor du slap, på web eller i appen.

Kurser: 22
Lektioner: 88

Start gratis Se hele kurset

Ofte stillede spørgsmål

Er lektionen “Udskydelse af indeks og constraints under indlæsning” gratis?

Ja — alle 3 lektioner i læringssporet Ydelsesoptimering og optimering af forespørgsler i PostgreSQL, inklusive “Udskydelse af indeks og constraints under indlæsning”, kan læses gratis i deres fulde længde her på webstedet. Derefter låser CoddyKit PRO alle lektioner op samt interaktive øvelser med en indbygget kodeeditor og en AI-underviser døgnet rundt. Ydelsesoptimering og optimering af forespørgsler i PostgreSQL-kurset indeholder 4 lektioner i alt.

Hvad lærer jeg i “Udskydelse af indeks og constraints under indlæsning”?

Fjern og genopbyg indeks og constraints omkring masseindlæsninger for at reducere write amplification markant. Du øver dig i Ydelsesoptimering og optimering af forespørgsler i PostgreSQL med praktisk kode, som du kører direkte i browseren, og en AI-vejleder døgnet rundt besvarer dine spørgsmål, mens du arbejder dig gennem lektionen.

Skal jeg have erfaring for at begynde på Ydelsesoptimering og optimering af forespørgsler i PostgreSQL?

Der kræves ingen tidligere erfaring. Ydelsesoptimering og optimering af forespørgsler i PostgreSQL på CoddyKit er tilrettelagt for både begyndere og øvede, så du kan starte her eller fra begyndelsen og lære i dit eget tempo. Dette er lektion 2 af 4.

Hvor lang tid tager lektionen “Udskydelse af indeks og constraints under indlæsning”?

De fleste CoddyKit-lektioner tager cirka 5–10 minutter. Hver lektion er kort og interaktiv, så du gør løbende fremskridt og kan fortsætte, hvor du slap – på både web og app.

Kan jeg skrive og køre kode i denne Ydelsesoptimering og optimering af forespørgsler i PostgreSQL-lektion?

Ja. Alle Ydelsesoptimering og optimering af forespørgsler i PostgreSQL-lektioner har en indbygget kodeeditor, så du kan skrive og køre rigtig kode direkte i din browser og få øjeblikkelig feedback fra AI – uden lokal opsætning.

Alle lektioner i dette kursus

Gennemløb for COPY kontra multi-row INSERT
Udskydelse af indeks og constraints under indlæsning
Justering af WAL og checkpoints til indlæsning
Upserts i stor skala med ON CONFLICT

← Tilbage til Ydelsesoptimering og optimering af forespørgsler i PostgreSQL