DBA: korraldame sünkroniseerimisi ja importimist oskuslikult

Kui keerukalt töötleda suuri andmekogusid (erinevad ETL-protsessid: impordid, konverteerimised ja sünkroonimised välise allikaga) tekib sageli vajadus ajaliselt "mäluda" ja korraga kiiresti töödelda mõnda suurust.

Selline tüüpiline ülesanne kõlab tavaliselt nii: "Siin on raamatupidamine eksportinud kliendipangast viimased laekunud maksed, tuleb need kiiresti veebilehele laadida ja siduda kontodega"

Kuid kui selle "millegi" maht hakkab mõõtma sadu megabait, samas kui teenus peab töötama andmebaasiga režiimis 24×7, tekib palju kõrvalmõjusid, mis hakkavad teie elu halvendama.

Nende vastu võitlemiseks PostgreSQL-is (ja mitte ainult seal) saab kasutada teatavaid optsioone optimeerimiseks, mis võimaldavad kõike töötleda kiiremini ja väiksema ressursikulu.

1. Kuhu laadida?

Esmalt laske meil mõista, kuhu me saame laadida andmed, mida soovime "töödeldaks".

1.1. Ajutised tabelid (TEMPORARY TABLE)

Põhimõtteliselt on ajutised tabelid PostgreSQL-is samasugused tabelid nagu kõik teised. Seetõttu ei ole vale uskumine, et "seal hoitakse kõike ainult mälus ja see võib otsa saada". Kuid on ka mitmeid olulisi erinevusi.

Igal DB ühendusel on oma "nimetamise ruum"

Kui kaks ühendust üritavad samal ajal käivitada CREATE TABLE x, siis keegi saab kindlasti andmebaasi objektide ainulaadsuse viga. Aga kui mõlemad üritavad käivitada

, siis mõlemad teevad seda normaalselt ja igaüks saab CREATE AJUTINE TABEL xoma eksemplari tabelist. Ja neil ei ole üksteisega midagi ühist. "Enesehävitamine" sulgemisel

Ühenduse sulgemisel kustutatakse kõik ajutised tabelid automaatselt, seega pole "käsi" teostada

DROP TABLE x mingit mõtet, välja arvatud... Kui töötate

pgbouncer'i tehingurežiimis , siis andmebaas arvab endiselt, et see ühendus on endiselt aktiivne, ja selles eksisteerib see ajutine tabel endiselt.Seega toob püüe seda uuesti luua, juba teise ühenduse kaudu pgbouncerile, kaasa vea. Kuid seda saab vältida, kasutades

Kuid parem on seda ikkagi mitte teha, sest siis võite "ootamatult" avastada seal "eelmistest omanikest" jääne andmed. Selle asemel on palju parem lugeda juhendit ja näha, et tabeli loomisel on võimalus juurde kirjutada LOO TEMPORARY TABLE KUI EI OLE x.

Tõepoolest, parem on seda mitte teha, kuna siis võib "ühtäkki" avastada seal endiselt olemasolevaid andmeid "eelmiselt omanikult". Selle asemel on palju parem lugeda manuaali ja näha, et tabeli loomisel on võimalus täiendavaid andmeid sisestada. REKISTERIMISES DROP — see, when the transaction is completed, the table will be automatically deleted.

Non-replication

Due to being assigned to a specific connection only, temporary tables are not replicated. However, this eliminates the need for double data writing in heap + WAL, hence INSERT/UPDATE/DELETE into it is significantly faster.

But since a temporary table is still "almost a regular" table, it can't be created on a replica either. At least, not yet, although the corresponding patch has been around for a long time.

1.2. Unlogged tables (UNLOGGED TABLE)

But what if, for example, you have some bulky ETL process that can't be implemented within a single transaction, and you indeed , siis andmebaas arvab endiselt, et see ühendus on endiselt aktiivne, ja selles eksisteerib see ajutine tabel endiselt.?..

Or the data stream is so large that the bandwidth of a single connection to the DB (read, one process on the CPU)?..

Or part of the operations go asynchronously in different connections?..

In this case, there is only one option — temporarily create a non-temporary table. A pun, indeed. That is:

• created "my" tables with maximally-random names to avoid any overlap
• Extract: loaded data from an external source into them
• Transform: transformed, filled key linking fields
• Laadi: poured the prepared data into target tables
• deleted "my" tables

