PostgreSQL:n suorituskyky ja kyselyjen optimointi · Oppitunti

Indeksien ja rajoitteiden lykkääminen latauksen ajaksi

Poistakaa ja rakentakaa indeksit sekä rajoitteet uudelleen massalatausten yhteydessä kirjoitusvahvistuksen vähentämiseksi.

Oppitunti 2/413 vaihetta

Indeksien ja rajoitteiden lykkääminen latauksen ajaksi on ilmainen PostgreSQL:n suorituskyky ja kyselyjen optimointi-oppitunti CoddyKitissä. Tämä on oppitunti 2/4. Voit lukea tästä oppimispolusta kokonaan mitkä tahansa 3 oppituntia ilmaiseksi — sen jälkeen CoddyKit PRO avaa kaikki oppitunnit sekä käytännön harjoittelun sisäänrakennetulla koodieditorilla ja ympäri vuorokauden toimivalla tekoälytuutorilla. Oppitunti kuuluu PostgreSQL:n suorituskyky ja kyselyjen optimointi-oppimispolkuun, ja edistymisesi synkronoituu verkon ja CoddyKit-sovelluksen välillä. PostgreSQL:n suorituskyky ja kyselyjen optimointi-kurssilla on yhteensä 4 oppituntia.

Miksi massalataukset hidastuvat

Kun lataat miljoonia rivejä tauluun, jossa on jo indeksejä ja rajoitteita, PostgreSQL maksaa piilokustannuksen jokaisesta rivistä.

Jokainen indeksi on päivitettävä (B-puun sivujen jakautumiset ja WAL-kirjoitukset).
Jokainen viiteavain käynnistää haun viitatusta taulusta.
Jokainen yksilöllisyys- tai tarkistusrajoite validoidaan rivikohtaisesti.

Tätä rivikohtaista työtä kutsutaan kirjoitusvahvistukseksi: yksi looginen INSERT muuttuu useiksi fyysisiksi kirjoituksiksi. Tämän oppitunnin keskeinen optimointi on siirtää työ myöhemmäksi — lataa raakatiedot ensin ja rakenna indeksit sekä validoi rajoitteet vasta sen jälkeen kerralla.

Yhden indeksin kustannus

B-tree-indeksin ylläpito latauksen aikana ei ole ilmaista. Jokaisen lisättävän rivin kohdalla PostgreSQL joutuu kulkemaan puussa, etsimään lehtisivun, mahdollisesti jakamaan sen ja kirjaamaan muutoksen WAL-lokiin.

Saman indeksin rakentaminen datan lataamisen jälkeen on paljon edullisempaa: PostgreSQL lajittelee kaikki avaimet kerralla ja kirjoittaa tiiviitä, peräkkäisiä sivuja. Taulukko, jossa on 5 indeksiä, aiheuttaa riveittäin ladattaessa noin kuusinkertaisen kirjoitusmäärän pelkkään heap-taulukon lataamiseen verrattuna.

Johtopäätös: mitä vähemmän indeksejä on käytössä latauksen aikana, sitä vähäisempää kirjoitusvahvistus on.

Malli: poista, lataa, rakenna uudelleen

Suuria tietomääriä vastaanottavan taulukon perinteinen ETL-malli on seuraava:

Poista toissijaiset indeksit.
Lataa data (COPY on nopein).
Rakenna uudelleen indeksit yhdellä kertaa.

Alla on perusrakenne. Huomaa, että säilytämme tällä kertaa primary key -rajoitteen ja poistamme vain toissijaiset indeksit, joita ei tarvita itse latauksen aikana.

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 voittaa INSERTin latauksessa

Kun indeksit eivät enää hidasta toimintaa, lataustavalla on merkitystä. COPY siirtää rivit yhdellä komennolla ja hyvin vähäisin rivikohtaisin kustannuksin, kun taas tuhannet yksittäiset INSERT-lauseet aiheuttavat kukin jäsentämis-, suunnittelu- ja verkkokutsukustannuksia.

ETL-suorituskyvyn kannalta kannattaa käyttää COPY-komentoa (tai psql:n \copy-komentoa) rivikohtaisten lisäysten sijaan. Jos joudut käyttämään INSERTiä, kokoa monta riviä samaan lauseeseen.

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

Vierasavainten tarkistuksen siirtäminen myöhemmäksi

Vierasavaimet tarkistetaan latauksen aikana rivi kerrallaan, ja jokaisella kerralla tehdään indeksihaku isäntätaulukosta. Voit välttää tämän määrittämällä rajoitteen ensin tilaan NOT VALID, lataamalla datan ja suorittamalla validoinnin sen jälkeen eränä.

