DBA: kompetentno organizirati sinhronizacije i uvoze

Za složenu obradu velikih skupova podataka (razl ETL procesi: uvoz, konverzije i sinhronizacija sa eksternim izvorom) često postoji potreba privremeno "zapamti" i odmah brzo obraditi nešto obimno.

Tipičan zadatak ove vrste obično zvuči otprilike ovako: "Upravo ovdje računovodstvo iskrcano iz banke klijenta posljednje primljene uplate, morate ih brzo učitati na web stranicu i povezati sa svojim računima.”

Ali kada obim ovog „nečega“ počne da se meri stotinama megabajta, a servis mora da nastavi da radi sa bazom podataka 24x7, javljaju se mnoge nuspojave koje će vam uništiti život.

Da biste se pozabavili njima u PostgreSQL-u (i ne samo u njemu), možete koristiti neke optimizacije koje će vam omogućiti da sve obradite brže i uz manju potrošnju resursa.

1. Gdje poslati?

Prvo, odlučimo gdje možemo učitati podatke koje želimo "obraditi".

1.1. Privremene tabele (TEMPORARY TABLE)

U principu, za PostgreSQL privremene tabele su iste kao i sve druge. Stoga sujeverja poput "Sve tamo je pohranjeno samo u memoriji, i može završiti". Ali postoji i nekoliko značajnih razlika.

Vaš vlastiti “imenski prostor” za svaku vezu s bazom podataka

Ako se dvije veze pokušaju povezati u isto vrijeme CREATE TABLE x, onda će neko sigurno dobiti greška nejedinstvenosti objekti baze podataka.

Ali ako oboje pokušaju izvršiti CREATE TEMPORARY TABLE x, onda će oboje normalno raditi, i svi će dobiti tvoj primjerak stolovi. I između njih neće biti ništa zajedničko.

"Samouništenje" prilikom prekida veze

Kada se veza zatvori, sve privremene tabele se automatski brišu, dakle ručno DROP TABLE x nema svrhe osim...

Ako radite kroz pgbouncer u transakcijskom modu, onda baza podataka nastavlja vjerovati da je ova veza još uvijek aktivna, au njoj ova privremena tablica još uvijek postoji.

Stoga, pokušaj ponovnog kreiranja, s druge veze na pgbouncer, rezultirat će greškom. Ali ovo se može zaobići upotrebom CREATE TEMPORARY TABLE IF NOT EXISTS x.

Istina, bolje je to ipak ne raditi, jer tada možete "odjednom" tamo pronaći podatke koji su ostali od "prethodnog vlasnika". Umjesto toga, puno je bolje pročitati priručnik i vidjeti da je prilikom kreiranja tabele moguće dodati ON COMMIT DROP - odnosno kada se transakcija završi, tabela će se automatski izbrisati.

Nereplikacija

Budući da pripadaju samo određenoj vezi, privremene tablice se ne repliciraju. Ali ovo eliminiše potrebu za dvostrukim snimanjem podataka u hrpi + WAL, pa je INSERT/UPDATE/DELETE u njega znatno brži.

Ali pošto je privremena tabela i dalje „skoro obična“ tabela, ne može se kreirati ni na replici. Barem za sada, iako odgovarajući flaster kruži već duže vrijeme.

1.2. UNLOGGED TABLE

Ali šta biste trebali učiniti, na primjer, ako imate neku vrstu glomaznog ETL procesa koji se ne može implementirati unutar jedne transakcije, ali još uvijek imate pgbouncer u transakcijskom modu? ..

Ili je protok podataka toliko velik da Nema dovoljno propusnog opsega na jednoj vezi iz baze podataka (čitaj, jedan proces po CPU-u)?..

Ili su neke operacije u toku asinhrono u različitim vezama?..

Ovde postoji samo jedna opcija - privremeno kreirati neprivremenu tabelu. Igra igre, da. To je:

• kreirao „svoje“ tabele sa maksimalno nasumičnim imenima kako se ne bi ukrštale ni sa kim
• ekstrakt: ispunio ih podacima iz vanjskog izvora
• Transformirati: pretvoreno, popunjena ključna polja za povezivanje
• opterećenje: sipa gotove podatke u ciljne tabele
• obrisali "moje" tabele

A sada - muva u masti. Zapravo, sva upisivanja u PostgreSQL se dešavaju dvaput - prvi u WAL-u, zatim u tijela tablice/indeksa. Sve ovo je urađeno kako bi se podržala ACID i ispravna vidljivost podataka između COMMIT'zabrinut i ROLLBACK'nulte transakcije.

Ali nama ovo ne treba! Imamo ceo proces Ili je bilo potpuno uspješno ili nije.. Nije bitno koliko će međutransakcija biti - nismo zainteresovani da „nastavljamo proces od sredine“, pogotovo kada nije jasno gde je to bilo.