And now — a teaspoon of tar. Essentially, all writing in PostgreSQL happens twice — first in the WAL, then in the bodies of tables/indices. All this is done to support ACID and correct data visibility between COMMITinner and ROLLBACKouter transactions.

But we don’t need this! Our entire process must either have completely succeeded or not at all.It doesn’t matter how many intermediate transactions there will be — we are not interested in "continuing the process from the middle", especially when it’s not clear where it was.

For this, PostgreSQL developers introduced a feature as early as version 9.1 called unlogged (UNLOGGED) tables.:

With this indication, the table is created as unlogged. Data written to unlogged tables does not go through the write-ahead log (see Chapter 29), resulting in such tables working much faster than regular ones.However, they are not crash-safe; in case of a crash or unexpected server shutdown, the unlogged table is automatically truncated.Moreover, the content of an unlogged table is not replicated. juhitud serverite jaoks. Iga indeksi, mis luuakse logimata tabeli jaoks, muutub automaatselt logimata.

Lühidalt, on see oluliselt kiirem, kuid kui andmebaasi server "kukkub", on see ebameeldiv. Kuid kui sageli see juhtub ja kas teie ETL-protsess suudab seda õigesti "keskelt" taastada pärast andmebaasi "taaselustamist"?..

Kui ei, ja eespool mainitud olukord sarnaneb teie omaga — kasutage UNLOGGED, kuid kunagi ärge lülitage seda atribuuti sisse reaalsetele tabelitele, mille andmed on teile olulised.

1.3. ON COMMIT { KUSTUTA RIDA | KUSTUTA }

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

Küsimus REKISTERIMISES DROP nagu ma juba eespool mainisin, genereerib see DROP TABLE, kuid siin on REKISTERIMISES KUSTUTA VREAD olukord huvitavam — siin genereeritakse TRUNCATE TABLE.

Kuna ajutise tabeli metaandmete salvestus infrastruktuur on sama, mis tavalistel, siis ajutiste tabelite pidev loomine-kustutamine toob kaasa süsteemsete tabelite tugeva "paisumise" pg_class, pg_attribute, pg_attrdef, pg_depend,…

Kujutage nüüd ette, et teil on töötaja, kes on otseühenduses andmebaasiga, ja avab igal sekundil uue tehingu, loob, täidab, töötleb ja kustutab ajutise tabeli… Süsteemsetesse tabelitesse koguneb liiga palju prügi, mis toob kaasa tarbetud lagged igas operatsioonis.

Üldiselt, ärge tehke nii! Sel juhul on palju tõhusam LOO AJUTINE TABEL x ... ON COMMIT KUSTUTA RIDA toimetada tehingu tsüklist välja — siis on iga uue tehingu alguseks tabelid juba olemas (säästame kutset CREATE), kuid olevad tühjad, tänu TRUNCATE (me säästsime ka selle kutse) eelmise tehingu lõpetamisel.

1.4. LIKE… SISALDAB …

Mainisin alguses, et üks tüüpiline ajutiste tabelite kasutusjuht on erinevad impordid — ja arendaja kopeerib väsinult sihttabeli väljade nimekirja oma ajutise kuulutusse…

Kuid laiskus on progressi mootor! Seetõttu uue tabeli loomine „mudeleid pidi“ on palju lihtsam:

LOO AJUTINE TABEL import_table(
  LIKE target_table
);

Kuna sellesse tabelisse on võimalik hiljem genereerida palju andmeid, siis otsingud selles muutuvad äärmiselt aeglaseks. Kuid selle vastu on traditsiooniline lahendus — indeksid! Ja jah, ajutistel tabelitel võivad samuti olla indeksid.

Kuna sageli jäävad vajalikud indeksid kokku sihtpärandi tabeli indeksitega, võib lihtsalt kirjutada NAGU target_table KAASAM NÄIDIKUD.

Kui vajate veel ka DEFAULT-väärtusi (näiteks primaarsete võtmete väärtuste täitmiseks), saab kasutada NAGU target_table KAASIED SÄHRIT. Noh, või lihtsalt — NAGU target_table KAAS KÕIK — kopeerib vaikeseaded, indeksid, piirangud,…

Aga siin tuleb juba aru saada, et kui olete loonud impordi-tabeli kohe indeksitega, siis andmete üleslaadimine kestab kauem, kui kui kõigepealt kogu sisu üles laadida ja alles seejärel indeksid rakendada — vaadake näiteks, kuidas seda teeb pg_dump.

Ühesõnaga, RTFM!

2. Kuidas kirjutada?

