🥇DBA: panga kwa ustadi usawazishaji na uagizaji

Kwa usindikaji mgumu wa seti kubwa za data (tofauti Michakato ya ETL: uagizaji, ubadilishaji na ulandanishi na chanzo cha nje) mara nyingi kuna hitaji kwa muda "kumbuka" na haraka mchakato kitu kikubwa.

Kazi ya kawaida ya aina hii kawaida husikika kama hii: "Hapa idara ya uhasibu iliyopakuliwa kutoka kwa benki ya mteja malipo ya mwisho yaliyopokelewa, unahitaji kuyapakia kwa haraka kwenye tovuti na kuyaunganisha kwenye akaunti yako.”

Lakini wakati kiasi cha "kitu" hiki kinapoanza kupima mamia ya megabytes, na huduma lazima iendelee kufanya kazi na database 24x7, madhara mengi hutokea ambayo yataharibu maisha yako.

Ili kukabiliana nao katika PostgreSQL (na sio ndani yake tu), unaweza kutumia uboreshaji fulani ambao utakuruhusu kuchakata kila kitu haraka na kwa utumiaji mdogo wa rasilimali.

1. Wapi kusafirisha?

Kwanza, hebu tuamue ni wapi tunaweza kupakia data ambayo tunataka "kuchakata."

1.1. Majedwali ya muda (TEMPORARY TABLE)

Kimsingi, kwa meza za muda za PostgreSQL ni sawa na nyingine yoyote. Kwa hiyo, ushirikina kama "Kila kitu kilihifadhiwa kwenye kumbukumbu tu, na kinaweza kuisha". Lakini pia kuna tofauti kadhaa muhimu.

"Nafasi ya majina" yako mwenyewe kwa kila muunganisho kwenye hifadhidata

Ikiwa viunganisho viwili jaribu kuunganisha kwa wakati mmoja CREATE TABLE x, basi mtu hakika atapata kosa lisilo la kipekee vitu vya hifadhidata.

Lakini ikiwa wote wawili watajaribu kutekeleza CREATE TEMPORARY TABLE x, basi wote wawili wataifanya kwa kawaida, na kila mtu atapata nakala yako meza. Na hakutakuwa na kitu cha kawaida kati yao.

"Kujiharibu" wakati wa kukata

Wakati uunganisho umefungwa, meza zote za muda zinafutwa moja kwa moja, hivyo kwa manually DROP TABLE x hakuna maana ila...

Ikiwa unafanya kazi kupitia pgbouncer katika hali ya muamala, basi hifadhidata inaendelea kuamini kwamba uunganisho huu bado unafanya kazi, na ndani yake meza hii ya muda bado ipo.

Kwa hivyo, kujaribu kuunda tena, kutoka kwa unganisho tofauti hadi pgbouncer, itasababisha hitilafu. Lakini hii inaweza kuzuiwa kwa kutumia CREATE TEMPORARY TABLE IF NOT EXISTS x.

Kweli, ni bora kutofanya hivyo, kwa sababu basi unaweza "ghafla" kupata data iliyobaki kutoka kwa "mmiliki wa awali". Badala yake, ni bora zaidi kusoma mwongozo na kuona kwamba wakati wa kuunda meza inawezekana kuongeza ON COMMIT DROP - yaani, wakati shughuli inakamilika, meza itafutwa moja kwa moja.

Kutorudia

Kwa sababu ni za muunganisho maalum tu, majedwali ya muda hayajaigwa. Lakini hii inaondoa hitaji la kurekodi data mara mbili katika lundo + WAL, kwa hivyo INGIA/USASISHA/FUTA ndani yake ni haraka zaidi.

Lakini kwa kuwa meza ya muda bado ni meza "karibu ya kawaida", haiwezi kuundwa kwenye replica ama. Angalau kwa sasa, ingawa kiraka kinacholingana kimekuwa kikizunguka kwa muda mrefu.

1.2. JEDWALI LISILOONDOLEWA

Lakini unapaswa kufanya nini, kwa mfano, ikiwa una aina fulani ya mchakato mbaya wa ETL ambao hauwezi kutekelezwa ndani ya shughuli moja, lakini bado unayo. pgbouncer katika hali ya muamala? ..

Au mtiririko wa data ni mkubwa sana Hakuna kipimo data cha kutosha kwenye muunganisho mmoja kutoka kwa hifadhidata (soma, mchakato mmoja kwa kila CPU)? ..

