DBA: skipuleggja samstillingar og innflutning á hæfan hátt

Fyrir flókna vinnslu á stórum gagnasöfnum (mismunandi ETL ferlar: innflutningur, umbreytingar og samstilling við utanaðkomandi uppsprettu) oft er þörf tímabundið „muna“ og vinna strax fljótt eitthvað umfangsmikið.

Dæmigert verkefni af þessu tagi hljómar venjulega eitthvað á þessa leið: "Hérna bókhaldsdeild losuð úr viðskiptabanka síðustu mótteknu greiðslurnar þarftu að hlaða þeim upp á vefsíðuna og tengja þær við reikningana þína.“

En þegar rúmmál þessa „eitthvaðs“ byrjar að mælast í hundruðum megabæta og þjónustan verður að halda áfram að vinna með gagnagrunninum 24x7, koma upp margar aukaverkanir sem munu eyðileggja líf þitt.

Til að takast á við þá í PostgreSQL (og ekki aðeins í því), geturðu notað nokkrar hagræðingar sem gera þér kleift að vinna úr öllu hraðar og með minni auðlindanotkun.

1. Hvert á að senda?

Fyrst skulum við ákveða hvar við getum hlaðið upp gögnunum sem við viljum „vinnsla“.

1.1. Tímabundnar töflur (tímabundin töflur)

Í grundvallaratriðum, fyrir PostgreSQL eru tímabundnar töflur þær sömu og allar aðrar. Því hjátrú eins og „Allt þar er aðeins geymt í minni og það getur endað“. En það er líka nokkur marktækur munur.

Þitt eigið „nafnarými“ fyrir hverja tengingu við gagnagrunninn

Ef tvær tengingar reyna að tengjast á sama tíma CREATE TABLE x, þá mun örugglega einhver fá óeiginleikavilla gagnagrunnshlutir.

En ef bæði reyna að framkvæma CREATE TEMPORARY TABLE x, þá munu báðir gera það venjulega, og allir munu fá þitt eintak borðum. Og það verður ekkert sameiginlegt á milli þeirra.

"Self-destruct" þegar þú aftengir

Þegar tengingunni er lokað er öllum tímabundnum töflum sjálfkrafa eytt, svo handvirkt DROP TABLE x það þýðir ekkert nema...

Ef þú ert að vinna í gegnum pgbouncer í viðskiptaham, þá heldur gagnagrunnurinn áfram að trúa því að þessi tenging sé enn virk og í henni er þessi bráðabirgðatafla enn til.

Þess vegna mun það leiða til villu að reyna að búa það til aftur, frá annarri tengingu við pgbouncer. En þetta er hægt að sniðganga með því að nota CREATE TEMPORARY TABLE IF NOT EXISTS x.

Að vísu er betra að gera þetta ekki samt, því þá geturðu „skyndilega“ fundið þar gögnin sem eftir eru frá „fyrri eiganda“. Þess í stað er miklu betra að lesa handbókina og sjá að þegar búið er til töflu er hægt að bæta við ON COMMIT DROP - það er, þegar viðskiptunum lýkur verður töflunni sjálfkrafa eytt.

Óafritun

Vegna þess að þær tilheyra aðeins ákveðinni tengingu eru tímabundnar töflur ekki endurteknar. En þetta útilokar þörfina fyrir tvöfalda skráningu gagna í hrúgu + WAL, þannig að INSERT/UPDATE/DELETE inn í það er verulega hraðari.

En þar sem tímabundið borð er enn „næstum venjulegt“ borð er ekki hægt að búa það til á eftirmynd heldur. Að minnsta kosti í bili, þó að samsvarandi plástur hafi verið í umferð í langan tíma.

1.2. ÚTÓLOGÐ TAFLA

En hvað ættirðu að gera, til dæmis ef þú ert með einhvers konar fyrirferðarmikið ETL ferli sem ekki er hægt að innleiða í einni færslu, en þú hefur samt pgbouncer í viðskiptaham? ..

