DBA: korraldage asjatundlikult sünkroonimist ja importi

Suurte andmehulkade (erinevad ETL protsessid: importimine, teisendamine ja sünkroonimine välise allikaga) sageli on vajadus ajutiselt "meelde jätta" ja kohe kiiresti töödelda midagi mahukat.

Seda tüüpi tüüpiline ülesanne kõlab tavaliselt umbes nii: "Siin samas raamatupidamisosakond kliendipangast maha laaditud viimased laekunud maksed, peate need kiiresti veebisaidile üles laadima ja oma kontodega linkima.

Kuid kui selle “millegi” maht hakkab mõõtma sadades megabaitides ja teenus peab andmebaasiga 24x7 töötama, ilmnevad paljud kõrvalmõjud, mis rikuvad teie elu.

Nendega tegelemiseks PostgreSQL-is (ja mitte ainult selles) saate kasutada mõningaid optimeerimisi, mis võimaldavad teil kõike kiiremini ja väiksema ressursikuluga töödelda.

1. Kuhu saata?

Esmalt otsustame, kuhu saame üles laadida andmed, mida tahame töödelda.

1.1. Ajutised lauad (TEMPORARY TABLE)

Põhimõtteliselt on PostgreSQL-i ajutised tabelid samad, mis kõik teised. Seetõttu ebausk meeldib "Kõik seal salvestatakse ainult mällu ja see võib lõppeda". Kuid on ka mitmeid olulisi erinevusi.

Iga andmebaasiühenduse jaoks oma nimeruum

Kui kaks ühendust proovivad korraga ühendust luua CREATE TABLE x, siis keegi kindlasti saab mitteainulaadsuse viga andmebaasi objektid.

Aga kui mõlemad proovivad täita CREATE TEMPORARY TABLE x, siis teevad mõlemad seda normaalselt ja kõik saavad teie koopia tabelid. Ja nende vahel ei saa olema midagi ühist.

"Enesehävitamine" lahtiühendamisel

Ühenduse sulgemisel kustutatakse kõik ajutised tabelid automaatselt, seega käsitsi DROP TABLE x pole mõtet, välja arvatud...

Kui töötate läbi pgbouncer tehingurežiimis, siis andmebaas usub jätkuvalt, et see ühendus on endiselt aktiivne ja selles on see ajutine tabel endiselt olemas.

Seetõttu, kui proovite seda uuesti luua teisest ühendusest pgbouncerisse, ilmneb viga. Kuid seda saab kasutades mööda hiilida CREATE TEMPORARY TABLE IF NOT EXISTS x.

Tõsi, parem on seda siiski mitte teha, sest siis leiate sealt "äkitselt" "eelmiselt omanikult" alles jäänud andmed. Selle asemel on palju parem lugeda juhendit ja vaadata, et tabeli loomisel on võimalik lisada ON COMMIT DROP - see tähendab, et kui tehing on lõppenud, kustutatakse tabel automaatselt.

Mittepaljundamine

Kuna need kuuluvad ainult konkreetsele ühendusele, ei kopeerita ajutisi tabeleid. Aga see välistab vajaduse andmete topeltsalvestuseks hunnikus + WAL, nii et INSERT/UPDATE/DELETE on sellesse oluliselt kiirem.

Kuid kuna ajutine tabel on endiselt "peaaegu tavaline" tabel, ei saa seda luua ka koopial. Vähemalt praegu, kuigi vastav plaaster on juba pikemat aega ringelnud.

1.2. LOGIMATA LAUD

Aga mida peaksite tegema näiteks siis, kui teil on mingi tülikas ETL-protsess, mida ei saa ühe tehinguga rakendada, kuid teil on siiski pgbouncer tehingurežiimis? ..

Või on andmevoog nii suur, et Ühel ühendusel pole piisavalt ribalaiust andmebaasist (loe, üks protsess protsessori kohta)?..

Või on käimas mingid operatsioonid asünkroonselt erinevates ühendustes?..

Siin on ainult üks võimalus - loo ajutiselt mitteajutine tabel. Pun, jah. See on:

• lõi "oma" tabelid maksimaalselt juhuslike nimedega, et mitte kellegagi ristuda
• ekstrakt: täitis need välisest allikast pärit andmetega
• Muutma: teisendatud, täidetud võtmelinkimise väljad
• Koormus: valas valmis andmed sihttabelitesse
• kustutasin "minu" tabelid

Ja nüüd – kärbes salvis. Tegelikult, kõik PostgreSQL-i kirjutised juhtuvad kaks korda - esimene WAL-is, seejärel tabeli/indeksi kehadesse. Seda kõike tehakse ACID-i toetamiseks ja andmete nähtavuse parandamiseks COMMIT'pähkline ja ROLLBACKnulltehingud.

Kuid me ei vaja seda! Meil on kogu protsess Kas see õnnestus täielikult või mitte.. Vahetehingute arv pole oluline – me ei ole huvitatud "protsessi jätkamisest keskelt", eriti kui pole selge, kus see oli.