Ütlen lihtsalt — kasutage COPY-voogu asemel „partiid“ INSERT, kiirus mitu korda. Võite isegi otse eelnevalt vormistatud failist.

3. Kuidas töödelda?

Oletame, et meie sisend näeb välja umbes nii:

• teil on andmebaasis tabel kliendiandmetega 1M kirjet
• iga päev saadab klient teile uue täieliku „pildi“
• kogenud inimesed teavad, et iga kord muutub vaid 10K kirjet

Klassikaline näide taolisest olukorrast on KLDAR andmebaas — aadresse on palju, kuid iganädalasel andmevoolul on muutusi (asulate nimede muutmine, tänavate ühendamine, uute majade ilmumine) vähe isegi kogu riigi ulatuses.

3.1. Täieliku sünkroonimise algoritm

Lihtsuse huvides oletame, et andmete struktureerimist pole vaja — lihtsalt viige tabel soovitud vormi, st:

• kustutada kõik, mida enam ei ole
• uuendada kõik, mis oli, ja tuleb uuendada
• sisestada kõik, mida polnud veel

Miks tuleb neid toiminguid täpselt niimoodi teha? Sest just nii kasvab tabeli suurus minimaalsetena (peab meeles MVCC!).

DELETE FROM dst

Ei, loomulikult saab hakkama ka vaid kahe toiminguga:

• kustutada (DELETE) tegelikult kõik
• sisestada kõik uuest pildist

Aga samas, MVCC tõttu, tabeli suurus kahekordistub! Saada +1M rekordite pilte tabelisse 10K uuendamise tõttu — pole just ideaalne üleliigsus…

TRUNCATE dst

Kogenum arendaja teab, et kogu tabelit on võimalik rahaliselt odavalt puhastada:

• puhta (TRUNCATE) kogu tabel
• sisestada kõik uuest pildist

Meetod on tõhus, mõnikord täiesti rakendatav, kuid see toob kaasa probleemi… Me joonistame 1M kirjet üle väga kaua, seega ei saa me endale lubada, et tabel oleks kogu selle aja tühjaks jäetud (nagu juhtuks ilma üheainsa tehingu katmiseneta).

Seega:

• me alustame pikk tehing
• TRUNCATE kehtestab AccessExclusive-lukku
• teeme inserte pikka aega, kuid kõik teised selle ajal ei saa isegi SELECT

Midagi ei tundu hästi tulevat…

ALTER TABLE… RENAME… / DROP TABLE …

Võimalusena võiks kõik üle kanda uude eraldi tabelisse ja seejärel lihtsalt vana tabeli nime muuta. Paar ebameeldivat pisiasja:

• ka need AccessExclusive, kuigi oluliselt vähem ajakulu
• kustuvad kõik päringute plaanid/statistika selle tabeli kohta, peab käivitama ANALYZE
• katkeb kõik välised võtmed (FK) sellele tabelile

Oli WIP-patch Simon Riggsilt, mis pakkus välja ALTER-operatsiooni tabeli sisu asendamiseks failitasandil, mõjutamata statistikat ja FK-d, kuid ei kogunud kvoori.

DELETE, UPDATE, INSERT

Nii et jääme kolme operatsiooni mitteblokeeriva variandi juurde. Peaaegu kolme… Kuidas seda kõige tõhusamalt teha?

-- teeme kõik tehingu raames, et keegi ei näeks "vahepealseid" olekuid
BEGIN;

-- loome ajutise tabeli imporditud andmetega
CREATE TEMPORARY TABLE tmp(
  LIKE dst INCLUDING INDEXES -- sarnaselt, koos indeksitega
) ON COMMIT DROP; -- tehingu raames ei ole see meile vajalik

-- kiirelt viime uue sisu sisse läbi COPY
COPY tmp FROM STDIN;
-- ...
-- .

-- kustutame puuduvad
DELETE FROM
  dst D
USING
  dst X
LEFT JOIN
  tmp Y
    USING(pk1, pk2) -- primaarklahvi väljad
WHERE
  (D.pk1, D.pk2) = (X.pk1, X.pk2) AND
  Y IS NOT DISTINCT FROM NULL; -- "anti-join"

-- uuendame jäänud
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); -- ei ole mõtet uuendada kattuvaid

-- sisestame puuduvad
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ärelprotsessimine

