DBA: konzekerani mwaluso zolumikizirana ndi zotumiza kunja

Kwa makonzedwe ovuta a ma data akuluakulu (osiyana Zotsatira za ETL: kuitanitsa, kutembenuka ndi kugwirizanitsa ndi gwero lakunja) nthawi zambiri pamakhala chosowa kwa kanthawi "kumbukirani" ndipo nthawi yomweyo ikonza chinthu voluminous.

Ntchito yodziwika bwino yamtunduwu nthawi zambiri imamveka motere: "Pomwepo dipatimenti yowerengera ndalama yotulutsidwa kuchokera ku banki ya kasitomala ndalama zomaliza zomwe mwalandira, muyenera kuziyika pawebusayiti mwachangu ndikuzilumikiza kuakaunti yanu. ”

Koma pamene voliyumu ya "chinachake" ichi iyamba kuyeza mazana a megabytes, ndipo utumiki uyenera kupitiriza kugwira ntchito ndi 24x7 Nawonso achichepere, zotsatira zoyipa zambiri zimachitika zomwe zingawononge moyo wanu.

Kuti muthane nawo mu PostgreSQL (osati momwemo mokha), mutha kugwiritsa ntchito kukhathamiritsa komwe kumakupatsani mwayi wokonza chilichonse mwachangu komanso osagwiritsa ntchito zinthu zochepa.

1. Kutumiza kuti?

Choyamba, tiyeni tisankhe komwe tingakweze zomwe tikufuna "kukonza".

1.1. Matebulo osakhalitsa (TEMPORARY TABLE)

M'malo mwake, matebulo osakhalitsa a PostgreSQL ndi ofanana ndi ena aliwonse. Choncho, zikhulupiriro ngati “Chilichonse chomwe chilipo chimasungidwa m’chikumbukiro, ndipo chimatha”. Koma palinso zosiyana zingapo.

"Namespace" yanu pa kulumikizana kulikonse ku database

Ngati maulumikizano awiri amayesa kulumikizana nthawi imodzi CREATE TABLE x, ndiye kuti wina adzapezadi cholakwika chosakhala chapadera zinthu za database.

Koma ngati onse amayesa kuchita CREATE TEMPORARY TABLE x, ndiye onse adzachita bwino, ndipo aliyense adzapeza kope lanu matebulo. Ndipo sipadzakhala chofanana pakati pawo.

"Kudziwononga" podula

Pamene kugwirizana chatsekedwa, onse osakhalitsa matebulo basi zichotsedwa, kotero pamanja DROP TABLE x palibe vuto koma...

Ngati mukugwira ntchito pgbouncer mu transaction mode, ndiye kuti databaseyo ikupitiriza kukhulupirira kuti kugwirizana uku kukugwirabe ntchito, ndipo mkati mwake tebulo losakhalitsali likadalipo.

Chifukwa chake, kuyesa kuyipanganso, kuchokera ku kulumikizana kosiyana kupita ku pgbouncer, kumabweretsa cholakwika. Koma izi zitha kupewedwa pogwiritsa ntchito CREATE TEMPORARY TABLE IF NOT EXISTS x.

Zoona, ndi bwino kuti musachite izi, chifukwa ndiye mukhoza "mwadzidzidzi" kupeza deta yomwe yatsala kuchokera kwa "mwiniwake wakale". M'malo mwake, ndi bwino kuwerenga bukuli ndikuwona kuti popanga tebulo ndizotheka kuwonjezera ON COMMIT DROP - ndiye kuti, ntchito ikatha, tebulo lidzachotsedwa.

Kusabwerezabwereza

Chifukwa amangolumikizana ndi gulu linalake, matebulo osakhalitsa samapangidwanso. Koma izi zimathetsa kufunika kawiri kujambula deta mu mulu + WAL, kotero INSERT/UPDATE/FUTA mmenemo ndiyofulumira kwambiri.

Koma popeza tebulo lakanthawi likadali tebulo "lapafupifupi", silingapangidwenso pachithunzichi. Osachepera pano, ngakhale chigamba chofananiracho chakhala chikuzungulira kwa nthawi yayitali.

1.2. TABELO OSALOGGWA

Koma muyenera kuchita chiyani, mwachitsanzo, ngati muli ndi mtundu wina wazovuta za ETL zomwe sizingachitike pakangochitika kamodzi, koma pgbouncer mu transaction mode? ..