Eða gagnaflæðið er svo mikið að Það er ekki næg bandbreidd á einni tengingu úr gagnagrunni (lesið, eitt ferli á hvern örgjörva)?..

Eða einhverjar aðgerðir eru í gangi ósamstilltur í mismunandi samböndum?..

Það er aðeins einn kostur hér - búa tímabundið til töflu sem ekki er tímabundið. Orðleikur, já. Það er:

• búið til „mínar eigin“ töflur með hámarks handahófskenndum nöfnum til að skerast ekki við neinn
• Þykkni: fyllti þau með gögnum frá utanaðkomandi aðilum
• Transform: breytt, fyllt út í helstu tengireiti
• hlaða: hellti tilbúnum gögnum í marktöflur
• eytt „mínum“ töflum

Og nú - fluga í smyrslinu. Reyndar, öll skrif í PostgreSQL gerast tvisvar - fyrst í WAL, síðan inn í töfluna/vísitöluna. Allt er þetta gert til að styðja við ACID og rétta gagnasýnileika á milli COMMIT'nöturlegur og ROLLBACK'nullviðskipti.

En við þurfum þetta ekki! Við erum með allt ferlið Annað hvort tókst það alveg eða ekki.. Það skiptir ekki máli hversu mörg millifærslur verða - við höfum ekki áhuga á að „halda ferlinu áfram frá miðju,“ sérstaklega þegar ekki er ljóst hvar það var.

Til að gera þetta kynntu PostgreSQL forritararnir, aftur í útgáfu 9.1, slíkt eins og ÓLOGGÐAR töflur:

Með þessari vísbendingu er taflan búin til sem óskráð. Gögn sem eru skrifuð í óskráðar töflur fara ekki í gegnum framritunarskrána (sjá kafla 29), sem veldur því að slíkar töflur vinna mun hraðar en venjulega. Hins vegar eru þau ekki ónæm fyrir bilun; ef miðlari bilar eða neyðarlokun, óskráð borð sjálfkrafa stytt. Auk þess innihald óskráðu töflunnar ekki endurtekið til þrælþjóna. Allar vísitölur sem eru búnar til á óskráðri töflu verða sjálfkrafa óskráðar.

Í stuttu máli, það verður miklu hraðari, en ef gagnagrunnsþjónninn „fellur“ verður það óþægilegt. En hversu oft gerist þetta og veit ETL ferlið þitt hvernig á að leiðrétta þetta rétt „frá miðju“ eftir að hafa „endurlífgað“ gagnagrunninn? ..

Ef ekki, og tilvikið hér að ofan er svipað og þitt, notaðu UNLOGGEDen aldrei ekki virkja þennan eiginleika á raunverulegum borðum, gögnin sem eru þér kær.

1.3. ON COMMIT { DELETE ROWS | DROPI}

Þessi uppbygging gerir þér kleift að tilgreina sjálfvirka hegðun þegar færslu er lokið þegar töflu er búin til.

á ON COMMIT DROP Ég skrifaði þegar hér að ofan, það býr til DROP TABLE, en með ON COMMIT DELETE ROWS ástandið er áhugaverðara - það myndast hér TRUNCATE TABLE.

Þar sem allur innviði til að geyma meta-lýsingu tímabundinnar töflu er nákvæmlega sá sami og venjulegrar töflu, þá Stöðug sköpun og eyðing tímabundinna taflna leiðir til mikillar „bólgu“ í kerfistöflum pg_class, pg_attribute, pg_attrdef, pg_depend,…

Ímyndaðu þér nú að þú sért með starfsmann í beinni tengingu við gagnagrunninn, sem opnar nýja færslu á hverri sekúndu, býr til, fyllir, vinnur og eyðir bráðabirgðatöflu... Það verður of mikið af rusli sem safnast fyrir í kerfistöflunum og þetta mun valda auka hemlum fyrir hverja aðgerð.

