PostgreSQL:n suorituskyky ja kyselyjen optimointi · Oppitunti

TOASTin sisäinen toiminta ja suurten arvojen tallennus

Ymmärtäkää, miten PostgreSQL tallentaa liian suuret sarakkeet, ja säätäkää pakkausta sekä ulkoisen tallennuksen raja-arvoja.

Oppitunti 4/413 vaihetta

TOASTin sisäinen toiminta ja suurten arvojen tallennus on ilmainen PostgreSQL:n suorituskyky ja kyselyjen optimointi-oppitunti CoddyKitissä. Tämä on oppitunti 4/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 TOAST on olemassa

PostgreSQL tallentaa rivit kiinteän kokoisille 8 kilotavun sivuille. Yksittäinen rivi ei voi ulottua usealle sivulle, joten leveä arvo (pitkä text, suuri jsonb tai bytea) ei muuten mahtuisi koskaan sivulle.

TOAST (The Oversized-Attribute Storage Technique) ratkaisee ongelman pakkaamalla liian suuret sarakkeet ja pilkkomalla ne tarvittaessa osiin, jotka tallennetaan erilliseen oheistauluun.

Pääkeon rivi pysyy pienenä ja välimuistin kannalta tehokkaana.
Looginen arvo voi olla huomattavasti yli 8 kilotavun kokoinen (enintään noin 1 Gt).
Toiminta tapahtuu automaattisesti ja kyselyille läpinäkyvästi.

TOAST-kynnys

TOAST otetaan käyttöön, kun rivin kokonaiskoko ylittäisi arvon TOAST_TUPLE_THRESHOLD, joka on oletusarvoisesti 2 kt (neljäsosa 8 kt:n sivusta).

Kun raja ylittyy, PostgreSQL pakkaa suurimmat TOASTattavat attribuutit ja/tai siirtää ne rivin ulkopuolelle, kunnes rivi mahtuu arvon TOAST_TUPLE_TARGET alle (myös noin 2 kt).

Vain muuttuvan pituiset tyypit (text, varchar, jsonb, bytea, taulukot jne.) voidaan TOASTata. Kiinteän leveyden tyyppejä, kuten integer tai timestamptz, ei koskaan TOASTata.

TOAST-taulun etsiminen

Jokaiselle taululle, jossa on vähintään yksi TOASTattava sarake, luodaan siihen liittyvä TOAST-taulu nimeltä pg_toast.pg_toast_<oid>. Voitte paikantaa sen luettelosta.

reltoastrelid-sarake yhdistää kekorelaation sen TOAST-relaatioon; arvo 0 tarkoittaa, ettei TOAST-taulua ole luotu.

SELECT c.relname,
       c.reltoastrelid,
       t.relname AS toast_table
FROM pg_class c
LEFT JOIN pg_class t ON t.oid = c.reltoastrelid
WHERE c.relname = 'documents';

Neljä tallennusstrategiaa

Jokaisella sarakkeella on tallennusstrategia, joka määrittää, voidaanko se pakata ja/tai siirtää rivin ulkopuolelle:

PLAIN — ei pakkausta eikä rivin ulkopuolista tallennusta; kelpaa vain tyypeille, joita ei voi TOASTata.
EXTENDED — sallii sekä pakkauksen että rivin ulkopuolisen tallennuksen (useimpien varlena-tyyppien oletus).
EXTERNAL — sallii rivin ulkopuolisen tallennuksen, mutta ei pakkausta (nopeampi osamerkkijonojen käyttö).
MAIN — sallii pakkauksen, mutta pitää arvon päätaulussa, ellei siirtäminen ole ehdottoman välttämätöntä.

Sarakekohtaisen tallennuksen tarkasteleminen

Kyselkää pg_attribute.attstorage-saraketta nähdäksenne kunkin sarakkeen strategian. Koodit vastaavat seuraavia arvoja: p=PLAIN, e=EXTERNAL, m=MAIN, x=EXTENDED.