Selleks võtsid PostgreSQL-i arendajad juba versioonis 9.1 kasutusele sellise asja nagu LOGIMATA lauad:

Selle tähise korral luuakse tabel logimata. Logimata tabelitesse kirjutatud andmed ei läbi ettekirjutamise logi (vt peatükk 29), mistõttu sellised tabelid võivad töötab palju kiiremini kui tavaliselt. Siiski ei ole nad immuunsed ebaõnnestumise eest; serveri rikke või hädaseiskamise korral logimata tabel automaatselt kärbitud. Lisaks logimata tabeli sisu ei korrata oriserveritele. Kõik logimata tabelis loodud indeksid tühistatakse automaatselt.

Lühidalt öeldes see on palju kiirem, aga kui andmebaasiserver “kukkub”, on see ebameeldiv. Kuid kui sageli seda juhtub ja kas teie ETL-protsess teab, kuidas seda õigesti "keskelt" pärast andmebaasi "elustamist" parandada?

Kui mitte ja ülaltoodud juhtum on teie omaga sarnane, kasutage UNLOGGED, aga mitte kunagi ära luba seda atribuuti päris tabelites, mille andmed on teile kallid.

1.3. KOHUSTUSEL { KUSTUTA RID | DROP}

See konstruktsioon võimaldab määrata tabeli loomisel automaatse käitumise tehingu lõpuleviimisel.

edasi ON COMMIT DROP Ma juba eespool kirjutasin, see genereerib DROP TABLE, kuid koos ON COMMIT DELETE ROWS olukord on huvitavam - see genereeritakse siin TRUNCATE TABLE.

Kuna kogu ajutise tabeli metakirjelduse salvestamise infrastruktuur on täpselt sama, mis tavalisel tabelil, siis Pidev ajutiste tabelite loomine ja kustutamine toob kaasa süsteemitabelite tõsise "paisumise". pg_class, pg_attribute, pg_attrdef, pg_depend,…

Kujutage nüüd ette, et teil on andmebaasiga otseühenduses töötaja, kes avab iga sekundi järel uue tehingu, loob, täidab, töötleb ja kustutab ajutise tabeli... Süsteemitabelitesse koguneb liiga palju prügi ja see põhjustab iga toimingu jaoks lisapidureid.

Üldiselt ärge seda tehke! Sel juhul on see palju tõhusam CREATE TEMPORARY TABLE x ... ON COMMIT DELETE ROWS võta see tehingutsüklist välja - siis iga uue tehingu alguseks on tabelid juba olemas olema olemas (salvestage kõne CREATE), aga jääb tühjaks, tänu TRUNCATE (salvestasime ka selle kõne) eelmise tehingu sooritamisel.

1.4. MEELDIB...KAASA arvatud...

Mainisin alguses, et üks tüüpilisi ajutiste tabelite kasutusjuhtumeid on mitmesugused impordid - ja arendaja kopeerib väsinult sihttabeli väljade loendi oma ajutiste tabelite deklaratsiooni.

Aga laiskus on progressi mootor! Sellepärast loo uus tabel "näidise põhjal" see võib olla palju lihtsam:

CREATE TEMPORARY TABLE import_table(
  LIKE target_table
);

Kuna saate sellesse tabelisse genereerida palju andmeid, ei ole selle kaudu otsimine kunagi kiire. Kuid sellele on traditsiooniline lahendus - indeksid! Ja jah, ajutisel tabelis võib olla ka indekseid.

Kuna sageli langevad nõutavad indeksid kokku sihttabeli indeksitega, saate lihtsalt kirjutada LIKE target_table INCLUDING INDEXES.

Kui ka vaja DEFAULT-väärtusi (näiteks primaarvõtme väärtuste täitmiseks), saate kasutada LIKE target_table INCLUDING DEFAULTS. Või lihtsalt - LIKE target_table INCLUDING ALL — kopeerib vaikeseaded, indeksid, piirangud,...

Kuid siin peate mõistma, et kui lõite importida tabel kohe koos indeksitega, siis võtab andmete laadimine kauem aegakui siis, kui täidate kõigepealt kõik ja alles seejärel kerite indeksid kokku – vaadake näitena, kuidas see seda teeb pg_dump.

Üldiselt RTFM!

2. Kuidas kirjutada?

Lubage mul lihtsalt öelda – kasutage seda COPY-vool "pakki" asemel INSERT, kohati kiirendus. Saate isegi otse eelgenereeritud failist.

3. Kuidas töödelda?

Niisiis, laseme meie sissejuhatusel välja näha umbes selline:

• teil on andmebaasis salvestatud tabel kliendiandmetega 1M rekordit
• iga päev saadab klient sulle uue täielik "pilt"
• kogemusest tead seda aeg-ajalt ei muudeta rohkem kui 10 XNUMX kirjet

Klassikaline näide sellisest olukorrast on KLADR alus — aadresse on kokku palju, kuid iganädalases üleslaadimises on muudatusi (asulate ümbernimetamine, tänavate ühendamine, uute majade tekkimine) väga vähe isegi riigi mastaabis.