Almennt, ekki gera þetta! Í þessu tilfelli er það miklu áhrifaríkara CREATE TEMPORARY TABLE x ... ON COMMIT DELETE ROWS taktu það úr viðskiptalotunni - þá eru töflurnar þegar í upphafi hverrar nýrrar færslu mun vera til (vistaðu símtal CREATE), en verður tómt, þökk sé TRUNCATE (við vistuðum líka símtalið) þegar fyrri færslu var lokið.

1.4. LIKE...ÞAR á meðal...

Ég nefndi í upphafi að eitt dæmigerða notkunartilvikið fyrir bráðabirgðatöflur er ýmiss konar innflutningur - og þróunaraðilinn copy-pasteir listann yfir reiti marktöflunnar þreytulega inn í yfirlýsinguna um tímabundna...

En leti er vél framfara! Þess vegna búa til nýja töflu "byggt á sýnishorni" það getur verið miklu einfaldara:

CREATE TEMPORARY TABLE import_table(
  LIKE target_table
);

Þar sem þú getur síðan búið til mikið af gögnum inn í þessa töflu, verður leit í gegnum hana aldrei hröð. En það er hefðbundin lausn á þessu - vísitölur! Og, já, tímabundin tafla getur einnig haft vísitölur.

Þar sem nauðsynlegar vísitölur falla oft saman við vísitölur marktöflunnar geturðu einfaldlega skrifað LIKE target_table INCLUDING INDEXES.

Ef þú þarft líka DEFAULT-gildi (til dæmis til að fylla út aðallykilgildin), þú getur notað LIKE target_table INCLUDING DEFAULTS. Eða einfaldlega - LIKE target_table INCLUDING ALL - afritar sjálfgefna, vísitölur, takmarkanir,...

En hér þarftu að skilja að ef þú bjóst til flyttu inn töflu strax með vísitölum, þá mun taka lengri tíma að hlaða gögninen ef þú fyllir fyrst allt upp og rúllar síðan upp vísitölunum - skoðaðu hvernig það gerir þetta sem dæmi pg_dump.

Almennt, RTFM!

2. Hvernig á að skrifa?

Leyfðu mér bara að segja - notaðu það COPY-flæði í stað "pakka" INSERT, hröðun stundum. Þú getur jafnvel beint úr formyndaðri skrá.

3. Hvernig á að vinna?

Svo, við skulum láta innganginn okkar líta einhvern veginn svona út:

• þú ert með töflu með gögnum viðskiptavina sem eru geymd í gagnagrunninum þínum 1M met
• á hverjum degi sendir viðskiptavinur þér nýjan full "mynd"
• af reynslu þú veist það af og til ekki er breytt meira en 10K skrám

Klassískt dæmi um slíkar aðstæður er KLADR bækistöð — það eru fullt af heimilisföngum í heildina, en í hverri vikulegu upphleðslu eru mjög fáar breytingar (nöfnun byggðar, sameining gatna, útlit nýrra húsa) jafnvel á landsvísu.

3.1. Full samstillingar reiknirit

Til einföldunar, segjum að þú þurfir ekki einu sinni að endurskipuleggja gögnin - færðu bara töfluna í það form sem þú vilt, það er:

• fjarlægja allt sem er ekki lengur til
• endurnýja allt sem þegar var til og þarf að uppfæra
• setja inn allt sem ekki hefur gerst ennþá

Hvers vegna ætti að gera aðgerðir í þessari röð? Vegna þess að þetta er hvernig borðstærðin mun stækka í lágmarki (mundu eftir MVCC!).

EYÐA ÚR dst

Nei, auðvitað kemstu af með aðeins tvær aðgerðir:

• fjarlægja (DELETE) allt almennt
• setja inn allt úr nýju myndinni

En á sama tíma, þökk sé MVCC, Stærð borðsins mun aukast nákvæmlega tvisvar! Að fá +1 milljón myndir af færslum í töflunni vegna 10K uppfærslu er svo sem offramboð...

TRUNCATE dst

Reyndari verktaki veit að hægt er að þrífa alla spjaldtölvuna á nokkuð ódýran hátt:

• hreinsa (TRUNCATE) allt borðið
• setja inn allt úr nýju myndinni

