DBA: järjestä synkronoinnit ja tuonnit asiantuntevasti

Suurten tietojoukkojen monimutkaiseen käsittelyyn (erilainen ETL-prosessit: tuonti, muunnokset ja synkronointi ulkoisen lähteen kanssa) usein on tarvetta väliaikaisesti "muistaa" ja käsitellä välittömästi nopeasti jotain tilavaa.

Tyypillinen tällainen tehtävä kuulostaa yleensä tältä: "Juuri täällä kirjanpito purku asiakaspankista viimeisimmät vastaanotetut maksut, sinun on ladattava ne nopeasti verkkosivustolle ja linkitettävä ne tiliisi"

Mutta kun tämän "jonkin" tilavuus alkaa mitata satoja megatavuja ja palvelun on jatkettava toimintaansa tietokannan kanssa 24x7, syntyy monia sivuvaikutuksia, jotka pilaavat elämäsi.

Niiden käsittelemiseksi PostgreSQL:ssä (eikä vain siinä) voit käyttää joitain optimointeja, joiden avulla voit käsitellä kaiken nopeammin ja vähemmän resursseja kuluttamalla.

1. Minne lähettää?

Päätetään ensin, minne voimme ladata tiedot, jotka haluamme "käsitellä".

1.1. Väliaikaiset pöydät (TEMPORARY TABLE)

Periaatteessa PostgreSQL:n väliaikaiset taulukot ovat samat kuin muutkin. Siksi taikausko kuten "Kaikki on tallennettu vain muistiin ja se voi päättyä". Mutta on myös useita merkittäviä eroja.

Oma "nimiavaruus" jokaiselle tietokantayhteydelle

Jos kaksi yhteyttä yrittää muodostaa yhteyden samanaikaisesti CREATE TABLE x, niin joku varmasti saa ei-ainutlaatuisuusvirhe tietokantaobjekteja.

Mutta jos molemmat yrittävät toteuttaa CREATE TEMPORARY TABLE x, niin molemmat tekevät sen normaalisti, ja kaikki saavat sinun kopiosi taulukoita. Eikä niiden välillä tule olemaan mitään yhteistä.

"Itsetuhoaminen" yhteyden katketessa

Kun yhteys suljetaan, kaikki väliaikaiset taulukot poistetaan automaattisesti, joten manuaalisesti DROP TABLE x ei ole mitään järkeä paitsi...

Jos työskentelet läpi pgbouncer tapahtumatilassa, silloin tietokanta uskoo edelleen, että tämä yhteys on edelleen aktiivinen, ja siinä tämä väliaikainen taulukko on edelleen olemassa.

Siksi sen luominen uudelleen eri yhteydestä pgbounceriin johtaa virheeseen. Mutta tämä voidaan kiertää käyttämällä CREATE TEMPORARY TABLE IF NOT EXISTS x.

Totta, on parempi olla tekemättä tätä joka tapauksessa, koska silloin voit "yhtäkkiä" löytää sieltä "edelliseltä omistajalta" jäljellä olevat tiedot. Sen sijaan on paljon parempi lukea käsikirja ja nähdä, että taulukkoa luotaessa on mahdollista lisätä ON COMMIT DROP - eli kun tapahtuma on valmis, taulukko poistetaan automaattisesti.

Replikoimattomuus

Koska ne kuuluvat vain tiettyyn yhteyteen, väliaikaisia ​​taulukoita ei replikoida. Mutta tämä poistaa tarpeen tallentaa tietoja kahteen kertaan kasossa + WAL, joten INSERT/UPDATE/DELETE siihen on paljon nopeampi.

Mutta koska väliaikainen taulukko on edelleen "melkein tavallinen" taulukko, sitä ei voi luoda myöskään replikalle. Ainakin toistaiseksi, vaikka vastaava laastari on ollut liikkeellä jo pitkään.

1.2. PÖYTÄ KIRJOITTAMAAN

Mutta mitä sinun pitäisi tehdä esimerkiksi, jos sinulla on jonkinlainen hankala ETL-prosessi, jota ei voida toteuttaa yhdessä tapahtumassa, mutta sinulla on silti pgbouncer tapahtumatilassa? ..

Tai tietovirta on niin suuri Yhdellä yhteydellä ei ole tarpeeksi kaistanleveyttä tietokannasta (lue, yksi prosessi prosessoria kohden)?..

Tai joitain operaatioita on meneillään asynkronisesti eri yhteyksissä?...