Kapena kuyenda kwa data kumakhala kwakukulu kwambiri Palibe bandwidth yokwanira pa kulumikizana kumodzi kuchokera ku database (werengani, njira imodzi pa CPU)?..

Kapena maopareshoni ena akuchitika asynchronously m'malo osiyanasiyana?..

Pali njira imodzi yokha apa - pangani kwakanthawi tebulo losakhalitsa. Pun, pa. Ndiko kuti:

• adapanga matebulo "anga" okhala ndi mayina osasinthika kuti asadutse aliyense
• Chotsani: adawadzaza ndi data yochokera kunja
• Sintha: otembenuzidwa, odzazidwa m'magawo akuluakulu olumikiza
• katundu: kutsanulira deta yokonzeka m'matebulo omwe mukufuna
• adachotsa "zanga" matebulo

Ndipo tsopano - ntchentche mu mafuta. Pamenepo, zonse zolembedwa mu PostgreSQL zimachitika kawiri - choyamba mu WAL, kenako kulowa mu tebulo/ma index. Zonsezi zimachitika kuti zithandizire ACID ndikuwongolera mawonekedwe a data pakati COMMIT'nutty ndi ROLLBACK'null transactions.

Koma sitikufuna izi! Tili ndi ndondomeko yonse Kaya zinali zopambana kwathunthu kapena sizinali choncho.. Ziribe kanthu kuti padzakhala ndalama zingati zapakatikati - sitikufuna "kupitiriza ndondomekoyi kuyambira pakati," makamaka pamene sizikudziwika komwe kunali.

Kuti muchite izi, oyambitsa PostgreSQL, kumbuyo kwa mtundu wa 9.1, adayambitsa zinthu monga ZOSAVUTIKA matebulo:

Ndi chisonyezo ichi, tebulo amapangidwa ngati unloggged. Zomwe zimalembedwera pamatebulo osatsegula sizidutsa pa chipika cholemberatu (onani Mutu 29), gwirani ntchito mwachangu kuposa nthawi zonse. Komabe, iwo salephera kulephera; ngati seva ikulephera kapena kuzimitsa mwadzidzidzi, tebulo losatsegulidwa zochepetsedwa zokha. Kuphatikiza apo, zomwe zili patsamba losatsegulidwa osabwerezedwa kwa ma seva. Ma index aliwonse omwe apangidwa patebulo losatsegulidwa amachotsedwa.

Mwachidule, idzakhala yachangu kwambiri, koma ngati seva ya database "igwa", sizikhala zosangalatsa. Koma izi zimachitika kangati, ndipo kodi njira yanu ya ETL imadziwa kuwongolera izi molondola "kuchokera pakati" pambuyo "kutsitsimutsa" nkhokwe?

Ngati sichoncho, ndipo vuto lomwe lili pamwambapa likufanana ndi lanu, gwiritsani ntchito UNLOGGEDkoma ayi osayambitsa izi pamatebulo enieni, zomwe zimakukondani.

1.3. ON ZOCHITIKA { FUTA MIZINDIKIRO | DONSE}

Kupanga uku kumakupatsani mwayi wofotokozera zomwe zimachitika zokha mukamaliza kupanga tebulo.

pa ON COMMIT DROP Ndalemba kale pamwambapa, imapanga DROP TABLE,koma ndi ON COMMIT DELETE ROWS mkhalidwewu ndi wosangalatsa kwambiri - wapangidwa apa TRUNCATE TABLE.

Popeza zida zonse zosungiramo mafotokozedwe a meta osakhalitsa ndizofanana ndendende ndi tebulo lanthawi zonse, ndiye Kulengedwa kosalekeza ndi kuchotsedwa kwa matebulo osakhalitsa kumabweretsa "kutupa" kwakukulu kwa matebulo a dongosolo pg_class, pg_attribute, pg_attrdef, pg_depend,…

Tsopano taganizirani kuti muli ndi wogwira ntchito pa kugwirizana kwachindunji ku database, yomwe imatsegula malonda atsopano sekondi iliyonse, imapanga, imadzaza, imayendetsa ndikuchotsa tebulo losakhalitsa ... izi zidzapangitsa mabuleki owonjezera pa ntchito iliyonse.

Kawirikawiri, musachite izi! Pankhaniyi ndizothandiza kwambiri CREATE TEMPORARY TABLE x ... ON COMMIT DELETE ROWS zichotseni pakusinthana - ndiye poyambira pazochitika zatsopano zilizonse matebulo ali kale adzakhalapo (sungani foni CREATE), koma adzakhala opanda kanthu, chifukwa cha TRUNCATE (tidasunganso kuyitana kwake) pomaliza ntchito yapitayi.