SELECT attname,
       atttypid::regtype AS type,
       CASE attstorage
         WHEN 'p' THEN 'plain'
         WHEN 'e' THEN 'external'
         WHEN 'm' THEN 'main'
         WHEN 'x' THEN 'extended'
       END AS storage
FROM pg_attribute
WHERE attrelid = 'documents'::regclass
  AND attnum > 0
  AND NOT attisdropped;

Sarakkeen strategian muuttaminen

Ohittakaa oletusarvo komennolla ALTER TABLE ... SET STORAGE. Yleinen optimointi on vaihtaa suurelle bytea-sarakkeelle strategia EXTERNAL-arvoon, jos luette siitä usein satunnaisia osamerkkijonoja (esimerkiksi blobin alueita). Tällöin arvot tallennetaan pakkaamattomina ja niistä voidaan hakea vain osa purkamatta koko arvoa.

Uusi strategia koskee vain muutoksen jälkeen kirjoitettuja rivejä; olemassa olevia tietoja ei kirjoiteta uudelleen ennen niiden päivittämistä.

ALTER TABLE documents
  ALTER COLUMN payload SET STORAGE EXTERNAL;

-- Force a rewrite to apply it to existing rows:
VACUUM FULL documents;

Pakkausalgoritmit: pglz ja lz4

PostgreSQL pakkaa TOAST-arvot ennen kuin se harkitsee rivin ulkopuolista tallennusta. Käytettävissä on kaksi algoritmia:

pglz — vanha sisäänrakennettu algoritmi, kohtuullinen pakkaussuhde ja hitaampi toiminta.
lz4 — käytettävissä PostgreSQL 14:stä lähtien; pakkaa ja purkaa huomattavasti nopeammin, mutta pakkaussuhde on hieman heikompi. Palvelin on käännettävä lz4-tuen kanssa.

Klusterin laajuisen oletuksen määrittää default_toast_compression.

SHOW default_toast_compression;

-- Set lz4 cluster-wide (postgresql.conf or per session):
SET default_toast_compression = 'lz4';

Sarakekohtainen pakkaus

PostgreSQL 14:stä lähtien voitte määrittää pakkausmenetelmän yksittäisille sarakkeille komennolla SET COMPRESSION. Tämä asetus on riippumaton tallennusstrategiasta.

Valitkaa lz4 usein käytetyille suurille sarakkeille, jos suorittimen käyttö luku- ja kirjoitusvaiheessa on tärkeää; säilyttäkää pglz tai käyttäkää EXTERNAL-strategiaa, jos pakkaussuhde tai osamerkkijonojen käyttö on tärkeämpää.

ALTER TABLE documents
  ALTER COLUMN body SET COMPRESSION lz4;

-- Inspect chosen method per column:
SELECT attname, attcompression
FROM pg_attribute
WHERE attrelid = 'documents'::regclass
  AND attnum > 0;

Rivin ulkopuolisen tallennuksen kohteen säätäminen

Reloption toast_tuple_target määrittää, kuinka hanakasti PostgreSQL siirtää attribuutteja rivin ulkopuolelle: se määrittää koon, jota kohti päämonikko pienennetään (sallittu alue on 128 tavusta noin 8160 tavuun).

Arvon pienentäminen siirtää useampia arvoja TOASTiin aikaisemmin, pitää pääkeon tiiviinä ja parantaa skannausten suorituskykyä, kun suuria sarakkeita luetaan harvoin. Arvon suurentaminen pitää enemmän tietoja rivin sisällä.

ALTER TABLE documents
  SET (toast_tuple_target = 512);

-- Verify current reloptions:
SELECT reloptions
FROM pg_class
WHERE relname = 'documents';

TOAST-tilankäytön mittaaminen

Käyttäkää kokofunktioita erottaaksenne päätaulun tavut TOAST-tavuista. Näin näette, sijaitseeko paisuminen keossa vai liian suurissa arvoissa.

pg_table_size — kekotaulu + TOAST + niiden indeksien TOAST, ei tavallisia indeksejä.
pg_relation_size(rel, 'main') — vain päähaara.