Tässä on vain yksi vaihtoehto - luoda väliaikaisesti ei-väliaikainen taulukko. Pun, joo. Tuo on:

• loin "omat" taulukot mahdollisimman satunnaisilla nimillä, jotta ne eivät leikkaa kenenkään kanssa
• Ote: täytti ne tiedoilla ulkoisesta lähteestä
• Muuttaa: muunnettu, täytetty avainlinkityskentät
• Ladata: kaatoi valmiit tiedot kohdetaulukoihin
• poisti "omani" taulukot

Ja nyt - kärpänen voidessa. Itse asiassa, kaikki PostgreSQL:n kirjoitukset tapahtuvat kahdesti - ensin WALissa, sitten taulukon/hakemiston runkoon. Kaikki tämä tehdään ACID:n tukemiseksi ja tietojen oikeaksi näkyvyyden välillä COMMIT'pähkinä ja ROLLBACK"tyhjät liiketoimet.

Mutta me emme tarvitse tätä! Meillä on koko prosessi Joko se onnistui täysin tai sitten ei.. Ei ole väliä kuinka monta välitapahtumaa tulee - emme ole kiinnostuneita "prosessin jatkamisesta keskeltä", varsinkaan kun ei ole selvää, missä se oli.

Tätä varten PostgreSQL-kehittäjät esittelivät versiossa 9.1 sellaisen asian kuin KIRJOITTAMATTOMAT pöydät:

Tällä ilmauksella taulukko luodaan kirjautumattomaksi. Kirjaamattomiin taulukoihin kirjoitetut tiedot eivät kulje eteenpäinkirjoituslokin läpi (katso luku 29), jolloin tällaiset taulukot toimii paljon tavallista nopeammin. Ne eivät kuitenkaan ole immuuneja epäonnistumiselle; palvelinvian tai hätäpysäytystilanteessa kirjautumaton taulukko automaattisesti katkaistu. Lisäksi kirjautumattoman taulukon sisältö ei kopioitu orjapalvelimille. Kaikki kirjaamattomaan taulukkoon luodut indeksit poistetaan automaattisesti.

Lyhyesti sanottuna, se on paljon nopeampi, mutta jos tietokantapalvelin "putoaa", se on epämiellyttävää. Mutta kuinka usein näin tapahtuu, ja osaako ETL-prosessisi korjata tämän oikein ”keskeltä” tietokannan ”elvytyksen” jälkeen?

Jos ei, ja yllä oleva tapaus on samanlainen kuin sinun, käytä UNLOGGEDmutta ei koskaan älä ota tätä attribuuttia käyttöön oikeissa taulukoissa, jonka tiedot ovat sinulle tärkeitä.

1.3. KÄYTÖSSÄ { POISTA RIVIT | PUDOTA}

Tämän rakenteen avulla voit määrittää automaattisen toiminnan, kun tapahtuma on suoritettu taulukkoa luotaessa.

Про ON COMMIT DROP Kirjoitin jo yllä, se tuottaa DROP TABLE, mutta kanssa ON COMMIT DELETE ROWS tilanne on mielenkiintoisempi - se syntyy täällä TRUNCATE TABLE.

Koska koko infrastruktuuri väliaikaisen taulukon metakuvauksen tallentamiseen on täsmälleen sama kuin tavallisen taulukon, Jatkuva väliaikaisten taulukoiden luominen ja poistaminen johtaa järjestelmätaulukoiden vakavaan "turvotukseen". pg_class, pg_attribute, pg_attrdef, pg_depend,…

Kuvittele nyt, että sinulla on työntekijä suorassa yhteydessä tietokantaan, joka avaa uuden tapahtuman joka sekunti, luo, täyttää, käsittelee ja poistaa väliaikaisen taulukon... Järjestelmätaulukoihin kertyy liikaa roskaa ja tämä aiheuttaa ylimääräisiä jarruja jokaisessa toiminnossa.

Yleensä älä tee tätä! Tässä tapauksessa se on paljon tehokkaampi CREATE TEMPORARY TABLE x ... ON COMMIT DELETE ROWS ota se pois tapahtumasyklistä - silloin jokaisen uuden tapahtuman alussa taulukot ovat jo tulee olemaan olemassa (tallenna puhelu CREATE), mutta tulee olemaan tyhjä, kiitokset TRUNCATE (tallentimme myös sen puhelun) edellisen tapahtuman suorittamisen yhteydessä.

1.4. TYKKÄÄ... MUKAAN MUKAAN...