1.4. MONGA...KUPHATIKIRA...

Ndidanenapo koyambirira kuti imodzi mwazinthu zomwe zimagwiritsidwa ntchito pamagome osakhalitsa ndi mitundu yosiyanasiyana ya zinthu zomwe zimachokera kunja - ndipo wopanga amakopera motopa ndikumata mndandanda wamagawo omwe akutsata kuti afotokoze kwakanthawi ...

Koma ulesi ndiye injini ya chitukuko! Ndichifukwa chake pangani tebulo latsopano "kutengera zitsanzo" zitha kukhala zophweka:

CREATE TEMPORARY TABLE import_table(
  LIKE target_table
);

Popeza mutha kupanga zambiri patebuloli, kusaka sikudzakhala mwachangu. Koma pali njira yachikhalidwe pa izi - ma index! Ndipo, inde, tebulo losakhalitsa likhozanso kukhala ndi ma index.

Popeza, nthawi zambiri, ma index omwe amafunikira amagwirizana ndi ma index a tebulo lomwe mukufuna, mutha kungolemba LIKE target_table INCLUDING INDEXES.

Ngati inunso muyenera DEFAULT-values ​​(mwachitsanzo, kudzaza makiyi oyambira), mutha kugwiritsa ntchito LIKE target_table INCLUDING DEFAULTS. Kapena mophweka - LIKE target_table INCLUDING ALL - makope osasintha, ma index, zopinga, ...

Koma apa muyenera kumvetsetsa kuti ngati mudapanga lowetsani tebulo nthawi yomweyo ndi ma index, ndiye kuti datayo idzatenga nthawi yayitali kuti muyikekuposa ngati mutadzaza zonse, ndiyeno pokhapo pindani ma index - onani momwe zimachitira izi monga chitsanzo pg_kuta.

Mwachidule, RTFM!

2. Kodi kulemba?

Ndiroleni ndingonena - gwiritsani ntchito COPY-kuyenda m'malo mwa "paketi" INSERT, mathamangitsidwe nthawi zina. Mukhoza ngakhale mwachindunji kuchokera chisanadze opangidwa wapamwamba.

3. Kodi pokonza?

Chifukwa chake, tiyeni tiwone mawu athu oyambira:

• muli ndi tebulo lomwe lili ndi data yamakasitomala yosungidwa munkhokwe yanu 1M mbiri
• tsiku lililonse kasitomala amakutumizirani yatsopano zonse "chithunzi"
• kuchokera muzochitikira mumadziwa kuti nthawi ndi nthawi zosaposa 10K zolemba zomwe zasinthidwa

Chitsanzo chodziwika bwino cha mkhalidwe wotero ndi Chithunzi cha KLADR - pali ma adilesi ambiri, koma pakukweza kwa mlungu uliwonse pali zosintha zochepa (kusinthidwa kwa malo okhala, kuphatikiza misewu, mawonekedwe a nyumba zatsopano) ngakhale pamlingo wadziko lonse.

3.1. Full synchronization aligorivimu

Kuti zikhale zosavuta, tinene kuti simukufunikanso kukonzanso deta - ingobweretsani tebulo mu mawonekedwe omwe mukufuna, ndiko kuti:

• chotsani chirichonse chomwe kulibenso
• zosintha zonse zomwe zidalipo kale ndipo ziyenera kusinthidwa
• onjezerani zonse zomwe sizinachitike

Chifukwa chiyani ntchitozo ziyenera kuchitidwa motere? Chifukwa umu ndi momwe kukula kwa tebulo kumakulira pang'ono (kumbukirani MVCC!).

FUTA KU dst

Ayi, mungathe kupirira ndi maopaleshoni awiri okha:

• chotsani (DELETE) chilichonse mwazonse
• onjezerani zonse kuchokera ku chithunzi chatsopano

Koma nthawi yomweyo, chifukwa cha MVCC, Kukula kwa tebulo kudzawonjezeka ndendende kawiri! Kupeza zithunzi + 1M zamarekodi patebulo chifukwa chakusintha kwa 10K ndikosowa kwambiri ...

TRUNCATE dst

Wopanga wodziwa zambiri amadziwa kuti piritsi lonse limatha kutsukidwa motsika mtengo:

• bwino (TRUNCATE) tebulo lonse
• onjezerani zonse kuchokera ku chithunzi chatsopano

Njira ndiyothandiza, nthawi zina zothandiza, koma pali vuto ... Tidzakhala tikuwonjezera zolemba za 1M kwa nthawi yaitali, kotero sitingakwanitse kusiya tebulo lopanda kanthu kwa nthawi yonseyi (monga momwe zidzachitikire popanda kuzikulunga muzochita kamodzi).

Zomwe zikutanthauza:

• tikuyamba ntchito yayitali
• TRUNCATE amakakamiza Access Exclusive-kutsekereza
• timalowetsamo kwa nthawi yayitali, ndi ena onse panthawiyi sindingathe ngakhale SELECT

China chake sichikuyenda bwino...

ALTER TABLE... TULENI TULANI… / DROP TABLE...

Njira ina ndikudzaza zonse mu tebulo latsopano, ndikungolitchulanso m'malo mwa yakaleyo. Tinthu tating'ono toyipa:

• pa pa Access Exclusive, ngakhale nthawi yochepa kwambiri
• Mafunso onse / ziwerengero za tebulo ili zakonzedwanso, muyenera kuthamanga ANALYZE
• makiyi onse akunja athyoka (FK) ku tebulo

Panali chigamba cha WIP chochokera kwa Simon Riggs chomwe chimalimbikitsa kupanga ALTER-Opaleshoni m'malo tebulo thupi pa wapamwamba mlingo, popanda kukhudza ziwerengero ndi FK, koma sanatolere quorum.

Chotsani, sinthani, lowetsani

Chifukwa chake, timakhazikika pazosankha zosatsekereza za ntchito zitatu. Pafupifupi atatu... Kodi mungachitire bwanji izi mogwira mtima?

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

Mu KLADR yomweyi, zolemba zonse zomwe zasinthidwa ziyenera kuyendetsedwanso pokonza pambuyo - zokhazikika, mawu osakira amawunikiridwa, ndikuchepetsedwa kukhala zofunikira. Koma mukudziwa bwanji - zomwe zidasinthapopanda kusokoneza kachidindo kalunzanitsidwe, popanda kuikhudza konse?

Ngati njira yanu yokhayo ili ndi mwayi wolembera panthawi yolumikizana, ndiye kuti mutha kugwiritsa ntchito choyambitsa chomwe chingatitengere zosintha zonse:

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

Tsopano titha kugwiritsa ntchito zoyambitsa tisanayambe kulunzanitsa (kapena kuwathandiza kudzera 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();

Kenako timachotsa mwakachetechete zosintha zonse zomwe timafunikira pamatebulo a logi ndikuziyendetsa kudzera mwa othandizira ena.

3.3. Kulowetsa Ma Seti Olumikizidwa

Pamwambapa tidaganizira nthawi zomwe magawo a data amachokera ndi kopita ali ofanana. Koma bwanji ngati kukweza kuchokera kudongosolo lakunja kuli ndi mawonekedwe osiyana ndi momwe amasungiramo mu database yathu?

Tiyeni titenge mwachitsanzo kusungirako makasitomala ndi maakaunti awo, njira yachidule ya "ambiri-to-one":

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

Koma kutsitsa kuchokera kunja kumabwera kwa ife mu mawonekedwe a "onse mumodzi":

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

Mwachiwonekere, deta yamakasitomala ikhoza kubwerezedwa mumtunduwu, ndipo mbiri yayikulu ndi "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

Mwachitsanzo, tingoyika zoyeserera zathu, koma kumbukirani - COPY bwino kwambiri!

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

Choyamba, tiyeni tiwunikire “zodulidwa” zomwe “zowona” zathu zimalozera. Kwa ife, ma invoice amatengera makasitomala:

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

Kuti mulumikizane bwino maakaunti ndi ma ID amakasitomala, choyamba tifunika kudziwa kapena kupanga zozindikiritsa izi. Tiyeni tiwonjezere minda pansi pawo:

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

Tiyeni tigwiritse ntchito njira yolumikizira tebulo yomwe yafotokozedwa pamwambapa ndikusintha pang'ono - sitisintha kapena kuchotsa chilichonse patebulo lomwe tikufuna, chifukwa timatumiza makasitomala "zowonjezera-zokha":

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

Kwenikweni, zonse zili mkati invoice_import Tsopano tadzaza malo olumikizana nawo client_id, yomwe tidzayika nayo invoice.

Source: www.habr.com