Nagy adathalmazok komplex feldolgozásához (különböző : importálás, átalakítás és szinkronizálás külső forrással) gyakran van szükség átmenetileg „emlékezzen” és azonnal gyorsan feldolgozza valami terjedelmes.
Egy ilyen tipikus feladat általában így hangzik: "Pont itt az utolsó beérkezett kifizetéseket gyorsan fel kell töltenie a webhelyre, és össze kell kapcsolnia a fiókjával."
Ám amikor ennek a „valaminek” a mennyisége több száz megabájtban kezd mérni, és a szolgáltatásnak továbbra is éjjel-nappal együtt kell működnie az adatbázissal, számos mellékhatás lép fel, amelyek tönkreteszik az életét.
A PostgreSQL-ben (és nem csak abban) való kezelésükhöz használhat néhány optimalizálást, amely lehetővé teszi, hogy mindent gyorsabban és kevesebb erőforrás-felhasználással dolgozzon fel.
1. Hová kell szállítani?
Először döntsük el, hova tölthetjük fel a „feldolgozni” kívánt adatokat.
1.1. Ideiglenes asztalok (TEMPORARY TABLE)
Elvileg a PostgreSQL esetében az ideiglenes táblák ugyanazok, mint a többi. Ezért a babonák, mint "Minden, ami ott van, csak a memóriában van tárolva, és véget érhet". De van néhány lényeges különbség is.
Saját „névtér” minden egyes adatbázis-kapcsolathoz
Ha két kapcsolat egyszerre próbál csatlakozni CREATE TABLE x, akkor valaki biztosan megkapja nem egyediség hiba adatbázis objektumok.
De ha mindketten megpróbálják végrehajtani CREATE TEMPORARY TABLE x, akkor mindkettő rendesen csinálja, és mindenki megkapja a te példányod táblázatok. És nem lesz köztük semmi közös.
"Önmegsemmisítés" leválasztáskor
A kapcsolat lezárásakor az összes ideiglenes tábla automatikusan törlődik, tehát manuálisan DROP TABLE x nincs értelme, kivéve...
Ha végigdolgozod pgbouncer tranzakciós módban, akkor az adatbázis továbbra is azt hiszi, hogy ez a kapcsolat még aktív, és benne ez az ideiglenes tábla még mindig létezik.
Ezért, ha újból megpróbálja létrehozni, egy másik kapcsolatról a pgbouncerhez, az hibát fog eredményezni. De ez használatával megkerülhető CREATE TEMPORARY TABLE IF NOT EXISTS x.
Igaz, jobb, ha ezt nem teszi meg, mert akkor „hirtelen” megtalálhatja ott az „előző tulajdonostól” megmaradt adatokat. Ehelyett sokkal jobb, ha elolvassa a kézikönyvet, és látja, hogy a táblázat létrehozásakor lehetséges-e hozzáadni ON COMMIT DROP - vagyis amikor a tranzakció befejeződik, a tábla automatikusan törlődik.
Nem replikáció
Mivel csak egy adott kapcsolathoz tartoznak, az ideiglenes táblák nem replikálódnak. De így szükségtelenné válik az adatok kettős rögzítése kupacban + WAL, így sokkal gyorsabban megy bele az INSERT/UPDATE/DELETE.
De mivel az ideiglenes tábla még mindig „majdnem közönséges” tábla, replikán sem hozható létre. Legalábbis egyelőre, bár a megfelelő patch már régóta kering.
1.2. NYILVÁNTARTOTT ASZTAL
De mit tegyen például, ha van egy nehézkes ETL folyamata, amelyet nem lehet egy tranzakción belül megvalósítani, de mégis pgbouncer tranzakciós módban? ..
Vagy akkora az adatáramlás, hogy Nincs elég sávszélesség egyetlen kapcsolaton adatbázisból (olvasva, CPU-nként egy folyamat)?
Vagy folyamatban van néhány művelet aszinkron módon különböző kapcsolatokban?...
Itt egyetlen lehetőség van - ideiglenesen hozzon létre egy nem ideiglenes táblát. Szójáték, igen. Azaz:
- "saját" táblákat készítettem maximálisan véletlenszerű névvel, hogy ne keresztezzen senkit
- kivonat: külső forrásból származó adatokkal töltötte meg őket
- Átalakítás: konvertálva, kulcsösszekötő mezők kitöltve
- Terhelés: kész adatokat öntött a céltáblákba
- törölte a „saját” táblákat
És most - egy légy a kenőcsben. Valójában, a PostgreSQL-ben minden írás kétszer történik - , majd a táblázat/index törzsekbe. Mindez azért történik, hogy támogassa az ACID-t és javítsa az adatok láthatóságát között COMMIT'diós és ROLLBACK'null tranzakciók.
De nekünk erre nincs szükségünk! Nálunk van az egész folyamat Vagy teljesen sikerült, vagy nem.. Nem számít, hány közbenső tranzakció lesz - nem vagyunk érdekeltek abban, hogy „a folyamatot középről folytassuk”, különösen akkor, ha nem világos, hogy hol volt.
Ennek érdekében a PostgreSQL fejlesztői még a 9.1-es verzióban bevezettek egy olyan dolgot, mint pl :
Ezzel a jelzéssel a tábla naplózatlanként jön létre. A naplózatlan táblákba írt adatok nem mennek át az előreírási naplón (lásd a 29. fejezetet), így az ilyen táblák a szokásosnál sokkal gyorsabban dolgozhat. Azonban nem mentesek a kudarctól; szerverhiba vagy vészleállás esetén egy naplózatlan tábla automatikusan csonka. Ezenkívül a naplózatlan tábla tartalma nem replikálódik szolga szerverekre. A naplózatlan táblán létrehozott indexek automatikusan törlődnek.
Röviden, sokkal gyorsabb lesz, de ha „leesik” az adatbázisszerver, az kellemetlen lesz. De milyen gyakran fordul elő ez, és az Ön ETL folyamata tudja-e ezt helyesen korrigálni „középről” az adatbázis „újraélesztése” után?
Ha nem, és a fenti eset hasonló az Önéhez, használja UNLOGGEDde soha ne engedélyezze ezt az attribútumot valós táblákon, amelyből az adatok kedvesek az Ön számára.
1.3. ON COMMIT { SOROK TÖRLÉSE | CSEPP}
Ez a konstrukció lehetővé teszi az automatikus viselkedés megadását a tranzakció befejezésekor a tábla létrehozásakor.
Про ON COMMIT DROP Fentebb már írtam, generál DROP TABLE, hanem azzal ON COMMIT DELETE ROWS a helyzet érdekesebb – itt generálódik TRUNCATE TABLE.
Mivel egy ideiglenes tábla meta-leírásának tárolására szolgáló teljes infrastruktúra pontosan ugyanaz, mint egy normál táblaé, Az ideiglenes táblák folyamatos létrehozása és törlése a rendszertáblák súlyos „duzzadásához” vezet pg_class, pg_attribute, pg_attrdef, pg_depend,…
Most képzelje el, hogy van egy dolgozója közvetlen kapcsolatban az adatbázissal, aki másodpercenként nyit egy új tranzakciót, létrehoz, kitölt, feldolgoz és töröl egy ideiglenes táblát... A rendszertáblákban több szemét halmozódik fel, és ez extra fékeket okoz minden egyes műveletnél.
Általában ne tedd ezt! Ebben az esetben sokkal hatékonyabb CREATE TEMPORARY TABLE x ... ON COMMIT DELETE ROWS vegye ki a tranzakciós ciklusból - akkor minden új tranzakció elejére a táblák már megvannak létezni fog (ment egy hívást CREATE), de üres lesz, köszönet TRUNCATE (a hívását is elmentettük) az előző tranzakció befejezésekor.
1.4. LIKE...BELEÉRTVE...
Az elején említettem, hogy az ideiglenes táblák egyik tipikus használati esete a különféle importálások – és a fejlesztő fáradtan másolja be a céltábla mezőinek listáját az ideiglenes...
De a lustaság a haladás motorja! Ezért hozzon létre egy új táblázatot "minta alapján" sokkal egyszerűbb is lehet:
CREATE TEMPORARY TABLE import_table(
LIKE target_table
);Mivel ezután sok adatot generálhat ebbe a táblázatba, a keresés soha nem lesz gyors. De erre van egy hagyományos megoldás - indexek! És igen, egy ideiglenes táblának is lehetnek indexei.
Mivel a szükséges indexek gyakran egybeesnek a céltábla indexeivel, egyszerűen írhat LIKE target_table INCLUDING INDEXES.
Ha neked is kell DEFAULT-értékeket (például az elsődleges kulcs értékeinek kitöltéséhez), használhatja LIKE target_table INCLUDING DEFAULTS. Vagy egyszerűen... LIKE target_table INCLUDING ALL - másolja az alapértelmezett értékeket, indexeket, megszorításokat,...
De itt meg kell értened, hogy ha létrehoztál azonnal importálja a táblát indexekkel, akkor az adatok betöltése tovább tartmintha először mindent kitöltene, és csak azután görgetné fel az indexeket – nézze meg, hogyan csinálja ezt példaként .
Általában véve, !
2. Hogyan kell írni?
Csak annyit mondok: használd -flow a "pack" helyett INSERT, . Akár közvetlenül egy előre generált fájlból is.
3. Hogyan kell feldolgozni?
Nézzük tehát a bevezetőnk valahogy így:
- van egy táblája az adatbázisban tárolt ügyféladatokkal 1 millió rekord
- minden nap egy ügyfél újat küld Önnek teljes "kép"
- tapasztalatból tudod, hogy időről időre legfeljebb 10 XNUMX rekord módosul
Egy ilyen helyzet klasszikus példája az — összesen sok a cím, de egy-egy heti feltöltésben országos szinten is nagyon kevés változás (települések átnevezése, utcaegyesítés, új házak megjelenése) történik.
3.1. Teljes szinkronizálási algoritmus
Az egyszerűség kedvéért tegyük fel, hogy nem is kell átstrukturálnia az adatokat – csak hozza a táblázatot a kívánt formába, azaz:
- eltávolít mindent, ami már nem létezik
- frissítés mindent, ami már létezett és frissítésre szorul
- betét mindent, ami még nem történt meg
Miért ebben a sorrendben kell a műveleteket elvégezni? Mert így az asztal mérete minimálisan nő ().
TÖRLÉS A dst
Nem, természetesen csak két művelettel boldogulsz:
- eltávolít (
DELETE) általában mindent - betét mindezt az új képből
Ugyanakkor az MVCC-nek köszönhetően Az asztal mérete pontosan kétszeresére nő! A rekordok +1 millió képének megjelenítése a táblázatban egy 10 XNUMX frissítés miatt annyira redundancia...
TRUNCATE dst
Egy tapasztaltabb fejlesztő tudja, hogy az egész tabletta meglehetősen olcsón tisztítható:
- tisztázni (
TRUNCATE) a teljes táblázatot - betét mindezt az új képből
A módszer hatékony, , de van egy probléma... Sokáig 1M rekordot fogunk hozzáadni, így nem engedhetjük meg magunknak, hogy a táblát ennyi időre üresen hagyjuk (ahogy egyetlen tranzakcióba csomagolás nélkül is megtörténik).
Ami azt jelenti:
- kezdjük hosszú távú tranzakció
TRUNCATEelőírja Exkluzív hozzáférés-blokkolás- sokáig csináljuk a beillesztést, és mindenki más ilyenkor nem is lehet
SELECT
Valami nem megy jól...
TÁBLÁZAT MÓDOSÍTÁSA… ÁTNEVEZÉS… / TÁBLÁZAT DROP…
Egy másik lehetőség, hogy mindent egy külön új táblába töltünk, majd egyszerűen átnevezzük a régi helyére. Pár csúnya apróság:
- még mindig Exkluzív hozzáférés, bár lényegesen kevesebb időt
- minden lekérdezési terv/statisztika ehhez a táblázathoz visszaállt,
- minden idegen kulcs törött (FK) az asztalhoz
Volt egy WIP-folt Simon Riggstől, amely az elkészítését javasolta ALTER-a tábla törzsének fájlszintű cseréje, a statisztikák és az FK érintése nélkül, de nem gyűjtötte össze a határozatképességet.
TÖRLÉS, FRISSÍTÉS, BESZÚRÁS
Tehát a három művelet nem blokkoló opciója mellett döntünk. Majdnem három... Hogyan lehet ezt a leghatékonyabban csinálni?
-- все делаем в рамках транзакции, чтобы никто не видел "промежуточных" состояний
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. Import utófeldolgozás
Ugyanabban a KLADR-ben az összes módosított rekordot utófeldolgozáson is le kell futtatni - normalizálni, kulcsszavakat kiemelni, és a szükséges struktúrákra redukálni. De honnan tudod... mi változott pontosana szinkronizációs kód bonyolítása nélkül, ideális esetben anélkül, hogy hozzáérnénk?
Ha a szinkronizáláskor csak a folyamatnak van írási jogosultsága, akkor használhat egy triggert, amely összegyűjti helyettünk az összes módosítást:
-- целевые таблицы
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;
Most már alkalmazhatunk triggereket a szinkronizálás megkezdése előtt (vagy engedélyezhetjük őket a 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();
Ezután nyugodtan kivonjuk az összes szükséges változtatást a naplótáblákból, és további kezelőkön keresztül futtatjuk őket.
3.3. Kapcsolt készletek importálása
Fentebb megvizsgáltuk azokat az eseteket, amikor a forrás és a cél adatszerkezete megegyezik. De mi van akkor, ha a külső rendszerről történő feltöltés formátuma eltér az adatbázisunkban lévő tárolási struktúrától?
Vegyük példának az ügyfelek és fiókjaik tárolását, a klasszikus „sok az egyhez” opciót:
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)
);De a külső forrásból történő letöltés „minden egyben” formában érkezik hozzánk:
CREATE TEMPORARY TABLE invoice_import(
client_inn
varchar
, client_name
varchar
, invoice_number
varchar
, invoice_dt
date
, invoice_sum
numeric(32,2)
);Nyilvánvaló, hogy ebben a verzióban az ügyféladatok megkettőzhetők, és a fő rekord a „számla”:
0123456789;Вася;A-01;2020-03-16;1000.00
9876543210;Петя;A-02;2020-03-16;666.00
0123456789;Вася;B-03;2020-03-16;9999.00
A modellhez egyszerűen beillesztjük a tesztadatokat, de ne feledje: COPY hatékonyabb!
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);Először is emeljük ki azokat a „vágásokat”, amelyekre „tényeink” utalnak. Esetünkben a számlák az ügyfelekre vonatkoznak:
CREATE TEMPORARY TABLE client_import AS
SELECT DISTINCT ON(client_inn)
-- можно просто SELECT DISTINCT, если данные заведомо непротиворечивы
client_inn inn
, client_name "name"
FROM
invoice_import;Ahhoz, hogy a fiókokat megfelelően társíthassuk az ügyfél-azonosítókkal, először meg kell találnunk vagy elő kell állítani ezeket az azonosítókat. Adjunk hozzá mezőket alájuk:
ALTER TABLE invoice_import ADD COLUMN client_id integer;
ALTER TABLE client_import ADD COLUMN client_id integer;Használjuk a fent leírt táblázatszinkronizálási módszert egy kis módosítással - a céltáblázatban nem frissítünk és nem törölünk semmit, mert a klienseket „csak hozzáfűzve” importáljuk:
-- проставляем в таблице импорта 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; -- прикладной ключ
Valójában minden benne van invoice_import Most már kitöltöttük a kapcsolati mezőt client_id, amellyel beillesztjük a számlát.
Forrás: will.com