Mainitsin alussa, että eräs tyypillisistä tilapäisten taulukoiden käyttötapauksista on erilaiset tuonnit - ja kehittäjä kopioi väsyneenä kohdetaulukon kenttien listan tilapäisen...

Mutta laiskuus on edistyksen moottori! Siksi luo uusi taulukko "näytteen perusteella" se voi olla paljon yksinkertaisempaa:

CREATE TEMPORARY TABLE import_table(
  LIKE target_table
);

Koska voit sitten luoda paljon dataa tähän taulukkoon, sen läpi hakeminen ei koskaan ole nopeaa. Mutta tähän on perinteinen ratkaisu - indeksit! Ja kyllä, väliaikaisessa taulukossa voi olla myös indeksejä.

Koska vaaditut indeksit ovat usein samat kuin kohdetaulukon indeksit, voit yksinkertaisesti kirjoittaa LIKE target_table INCLUDING INDEXES.

Jos myös tarvitset DEFAULT-arvoja (esimerkiksi ensisijaisen avaimen arvojen täyttämiseen), voit käyttää LIKE target_table INCLUDING DEFAULTS. Tai yksinkertaisesti- LIKE target_table INCLUDING ALL - kopioi oletusasetukset, indeksit, rajoitukset,...

Mutta tässä sinun on ymmärrettävä se, jos loit Tuo taulukko heti indekseillä, niin tietojen lataaminen kestää kauemminkuin jos täytät ensin kaiken ja vasta sitten rullaat indeksit - katso esimerkkinä, kuinka se tekee tämän pg_dump.

Kaiken RTFM!

2. Kuinka kirjoittaa?

Sanon vain - käytä sitä COPY-virtaus "pakkauksen" sijaan INSERT, välillä kiihdytystä. Voit jopa suoraan esiluodusta tiedostosta.

3. Kuinka käsitellä?

Joten, annetaan esittelymme näyttää tältä:

• sinulla on tietokantaasi tallennettu taulukko asiakastiedoista 1M ennätystä
• joka päivä asiakas lähettää sinulle uuden koko "kuva"
• kokemuksesta tiedät sen silloin tällöin enintään 10 XNUMX tietuetta muutetaan

Klassinen esimerkki tällaisesta tilanteesta on KLADR pohja — osoitteita on kaikkiaan paljon, mutta jokaisessa viikoittaisessa latauksessa muutoksia (asuntojen uudelleennimeäminen, katujen yhdistäminen, uusien talojen ilmaantuminen) on valtakunnallisestikin vähän.

3.1. Täysi synkronointialgoritmi

Yksinkertaisuuden vuoksi sanotaan, että sinun ei tarvitse edes järjestellä tietoja uudelleen - tuo vain taulukko haluttuun muotoon, eli:

• poistaa kaikkea mitä ei enää ole
• päivitys kaikki, mikä on jo olemassa ja pitää päivittää
• lisätä kaikkea mitä ei ole vielä tapahtunut

Miksi toimenpiteet pitäisi tehdä tässä järjestyksessä? Koska näin pöydän koko kasvaa minimaalisesti (muista MVCC!).

POISTA dst

Ei, tietysti selviät kahdella toimenpiteellä:

• poistaa (DELETE) kaiken kaikkiaan
• lisätä kaikki uudesta kuvasta

Mutta samalla MVCC:n ansiosta Pöydän koko kasvaa tasan kaksinkertaiseksi! +1 miljoonan kuvan saaminen tietueista taulukkoon 10 XNUMX päivityksen vuoksi on niin ylimääräistä...

TRUNCATE dst

Kokeneempi kehittäjä tietää, että koko tabletti voidaan puhdistaa melko edullisesti:

• puhdas (TRUNCATE) koko pöytä
• lisätä kaikki uudesta kuvasta

Menetelmä on tehokas, joskus aivan soveltuva, mutta on ongelma... Lisäämme 1 miljoonaa tietuetta pitkään, joten meillä ei ole varaa jättää taulukkoa tyhjäksi koko ajan (kuten tapahtuu ilman, että se kääritään yhteen tapahtumaan).

Joka tarkoittaa:

• aloitamme pitkäaikainen kauppa
• TRUNCATE määrää Access Exclusive- esto
• teemme lisäyksen pitkään, ja kaikki muutkin tällä hetkellä ei voi edes SELECT

Jokin ei mene hyvin...

ALTER TABLE… NIMEÄ UUDELLEEN… / PUDOTA PÖYTÄ…