3.1. Täielik sünkroonimisalgoritm

Lihtsuse huvides oletame, et te ei pea isegi andmeid ümber struktureerima – lihtsalt viige tabel soovitud kujule, see tähendab:

• eemaldada kõike, mida enam ei eksisteeri
• uuendada kõik, mis oli juba olemas ja vajab uuendamist
• sisesta kõike, mida pole veel juhtunud

Miks peaks toiminguid tegema just selles järjekorras? Sest nii kasvab laua suurus minimaalselt (pidage meeles MVCC-d!).

KUSTUTA dst

Ei, muidugi saate hakkama vaid kahe toiminguga:

• eemaldada (DELETE) kõike üldiselt
• sisesta kõik uuest pildist

Kuid samal ajal tänu MVCC-le Tabeli suurus suureneb täpselt kaks korda! 1K värskenduse tõttu +10M kirjete kujutiste saamine tabelisse on nii-nii üleliigne...

TRUNCATE dst

Kogenum arendaja teab, et terve tahvelarvuti saab üsna soodsalt puhtaks:

• selge (TRUNCATE) kogu tabel
• sisesta kõik uuest pildist

Meetod on tõhus, mõnikord üsna kohaldatav, aga on probleem... Lisame veel kaua aega 1M kirjet, nii et me ei saa endale lubada kogu selle aja tabeli tühjaks jätmist (nagu juhtub ilma ühe tehinguga pakkimata).

Mis tähendab:

• me alustame pikaajaline tehing
• TRUNCATE kehtestab Eksklusiivne juurdepääs- blokeerimine
• me teeme sisestamist pikka aega ja kõik teised sel ajal isegi ei saa SELECT

Midagi ei lähe hästi...

MUUDA TABELIT… NIMETAMINE ÜMBER… / KÜLITA TABEL…

Teine võimalus on täita kõik eraldi uude tabelisse ja seejärel lihtsalt vana asemele nimetada. Paar vastikut pisiasja:

• ikka ka Eksklusiivne juurdepääs, kuigi oluliselt vähem aega
• kõik selle tabeli päringuplaanid/statistika lähtestatakse, tuleb käivitada ANALÜÜS
• kõik võõrvõtmed on katki (FK) lauale

Seal oli Simon Riggsi WIP-plaaster, mis soovitas teha ALTER-toiming tabeli keha asendamiseks faili tasemel, puudutamata statistikat ja FK-d, kuid ei kogunud kvoorumit.

KUSTUTAMINE, VÄRSKENDAMINE, LISAMINE

Seega otsustame kolme toimingu mitteblokeerimise võimalusega. Peaaegu kolm... Kuidas seda kõige tõhusamalt teha?

-- все делаем в рамках транзакции, чтобы никто не видел "промежуточных" состояний
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. Impordi järeltöötlus

Samas KLADRis tuleb kõik muudetud kirjed täiendavalt läbida järeltöötluse – normaliseerida, märksõnad esile tõsta ja taandada vajalikele struktuuridele. Aga kuidas sa tead - mis täpselt muutusilma sünkroonimiskoodi keerulisemaks muutmata, ideaalis ilma seda üldse puudutamata?

Kui ainult teie protsessil on sünkroonimise ajal kirjutamisõigus, saate kasutada päästikut, mis kogub meie eest kõik muudatused:

-- целевые таблицы
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;

Nüüd saame enne sünkroonimise alustamist rakendada päästikuid (või lubada need kaudu 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 siis võtame logitabelitest rahulikult välja kõik vajalikud muudatused ja laseme need läbi täiendavate töötlejate.

3.3. Lingitud komplektide importimine

Eespool käsitlesime juhtumeid, kui allika ja sihtkoha andmestruktuurid on samad. Aga mis siis, kui välisest süsteemist üleslaadimise vorming erineb meie andmebaasi salvestusstruktuurist?

Võtame näiteks klientide ja nende kontode salvestuse, klassikalise "mitu-ühele" valiku:

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

Kuid välisest allikast allalaadimine jõuab meile vormingus "kõik ühes":

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

Ilmselgelt saab selles versioonis kliendiandmeid dubleerida ja põhikirje on “konto”:

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

Mudeli jaoks sisestame lihtsalt oma testiandmed, kuid pidage meeles - COPY tõhusam!

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

Esiteks tõstame esile need "kärped", millele meie "faktid" viitavad. Meie puhul viitavad arved klientidele:

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

Kontode korrektseks sidumiseks kliendi ID-dega peame esmalt need identifikaatorid välja selgitama või genereerima. Lisame nende alla väljad:

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

Kasutame ülalkirjeldatud tabeli sünkroonimismeetodit väikese muudatusega - me ei värskenda ega kustuta sihttabelis midagi, kuna impordime kliente "ainult lisades":

-- проставляем в таблице импорта 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; -- прикладной ключ

Tegelikult on kõik sees invoice_import Nüüd on meil kontaktiväli täidetud client_id, millega sisestame arve.

Allikas: www.habr.com