PostgreSQL:n suorituskyky ja kyselyjen optimointi · Oppitunti

Julkaisut, tilaukset ja replikointitunniste

Määrittäkää valikoiva replikointi ja replikointitunniste, joita tarvitaan UPDATE- ja DELETE-muutosten oikeaan suoratoistoon.

Oppitunti 1/413 vaihetta

Julkaisut, tilaukset ja replikointitunniste on ilmainen PostgreSQL:n suorituskyky ja kyselyjen optimointi-oppitunti CoddyKitissä. Tämä on oppitunti 1/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 loogista replikaatiota käytetään suorituskyvyn parantamiseen

Fyysinen (streaming-)replikointi siirtää koko WAL-lokin tavu tavulta samanlaisille replikoille. Looginen replikointi purkaa WAL-lokin sen sijaan rivitason muutos-tapahtumiksi (INSERT/UPDATE/DELETE) ja siirtää vain valitsemanne taulukot.

Valikoivuus: replikoi paljon käytetyn osan taulukoista koko klusterin sijaan.
Versioiden ja skeemojen välinen yhteensopivuus: julkaisijan ja tilaajan pääversiot voivat poiketa toisistaan, ja tilaajalla voi olla ylimääräisiä sarakkeita tai indeksejä.
Suorituskykykäyttötapaukset: siirtäkää raportointikyselyt lukureplikalle, rakentakaa pelkistetympi OLAP-kopio tai jakakaa kirjoitusliikenne osiin.

Kaksi perusrakennetta ovat lähteessä oleva PUBLICATION ja kohteessa oleva SUBSCRIPTION.

Loogisen dekoodauksen ottaminen käyttöön

Looginen replikointi edellyttää, että WAL-lokissa on riittävästi tietoa rivien rekonstruoimiseksi. Asettakaa julkaisijassa wal_level = logical (palvelin on käynnistettävä uudelleen).

max_wal_senders-arvon on sallittava yksi paikka kutakin tilausta varten sekä riittävä varakapasiteetti.
max_replication_slots rajoittaa loogisten replikointipaikkojen määrää.

Kukin tilaus käyttää yhtä replikointipaikkaa, joka pitää WAL-lokin julkaisijassa tallessa, kunnes tilaaja vahvistaa sen käsitellyksi. Passiivinen tilaaja voi näin ollen aiheuttaa WAL-lokin rajattoman kasvun.

SHOW wal_level;

ALTER SYSTEM SET wal_level = 'logical';
ALTER SYSTEM SET max_wal_senders = 10;
ALTER SYSTEM SET max_replication_slots = 10;

-- Restart PostgreSQL, then verify
SHOW wal_level;

Julkaisun luominen

Julkaisu on nimetty joukko muutoksia yhdestä tai useammasta taulukosta. Päätätte itse, mitkä taulukot ja mitkä operaatiot julkaistaan.

FOR TABLE luettelee tietyt taulukot.
FOR ALL TABLES julkaisee kaikki nykyiset ja tulevat taulukot (vain superuser voi käyttää tätä).
publish määrittää, mitkä DML-tyypit siirretään: insert, update, delete, truncate.

Vain tarvittavien operaatioiden julkaiseminen vähentää WAL-lokin dekoodauksen työmäärää ja verkkoliikennettä.

CREATE PUBLICATION orders_pub
  FOR TABLE orders, order_items
  WITH (publish = 'insert, update, delete');

-- A reporting feed that ignores deletes entirely
CREATE PUBLICATION analytics_pub
  FOR TABLE orders
  WITH (publish = 'insert, update');

Rivi- ja sarakesuodatus (PG 15+)

PostgreSQL 15 lisäsi julkaisuihin rividatan suodattimet ja sarakeluettelot, joiden avulla voitte replikoi-da vain sen datan osan, jota tilaaja tarvitsee. Tämä pienentää replikoitavaa aineistoa ja dekoodauksen kustannuksia.

WHERE-lause suodattaa rivejä; se saa viitata vain replikoitaviin sarakkeisiin.
Sarakeluettelo replikoi osan sarakkeista, ja sen on sisällettävä replikointi-identiteetin sarakkeet.

Tämä sopii erityisen hyvin pelkistetyn raportointikopion rakentamiseen, kun arkistoidut rivit tai arkaluonteiset sarakkeet halutaan jättää pois.

-- Only stream active, recent orders, and only chosen columns
CREATE PUBLICATION active_orders_pub
  FOR TABLE orders (id, customer_id, total, status)
    WHERE (status = 'active' AND created_at > '2025-01-01');

Tilauksen luominen