Vaihtoehtona on täyttää kaikki erilliseen uuteen taulukkoon ja nimetä se sitten uudelleen vanhan tilalle. Pari ikävää pientä asiaa:

• vieläkin Access Exclusivetosin huomattavasti vähemmän aikaa
• kaikki tämän taulukon kyselysuunnitelmat/tilastot nollataan, täytyy suorittaa ANALYZE
• kaikki vierasavaimet ovat rikki (FK) pöytään

Siellä oli Simon Riggsin WIP-korjaus, joka ehdotti tekemistä ALTER-toiminto taulukon rungon korvaamiseksi tiedostotasolla koskematta tilastoihin ja FK:han, mutta ei kerännyt päätösvaltaisuutta.

POISTA, PÄIVITYS, LISÄÄ

Päädymme siis kolmen toiminnon ei-esto-vaihtoehtoon. Melkein kolme... Kuinka tehdä tämä tehokkaimmin?

-- все делаем в рамках транзакции, чтобы никто не видел "промежуточных" состояний
BEGIN;

-- создаем временную таблицу с импортируемыми данными
CREATE TEMPORARY TABLE tmp(
  LIKE dst INCLUDING INDEXES -- по образу и подобию, вместе с индексами
) ON COMMIT DROP; -- за рамками транзакции она нам не нужна

-- быстро-быстро вливаем новый образ через COPY
COPY tmp FROM STDIN;
-- ...
-- .

-- удаляем отсутствующие
DELETE FROM
  dst D
USING
  dst X
LEFT JOIN
  tmp Y
    USING(pk1, pk2) -- поля первичного ключа
WHERE
  (D.pk1, D.pk2) = (X.pk1, X.pk2) AND
  Y IS NOT DISTINCT FROM NULL; -- "антиджойн"

-- обновляем оставшиеся
UPDATE
  dst D
SET
  (f1, f2, f3) = (T.f1, T.f2, T.f3)
FROM
  tmp T
WHERE
  (D.pk1, D.pk2) = (T.pk1, T.pk2) AND
  (D.f1, D.f2, D.f3) IS DISTINCT FROM (T.f1, T.f2, T.f3); -- незачем обновлять совпадающие

-- вставляем отсутствующие
INSERT INTO
  dst
SELECT
  T.*
FROM
  tmp T
LEFT JOIN
  dst D
    USING(pk1, pk2)
WHERE
  D IS NOT DISTINCT FROM NULL;

COMMIT;

3.2. Tuo jälkikäsittely

Samassa KLADR:ssä kaikki muuttuneet tietueet on lisäksi suoritettava jälkikäsittelyn läpi - normalisoitava, korostettava avainsanat ja pienennettävä vaadittuihin rakenteisiin. Mutta mistä sinä tiedät - mikä tarkalleen muuttuimutkistamatta synkronointikoodia, mieluiten koskematta siihen ollenkaan?

Jos vain prosessillasi on kirjoitusoikeus synkronoinnin aikana, voit käyttää triggeriä, joka kerää kaikki muutokset puolestamme:

-- целевые таблицы
CREATE TABLE kladr(...);
CREATE TABLE kladr_house(...);

-- таблицы с историей изменений
CREATE TABLE kladr$log(
  ro kladr, -- тут лежат целые образы записей старой/новой
  rn kladr
);

CREATE TABLE kladr_house$log(
  ro kladr_house,
  rn kladr_house
);

-- общая функция логирования изменений
CREATE OR REPLACE FUNCTION diff$log() RETURNS trigger AS $$
DECLARE
  dst varchar = TG_TABLE_NAME || '$log';
  stmt text = '';
BEGIN
  -- проверяем необходимость логгирования при обновлении записи
  IF TG_OP = 'UPDATE' THEN
    IF NEW IS NOT DISTINCT FROM OLD THEN
      RETURN NEW;
    END IF;
  END IF;
  -- создаем запись лога
  stmt = 'INSERT INTO ' || dst::text || '(ro,rn)VALUES(';
  CASE TG_OP
    WHEN 'INSERT' THEN
      EXECUTE stmt || 'NULL,$1)' USING NEW;
    WHEN 'UPDATE' THEN
      EXECUTE stmt || '$1,$2)' USING OLD, NEW;
    WHEN 'DELETE' THEN
      EXECUTE stmt || '$1,NULL)' USING OLD;
  END CASE;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Nyt voimme käyttää laukaisimia ennen synkronoinnin aloittamista (tai ottaa ne käyttöön ALTER TABLE ... ENABLE TRIGGER ...):