Au shughuli zingine zinaendelea kwa usawa katika mahusiano tofauti?..

Kuna chaguo moja tu hapa - tengeneza jedwali lisilo la muda kwa muda. Pun, ndio. Hiyo ni:

iliunda majedwali "yangu mwenyewe" yenye majina ya nasibu ya kiwango cha juu zaidi ili isiingiliane na mtu yeyote
Dondoo: aliwajaza na data kutoka chanzo cha nje
Kubadilisha: imebadilishwa, imejazwa katika sehemu muhimu za kuunganisha
mzigo: ilimimina data iliyo tayari kwenye majedwali lengwa
imefuta meza "zangu".

Na sasa - kuruka katika marashi. Kwa kweli, maandishi yote katika PostgreSQL hufanyika mara mbili - kwanza katika WAL, kisha kwenye miili ya meza/index. Haya yote yanafanywa ili kusaidia ACID na kusahihisha mwonekano wa data kati ya COMMIT'nutty na ROLLBACK'null shughuli.

Lakini hatuhitaji hii! Tuna mchakato mzima Labda ilifanikiwa kabisa au haikuwa hivyo.. Haijalishi kutakuwa na shughuli ngapi za kati - hatupendezwi na "kuendeleza mchakato kutoka katikati," haswa wakati haijulikani wazi ilikuwa wapi.

Ili kufanya hivyo, watengenezaji wa PostgreSQL, nyuma katika toleo la 9.1, walianzisha kitu kama vile Jedwali LISILOGULIWA:

Kwa dalili hii, meza imeundwa kama haijafunguliwa. Data iliyoandikwa kwa majedwali ambayo hayajasajiliwa haipitii kwenye logi ya kuandika mbele (ona Sura ya 29), na kusababisha majedwali kama haya fanya kazi haraka kuliko kawaida. Hata hivyo, hawana kinga ya kushindwa; ikiwa seva itashindwa au kuzima kwa dharura, jedwali ambalo halijasajiliwa kupunguzwa kiotomatiki. Zaidi ya hayo, yaliyomo kwenye jedwali ambalo halijasajiliwa haijaigwa kwa seva za watumwa. Faharasa zozote zilizoundwa kwenye jedwali ambalo halijasajiliwa huondolewa kiotomatiki.

Kwa ufupi, itakuwa haraka sana, lakini seva ya hifadhidata "ikianguka", itakuwa mbaya. Lakini hii hufanyika mara ngapi, na je, mchakato wako wa ETL unajua jinsi ya kusahihisha hii kwa usahihi "kutoka katikati" baada ya "kuhuisha" hifadhidata?

Ikiwa sivyo, na kesi hapo juu ni sawa na yako, tumia UNLOGGED, lakini kamwe usiwezeshe sifa hii kwenye jedwali halisi, data ambayo ni muhimu kwako.

1.3. JUU YA KUJITOA { FUTA SAFU | DROP}

Muundo huu hukuruhusu kutaja tabia otomatiki wakati shughuli imekamilika wakati wa kuunda jedwali.

juu ya ON COMMIT DROP Tayari niliandika hapo juu, inazalisha DROP TABLE, lakini na ON COMMIT DELETE ROWS hali ni ya kuvutia zaidi - inazalishwa hapa TRUNCATE TABLE.

Kwa kuwa miundombinu yote ya kuhifadhi maelezo ya meta ya meza ya muda ni sawa na ile ya meza ya kawaida, basi. Uumbaji wa mara kwa mara na kufuta meza za muda husababisha "uvimbe" mkali wa meza za mfumo pg_class, pg_attribute, pg_attrdef, pg_depend,...

Sasa fikiria kuwa una mfanyakazi kwenye uunganisho wa moja kwa moja kwenye hifadhidata, ambayo inafungua shughuli mpya kila sekunde, inaunda, inajaza, inashughulikia na kufuta meza ya muda ... Kutakuwa na ziada ya takataka iliyokusanywa kwenye meza za mfumo, na hii itasababisha breki za ziada kwa kila operesheni.

