Pri zložitej spracovaní veľkých súborov dát (rôzne : importy, konverzie a synchronizácie s externým zdrojom) často nastáva potreba dočasne 'zapamätať' a rýchlo spracovať niečo objemné.
Typická úloha tohto druhu znie zvyčajne takto: 'Tu posledné prijaté platby, treba ich rýchlo nahrať na web a spojiť s účtami.'
Ale keď objem tohto 'niečoho' začína dosahovať stovky megabajtov a služba musí naďalej fungovať s databázou v režime 24×7, vzniká množstvo vedľajších efektov, ktoré vám znepríjemnia život.
Aby sme sa s nimi vysporiadali v PostgreSQL (a nielen tam), môžeme využiť niektoré možnosti optimalizácie, ktoré umožnia rýchlejšie spracovanie všetkého s menšou spotrebou zdrojov.
1. Kam nahrávať?
Najprv si určíme, kam môžeme nahrať dáta, ktoré chceme 'spracovať'.
1.1. Dočasné tabuľky (TEMPORARY TABLE)
V zásade sú dočasné tabuľky v PostgreSQL rovnaké ako akékoľvek iné tabuľky. Preto sú povrchné povesti ako 'tam sa všetko uchováva len v pamäti a tá môže skončiť.'Ale existujú aj niektoré kľúčové rozdiely.
Svoj 'namespace' pre každé pripojenie k DB
Ak sa dva pripojenia pokúsia súčasne vykonať CREATE TABLE x, tak niekto určite dostane chybu nejedinečnosti objektov DB.
Ale ak sa obaja pokúsia vykonať VYTVORIŤ DOČASNÁ TABUĽKA x, tak obaja to urobia bez problémov a každý dostane svoj vlastný exemplár tabuľky. A medzi nimi nebude nič spoločné.
'Sebazničenie' pri odpojení
Pri uzavretí pripojenia sa všetky dočasné tabuľky automaticky odstránia, preto 'manuálne' vykonávať DROP TABLE x nemá zmysel, okrem...
Ak pracujete cez pgbouncer v transakčnom režime, databáza síce naďalej považuje toto pripojenie za aktívne a dočasná tabuľka v ňom stále existuje.
Preto pokus o jej opätovné vytvorenie z iného pripojenia k pgbouncer povedie k chybe. Ale to sa dá obísť použítím VYTVOŘIT DOČASNOU TABULKU POKUD NEEXISTUJE x.
Pravda, ospravedlnenie, aby sa to nemalo robiť, pretože neskôr môžete 'náhodou' objaviť údaje zostávajúce od 'predchádzajúceho vlastníka'. Namiesto toho je oveľa lepšie prečítať si manuál a vidieť, že pri vytváraní tabuľky existuje možnosť dopísať PRI PRIHLÁSENÍ DROP — to znamená, že po skončení transakcie sa tabuľka automaticky odstráni.
Ne-archivácia
Pretože patrí iba k určitému pripojeniu, dočasné tabuľky sa nearchivujú. Namiesto toho to zbavuje potreby dvojakého zápisu dát do heap + WAL, takže INSERT/UPDATE/DELETE do nej sú podstatne rýchlejšie.
Ale pretože dočasná tabuľka je predsa len „takmer bežná“ tabuľka, ani na replikácii ju nie je možné vytvoriť. Aspoň zatiaľ, aj keď zodpovedajúci patch už dávno existuje.
1.2. Nezaznamenávané tabuľky (UNLOGGED TABLE)
Ale čo robiť, ak máte nejaký objemný ETL proces, ktorý nie je možné realizovať v rámci jednej transakcie, a máte pgbouncer v transakčnom režime?..
Alebo dátový tok je taký veľký, že kapacita jedného pripojenia k DB (t.j. jedného procesu na CPU) nie je dostatočná?.. Alebo časť operácií prebieha
asynchrónne v rôznych pripojeniach?.. Tu je len jedna možnosť —
dočasne vytvoriť ne-dočasnú tabuľku . Hračka, že? Takže:vytvoril „svoje“ tabuľky s maximálne náhodnými názvami, aby sa s nikým neprekrývali
- Extrahovať
- : nahral do nich dáta z externého zdrojaTransformovať
- : upravil, vyplnil kľúčové prepojené polia: preniesol pripravené dáta do cieľových tabuliek
- Loadvymazal „svoje“ tabuľky
- A teraz — lyžica dechtu. V podstate,
celý zápis v PostgreSQL prebieha dvakrát najskôr do WAL — vnútornými a COMMITvnútornými transakciami. ROLLBACKAle nám to nie je potrebné! Náš celý proces
buď úspešne prebehol, alebo nie . Nezáleží na tom, koľko bude mať medziľahlých transakcií — nezaujíma nás „pokračovať v procese od polovice“, najmä keď nie je jasné, kde bola.Na to vývojári PostgreSQL ešte vo verzii 9.1 zaviedli takú vec, ako
nezaznamenávané (UNLOGGED) tabuľky :
fungujú oveľa rýchlejšie ako bežné . Avšak, nie sú chránené pred poruchou; pri zlyhaní alebo havarijnom vypnutí servera sa nezaznamenávaná tabuľkaautomaticky odstredí. . Okrem toho, obsah nezaznamenávanej tabuľkysa nearchivuje. не реплицируется na riadené servery. Akékoľvek indexy vytvorené pre nezaúčtovanú tabuľku sa automaticky stávajú nezaúčtovanými.
Krátko, bude výrazne rýchlejšie, ale ak server DB "spadne" — bude to nepríjemné. Ale stáva sa to často a vie váš ETL proces správne dokončiť "z prostriedku" po "oživení" DB?..
Ak nie, a váš prípad je podobný vyššie uvedenému — použite UNLOGGED, ale nikdy nezapínajte tento atribút na reálnych tabuľkách, z ktorých sú dáta pre vás cenné.
1.3. ON COMMIT { DELETE ROWS | DROP }
Táto konštrukcia umožňuje pri vytváraní tabuľky nastaviť automatické správanie pri ukončení transakcie.
O PRI PRIHLÁSENÍ DROP už som napísal vyššie, generuje to DROP TABLE, ale situácia s PRI PRIHLÁSENÍ ZMAZAŤ RIADKY je zaujímavejšia — tu sa generuje TRUNCATE TABLE.
Keďže celá infraštruktúra ukladania metapopisov dočasnej tabuľky je presne rovnaká ako u bežnej, neustále vytváranie a odstraňovanie dočasných tabuliek vedie k výraznému "nabubreniu" systémových tabuliek pg_class, pg_attribute, pg_attrdef, pg_depend,…
Teraz si predstavte, že máte pracovníka na priamom spojení s DB, ktorý každú sekundu otvára novú transakciu, vytvára, napĺňa, spracováva a odstraňuje dočasnú tabuľku… Odpadu v systémových tabuľkách sa nahromadí dostatok, a to spôsobuje zbytočné spomalenie pri každej operácii.
Celkovo, nerobte to! V tomto prípade je oveľa efektívnejšie CREATE TEMPORARY TABLE x ... ON COMMIT DELETE ROWS vyniesť to mimo cyklus transakcií — vtedy na začiatku každej novej transakcie tabuľky už budú existovať (šetríme volanie VYTVORIŤ), ale bude prázdna, vďaka TRUNCATE (jeho volanie sme tiež ušetrili) pri ukončení predchádzajúcej transakcie.
1.4. LIKE… INCLUDING …
Už som spomenul na začiatku, že jedným z typických praktických využití pre dočasné tabulky sú rôzne druhy importov — a vývojár unavene kopíruje zoznam polí cieľovej tabuľky do vyhlásenia svojej dočasnej…
Ale lenivosť je motor pokroku! Preto vytvoriť novú tabuľku "podľa vzoru" je oveľa jednoduchšie:
CREATE TEMPORARY TABLE import_table(
LIKE target_table
);Keďže do tejto tabuľky je možné generovať naozaj veľa dát, hľadanie v nej sa stane rozhodne nepríjemným. Ale proti tomu existuje tradičné riešenie — indexy! A, áno, dočasná tabuľka môže mať tiež indexy.
Keďže často potrebné indexy sú rovnaké ako indexy cieľovej tabuľky, jednoducho môžete napísať PODOBNÉ ako target_table ZAHŔŇAJÚC INDEXY.
Ak potrebujete aj DEFAULT-hodnoty (napríklad na vyplnenie hodnôt primárneho kľúča) môžete využiť PODOBNÉ ako target_table VRÁTANE VYHODNOCOV. Alebo jednoducho — PODOBNÉ ako target_table VRÁTANE VŠETKÉHO — skopíruje predvolené hodnoty, indexy, obmedzenia,…
Ale tu už treba chápať, že ak ste vytvorili importnú tabuľku rovno s indexmi, dáta sa budú vkladať dlhšie, ako keby ste najprv vložili všetky dáta a potom aplikovali indexy — pozrite sa na príklad, ako to robí .
V podstate, !
2. Ako písať?
Poviem to jednoducho — používajte -potok namiesto „balíka“ INSERT, . Môžete dokonca priamo z predtým vytvoreného súboru.
3. Ako spracovávať?
Takže, nech je naša vstupná situácia približne takáto:
- máte v databáze uloženú tabuľku s údajmi o klientoch na 1M záznamov
- každý deň vám klient posiela nový úplný „obraz“
- na základe skúsenosti viete, že sa zakaždým mení maximálne 10K záznamov
Klasickým príkladom takejto situácie je — adresov je veľa, ale v každej týždennej exporte zmien (prezývanie obcí, zlúčenie ulíc, vznik nových domov) je veľmi málo, dokonca aj v meradle celej krajiny.
3.1. Algoritmus úplnej synchronizácie
Pre jednoduchosť predpokladajme, že vám ani nemusí preštruktúrovať dáta — stačí upraviť tabuľku do požadovaného tvaru, to znamená:
- vymazať všetko, čo už neexistuje
- aktualizovať všetko, čo už existovalo a treba aktualizovať
- vložiť všetko, čo ešte neexistovalo
Prečo robiť operácie práve v tomto poradí? Pretože práve tak sa veľkosť tabuľky zvýši minimálne ().
DELETE FROM dst
Nie, samozrejme, môžete sa obísť len dvoma operáciami:
- vymazať (
DELETE) celkom všetko - vložiť všetko z nového obrazu
Ale pritom, vďaka MVCC, sa veľkosť tabuľky zvýši presne na dvojnásobok! Získať +1M obrazov záznamov v tabuľke kvôli aktualizácii 10K — to je dosť nadbytočné…
TRUNCATE dst
Skúsenejší vývojár vie, že celú tabuľku možno veľmi lacno vyčistiť:
- vyčistiť (
TRUNCATE) celú tabuľku - vložiť všetko z nového obrazu
Metóda je účinná, , ale je tu problém… Vkladať 1M záznamov budeme dlho, preto si nemôžeme dovoliť mať tabuľku prázdnu počas celého tohto času (ako sa stane bez zahalenia do jednej transakcie).
To znamená:
- začíname dlhú transakciu
TRUNCATEuloží AccessExclusive-blokovanie- dlho vykonávame vloženie, a všetci ostatní zatiaľ ani nemôžu
SELECT
Niečo nevychádza dobre…
ALTER TABLE… RENAME… / DROP TABLE …
Ako variant — nahrať všetko do novej tabuľky a potom ju jednoducho premenovať na miesto starej. Niekoľko nepríjemných drobností:
- takisto aj AccessExclusive, aj keď podstatne menej časovo náročné
- vymažú sa všetky plány dopytov/statistika tejto tabuľky,
- rozpadnú sa všetky cudzí kľúče (FK) na tabuľku
Bol WIP-patch od Simona Riggsa, ktorý navrhol urobiť ALTER-operáciu na výmenu tela tabuľky na úrovni súboru, bez zasahovania do štatistiky a FK, ale nezískal kvórum.
DELETE, UPDATE, INSERT
Takže sa zastavíme na neblokovacej variante z troch operácií. Takmer troch… Ako to urobiť čo najefektívnejšie?
-- všetko robíme v rámci transakcie, aby nikto nevidel "medzistavy"
BEGIN;
-- vytvoríme dočasnú tabuľku s importovanými údajmi
CREATE TEMPORARY TABLE tmp(
LIKE dst INCLUDING INDEXES -- podľa vzoru, spolu s indexmi
) ON COMMIT DROP; -- mimo transakcie ju nepotrebujeme
-- rýchlo vložíme nový obraz cez COPY
COPY tmp FROM STDIN;
-- ...
-- .
-- vymažeme chýbajúce
DELETE FROM
dst D
USING
dst X
LEFT JOIN
tmp Y
USING(pk1, pk2) -- polia primárneho kľúča
WHERE
(D.pk1, D.pk2) = (X.pk1, X.pk2) AND
Y IS NOT DISTINCT FROM NULL; -- "antijoin"
-- aktualizujeme zvyšné
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); -- nie je potrebné aktualizovať zhodujúce sa
-- vkladáme chýbajúce
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. Postprocessing importu
Vo všetkých zmenených záznamoch v KLADRi je potrebné dodatočne prejsť postprocessingom — normalizovať, vyzdvihnúť kľúčové slová, doniesť do požadovaných štruktúr. Ale ako zistiť — čo presne sa menilo, pričom kód synchronizácie sa týmto zbytočne nezložití, ideálne ho vôbec nezasahujeme?
Ak je v čase synchronizácie prístup na zápis len vo vašom procese, môžeme využiť spúšťač, ktorý zbierajú všetky zmeny:
-- ciele tabulky
CREATE TABLE kladr(...);
CREATE TABLE kladr_house(...);
-- tabulky s historií změn
CREATE TABLE kladr$log(
ro kladr, -- zde leží celé obrazy záznamů staré/nové
rn kladr
);
CREATE TABLE kladr_house$log(
ro kladr_house,
rn kladr_house
);
-- obecná funkce pro logování změn
CREATE OR REPLACE FUNCTION diff$log() RETURNS trigger AS $$
DECLARE
dst varchar = TG_TABLE_NAME || '$log';
stmt text = '';
BEGIN
-- kontrolujeme potřebu logování při aktualizaci záznamu
IF TG_OP = 'UPDATE' THEN
IF NEW IS NOT DISTINCT FROM OLD THEN
RETURN NEW;
END IF;
END IF;
-- vytváříme záznam logu
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;
Teraz môžeme pred začiatkom synchronizácie nasadiť (alebo aktivovať cez 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 potom pokojne z log-tabúľ vyberáme všetky potrebné zmeny a spracovávame ich cez dodatočné spracovatele.
3.3. Import súvisiacich množín
Vyššie sme sa zaoberali prípadmi, keď štruktúry dát zdroja a príjemcu sú identické. Ale čo robiť, ak export z vonkajšieho systému má formát odlišný od štruktúry, ktorú máme v databáze?
Vezmime si za príklad ukladanie klientov a ich faktúr, klasický prípad „mnohí-k-jednému“:
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)
);A export z externého zdroja prichádza v podobe „všetko v jednom“:
CREATE TEMPORARY TABLE invoice_import(
client_inn
varchar
, client_name
varchar
, invoice_number
varchar
, invoice_dt
date
, invoice_sum
numeric(32,2)
);Je zrejmé, že údaje o klientoch sa môžu v takomto variante dupliciť, a základný záznam predstavuje „faktúra“:
0123456789;Václav;A-01;2020-03-16;1000.00
9876543210;Petr;A-02;2020-03-16;666.00
0123456789;Václav;B-03;2020-03-16;9999.00
Pre model jednoducho vložíme naše testovacie údaje, ale pamätajme — KOPÍROVAŤ efektívnejšie!
INSERT INTO invoice_import
VALUES
('0123456789', 'Václav', 'A-01', '2020-03-16', 1000.00)
, ('9876543210', 'Petr', 'A-02', '2020-03-16', 666.00)
, ('0123456789', 'Václav', 'B-03', '2020-03-16', 9999.00);Najprv vyberieme tie „rezne“, na ktoré naše „fakty“ odkazujú. V našom prípade faktúry odkazujú na klientov:
VYTVORTE DOČASNÚ TABUĽKU client_import AS
SELECT DISTINCT ON(client_inn)
-- je možné jednoducho SELECT DISTINCT, ak sú údaje jednoznačne nezrozumiteľné
client_inn inn
, client_name "name"
FROM
invoice_import;Aby sme správne prepojili faktúry s ID klientov, musíme najprv tieto identifikátory získať alebo vygenerovať. Pridáme pre ne polia:
ALTER TABLE invoice_import ADD COLUMN client_id integer;
ALTER TABLE client_import ADD COLUMN client_id integer;Využijeme vyššie opísaný spôsob synchronizácie tabuliek s malou úpravou — nebudeme nič aktualizovať a odstraňovať v cielovej tabuľke, pretože import klientov máme „append-only”:
-- priraďujeme v tabuľke importu ID už existujúcich záznamov
UPDATE
client_import T
SET
client_id = D.client_id
FROM
client D
WHERE
T.inn = D.inn; -- unikátne kľúč
-- vložíme chýbajúce záznamy a priraďujeme ich ID
WITH ins AS (
INSERT INTO client(
inn
, name
)
SELECT
inn
, name
FROM
client_import
WHERE
client_id IS NULL -- ak ID nebolo priradené
RETURNING *
)
UPDATE
client_import T
SET
client_id = D.client_id
FROM
ins D
WHERE
T.inn = D.inn; -- unikátne kľúč
-- priraďujeme ID klientov k záznamom faktúr
UPDATE
invoice_import T
SET
client_id = D.client_id
FROM
client_import D
WHERE
T.client_inn = D.inn; -- aplikovaný kľúč
Vlastne, všetko — v invoice_import teraz máme vyplnené pole väzby client_id, s ktorým vložíme faktúru.
Zdroj: habr.com