ADD CONSTRAINT ... NOT VALID lisää vierasavainrajoitteen tarkistamatta olemassa olevia rivejä. Uudet rivit tarkistetaan silti lisäyksen yhteydessä. Jos haluat todella ohittaa rivikohtaisen työn, poista rajoite ja lisää se uudelleen latauksen jälkeen tai lataa data ennen vierasavaimen lisäämistä.

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

Miksi NOT VALID ja sitten VALIDATE auttaa

Vierasavaimen lisääminen tavallisella tavalla ottaa ACCESS EXCLUSIVE -lukon ja käy koko taulukon läpi estäen samalla kirjoitukset. Kaksivaiheinen lähestymistapa jakaa työn:

ADD ... NOT VALID on nopea ja tarvitsee vahvan lukon vain lyhyeksi aikaa rajoitteen tallentamista varten.
VALIDATE CONSTRAINT käy taulukon läpi heikomman SHARE UPDATE EXCLUSIVE -lukon alaisena, joten samanaikaiset luku- ja kirjoitusoperaatiot ovat mahdollisia.

Latausten kannalta tämä tarkoittaa, että kallis validointi tehdään kerran kaiken datan ollessa paikallaan, ei jokaiselle riville erikseen.

DEFERRABLE-rajoitteet tapahtuman sisällä

PostgreSQL tukee myös DEFERRABLE-rajoitteita, joiden tarkistus siirtyy tapahtuman loppuun (COMMIT). Tämä eroaa rajoitteen poistamisesta: tarkistus suoritetaan edelleen, mutta myöhemmin.

Tästä on hyötyä, kun rivit saapuvat järjestyksessä, joka rikkoo tilapäisesti vierasavain- tai yksilöllisyysrajoitetta, esimerkiksi kun lapsirivit saapuvat ennen isäntärivejä saman tapahtuman aikana.

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;

Myöhennetty tarkistus ja poistettu rajoite

On tärkeää ymmärtää erot:

DEFERRABLE INITIALLY DEFERRED tarkistaa edelleen jokaisen rivin, mutta vasta COMMIT-komennon yhteydessä INSERTin sijaan. Se ratkaisee järjestysongelmat, mutta ei poista validoinnin kustannuksia.
Poista ja lisää uudelleen (tai NOT VALID + VALIDATE) poistaa rivikohtaisen työn kokonaan ja tarkistaa datan uudelleen yhdellä tehokkaalla läpikäynnillä.

Kun tavoitteena on valtavien latausten suurin mahdollinen läpimeno, poistaminen ja uudelleenrakentaminen on paras vaihtoehto. Kun taas oikeellisuus riippuu hankalasta lisäysjärjestyksestä, deferrable-rajoite on oikea työkalu.

Indeksien uudelleenrakentamisen säätäminen

Indeksien rakentaminen uudelleen latauksen jälkeen on itsessään paljon lajittelua vaativa operaatio. Kaksi asetusta nopeuttaa sitä merkittävästi latauksen suorittavassa istunnossa:

maintenance_work_mem — suurempi muisti vähentää indeksien rakentamisen aikana tarvittavia ulkoisia lajittelujen yhdistämisiä.
max_parallel_maintenance_workers — sallii yhden CREATE INDEX -komennon käyttää useita suorittimia.

Nosta näitä arvoja latausistuntoa varten ja rakenna sitten indeksit.

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

Täydellinen ETL-järjestys

Kun olemassa olevaan taulukkoon tehdään suuri inkrementaalinen lataus, kestävä toimintajärjestys on seuraava:

Poista toissijaiset indeksit.
Poista käytöstä tai poista raskaat vierasavaimet.
Nosta asetuksen maintenance_work_mem arvoa.
Lataa data COPY-komennolla.
Rakenna indeksit uudelleen.
Lisää vierasavaimet uudelleen ja suorita VALIDATE.
Suorita ANALYZE, jotta suunnittelijalla on tuoreet tilastot.

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;

Älä unohda ANALYZEa

Suuren latauksen jälkeen suunnittelijan käyttämät taulukon tilastot ovat vanhentuneet — se saattaa edelleen luulla taulukkoa hyvin pieneksi. Tämä johtaa huonoihin suunnitelmiin, kuten peräkkäisiin taulukkohakuihin indeksin käytön sijaan tai vääriin liitosten suoritusjärjestyksiin.

Suorita aina ANALYZE (tai VACUUM ANALYZE) juuri ladatuille taulukoille ennen kuin suoritat niihin kohdistuvia kyselyitä. Indeksien uudelleenrakentaminen ei päivitä suunnittelijan tilastoja; vain ANALYZE tekee sen.

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

Pikatarkistus

Testaa ymmärryksesi läpimenon kompromisseista.

Vastaa interaktiivisessa oppitunnissa

Kertaus

Keskeiset opit indeksien ja rajoitteiden siirtämisestä myöhemmäksi massalatausten aikana:

Käytössä olevat indeksit ja rajoitteet aiheuttavat kirjoitusvahvistusta — yhdestä INSERTistä tulee useita fyysisiä kirjoituksia.
Toimivin malli on poista, lataa, rakenna uudelleen: poista toissijaiset indeksit ja vierasavaimet, lataa data COPY-komennolla ja luo ne sitten uudelleen yhdellä kertaa.
ADD CONSTRAINT ... NOT VALID ja sitä seuraava VALIDATE CONSTRAINT siirtävät vierasavainten tarkistuksen pois rivikohtaisesta käsittelystä yhteen massatarkistukseen kevyemmän lukon alaisena.
DEFERRABLE INITIALLY DEFERRED siirtää tarkistukset vain COMMIT-komennon yhteyteen — se ratkaisee lisäysjärjestykseen liittyvät ongelmat, mutta ei poista validoinnin kustannuksia.
Nosta asetusten maintenance_work_mem ja max_parallel_maintenance_workers arvoja nopeuttaaksesi uudelleenrakentamista.
Viimeistele aina suorittamalla ANALYZE, jotta suunnittelija näkee uuden datan.

Aloita maksutta

Opi SQL tekoälytuutorin avulla — ilmaiseksi

Kirjoita ja suorita oikeaa koodia selaimessa, saa välitöntä apua tekoälytuutorilta ympäri vuorokauden ja jatka siitä, mihin jäit, verkossa tai sovelluksessa.

Kurssit: 22
Oppitunnit: 88

Aloita opiskelu maksutta Näytä koko kurssi

Usein kysytyt kysymykset

Onko oppitunti ”Indeksien ja rajoitteiden lykkääminen latauksen ajaksi” ilmainen?

Kyllä — voit lukea täällä verkossa kokonaan ilmaiseksi mitkä tahansa PostgreSQL:n suorituskyky ja kyselyjen optimointi-oppimispolun 3 oppituntia, myös oppitunnin “Indeksien ja rajoitteiden lykkääminen latauksen ajaksi”. Sen jälkeen CoddyKit PRO avaa kaikki oppitunnit sekä interaktiiviset harjoitukset sisäänrakennetulla koodieditorilla ja ympäri vuorokauden toimivalla tekoälytuutorilla. PostgreSQL:n suorituskyky ja kyselyjen optimointi-kurssilla on yhteensä 4 oppituntia.

Mitä opin oppitunnilla ”Indeksien ja rajoitteiden lykkääminen latauksen ajaksi”?

Poistakaa ja rakentakaa indeksit sekä rajoitteet uudelleen massalatausten yhteydessä kirjoitusvahvistuksen vähentämiseksi. Harjoittelet PostgreSQL:n suorituskyky ja kyselyjen optimointi-aihetta koodilla, jonka suoritat suoraan selaimessa. Ympäri vuorokauden käytettävissä oleva tekoälytuutori vastaa kysymyksiisi oppitunnin aikana.

Tarvitsenko kokemusta aloittaakseni PostgreSQL:n suorituskyky ja kyselyjen optimointi-opiskelun?

Aiempi kokemus ei ole tarpeen. CoddyKitin PostgreSQL:n suorituskyky ja kyselyjen optimointi-oppimispolku sopii vasta-alkajista edistyneisiin, joten voit aloittaa tästä tai alusta ja edetä omaan tahtiisi. Tämä on oppitunti 2/4.

Kuinka kauan ”Indeksien ja rajoitteiden lykkääminen latauksen ajaksi”-oppitunnin suorittaminen kestää?

Useimmat CoddyKitin oppitunnit kestävät noin 5–10 minuuttia. Jokainen oppitunti on lyhyt ja interaktiivinen, joten edistyt tasaisesti ja voit jatkaa siitä, mihin jäit – sekä verkossa että sovelluksessa.

Voinko kirjoittaa ja suorittaa koodia tällä PostgreSQL:n suorituskyky ja kyselyjen optimointi-oppitunnilla?

Kyllä. Jokainen PostgreSQL:n suorituskyky ja kyselyjen optimointi-oppitunti sisältää sisäänrakennetun koodieditorin, joten voit kirjoittaa ja suorittaa oikeaa koodia suoraan selaimessa ja saada välitöntä palautetta tekoälyltä – paikallista asennusta ei tarvita.

Kaikki tämän kurssin oppitunnit

COPY- ja monirivisen INSERT-komennon suorituskyky
Indeksien ja rajoitteiden lykkääminen latauksen ajaksi
WALin ja tarkistuspisteiden säätäminen tiedonsyöttöä varten
Laajamittaiset upsertit ON CONFLICT -komennolla

← Takaisin: PostgreSQL:n suorituskyky ja kyselyjen optimointi