Kwa ujumla, usifanye hivi! Katika kesi hii ni ufanisi zaidi CREATE TEMPORARY TABLE x ... ON COMMIT DELETE ROWS iondoe kwenye mzunguko wa manunuzi - basi kwa mwanzo wa kila shughuli mpya meza tayari itakuwepo (hifadhi simu CREATE), lakini itakuwa tupu, Shukrani kwa TRUNCATE (pia tulihifadhi simu yake) wakati wa kukamilisha muamala uliopita.

1.4. LIKE...PAMOJA...

Nilitaja hapo mwanzo kuwa moja ya kesi za kawaida za utumiaji wa jedwali la muda ni aina tofauti za uagizaji - na msanidi programu anakili kwa uchovu-kubandika orodha ya sehemu za jedwali linalolengwa katika tamko la muda wake ...

Lakini uvivu ni injini ya maendeleo! Ndiyo maana tengeneza jedwali jipya "kulingana na sampuli" inaweza kuwa rahisi zaidi:

CREATE TEMPORARY TABLE import_table(
  LIKE target_table
);

Kwa kuwa unaweza kutoa data nyingi kwenye jedwali hili, kuitafuta hakutakuwa haraka. Lakini kuna suluhisho la jadi kwa hili - faharisi! Na, ndio, jedwali la muda pia linaweza kuwa na vielelezo.

Kwa kuwa, mara nyingi, faharisi zinazohitajika zinapatana na faharisi za meza inayolengwa, unaweza kuandika tu LIKE target_table INCLUDING INDEXES.

Ikiwa unahitaji pia DEFAULT-maadili (kwa mfano, kujaza maadili ya msingi), unaweza kutumia LIKE target_table INCLUDING DEFAULTS. Au kwa urahisi - LIKE target_table INCLUDING ALL - nakala chaguo-msingi, faharisi, vikwazo,...

Lakini hapa unahitaji kuelewa kwamba ikiwa umeunda ingiza jedwali mara moja na faharasa, basi data itachukua muda mrefu kupakiwakuliko ikiwa unajaza kila kitu kwanza, na kisha tu kukunja faharisi - angalia jinsi inavyofanya hivi kama mfano pg_dampo.

Kwa ujumla, RTFM!

2. Jinsi ya kuandika?

Acha niseme tu - itumie COPY- mtiririko badala ya "pakiti" INSERT, kuongeza kasi mara kwa mara. Unaweza hata moja kwa moja kutoka kwa faili iliyotengenezwa awali.

3. Jinsi ya kusindika?

Kwa hivyo, wacha utangulizi wetu uonekane kama hii:

una jedwali na data ya mteja iliyohifadhiwa kwenye hifadhidata yako Rekodi za 1M
kila siku mteja anakutumia mpya "picha" kamili
kutokana na uzoefu unajua hilo mara kwa mara hakuna rekodi zaidi ya 10K zinabadilishwa

Mfano wa kawaida wa hali kama hiyo ni Msingi wa KLADR - kuna anwani nyingi kwa jumla, lakini katika kila upakiaji wa kila wiki kuna mabadiliko machache sana (kubadilisha jina la makazi, kuchanganya mitaa, kuonekana kwa nyumba mpya) hata kwa kiwango cha kitaifa.

3.1. Algorithm kamili ya maingiliano

Kwa unyenyekevu, wacha tuseme kwamba hauitaji hata kupanga upya data - kuleta jedwali katika fomu inayotaka, ambayo ni:

kufuta kila kitu ambacho hakipo tena
kuburudisha kila kitu ambacho tayari kilikuwepo na kinahitaji kusasishwa
ingiza kila kitu ambacho hakijatokea bado

Kwa nini shughuli zifanyike kwa utaratibu huu? Kwa sababu hivi ndivyo saizi ya meza itakua kidogo (kumbuka MVCC!).

FUTA KUTOKA KWA dst

Hapana, bila shaka unaweza kuendelea na shughuli mbili tu:

kufuta (DELETE) kila kitu kwa ujumla
ingiza yote kutoka kwa picha mpya

Lakini wakati huo huo, shukrani kwa MVCC, Ukubwa wa meza itaongezeka mara mbili! Kupata picha za +1M za rekodi kwenye jedwali kutokana na sasisho la 10K ni jambo la lazima sana...

TRUNCATE dst

Msanidi programu mwenye uzoefu zaidi anajua kwamba kompyuta kibao nzima inaweza kusafishwa kwa bei nafuu:

wazi (TRUNCATE) meza nzima
ingiza yote kutoka kwa picha mpya