CREATE TRIGGER log
  AFTER INSERT OR UPDATE OR DELETE
  ON kladr
    FOR EACH ROW
      EXECUTE PROCEDURE diff$log();

CREATE TRIGGER log
  AFTER INSERT OR UPDATE OR DELETE
  ON kladr_house
    FOR EACH ROW
      EXECUTE PROCEDURE diff$log();

Ja sitten poimimme rauhallisesti lokitaulukoista kaikki tarvitsemamme muutokset ja suoritamme ne lisäkäsittelijöiden kautta.

3.3. Linkitettyjen sarjojen tuonti

Yllä tarkasteltiin tapauksia, joissa lähteen ja kohteen tietorakenteet ovat samat. Mutta entä jos ulkoisesta järjestelmästä ladatun tiedoston muoto eroaa tietokannassamme olevasta tallennusrakenteesta?

Otetaan esimerkkinä asiakkaiden ja heidän tiliensä tallennus, klassinen "monet yhteen" -vaihtoehto:

CREATE TABLE client(
  client_id
    serial
      PRIMARY KEY
, inn
    varchar
      UNIQUE
, name
    varchar
);

CREATE TABLE invoice(
  invoice_id
    serial
      PRIMARY KEY
, client_id
    integer
      REFERENCES client(client_id)
, number
    varchar
, dt
    date
, sum
    numeric(32,2)
);

Mutta lataus ulkoisesta lähteestä tulee meille "kaikki yhdessä" muodossa:

CREATE TEMPORARY TABLE invoice_import(
  client_inn
    varchar
, client_name
    varchar
, invoice_number
    varchar
, invoice_dt
    date
, invoice_sum
    numeric(32,2)
);

Ilmeisesti asiakastiedot voidaan kopioida tässä versiossa, ja päätietue on "tili":

0123456789;Вася;A-01;2020-03-16;1000.00
9876543210;Петя;A-02;2020-03-16;666.00
0123456789;Вася;B-03;2020-03-16;9999.00

Mallia varten lisäämme vain testitietomme, mutta muista - COPY tehokkaampi!

INSERT INTO invoice_import
VALUES
  ('0123456789', 'Вася', 'A-01', '2020-03-16', 1000.00)
, ('9876543210', 'Петя', 'A-02', '2020-03-16', 666.00)
, ('0123456789', 'Вася', 'B-03', '2020-03-16', 9999.00);

Ensin korostetaan niitä "leikkauksia", joihin "faktamme" viittaavat. Meidän tapauksessamme laskut koskevat asiakkaita:

CREATE TEMPORARY TABLE client_import AS
SELECT DISTINCT ON(client_inn)
-- можно просто SELECT DISTINCT, если данные заведомо непротиворечивы
  client_inn inn
, client_name "name"
FROM
  invoice_import;

Jotta voimme liittää tilit oikein asiakastunnuksiin, meidän on ensin selvitettävä tai luotava nämä tunnisteet. Lisätään kentät niiden alle:

ALTER TABLE invoice_import ADD COLUMN client_id integer;
ALTER TABLE client_import ADD COLUMN client_id integer;

Käytetään yllä kuvattua taulukon synkronointimenetelmää pienellä muutoksella - emme päivitä tai poista mitään kohdetaulukosta, koska tuomme asiakkaita "vain liittäen":

-- проставляем в таблице импорта ID уже существующих записей
UPDATE
  client_import T
SET
  client_id = D.client_id
FROM
  client D
WHERE
  T.inn = D.inn; -- unique key

-- вставляем отсутствовавшие записи и проставляем их ID
WITH ins AS (
  INSERT INTO client(
    inn
  , name
  )
  SELECT
    inn
  , name
  FROM
    client_import
  WHERE
    client_id IS NULL -- если ID не проставился
  RETURNING *
)
UPDATE
  client_import T
SET
  client_id = D.client_id
FROM
  ins D
WHERE
  T.inn = D.inn; -- unique key

-- проставляем ID клиентов у записей счетов
UPDATE
  invoice_import T
SET
  client_id = D.client_id
FROM
  client_import D
WHERE
  T.client_inn = D.inn; -- прикладной ключ

Itse asiassa kaikki on sisällä invoice_import Yhteydenottokenttä on nyt täytetty client_id, jolla lisäämme laskun.

Lähde: will.com