Kohdepalvelimella SUBSCRIPTION muodostaa yhteyden julkaisijaan, luo replikointipaikan ja alkaa soveltaa muutoksia. Kohdetaulukoiden on oltava olemassa etukäteen, ja niiden sarakkeiden on oltava yhteensopivia.

copy_data = true (oletus) ottaa ensin tilannevedoksen olemassa olevista riveistä ja siirtää sen jälkeen reaaliaikaiset muutokset.
Kukin tilaus käynnistää tilaajassa apply worker -työprosessin.

Yhteysmerkkijono osoittaa takaisin julkaisijaan roolilla, jolla on REPLICATION-oikeus (tai joka on superuser) ja joka voi lukea julkaistut taulukot.

CREATE SUBSCRIPTION orders_sub
  CONNECTION 'host=pub.db port=5432 dbname=shop user=repl password=secret'
  PUBLICATION orders_pub
  WITH (copy_data = true, create_slot = true, enabled = true);

Ydinongelma: rivien tunnistaminen

INSERT sisältää koko uuden rivin, joten tilaaja voi yksinkertaisesti lisätä sen. UPDATE- tai DELETE-operaation on kuitenkin ilmoitettava tilaajalle, mikä olemassa oleva rivi muutetaan. Tätä varten WAL-lokin on sisällettävä rivin tunnistava "vanha" esitysmuoto.

Tätä tunnistavaa esitysmuotoa kutsutaan replikointi-identiteetiksi. Ilman sitä julkaisija ei voi koodata vanhoja avainarvoja, jolloin muutoksen käyttöönotto epäonnistuu tai muutos jätetään hiljaisesti käsittelemättä.

INSERT: ei tarvitse replikointi-identiteettiä.
UPDATE / DELETE: julkaistulla taulukolla on oltava käyttökelpoinen replikointi-identiteetti.

Replikointi-identiteetin tilat

Jokaisella taulukolla on REPLICA IDENTITY -asetus, joka määrittää, mitä vanhan rivin tietoja kirjoitetaan WAL-lokiin UPDATE- ja DELETE-operaatioissa:

DEFAULT: kirjaa perusavaimen sarakkeet. Tavallinen ja tehokas valinta.
USING INDEX: kirjaa valitun yksikäsitteisen, osittamattoman ja NOT NULL -indeksin sarakkeet.
FULL: kirjaa koko vanhan rivin; tilaaja etsii vastaavuuden kaikkien sarakkeiden perusteella. Oikea mutta kallis vaihtoehto.
NOTHING: ei kirjaa vanhaa esitysmuotoa; taulukon UPDATE/DELETE-operaatiot eivät tällöin replikoidu.

ALTER TABLE orders REPLICA IDENTITY DEFAULT;
ALTER TABLE orders REPLICA IDENTITY USING INDEX orders_uniq_idx;
ALTER TABLE orders REPLICA IDENTITY FULL;
ALTER TABLE orders REPLICA IDENTITY NOTHING;

Taulukot ilman perusavainta

Kun replikointi-identiteetti on DEFAULT, mutta perusavainta ei ole, taulukko toimii käytännössä kuten NOTHING: UPDATE- ja DELETE-operaatioita ei voida replikoi-da. PostgreSQL antaa esimerkiksi virheen "cannot update table because it does not have a replica identity and publishes updates".

Voitte korjata tilanteen kolmella tavalla seuraavassa suosituimmuusjärjestyksessä:

Lisätkää perusavain (paras suorituskyvyn kannalta).
Lisätkää yksikäsitteinen NOT NULL -indeksi ja asettakaa REPLICA IDENTITY USING INDEX.
Asettakaa REPLICA IDENTITY FULL viimeisenä vaihtoehtona.

-- Preferred: give the table a stable key
ALTER TABLE events ADD COLUMN id bigint GENERATED ALWAYS AS IDENTITY;
ALTER TABLE events ADD PRIMARY KEY (id);

-- Or pin an existing unique index as the identity
CREATE UNIQUE INDEX events_key ON events (tenant_id, occurred_at);
ALTER TABLE events REPLICA IDENTITY USING INDEX events_key;

REPLICA IDENTITY FULL -asetuksen kustannus

REPLICA IDENTITY FULL kirjoittaa vanhan rivin jokaisen sarakkeen WAL-lokiin jokaisen UPDATE- ja DELETE-operaation yhteydessä. Leveissä tai paljon muuttuvissa taulukoissa tämä kasvattaa WAL-lokin määrää ja hidastaa julkaisijaa.

Se kuormittaa myös tilaajaa: vastaavan rivin löytämiseksi sen on verrattava kaikkia sarakkeita. Ennen PostgreSQL 16:ta tämä tarkoitti jokaisen muutoksen yhteydessä tehtävää peräkkäistä skannausta; PG 16+ voi käyttää tilaajassa käyttökelpoista indeksiä, mutta koko rivin perusteella tehtävä haku on silti kalliimpi kuin avainhaku.