Da bi to učinili, programeri PostgreSQL-a, još u verziji 9.1, predstavili su nešto kao UNLOGGED tables:

Sa ovom indikacijom, tabela se kreira kao neregistrovana. Podaci upisani u neregistrovane tabele ne prolaze kroz dnevnik upisa unapred (pogledajte Poglavlje 29), što dovodi do toga da takve tabele rade mnogo brže nego inače. Međutim, oni nisu imuni na neuspjeh; u slučaju kvara servera ili gašenja u nuždi, neregistrovana tabela automatski skraćeno. Dodatno, sadržaj neregistrovane tabele nije replicirano na slave servere. Svi indeksi kreirani na neregistrovanoj tabeli automatski postaju neregistrovani.

Ukratko, to će biti mnogo brže, ali ako server baze podataka “padne”, to će biti neugodno. Ali koliko često se to dešava i da li vaš ETL proces zna kako to ispravno ispraviti “iz sredine” nakon “revitalizacije” baze podataka?..

Ako nije, a gornji slučaj je sličan vašem, koristite UNLOGGEDali nikad nemojte omogućiti ovaj atribut na stvarnim tablicama, podaci iz kojih su Vam dragi.

1.3. ON COMMIT { IZBRIŠI REDOVE | DROP}

Ova konstrukcija vam omogućava da navedete automatsko ponašanje kada se transakcija završi prilikom kreiranja tabele.

na ON COMMIT DROP Već sam gore napisao, generiše DROP TABLE, ali sa ON COMMIT DELETE ROWS situacija je interesantnija - ona se generiše ovde TRUNCATE TABLE.

Budući da je cjelokupna infrastruktura za pohranjivanje meta-opisa privremene tablice potpuno ista kao i obične tablice, onda Konstantno kreiranje i brisanje privremenih tabela dovodi do ozbiljnog "oticanja" sistemskih tabela pg_class, pg_attribute, pg_attrdef, pg_depend,…

Sada zamislite da imate radnika na direktnoj vezi sa bazom podataka, koji svake sekunde otvara novu transakciju, kreira, popunjava, obrađuje i briše privremenu tabelu... U sistemskim tabelama će se nakupiti višak smeća i ovo će uzrokovati dodatne kočnice za svaku operaciju.

Generalno, nemojte ovo raditi! U ovom slučaju je mnogo efikasniji CREATE TEMPORARY TABLE x ... ON COMMIT DELETE ROWS izbacite ga iz ciklusa transakcije - tada su do početka svake nove transakcije tabele već postojat će (sačuvaj poziv CREATE), ali će biti prazan, Hvala za TRUNCATE (također smo sačuvali njegov poziv) prilikom završetka prethodne transakcije.

1.4. LIKE...UKLJUČUJUĆI...

Na početku sam spomenuo da je jedan od tipičnih slučajeva upotrebe privremenih tabela razne vrste uvoza - a programer umorno kopira-paste listu polja ciljne tabele u deklaraciju svog privremenog...

Ali lenjost je motor napretka! Zbog toga kreirajte novu tabelu "na osnovu uzorka" može biti mnogo jednostavnije:

CREATE TEMPORARY TABLE import_table(
  LIKE target_table
);

Budući da tada možete generirati mnogo podataka u ovu tabelu, pretraživanje kroz nju nikada neće biti brzo. Ali postoji tradicionalno rješenje za ovo - indeksi! i da, privremena tabela takođe može imati indekse.

Pošto se, često, traženi indeksi poklapaju sa indeksima ciljne tabele, možete jednostavno pisati LIKE target_table INCLUDING INDEXES.

Ako i vama treba DEFAULT-vrijednosti (na primjer, za popunjavanje vrijednosti primarnog ključa), možete koristiti LIKE target_table INCLUDING DEFAULTS. Ili jednostavno - LIKE target_table INCLUDING ALL — kopira zadane postavke, indekse, ograničenja,...

Ali ovdje morate razumjeti da ako ste kreirali odmah uvezite tabelu sa indeksima, tada će podacima trebati duže da se učitajunego ako prvo sve popuniš, pa tek onda smotaš indekse - pogledaj na primjeru kako to radi pg_dump.

Ukratko, RTFM!

2. Kako pisati?

Samo da kažem - iskoristite COPY-tok umjesto "pakovati" INSERT, ubrzanje na momente. Možete čak i direktno iz unaprijed generiranog fajla.

3. Kako obraditi?

Dakle, neka naš uvod izgleda otprilike ovako:

• imate tabelu sa podacima o klijentima pohranjenim u vašoj bazi podataka 1M zapisa
• svaki dan vam klijent pošalje novu puna "slika"
• iz iskustva znate da s vremena na vrijeme ne mijenja se više od 10K zapisa

