Pre komplexné spracovanie veľkých súborov údajov (rôzne : importy, konverzie a synchronizácia s externým zdrojom) často existuje potreba dočasne „zapamätať“ a okamžite rýchlo spracovať niečo objemné.
Typická úloha tohto druhu zvyčajne znie asi takto: "Práve tu posledné prijaté platby, musíte ich rýchlo nahrať na webovú stránku a prepojiť ich s vašimi účtami.“
Keď sa však objem tohto „niečoho“ začne merať v stovkách megabajtov a služba musí naďalej pracovať s databázou 24 hodín denne, 7 dní v týždni, objavia sa mnohé vedľajšie účinky, ktoré vám zničia život.
Aby ste si s nimi poradili v PostgreSQL (a nielen v ňom), môžete použiť niektoré optimalizácie, ktoré vám umožnia spracovať všetko rýchlejšie a s menšou spotrebou zdrojov.
1. Kam poslať?
Najprv sa rozhodnime, kam môžeme nahrať údaje, ktoré chceme „spracovať“.
1.1. Dočasné stoly (TEMPORARY TABLE)
V zásade sú dočasné tabuľky pre PostgreSQL rovnaké ako akékoľvek iné. Preto povery ako "Všetko, čo tam je, je uložené iba v pamäti a môže to skončiť". Existuje však aj niekoľko podstatných rozdielov.
Váš vlastný „namespace“ pre každé pripojenie k databáze
Ak sa dve pripojenia pokúsia pripojiť súčasne CREATE TABLE x, tak to určite niekto dostane chyba nejedinečnosti databázové objekty.
Ale ak sa obaja pokúsia vykonať CREATE TEMPORARY TABLE x, potom to obaja urobia normálne a všetci dostanú vaša kópia tabuľky. A nebude medzi nimi nič spoločné.
"Sebadeštrukcia" pri odpojení
Po zatvorení spojenia sa všetky dočasné tabuľky automaticky vymažú, takže manuálne DROP TABLE x nema to zmysel okrem...
Ak pracujete cez pgbouncer v režime transakcie, potom databáza naďalej verí, že toto pripojenie je stále aktívne a táto dočasná tabuľka v ňom stále existuje.
Preto pokus o jeho opätovné vytvorenie z iného pripojenia k pgbouncer bude mať za následok chybu. Ale to sa dá obísť používaním CREATE TEMPORARY TABLE IF NOT EXISTS x.
Je pravda, že je lepšie to aj tak nerobiť, pretože potom tam môžete „náhle“ nájsť zostávajúce údaje od „predchádzajúceho vlastníka“. Namiesto toho je oveľa lepšie prečítať si príručku a vidieť, že pri vytváraní tabuľky je možné pridať ON COMMIT DROP - to znamená, že po dokončení transakcie sa tabuľka automaticky vymaže.
Nereplikácia
Keďže patria iba ku konkrétnemu pripojeniu, dočasné tabuľky sa nereplikujú. ale tým sa eliminuje potreba dvojitého zaznamenávania údajov v halde + WAL, takže INSERT/UPDATE/DELETE do nej je oveľa rýchlejšie.
Ale keďže dočasná tabuľka je stále „takmer obyčajná“ tabuľka, nemožno ju vytvoriť ani na replike. Aspoň zatiaľ, hoci príslušný patch koluje už dlhšie.
1.2. NELOGGOVANÁ TABUĽKA
Čo by ste však mali robiť, napríklad ak máte nejaký ťažkopádny proces ETL, ktorý sa nedá implementovať v rámci jednej transakcie, ale stále máte pgbouncer v režime transakcie? ..
Alebo dátový tok je taký veľký, že Na jednom pripojení nie je dostatočná šírka pásma z databázy (čítanie, jeden proces na CPU)?..
Alebo prebiehajú nejaké operácie asynchrónne v rôznych súvislostiach?...
Tu je len jedna možnosť - dočasne vytvorte nedočasnú tabuľku. Hra, áno. To je:
- vytvoril „vlastné“ tabuľky s maximálne náhodnými názvami, aby sa s nikým nekrížili
- Výpis: naplnil ich údajmi z externého zdroja
- Premeniť: prevedené, vyplnené kľúčové prepojovacie polia
- Load: nalial hotové dáta do cieľových tabuliek
- vymazali „moje“ tabuľky
A teraz - mucha v masti. V skutočnosti, všetky zápisy v PostgreSQL sa dejú dvakrát - a potom do tela tabuľky/indexu. To všetko sa robí pre podporu ACID a správnu viditeľnosť údajov medzi nimi COMMIT„orechový a ROLLBACK„nulové transakcie.
Ale toto nepotrebujeme! Máme celý proces Buď to bolo úplne úspešné, alebo nie.. Nezáleží na tom, koľko prechodných transakcií bude – nemáme záujem „pokračovať v procese od stredu“, najmä keď nie je jasné, kde to bolo.
Za týmto účelom vývojári PostgreSQL vo verzii 9.1 zaviedli niečo ako :
S týmto označením sa tabuľka vytvorí ako neprihlásená. Údaje zapísané do nezaprotokolovaných tabuliek neprechádzajú cez protokol zapisovania dopredu (pozri kapitolu 29), čo spôsobuje, že takéto tabuľky pracovať oveľa rýchlejšie ako zvyčajne. Nie sú však imúnne voči zlyhaniu; v prípade zlyhania servera alebo núdzového vypnutia odhlásená tabuľka automaticky skrátené. Okrem toho obsah neprihlásenej tabuľky nereplikované na podriadené servery. Všetky indexy vytvorené v neprihlásenej tabuľke sa automaticky odhlásia.
Stručne povedané, bude to oveľa rýchlejšie, ale ak databázový server „spadne“, bude to nepríjemné. Ale ako často sa to stáva a vie váš proces ETL, ako to správne opraviť „od stredu“ po „revitalizácii“ databázy?...
Ak nie a prípad uvedený vyššie je podobný ako váš, použite UNLOGGEDale nikdy nepovoľte tento atribút na skutočných tabuľkách, údaje z ktorých sú vám drahé.
1.3. ON COMMIT { DELETE ROWS | POKLES}
Táto konštrukcia vám umožňuje určiť automatické správanie po dokončení transakcie pri vytváraní tabuľky.
o ON COMMIT DROP Už som písal vyššie, generuje DROP TABLE, ale s ON COMMIT DELETE ROWS situácia je zaujímavejšia - generuje sa tu TRUNCATE TABLE.
Keďže celá infraštruktúra na ukladanie metapopisu dočasnej tabuľky je úplne rovnaká ako pri bežnej tabuľke, potom Neustále vytváranie a odstraňovanie dočasných tabuliek vedie k vážnemu „napučiavaniu“ systémových tabuliek pg_class, pg_attribute, pg_attrdef, pg_depend,…
Teraz si predstavte, že máte pracovníka na priamom pripojení k databáze, ktorý každú sekundu otvorí novú transakciu, vytvorí, vyplní, spracuje a vymaže dočasnú tabuľku... V systémových tabuľkách sa bude hromadiť prebytok odpadu a to spôsobí ďalšie brzdy pre každú operáciu.
Vo všeobecnosti to nerobte! V tomto prípade je to oveľa efektívnejšie CREATE TEMPORARY TABLE x ... ON COMMIT DELETE ROWS vyraďte to z transakčného cyklu - potom na začiatku každej novej transakcie sú už tabuľky bude existovať (uložiť hovor CREATE), ale bude prázdny, vďaka TRUNCATE (uložili sme aj jeho hovor) pri dokončení predchádzajúcej transakcie.
1.4. AKO...VRÁTANE...
Na začiatku som spomenul, že jedným z typických prípadov použitia dočasných tabuliek sú rôzne druhy importov - a vývojár unavene kopíruje a vkladá zoznam polí cieľovej tabuľky do deklarácie svojej dočasnej...
Ale lenivosť je motorom pokroku! Preto vytvoriť novú tabuľku „na základe vzorky“ môže to byť oveľa jednoduchšie:
CREATE TEMPORARY TABLE import_table(
LIKE target_table
);Keďže potom môžete do tejto tabuľky vygenerovať množstvo údajov, vyhľadávanie v nej nebude nikdy rýchle. Ale na to existuje tradičné riešenie - indexy! A áno, dočasná tabuľka môže mať aj indexy.
Keďže sa požadované indexy často zhodujú s indexmi cieľovej tabuľky, môžete jednoducho písať LIKE target_table INCLUDING INDEXES.
Ak tiež potrebujete DEFAULT-hodnoty (napríklad na vyplnenie hodnôt primárneho kľúča), môžete použiť LIKE target_table INCLUDING DEFAULTS. Alebo jednoducho - LIKE target_table INCLUDING ALL — skopíruje predvolené hodnoty, indexy, obmedzenia,...
Ale tu musíte pochopiť, že ak ste vytvorili okamžite importujte tabuľku s indexmi, načítanie údajov bude trvať dlhšieako keď najprv všetko vyplníte a až potom zrolujete indexy - pozrite sa, ako to robí ako príklad .
Všeobecne možno povedať, !
2. Ako písať?
Dovoľte mi povedať - použite to - tok namiesto „baliť“ INSERT, . Môžete dokonca priamo z vopred vygenerovaného súboru.
3. Ako spracovať?
Necháme teda naše intro vyzerať asi takto:
- máte vo svojej databáze uloženú tabuľku s údajmi o klientoch 1 milión záznamov
- každý deň vám klient pošle novú úplný "obraz"
- zo skúsenosti to z času na čas viete nezmení sa viac ako 10 XNUMX záznamov
Klasickým príkladom takejto situácie je — adries je celkovo veľa, ale v každom týždennom uploade je len veľmi málo zmien (premenovanie sídiel, kombinovanie ulíc, vzhľad nových domov) aj v celoštátnom meradle.
3.1. Plný synchronizačný algoritmus
Pre jednoduchosť povedzme, že nepotrebujete ani reštrukturalizovať údaje – stačí tabuľku preniesť do požadovaného tvaru, teda:
- odstrániť všetko, čo už neexistuje
- aktualizovať všetko, čo už existuje a je potrebné aktualizovať
- vložiť všetko, čo sa ešte nestalo
Prečo by sa operácie mali vykonávať v tomto poradí? Pretože takto sa veľkosť tabuľky zväčší minimálne ().
VYMAZAŤ Z dst
Nie, samozrejme si vystačíte iba s dvoma operáciami:
- odstrániť (
DELETE) všetko vo všeobecnosti - vložiť všetko z nového obrázku
Zároveň však vďaka MVCC Veľkosť stola sa zväčší presne dvakrát! Získanie +1 milióna obrázkov záznamov v tabuľke vďaka 10 XNUMX aktualizácii je tak nadbytočné...
TRUNCATE dst
Skúsenejší vývojár vie, že celý tablet sa dá vyčistiť pomerne lacno:
- čistý (
TRUNCATE) celú tabuľku - vložiť všetko z nového obrázku
Metóda je účinná, , ale je tu problém... Dlho budeme pridávať 1M záznamov, takže si nemôžeme dovoliť nechať tabuľku po celú dobu prázdnu (ako sa to stane bez zabalenia do jedinej transakcie).
Čo znamená:
- začíname dlhotrvajúca transakcia
TRUNCATEukladá Exkluzívny prístup-blokovanie- vkladanie robíme dlho a všetci ostatní v tomto čase ani nemôže
SELECT
Niečo nejde dobre...
ZMENIŤ TABUĽKU… PREMENOVAŤ… / PUSTIŤ TABUĽKU…
Alternatívou je vyplniť všetko do samostatnej novej tabuľky a potom ju jednoducho premenovať na miesto starej. Pár nepríjemných drobností:
- stále tiež Exkluzívny prístup, aj keď podstatne menej času
- všetky plány/štatistiky dotazov pre túto tabuľku sa vynulujú,
- všetky cudzie kľúče sú zlomené (FK) k stolu
Bol tam WIP patch od Simona Riggsa, ktorý navrhoval vytvorenie ALTER-operácia na nahradenie tela tabuľky na úrovni súboru bez toho, aby sa dotkla štatistiky a FK, ale nezískala kvórum.
VYMAZAŤ, AKTUALIZOVAŤ, VLOŽIŤ
Takže sme sa rozhodli pre možnosť neblokovania troch operácií. Takmer tri... Ako to urobiť najefektívnejšie?
-- все делаем в рамках транзакции, чтобы никто не видел "промежуточных" состояний
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. Následné spracovanie importu
V tom istom KLADR musia byť všetky zmenené záznamy dodatočne spustené následným spracovaním – normalizované, kľúčové slová zvýraznené a zredukované na požadované štruktúry. Ale ako vieš - čo sa presne zmenilobez skomplikovania synchronizačného kódu, ideálne bez toho, aby ste sa ho vôbec dotkli?
Ak má v čase synchronizácie prístup na zápis iba váš proces, môžete použiť spúšťač, ktorý za nás zhromaždí všetky zmeny:
-- целевые таблицы
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;
Teraz môžeme použiť spúšťače pred spustením synchronizácie (alebo ich povoliť 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 vytiahneme všetky potrebné zmeny z tabuliek protokolov a spustíme ich cez ďalšie obslužné programy.
3.3. Import prepojených sád
Vyššie sme uvažovali o prípadoch, keď sú dátové štruktúry zdroja a cieľa rovnaké. Čo ak však nahrávanie z externého systému má formát odlišný od štruktúry úložiska v našej databáze?
Vezmime si ako príklad ukladanie klientov a ich účtov, klasickú možnosť „mnoho na jedného“:
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)
);Ale sťahovanie z externého zdroja k nám prichádza vo forme „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 zákazníkoch môžu byť v tejto verzii duplikované a hlavným záznamom je „účet“:
0123456789;Вася;A-01;2020-03-16;1000.00
9876543210;Петя;A-02;2020-03-16;666.00
0123456789;Вася;B-03;2020-03-16;9999.00
Pre model jednoducho vložíme naše testovacie údaje, ale pamätajte - COPY viac efektívny!
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);Najprv zvýraznime tie „strihy“, na ktoré sa vzťahujú naše „fakty“. V našom prípade sa faktúry týkajú zákazníkov:
CREATE TEMPORARY TABLE client_import AS
SELECT DISTINCT ON(client_inn)
-- можно просто SELECT DISTINCT, если данные заведомо непротиворечивы
client_inn inn
, client_name "name"
FROM
invoice_import;Aby sme mohli správne priradiť účty k ID zákazníkov, musíme tieto identifikátory najskôr zistiť alebo vygenerovať. Pridajme pod ne polia:
ALTER TABLE invoice_import ADD COLUMN client_id integer;
ALTER TABLE client_import ADD COLUMN client_id integer;Využime metódu synchronizácie tabuľky opísanú vyššie s malou úpravou – v cieľovej tabuľke nebudeme nič aktualizovať ani odstraňovať, pretože importujeme klientov „iba na pripojenie“:
-- проставляем в таблице импорта 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; -- прикладной ключ
V skutočnosti je všetko in invoice_import Teraz máme vyplnené kontaktné pole client_id, ktorým vložíme faktúru.
Zdroj: hab.com