DBA: organiseer sinchronisasies en invoere bekwaam

Vir komplekse verwerking van groot datastelle (verskillende ETL prosesse: invoere, omskakelings en sinchronisasie met 'n eksterne bron) dikwels is daar 'n behoefte tydelik “onthou” en dadelik vinnig verwerk iets lywigs.

'n Tipiese taak van hierdie soort klink gewoonlik so: "Net hier rekeningkundige afdeling van die kliëntbank afgelaai die laaste betalings wat u ontvang het, moet u dit vinnig na die webwerf oplaai en dit aan u rekeninge koppel"

Maar wanneer die volume van hierdie “iets” begin meet in honderde megagrepe, en die diens moet aanhou werk met die databasis 24x7, ontstaan ​​baie newe-effekte wat jou lewe sal verwoes.

Om dit in PostgreSQL te hanteer (en nie net daarin nie), kan u 'n paar optimaliserings gebruik wat u in staat sal stel om alles vinniger en met minder hulpbronverbruik te verwerk.

1. Waarheen om te stuur?

Kom ons besluit eers waar ons die data kan oplaai wat ons wil “verwerk”.

1.1. Tydelike tabelle (TYDELIKE TABEL)

In beginsel, vir PostgreSQL is tydelike tabelle dieselfde as enige ander. Daarom, bygelowe soos "Alles daar word net in die geheue gestoor, en dit kan eindig". Maar daar is ook verskeie beduidende verskille.

Jou eie "naamruimte" vir elke verbinding met die databasis

As twee verbindings gelyktydig probeer koppel CREATE TABLE x, dan sal iemand beslis kry nie-uniekheid fout databasis voorwerpe.

Maar as albei probeer om uit te voer CREATE TEMPORARY TABLE x, dan sal albei dit normaal doen, en almal sal kry jou kopie tafels. En daar sal niks in gemeen tussen hulle wees nie.

"Selfvernietig" wanneer u ontkoppel

Wanneer die verbinding gesluit is, word alle tydelike tabelle outomaties uitgevee, dus handmatig DROP TABLE x daar is geen punt behalwe...

As jy deurwerk pgbouncer in transaksiemodus, dan bly die databasis glo dat hierdie verbinding steeds aktief is, en daarin bestaan ​​hierdie tydelike tabel steeds.

Om dit dus weer te probeer skep, vanaf 'n ander verbinding na pgbouncer, sal 'n fout tot gevolg hê. Maar dit kan omseil word deur gebruik te maak CREATE TEMPORARY TABLE IF NOT EXISTS x.

Dit is waar, dit is in elk geval beter om dit nie te doen nie, want dan kan jy "skielik" die data wat oorbly van die "vorige eienaar" vind. In plaas daarvan is dit baie beter om die handleiding te lees en te sien dat dit moontlik is om by te voeg wanneer 'n tabel geskep word ON COMMIT DROP - dit wil sê, wanneer die transaksie voltooi is, sal die tabel outomaties uitgevee word.

Nie-replikasie

Omdat hulle slegs aan 'n spesifieke verbinding behoort, word tydelike tabelle nie gerepliseer nie. Maar dit skakel die behoefte vir dubbele opname van data uit in hoop + WAL, so INSERT/UPDATE/DELETE daarin is baie vinniger.

Maar aangesien 'n tydelike tabel steeds 'n "amper gewone" tafel is, kan dit ook nie op 'n replika geskep word nie. Ten minste vir nou, alhoewel die ooreenstemmende pleister al lank in die rondte is.

1.2. ONTEKEN TABEL

Maar wat moet jy doen, byvoorbeeld, as jy 'n soort omslagtige ETL-proses het wat nie binne een transaksie geïmplementeer kan word nie, maar jy het steeds pgbouncer in transaksiemodus? ..

Of die datavloei is so groot dat Daar is nie genoeg bandwydte op een verbinding nie vanaf 'n databasis (lees, een proses per SVE)?..

Of sommige operasies is aan die gang asynchronies in verskillende verbande? ..

Hier is net een opsie - skep tydelik 'n nie-tydelike tabel. Woordspeling, ja. Dit is:

• "my eie" tabelle met maksimum ewekansige name geskep om nie met iemand te sny nie
• Uittreksel: het hulle gevul met data van 'n eksterne bron
• Transformeer: omgeskakel, sleutelskakelvelde ingevul
• vrag: gereed data in teikentabelle gegooi
• "my" tabelle geskrap

En nou - 'n vlieg in die salf. In werklikheid, alle skrywes in PostgreSQL gebeur twee keer - eerste in WAL, dan in die tabel/indeksliggame. Dit alles word gedoen om ACID te ondersteun en korrekte datasigbaarheid tussen COMMIT'moerig en ROLLBACK'nul transaksies.

Maar ons het dit nie nodig nie! Ons het die hele proses Of dit was heeltemal suksesvol of dit was nie.. Dit maak nie saak hoeveel intermediêre transaksies daar sal wees nie - ons stel nie daarin belang om die proses van die middel af voort te sit nie, veral as dit nie duidelik is waar dit was nie.

Om dit te doen, het die PostgreSQL-ontwikkelaars, terug in weergawe 9.1, so iets bekendgestel soos ONTEKEN tabelle:

Met hierdie aanduiding word die tabel as ongelog geskep. Data wat na onaangetekende tabelle geskryf word, gaan nie deur die vooruitskryflogboek nie (sien Hoofstuk 29), wat veroorsaak dat sulke tabelle werk baie vinniger as gewoonlik. Hulle is egter nie immuun teen mislukking nie; in die geval van bedienerfout of noodafsluiting, 'n onaangetekende tabel outomaties afgekap. Daarbenewens, die inhoud van die onaangetekende tabel nie herhaal nie aan slawebedieners. Enige indekse wat op 'n onaangetekende tabel geskep word, word outomaties ontmeld.

Kortom dit sal baie vinniger wees, maar as die databasisbediener “val”, sal dit onaangenaam wees. Maar hoe gereeld gebeur dit, en weet jou ETL-proses hoe om dit korrek “van die middel” af reg te stel nadat jy die databasis “herleef” het?

Indien nie, en die geval hierbo is soortgelyk aan joune, gebruik UNLOGGEDmaar nooit moenie hierdie kenmerk op regte tabelle aktiveer nie, waaruit die data dierbaar is vir jou.

1.3. ON COMMIT { VERWYD RYE | VAL}

Hierdie konstruksie laat jou toe om outomatiese gedrag te spesifiseer wanneer 'n transaksie voltooi word wanneer 'n tabel geskep word.

op ON COMMIT DROP Ek het reeds hierbo geskryf, dit genereer DROP TABLE, maar met ON COMMIT DELETE ROWS die situasie is interessanter - dit word hier gegenereer TRUNCATE TABLE.

Aangesien die hele infrastruktuur vir die stoor van die meta-beskrywing van 'n tydelike tabel presies dieselfde is as dié van 'n gewone tabel, dan Voortdurende skepping en uitwissing van tydelike tabelle lei tot ernstige "swelling" van stelseltabelle pg_class, pg_attribute, pg_attrdef, pg_depend,...

Stel jou nou voor dat jy 'n werker het op 'n direkte verbinding met die databasis, wat elke sekonde 'n nuwe transaksie oopmaak, 'n tydelike tabel skep, invul, verwerk en uitvee ... Daar sal 'n oormaat vullis in die stelseltabelle opgehoop word, en dit sal ekstra remme vir elke operasie veroorsaak.

Oor die algemeen, moenie dit doen nie! In hierdie geval is dit baie meer effektief CREATE TEMPORARY TABLE x ... ON COMMIT DELETE ROWS haal dit uit die transaksiesiklus - dan is die tabelle reeds aan die begin van elke nuwe transaksie sal bestaan (stoor 'n oproep CREATE), maar leeg sal wees, te danke aan TRUNCATE (ons het ook sy oproep gestoor) toe die vorige transaksie voltooi is.

1.4. LIKE ... INSLUITEND ...

Ek het aan die begin genoem dat een van die tipiese gebruiksgevalle vir tydelike tabelle verskeie soorte invoere is - en die ontwikkelaar kopieer-plak moeg die lys velde van die teikentabel in die verklaring van sy tydelike...

Maar luiheid is die enjin van vooruitgang! Dis hoekom skep 'n nuwe tabel "gebaseer op voorbeeld" dit kan baie eenvoudiger wees:

CREATE TEMPORARY TABLE import_table(
  LIKE target_table
);

Aangesien jy dan baie data in hierdie tabel kan genereer, sal dit nooit vinnig wees om daardeur te soek nie. Maar daar is 'n tradisionele oplossing hiervoor - indekse! En, ja, 'n tydelike tabel kan ook indekse hê.

Aangesien die vereiste indekse dikwels saamval met die indekse van die teikentabel, kan u eenvoudig skryf LIKE target_table INCLUDING INDEXES.

As jy ook nodig het DEFAULT-waardes (byvoorbeeld om die primêre sleutelwaardes in te vul), kan jy gebruik LIKE target_table INCLUDING DEFAULTS. Of eenvoudig - LIKE target_table INCLUDING ALL - kopieer verstek, indekse, beperkings, ...

Maar hier moet jy verstaan ​​dat as jy geskep het invoer tabel onmiddellik met indekse, dan sal die data langer neem om te laaias jy eers alles invul, en dan eers die indekse oprol - kyk hoe dit dit doen as voorbeeld bl_dump.

In die algemeen, RTFM!

2. Hoe om te skryf?

Laat ek net sê – gebruik dit COPY-vloei in plaas van "pak" INSERT, versnelling by tye. Jy kan selfs direk vanaf 'n vooraf-gegenereerde lêer.

3. Hoe om te verwerk?

So, laat ons ons inleiding so iets lyk:

• jy het 'n tabel met kliëntdata wat in jou databasis gestoor is 1M rekords
• elke dag stuur 'n kliënt vir jou 'n nuwe een volledige "beeld"
• uit ondervinding weet jy dit van tyd tot tyd nie meer as 10K rekords word verander nie

'n Klassieke voorbeeld van so 'n situasie is KLADR basis — daar is in totaal baie adresse, maar in elke weeklikse oplaai is daar baie min veranderinge (hernoeming van nedersettings, samevoeging van strate, voorkoms van nuwe huise) selfs op nasionale skaal.

3.1. Volledige sinchronisasie-algoritme

Kom ons sê vir eenvoud dat jy nie eers die data hoef te herstruktureer nie - bring net die tabel in die gewenste vorm, dit is:

• verwyder alles wat nie meer bestaan ​​nie
• Opdateer alles wat reeds bestaan ​​het en opgedateer moet word
• voeg alles wat nog nie gebeur het nie

Hoekom moet die operasies in hierdie volgorde gedoen word? Want dit is hoe die tafelgrootte minimaal sal groei (onthou MVCC!).

SKRYF UIT dst

Nee, jy kan natuurlik klaarkom met net twee operasies:

• verwyder (DELETE) alles in die algemeen
• voeg alles vanaf die nuwe beeld

Maar terselfdertyd, danksy MVCC, Die grootte van die tafel sal presies twee keer toeneem! Om +1M beelde van rekords in die tabel te kry as gevolg van 'n 10K-opdatering is so-so oortolligheid ...

TRUNCATE dst

'n Meer ervare ontwikkelaar weet dat die hele tablet redelik goedkoop skoongemaak kan word:

• skoon (TRUNCATE) die hele tafel
• voeg alles vanaf die nuwe beeld

Die metode is effektief, soms redelik toepaslik, maar daar is 'n probleem... Ons sal vir 'n lang tyd 1M rekords byvoeg, so ons kan nie bekostig om die tafel vir die hele tyd leeg te laat nie (soos sal gebeur sonder om dit in 'n enkele transaksie toe te draai).

Wat beteken:

• ons begin langlopende transaksie
• TRUNCATE oplê Toegang eksklusief-blokkeer
• ons doen die invoeging vir 'n lang tyd, en almal anders op hierdie tyd kan nie eers nie SELECT

Iets gaan nie goed nie...

VERANDER TABEL... HERNOEM... / LAAT TABEL LAAT...

'n Alternatief is om alles in 'n aparte nuwe tabel in te vul en dit dan eenvoudig te hernoem in die plek van die ou een. 'n Paar nare dingetjies:

• nog steeds ook Toegang eksklusief, hoewel aansienlik minder tyd
• alle navraagplanne/statistieke vir hierdie tabel word teruggestel, moet ANALYSE hardloop
• alle vreemde sleutels is gebreek (FK) na die tafel

Daar was 'n WIP-pleister van Simon Riggs wat voorgestel het om te maak ALTER-'n bewerking om die tabelliggaam op die lêervlak te vervang, sonder om statistieke en FK aan te raak, maar het nie kworum ingesamel nie.

SKEE, DATEER OP, VOEG IN

Dus, ons besluit op die nie-blokkerende opsie van drie operasies. Amper drie ... Hoe om dit die doeltreffendste te doen?

-- все делаем в рамках транзакции, чтобы никто не видел "промежуточных" состояний
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. Voer naverwerking in

In dieselfde KLADR moet alle veranderde rekords addisioneel deur naverwerking uitgevoer word - genormaliseer, sleutelwoorde uitgelig en verminder tot die vereiste strukture. Maar hoe weet jy - wat presies verander hetsonder om die sinchronisasiekode te bemoeilik, verkieslik sonder om dit enigsins aan te raak?

As slegs jou proses skryftoegang het ten tyde van sinchronisasie, dan kan jy 'n sneller gebruik wat al die veranderinge vir ons sal versamel:

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

Nou kan ons snellers toepas voordat ons sinchronisasie begin (of dit aktiveer 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();

En dan haal ons rustig al die veranderinge wat ons nodig het uit die log-tabelle en voer dit deur bykomende hanteerders.

3.3. Invoer van gekoppelde stelle

Hierbo het ons gevalle oorweeg wanneer die datastrukture van die bron en bestemming dieselfde is. Maar wat as die oplaai vanaf 'n eksterne stelsel 'n formaat het wat verskil van die stoorstruktuur in ons databasis?

Kom ons neem as voorbeeld die berging van kliënte en hul rekeninge, die klassieke "baie-tot-een" opsie:

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

Maar die aflaai van 'n eksterne bron kom na ons toe in die vorm van "alles in een":

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

Uiteraard kan klantdata in hierdie weergawe gedupliseer word, en die hoofrekord is "rekening":

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

Vir die model sal ons eenvoudig ons toetsdata invoeg, maar onthou - COPY meer effektief!

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

Eerstens, kom ons beklemtoon daardie “snitte” waarna ons “feite” verwys. In ons geval verwys fakture na kliënte:

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

Om rekeninge korrek met klant-ID's te assosieer, moet ons eers hierdie identifiseerders uitvind of genereer. Kom ons voeg velde onder hulle by:

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

Kom ons gebruik die tabelsinchronisasiemetode wat hierbo beskryf is met 'n klein wysiging - ons sal niks in die teikentabel opdateer of uitvee nie, want ons voer kliënte "byvoeg-slegs" in:

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

Eintlik is alles in invoice_import Nou het ons die kontakveld ingevul client_id, waarmee ons die faktuur sal invoeg.

Bron: will.com