Klasičan primjer takve situacije je baza KLADR — ukupno je mnogo adresa, ali u svakom nedeljnom upload-u ima vrlo malo promena (preimenovanje naselja, kombinovanje ulica, izgled novih kuća) čak i na nacionalnom nivou.

3.1. Potpuni algoritam sinhronizacije

Radi jednostavnosti, recimo da ne morate čak ni restrukturirati podatke - samo dovedite tabelu u željeni oblik, to jest:

• ukloniti sve sto vise ne postoji
• nadogradnja sve što je već postojalo i što je potrebno ažurirati
• umetnite sve sto se jos nije desilo

Zašto bi se operacije radile ovim redoslijedom? Zato što će na ovaj način veličina stola minimalno rasti (zapamtite MVCC!).

IZBRIŠI IZ dst

Ne, naravno da možete proći sa samo dvije operacije:

• ukloniti (DELETE) sve općenito
• umetnite sve sa nove slike

Ali u isto vrijeme, zahvaljujući MVCC-u, Veličina stola će se povećati tačno dva puta! Dobivanje +1M slika zapisa u tabeli zbog ažuriranja od 10K je tako-tako redundantnost...

TRUNCATE dst

Iskusniji programer zna da se cijeli tablet može očistiti prilično jeftino:

• jasno (TRUNCATE) cijelu tabelu
• umetnite sve sa nove slike

Metoda je efikasna, ponekad sasvim primenljivo, ali postoji problem... Mi ćemo još dugo dodavati 1M zapisa, tako da ne možemo priuštiti da ostavimo tabelu praznu sve ovo vrijeme (kao što će se dogoditi bez umotavanja u jednu transakciju).

Što znači:

• počinjemo dugotrajnu transakciju
• TRUNCATE nameće Pristup ekskluzivno-blokiranje
• umetanje radimo dugo, a svi ostali u ovom trenutku ne mogu čak SELECT

Nesto ne ide kako treba...

PROMIJENI TABELU… PREIMENUJ… / ISPUSTI TABELU…

Alternativa je da sve popunite u posebnu novu tabelu, a zatim je jednostavno preimenujete umjesto stare. Par gadnih sitnica:

• još uvek Pristup ekskluzivno, iako znatno manje vremena
• svi planovi upita/statistika za ovu tabelu su resetirani, potrebno je pokrenuti ANALYZE
• svi strani ključevi su pokvareni (FK) za sto

Postojala je WIP zakrpa od Simona Riggsa koja je sugerirala izradu ALTER-operacija zamene tela tabele na nivou fajla, bez dodirivanja statistike i FK, ali nije prikupio kvorum.

IZBRIŠI, AŽURIRAJ, UMETNI

Dakle, odlučujemo se o neblokirajućoj opciji od tri operacije. Skoro tri... Kako to najefikasnije uraditi?

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

U istom KLADR-u, svi izmenjeni zapisi moraju se dodatno proći kroz naknadnu obradu - normalizovani, istaknute ključne reči i svedene na potrebne strukture. Ali kako znaš - šta se tačno promenilobez kompliciranja koda za sinhronizaciju, idealno bez dodirivanja?

Ako samo vaš proces ima pristup za pisanje u vrijeme sinhronizacije, tada možete koristiti okidač koji će prikupiti sve promjene za nas:

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

Sada možemo primijeniti okidače prije početka sinkronizacije (ili ih omogućiti putem 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();

A onda mirno izvlačimo sve promjene koje su nam potrebne iz tablica dnevnika i izvodimo ih kroz dodatne rukovaoce.

3.3. Uvoz povezanih skupova

Gore smo razmatrali slučajeve kada su strukture podataka izvora i odredišta iste. Ali šta ako otpremanje sa eksternog sistema ima format koji se razlikuje od strukture skladištenja u našoj bazi podataka?

Uzmimo kao primjer skladištenje klijenata i njihovih računa, klasičnu opciju „više na jedan“:

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

Ali preuzimanje iz vanjskog izvora dolazi nam u obliku "sve u jednom":

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

Očigledno je da se u ovoj verziji podaci o klijentima mogu duplicirati, a glavni zapis je "račun":

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

Za model ćemo jednostavno umetnuti naše testne podatke, ali zapamtite - COPY efikasnije!

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

Prvo, hajde da istaknemo one „rezove“ na koje se odnose naše „činjenice“. U našem slučaju, fakture se odnose na kupce:

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

Da bismo ispravno povezali račune s ID-ovima kupaca, prvo moramo saznati ili generirati ove identifikatore. Dodajmo polja ispod njih:

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

Koristimo gore opisanu metodu sinkronizacije tablice s malom izmjenom - nećemo ažurirati ili izbrisati ništa u ciljnoj tablici, jer uvozimo klijente "samo za dodavanje":

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

Zapravo, sve je unutra invoice_import Sada imamo popunjeno polje za kontakt client_id, uz koje ćemo umetnuti račun.

izvor: www.habr.com