Käyttäkää FULL-asetusta vain, kun yksikäsitteistä avainta ei ole eikä sellaista voida lisätä.

Replikointi-identiteetin tarkistaminen

Replikointi-identiteetti tallennetaan kohteeseen pg_class.relreplident: d = oletus (perusavain), i = indeksin käyttö, f = täysi, n = ei mitään. Tarkastakaa julkaistut taulukot ennen käyttöönottoa, jotta mikään UPDATE/DELETE-operaatio ei epäonnistu replikoinnissa huomaamatta.

Taulukoilla, jotka julkaistaan update- tai delete-operaatioilla, ei saa olla arvoa relreplident = 'n', ellei niiltä myös puutu käyttökelpoinen avain.
psql-komennon \d+ tablename tulostuksessa näkyy myös replikointi-identiteetti.

SELECT n.nspname AS schema,
       c.relname AS table,
       c.relreplident AS replica_identity
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
  AND n.nspname = 'public'
ORDER BY c.relname;

Viiveen ja replikointipaikkojen tilan valvonta

Suorituskykyarkkitehtuurissa replikoinnin tilaa on valvottava. Julkaisija tarjoaa käyttöönoton etenemistiedot näkymässä pg_stat_replication ja replikointipaikkojen säilytystiedot näkymässä pg_replication_slots.

pg_replication_slots.active-arvo false yhdessä kasvavan restart_lsn-etäisyyden kanssa tarkoittaa, että WAL-lokia kertyy, koska tilaaja on jumissa.
Tilaajassa pg_stat_subscription.latest_end_lsn-arvon vertaaminen julkaisijan nykyiseen LSN-arvoon näyttää käyttöönoton viiveen.

Asettakaa max_slot_wal_keep_size, jotta toimintakyvytön tilaaja ei voi täyttää julkaisijan levyä.

SELECT slot_name,
       active,
       wal_status,
       pg_size_pretty(
         pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)
       ) AS retained_wal
FROM pg_replication_slots
WHERE slot_type = 'logical';

Pikatarkistus: Replikointi-identiteetti

Julkaistu taulukko tarvitsee oikean replikointi-identiteetin UPDATE/DELETE-operaatioiden siirtämistä varten. Testatkaa ymmärryksenne.

Vastaa interaktiivisessa oppitunnissa

Kertaus

Määrititte valikoivan loogisen replikoinnin ja replikointi-identiteetin, joka tekee UPDATE/DELETE-operaatioiden siirtämisestä oikeellista.

Julkaisija: wal_level = logical ja sen jälkeen CREATE PUBLICATION valituilla taulukoilla, operaatioilla sekä (PG 15+) rivi- ja sarakesuodattimilla.
Tilaaja: CREATE SUBSCRIPTION käynnistää apply worker -työprosessin ja luo replikointipaikan; copy_data siirtää olemassa olevat rivit alustavasti.
Replikointi-identiteetti: INSERT ei tarvitse sitä; UPDATE/DELETE tarvitsevat arvon DEFAULT (perusavain), USING INDEX (yksikäsitteinen NOT NULL -indeksi) tai FULL. NOTHING ja perusavaimeton DEFAULT rikkovat UPDATE/DELETE-operaatiot.
Suorituskyky: suosikaa avainpohjaista identiteettiä FULL-asetuksen sijaan, valvokaa replikointipaikkoja ja viivettä sekä rajoittakaa säilytettävää WAL-lokia asetuksella max_slot_wal_keep_size.

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 ”Julkaisut, tilaukset ja replikointitunniste” ilmainen?

Kyllä — voit lukea täällä verkossa kokonaan ilmaiseksi mitkä tahansa PostgreSQL:n suorituskyky ja kyselyjen optimointi-oppimispolun 3 oppituntia, myös oppitunnin “Julkaisut, tilaukset ja replikointitunniste”. 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 ”Julkaisut, tilaukset ja replikointitunniste”?

Määrittäkää valikoiva replikointi ja replikointitunniste, joita tarvitaan UPDATE- ja DELETE-muutosten oikeaan suoratoistoon. 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 1/4.

Kuinka kauan ”Julkaisut, tilaukset ja replikointitunniste”-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

Julkaisut, tilaukset ja replikointitunniste
Luku- ja analytiikkakuormien siirtäminen muualle
Suurten versiopäivitysten toteuttaminen lähes ilman käyttökatkoa
Replikointiviiveen ja slot-paisumisen seuranta

← Takaisin: PostgreSQL:n suorituskyky ja kyselyjen optimointi