Mbinu ni ya ufanisi, wakati mwingine inatumika kabisa, lakini kuna tatizo... Tutaongeza rekodi za 1M kwa muda mrefu, kwa hivyo hatuwezi kumudu kuacha meza tupu kwa muda wote huu (kama itatokea bila kuifunga katika shughuli moja).

Inamaanisha:

tunaanza shughuli ya muda mrefu
TRUNCATE inalazimisha Ufikiaji wa Kipekee-kuzuia
tunafanya uingizaji kwa muda mrefu, na kila mtu mwingine kwa wakati huu hawezi hata SELECT

Kuna kitu hakiendi sawa...

Alter TABLE... JINA TENA... / DROP TABLE...

Njia mbadala ni kujaza kila kitu kwenye jedwali mpya tofauti, na kisha uipe jina tena badala ya ile ya zamani. Vitu vichache vibaya:

bado pia Ufikiaji wa Kipekee, ingawa muda mfupi sana
mipango/takwimu zote za hoja za jedwali hili zinawekwa upya, haja ya kukimbia ANALYSE
funguo zote za kigeni zimevunjwa (FK) kwenye meza

Kulikuwa na kiraka cha WIP kutoka kwa Simon Riggs ambacho kilipendekeza kutengeneza ALTER-operesheni ya kuchukua nafasi ya mwili wa meza kwenye kiwango cha faili, bila kugusa takwimu na FK, lakini haikukusanya akidi.

FUTA, SASISHA, WEKA

Kwa hiyo, tunatatua chaguo lisilo la kuzuia la shughuli tatu. Karibu tatu ... Jinsi ya kufanya hivyo kwa ufanisi zaidi?

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

Katika KLADR sawa, rekodi zote zilizobadilishwa lazima ziendeshwe kwa njia ya uchakataji - kusawazishwa, maneno muhimu kuangaziwa, na kupunguzwa kwa miundo inayohitajika. Lakini unajuaje - nini hasa kilibadilikabila kutatiza msimbo wa maingiliano, bila kugusa hata kidogo?

Ikiwa tu mchakato wako una ufikiaji wa kuandika wakati wa kusawazisha, basi unaweza kutumia kichochezi ambacho kitakusanya mabadiliko yote kwa ajili yetu:

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

Sasa tunaweza kutumia vichochezi kabla ya kuanza kusawazisha (au kuwawezesha kupitia 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();

Na kisha tunaondoa kwa utulivu mabadiliko yote tunayohitaji kutoka kwa meza za logi na kuziendesha kupitia washughulikiaji wa ziada.

3.3. Inaleta Seti Zilizounganishwa

Hapo juu tulizingatia kesi wakati miundo ya data ya chanzo na lengwa ni sawa. Lakini vipi ikiwa upakiaji kutoka kwa mfumo wa nje una umbizo tofauti na muundo wa hifadhi katika hifadhidata yetu?

Wacha tuchukue kama mfano uhifadhi wa wateja na akaunti zao, chaguo la kawaida la "wengi-kwa-moja":

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

Lakini upakuaji kutoka kwa chanzo cha nje huja kwetu kwa njia ya "yote kwa moja":

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

Ni wazi, data ya mteja inaweza kunakiliwa katika toleo hili, na rekodi kuu ni "akaunti":

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

Kwa mfano, tutaingiza data yetu ya jaribio, lakini kumbuka - COPY ufanisi zaidi!

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

Kwanza, hebu tuangazie hizo "mikato" ambayo "ukweli" wetu unarejelea. Kwa upande wetu, ankara hurejelea wateja:

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

Ili kuhusisha akaunti na vitambulisho vya mteja kwa usahihi, tunahitaji kwanza kujua au kutengeneza vitambulisho hivi. Wacha tuongeze uwanja chini yao:

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

Hebu tutumie mbinu ya ulandanishi ya jedwali iliyoelezwa hapo juu pamoja na marekebisho madogo - hatutasasisha au kufuta chochote katika jedwali lengwa, kwa sababu tunaagiza wateja "ambatisha-pekee":

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

Kwa kweli, kila kitu kiko ndani invoice_import Sasa tumejaza sehemu ya mawasiliano client_id, ambayo tutaingiza ankara.

Chanzo: mapenzi.com

DBA: panga maingiliano na uagizaji kwa ustadi