Samas KLADris tuleb kõik muudetud kirjed täiendavalt läbi viia järelprotsessimine — normaliseerida, eraldada märksõnad, viia sobivatesse struktuuridesse. Kuid kuidas teada — mida täpselt muudeti, keerukust süvenemata sünkroniseerimise koodi, ideaaljuhul, mitte puudutades seda üldse?

Kui kirjutamisõigus sünkroniseerimise hetkel on ainult teie protsessil, siis võib kasutada käivitusklahvi, mis kogub kõik meie muudatused:

-- sihtlaud tabelid
CREATE TABLE kladr(...);
CREATE TABLE kladr_house(...);

-- muutuste ajalooga tabelid
CREATE TABLE kladr$log(
  ro kladr, -- siin on vanade/uute kirgede täpsed koopiad
  rn kladr
);

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

-- üldine funktsioon muudatuste logimiseks
CREATE OR REPLACE FUNCTION diff$log() RETURNS trigger AS $$
DECLARE
  dst varchar = TG_TABLE_NAME || '$log';
  stmt text = '';
BEGIN
  -- kontrollime vajadust logimise järel uuendamisel
  IF TG_OP = 'UPDATE' THEN
    IF NEW IS NOT DISTINCT FROM OLD THEN
      RETURN NEW;
    END IF;
  END IF;
  -- loome logikirje
  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ünkroniseerimise algust käivitada (või aktiveerida via 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();

Seejärel saame logitabelitest rahulikult välja tõmmata kõik vajalikud muudatused ning saata need edasi täiendavatele töötlejatele.

3.3. Seotud komplektide import

Ülal arutlesime juhtumeid, kus allika ja sihtkoha andmestruktuurid kattuvad. Kuid mis juhul, kui välistest süsteemidest saadud eksport on formaadilt erinev meie andmebaasi ladustamisstruktuurist?

Võtame näiteks klientide ja nende arvete hoidmise, klassikalise „ühed-palat“ variandi:

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äline allika eksport tuleb meile „kõik ühes“ kujul:

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

Ilmselt võivad klientide andmed sellises variandis dubleeruda, kuid peamine kirje on „arve“:

0123456789;Vasily;A-01;2020-03-16;1000.00
9876543210;Peter;A-02;2020-03-16;666.00
0123456789;Vasily;B-03;2020-03-16;9999.00

Mudeli jaoks sisestame lihtsalt oma testandmed, kuid peame meeles pidama — COPY efektiivsemalt!

INSERT INTO invoice_import
VALUES
  ('0123456789', 'Vasily', 'A-01', '2020-03-16', 1000.00)
, ('9876543210', 'Peter', 'A-02', '2020-03-16', 666.00)
, ('0123456789', 'Vasily', 'B-03', '2020-03-16', 9999.00);

Alustame nende „võtmete“ esitlemisest, millele meie „faktid“ viitavad. Meie puhul viitavad arved klientidele:

LOO TEMPORARY TABLE client_import AS
SELECT DISTINCT ON(client_inn)
-- võite lihtsalt kasutada SELECT DISTINCT, kui andmed on kindlasti vastuolulised
  client_inn inn
, client_name "name"
FROM
  invoice_import;

Kuna soovime õigesti siduda arvete ID-d klientidega, peame esmalt need identifikaatorid välja selgitama või genereerima. Lisame nende jaoks väljad:

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

Kasutame ülaltoodud viisi tabelite sünkroniseerimiseks, kuid väikese muudatusega — me ei uuenda ega kustuta siht-tabletis, kuna klientide import on meil "append-only":

-- määrame imporditabelis olemasolevate kirjete ID-d
UPDATE
  client_import T
SET
  client_id = D.client_id
FROM
  client D
WHERE
  T.inn = D.inn; -- unikaalne võti

-- sisestame puuduvad kirjed ja määrame nende ID-d
WITH ins AS (
  INSERT INTO client(
    inn
  , name
  )
  SELECT
    inn
  , name
  FROM
    client_import
  WHERE
    client_id IS NULL -- kui ID ei ole määratud
  RETURNING *
)
UPDATE
  client_import T
SET
  client_id = D.client_id
FROM
  ins D
WHERE
  T.inn = D.inn; -- unikaalne võti

-- määrame klientide ID-d arvete kirjetes
UPDATE
  invoice_import T
SET
  client_id = D.client_id
FROM
  client_import D
WHERE
  T.client_inn = D.inn; -- rakenduslik võti

Sisuliselt on kõik — invoice_import nüüd on meil täidetud seose väli client_id, millega me ka arve sisestame.

Allikas: habr.com