SELECT
  pg_size_pretty(pg_relation_size('documents'))        AS heap,
  pg_size_pretty(
    pg_total_relation_size(reltoastrelid)
  )                                                     AS toast,
  pg_size_pretty(pg_total_relation_size('documents'))  AS total
FROM pg_class
WHERE relname = 'documents';

Käytännön suorituskykyvaikutukset

TOAST on näkymätön, kunnes se ei enää ole. Muistakaa nämä keskeiset vaikutukset:

TOASTatun sarakkeen lukeminen aiheuttaa ylimääräisiä indeksihakuja TOAST-tauluun ja mahdollisesti purkamisen — välttäkää SELECT * -kyselyä, kun tarvitsette vain pieniä sarakkeita.
Arvot pysyvät TOASTattuina niiden päivitysten aikana, jotka eivät koske kyseisiä arvoja, joten toisistaan riippumattomat päivitykset ovat edullisia.
EXTERNAL mahdollistaa suurten pakkaamattomien blobien tehokkaat substr()- ja aluehaut.
Vaihtaminen arvosta pglz arvoon lz4 voi vähentää kirjoituksiin kuluvaa suoritinaikaa huomattavasti työkuormissa, joissa lisätään paljon suuria arvoja.

Pikatarkistus

Taulunne suurta bytea-saraketta luetaan pääasiassa substr()-kutsuilla pienistä tavuväleistä, ja lukeminen on hidasta, koska jokainen käyttö purkaa koko arvon. Mikä yksittäinen muutos korjaa ongelman parhaiten?

Vastaa interaktiivisessa oppitunnissa

Kertaus

Opitte, miten PostgreSQL käsittelee liian suuria arvoja:

TOAST käynnistyy, kun rivi ylittäisi noin 2 kt:n kynnyksen, ja pakkaa suuret varlena-sarakkeet ennen niiden siirtämistä pg_toast.*-tauluihin.
Neljä strategiaa — PLAIN, MAIN, EXTENDED (oletus) ja EXTERNAL — hallitsevat pakkausta ja rivin ulkopuolista sijoittelua, ja ne asetetaan komennolla ALTER TABLE ... SET STORAGE.
lz4 (PG14+) tarjoaa nopeamman pakkauksen kuin pglz; valitkaa se sarakekohtaisesti komennolla SET COMPRESSION tai koko klusterille asetuksella default_toast_compression.
toast_tuple_target säätää, kuinka nopeasti arvot poistuvat keosta; kokofunktiot paljastavat, kuinka paljon tallennustilaa käytetään TOASTiin.
Valitkaa EXTERNAL osamerkkijono- ja aluehakuja varten, lz4 paljon kirjoittaville suurille arvoille ja välttäkää SELECT * -kyselyä tarpeettoman TOAST-purkamisen välttämiseksi.

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 ”TOASTin sisäinen toiminta ja suurten arvojen tallennus” ilmainen?

Kyllä — voit lukea täällä verkossa kokonaan ilmaiseksi mitkä tahansa PostgreSQL:n suorituskyky ja kyselyjen optimointi-oppimispolun 3 oppituntia, myös oppitunnin “TOASTin sisäinen toiminta ja suurten arvojen tallennus”. 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 ”TOASTin sisäinen toiminta ja suurten arvojen tallennus”?

Ymmärtäkää, miten PostgreSQL tallentaa liian suuret sarakkeet, ja säätäkää pakkausta sekä ulkoisen tallennuksen raja-arvoja. 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 4/4.

Kuinka kauan ”TOASTin sisäinen toiminta ja suurten arvojen tallennus”-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

Taulujen ja indeksien paisumisen tarkka mittaaminen
Tilan vapauttaminen pg_repackilla
Fillfactor-asetuksen säätäminen paljon päivitettäville tauluille
TOASTin sisäinen toiminta ja suurten arvojen tallennus

← Takaisin: PostgreSQL:n suorituskyky ja kyselyjen optimointi