Aðferðin er áhrifarík, stundum alveg við, en það er vandamál... Við munum bæta við 1M færslum í langan tíma, svo við höfum ekki efni á að skilja borðið eftir tómt allan þennan tíma (eins og mun gerast án þess að pakka því inn í eina færslu).

Sem þýðir:

• við erum að byrja langvarandi viðskipti
• TRUNCATE leggur Aðgangur eingöngu-blokkandi
• við gerum innsetninguna í langan tíma, og allir aðrir á þessum tíma get ekki einu sinni SELECT

Eitthvað gengur ekki vel...

BREYTA TÖFLU... ENDURNefna... / SLIPPA TÖFLU...

Annar kostur er að fylla allt inn í sérstaka nýja töflu og síðan einfaldlega endurnefna hana í stað þeirrar gömlu. Nokkrir viðbjóðslegir hlutir:

• samt líka Aðgangur eingöngu, þó verulega styttri tíma
• allar fyrirspurnaáætlanir/tölfræði fyrir þessa töflu eru endurstillt, þarf að keyra ANALYZE
• allir erlendir lyklar eru bilaðir (FK) að borðinu

Það var WIP plástur frá Simon Riggs sem stingur upp á gerð ALTER-aðgerð til að skipta um meginmál töflunnar á skráarstigi, án þess að snerta tölfræði og FK, en safnaði ekki ályktun.

Eyða, uppfæra, setja inn

Þannig að við sættum okkur við þann valmöguleika sem ekki hindrar þrjár aðgerðir. Næstum þrír... Hvernig á að gera þetta á skilvirkasta hátt?

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

Í sama KLADR verður að keyra allar breyttar færslur til viðbótar í gegnum eftirvinnslu - staðlaða, leitarorð auðkennd og minnkað í nauðsynlegar byggingar. En hvernig veistu - hvað nákvæmlega breyttistán þess að flækja samstillingarkóðann, helst án þess að snerta hann yfirleitt?

Ef aðeins ferlið þitt hefur skrifaðgang við samstillingu, þá geturðu notað kveikju sem safnar öllum breytingum fyrir okkur:

-- целевые таблицы
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ú getum við beitt kveikjum áður en samstilling hefst (eða virkjað þær í gegnum 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();

Og svo tökum við rólegar allar breytingarnar sem við þurfum úr logtöflunum og keyrum þær í gegnum fleiri meðhöndlunaraðila.

3.3. Flytja inn tengd sett

Hér að ofan skoðuðum við tilvik þegar gagnauppbygging upprunans og áfangastaðarins er sú sama. En hvað ef upphleðslan frá utanaðkomandi kerfi hefur annað snið en geymsluskipulagið í gagnagrunninum okkar?

Tökum sem dæmi geymslu viðskiptavina og reikninga þeirra, hinn klassíska „margir í einn“ valmöguleika:

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

En niðurhalið frá utanaðkomandi aðilum kemur til okkar í formi „allt í einu“:

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

Augljóslega er hægt að afrita gögn viðskiptavina í þessari útgáfu og aðalskráin er „reikningur“:

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

Fyrir líkanið munum við einfaldlega setja inn prófunargögnin okkar, en mundu - COPY skilvirkara!

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

Í fyrsta lagi skulum við varpa ljósi á þá „skerðingar“ sem „staðreyndir“ okkar vísa til. Í okkar tilviki vísa reikningar til viðskiptavina:

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

Til þess að tengja reikninga rétt við auðkenni viðskiptavina þurfum við fyrst að finna út eða búa til þessi auðkenni. Við skulum bæta við reitum undir þeim:

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

Við skulum nota töflusamstillingaraðferðina sem lýst er hér að ofan með smá breytingu - við munum ekki uppfæra eða eyða neinu í marktöflunni, vegna þess að við flytjum inn viðskiptavini „aðeins“:

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

Reyndar er allt inni invoice_import Nú höfum við fyllt út tengiliðareitinn client_id, sem við munum setja inn reikninginn með.

Heimild: www.habr.com