Foar komplekse ferwurking fan grutte datasets (ferskillende : ymport, bekearing en syngronisaasje mei in eksterne boarne) faak is der ferlet tydlik "ûnthâld" en fuortendaliks fluch ferwurkje wat voluminous.
In typyske taak fan dit soarte klinkt gewoanlik sa: "Hjir de lêste ûntfongen betellingen, jo moatte se fluch uploade nei de webside en keppelje se oan jo akkounts.
Mar as it folume fan dit "wat" begjint te mjitten yn hûnderten megabytes, en de tsjinst moat trochgean te wurkjen mei de databank 24x7, ûntsteane in protte side-effekten dy't jo libben ferneatigje.
Om mei har te behanneljen yn PostgreSQL (en net allinich dêryn), kinne jo wat optimisaasjes brûke wêrmei jo alles rapper kinne ferwurkje en mei minder boarneferbrûk.
1. Wêr te ferstjoeren?
Litte wy earst beslute wêr't wy de gegevens kinne uploade dy't wy wolle "ferwurkje."
1.1. Tydlike tabellen (TYDLIKE TABEL)
Yn prinsipe binne tydlike tabellen foar PostgreSQL itselde as alle oare. Dêrom, byleauwe lykas "Alles dêr is allinich yn it ûnthâld bewarre, en it kin einigje". Mar d'r binne ek ferskate wichtige ferskillen.
Jo eigen "nammeromte" foar elke ferbining mei de databank
As twa ferbiningen besykje tagelyk te ferbinen CREATE TABLE x, dan sil immen grif krije non-unike flater databank objekten.
Mar as beide besykje út te fieren CREATE TEMPORARY TABLE x, dan sille beide dwaan it normaal, en elkenien sil krije dyn kopy tabellen. En der sil neat mienskiplik wêze tusken har.
"Selfdestruct" by it loskoppelen
As de ferbining sletten is, wurde alle tydlike tabellen automatysk wiske, dus mei de hân DROP TABLE x d'r is gjin punt behalve ...
As jo wurkje troch pgbouncer yn transaksjemodus, dan bliuwt de databank leauwe dat dizze ferbining noch aktyf is, en dêryn bestiet dizze tydlike tabel noch.
Dêrom, besykje it opnij te meitsjen, fan in oare ferbining mei pgbouncer, sil resultearje yn in flater. Mar dit kin wurde omseame troch it brûken CREATE TEMPORARY TABLE IF NOT EXISTS x.
Wier, it is better net te dwaan dit dochs, want dan kinne jo "ynienen" fine de oerbleaune gegevens fan de "foarige eigner". Ynstee is it folle better om de hânlieding te lêzen en te sjen dat by it meitsjen fan in tabel it mooglik is om ta te foegjen ON COMMIT DROP - dat is, as de transaksje foltôge is, sil de tabel automatysk wiske wurde.
Non-replikaasje
Om't se allinich by in spesifike ferbining hearre, wurde tydlike tabellen net replikearre. Mar dit elimineert de needsaak foar dûbele opname fan gegevens yn heap + WAL, dus YNFOGJE / UPDATE / DELETE deryn is signifikant flugger.
Mar om't in tydlike tafel noch altyd in "hast gewoane" tafel is, kin it ek net oanmakke wurde op in replika. Alteast foar no, hoewol de oerienkommende patch al in lange tiid sirkulearret.
1.2. UNLOGGED TABEL
Mar wat moatte jo dwaan, bygelyks, as jo in soarte fan omslachtig ETL-proses hawwe dat net binnen ien transaksje kin wurde ymplementearre, mar jo hawwe noch altyd pgbouncer yn transaksjemodus? ..
Of de gegevensstream is sa grut dat D'r is net genôch bânbreedte op ien ferbining fan in databank (lêzen, ien proses per CPU)? ..
Of guon operaasjes binne geande asynchronously yn ferskate ferbiningen? ..
D'r is mar ien opsje hjir - tydlik meitsje in net-tydlike tabel. Pun, jo. Dat is:
- makke "myn eigen" tabellen mei maksimaal willekeurige nammen om net te krúsjen mei immen
- Extract: folje se mei gegevens fan in eksterne boarne
- Transform: konvertearre, ynfolle kaai keppeling fjilden
- Lade: getten klear gegevens yn doel tabellen
- wiske "myn" tabellen
En no - in fly yn 'e salve. Yn feite, alle skriuwt yn PostgreSQL bart twa kear - , dan yn 'e tabel / yndeks lichems. Dit alles wurdt dien om ACID te stypjen en korrekte gegevenssichtberens tusken COMMIT'nut en ROLLBACK'null transaksjes.
Mar wy hawwe dit net nedich! Wy hawwe it hiele proses Of it wie folslein suksesfol of it wie net.. It makket net út hoefolle tuskentransaksjes d'r sille wêze - wy binne net ynteressearre yn "trochgean mei it proses fanôf it midden," foaral as it net dúdlik is wêr't it wie.
Om dit te dwaan, yntrodusearre de PostgreSQL-ûntwikkelders, werom yn ferzje 9.1, sa'n ding as :
Mei dizze yndikaasje wurdt de tabel makke as net oanmeld. Gegevens dy't skreaun binne nei net-oanmelde tabellen geane net troch it skriuw-foarút-log (sjoch haadstik 29), wêrtroch't sokke tabellen wurkje folle flugger as gewoanlik. Se binne lykwols net ymmún foar mislearring; yn gefal fan tsjinner flater of need shutdown, in unlogged tabel automatysk ôfkoarte. Derneist is de ynhâld fan 'e net oanmelde tabel net replikearre oan slave tsjinners. Alle yndeksen dy't makke binne op in net-oanmelde tabel wurde automatysk útlogd.
Koartsein, it sil folle flugger wêze, mar as de databanktsjinner "falt", sil it onaangenaam wêze. Mar hoe faak bart dit, en wit jo ETL-proses hoe't jo dit korrekt "fanút it midden" kinne korrigearje nei it "revitalisearjen" fan 'e database? ..
As net, en it gefal hjirboppe is fergelykber mei dy, brûk dan UNLOGGEDmar nea net ynskeakelje dit attribút op echte tabellen, de gegevens út dêr't is dierber foar dy.
1.3. ON COMMIT { DELETE RILEN | FALLE}
Dit konstruksje lit jo automatysk gedrach oanjaan as in transaksje foltôge is by it meitsjen fan in tabel.
op ON COMMIT DROP Ik al skreau boppe, it generearret DROP TABLE,mar mei ON COMMIT DELETE ROWS de situaasje is ynteressanter - it wurdt hjir generearre TRUNCATE TABLE.
Sûnt de hiele ynfrastruktuer foar it opslaan fan de meta-beskriuwing fan in tydlike tabel is krekt itselde as dy fan in gewoane tabel, dan Konstante oanmeitsjen en wiskjen fan tydlike tabellen liedt ta slimme "swelling" fan systeemtabellen pg_class, pg_attribute, pg_attrdef, pg_depend,...
Stel jo no foar dat jo in arbeider hawwe op in direkte ferbining mei de databank, dy't elke sekonde in nije transaksje iepenet, in tydlike tabel oanmakket, foltôget, ferwurket en wisket ... dit sil feroarsaakje ekstra remmen foar eltse operaasje.
Yn it algemien, doch dit net! Yn dit gefal is it folle effektiver CREATE TEMPORARY TABLE x ... ON COMMIT DELETE ROWS nim it út 'e transaksjesyklus - dan by it begjin fan elke nije transaksje binne de tabellen al sil bestean (in oprop bewarje CREATE), mar sil leech wêze, tank oan TRUNCATE (wy hawwe ek syn oprop bewarre) by it foltôgjen fan de foarige transaksje.
1.4. LIKE ... INKLUDERT ...
Ik neamde oan it begjin dat ien fan 'e typyske gebrûksgefallen foar tydlike tabellen ferskate soarten ymporten is - en de ûntwikkelder kopiearje-plakt wurch de list mei fjilden fan' e doeltabel yn 'e ferklearring fan syn tydlike ...
Mar luiheid is de motor fan foarútgong! Dêrom meitsje in nije tabel "basearre op foarbyld" it kin folle ienfâldiger:
CREATE TEMPORARY TABLE import_table(
LIKE target_table
);Om't jo dan in protte gegevens yn dizze tabel kinne generearje, sil it sykjen dertroch nea fluch wêze. Mar d'r is in tradisjonele oplossing foar dit - yndeksen! En ja, in tydlike tabel kin ek hawwe yndeksen.
Om't faak de fereaske yndeksen gearfalle mei de yndeksen fan 'e doeltabel, kinne jo gewoan skriuwe LIKE target_table INCLUDING INDEXES.
As jo ek nedich DEFAULT-wearden (bygelyks om de primêre kaaiwearden yn te foljen), kinne jo brûke LIKE target_table INCLUDING DEFAULTS. Of gewoan - LIKE target_table INCLUDING ALL - kopiearret standerts, yndeksen, beheiningen, ...
Mar hjir moatte jo begripe dat as jo makke ymportearje tabel fuortendaliks mei yndeksen, dan sil de gegevens langer duorje om te ladendan as jo earst alles ynfolje, en dan pas de yndeksen oprolje - sjoch as foarbyld hoe't it dit docht .
Koartsein, !
2. Hoe skriuwe?
Lit my gewoan sizze - brûk it -flow ynstee fan "pack" INSERT, . Jo kinne sels direkt út in pre-generearre triem.
3. Hoe ferwurkje?
Dat, litte wy ús yntro der sa útsjen:
- jo hawwe in tabel mei kliïntgegevens opslein yn jo databank 1M records
- eltse dei in klant stjoert dy in nij folsleine "ôfbylding"
- út ûnderfining witte jo dat sa no en dan net mear as 10K records wurde feroare
In klassyk foarbyld fan sa'n situaasje is - yn totaal binne d'r in protte adressen, mar yn elke wyklikse upload binne d'r heul pear feroarings (neaming fan delsettings, kombinearjen fan strjitten, ferskining fan nije huzen) sels op lanlike skaal.
3.1. Folsleine syngronisaasjealgoritme
Litte wy foar de ienfâld sizze dat jo de gegevens net iens hoege te strukturearjen - bring de tabel gewoan yn 'e winske foarm, dat is:
- fuortsmite alles dat net mear bestiet
- upgrade alles wat al bestie en bywurke wurde moat
- ynfoegje alles wat noch net bard is
Wêrom moatte de operaasjes yn dizze folchoarder dien wurde? Om't dit is hoe't de tafelgrutte minimaal sil groeie ().
DELETE FROM dst
Nee, fansels kinne jo troch mar twa operaasjes komme:
- fuortsmite (
DELETE) alles yn it algemien - ynfoegje allegear út de nije ôfbylding
Mar tagelyk, tank oan MVCC, De grutte fan 'e tafel sil tanimme krekt twa kear! +1M ôfbyldings fan records yn 'e tabel krije fanwegen in 10K update is sa-sa oerstallich ...
TRUNCATE dst
In mear betûfte ûntwikkelder wit dat de folsleine tablet frij goedkeap kin wurde skjinmakke:
- klar (
TRUNCATE) de hiele tabel - ynfoegje allegear út de nije ôfbylding
De metoade is effektyf, , mar d'r is in probleem ... Wy sille 1M-records foar in lange tiid tafoegje, dus wy kinne it net betelje om de tafel foar al dizze tiid leech te litten (lykas sil barre sûnder it yn ien transaksje yn te pakken).
Wat betsjut:
- wy begjinne lange-rinnende transaksje
TRUNCATEoplizze Eksklusyf tagong- blokkearje- wy dogge de ynfoegje foar in lange tiid, en alle oaren op dit stuit kin net iens
SELECT
Der giet wat net goed...
TABEL ALTER... RENAME... / TABEL FERGESE...
In alternatyf is om alles yn in aparte nije tabel te foljen, en dan gewoan omneame yn plak fan 'e âlde. In pear ferfelende lytse dingen:
- ek noch Eksklusyf tagong, hoewol signifikant minder tiid
- alle queryplannen/statistiken foar dizze tabel wurde weromset,
- alle bûtenlânske kaaien binne brutsen (FK) oan tafel
D'r wie in WIP-patch fan Simon Riggs dy't suggerearre meitsje ALTER-in operaasje te ferfangen de tabel lichem op de triem nivo, sûnder oanreitsjen fan statistiken en FK, mar net sammelje quorum.
DELETE, UPDATE, Foegje
Dat, wy regelje oer de net-blokkearjende opsje fan trije operaasjes. Hast trije ... Hoe dit it meast effektyf te dwaan?
-- все делаем в рамках транзакции, чтобы никто не видел "промежуточных" состояний
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. Ymportearje post-ferwurking
Yn deselde KLADR moatte alle feroare records ek wurde útfierd troch post-ferwurking - normalisearre, kaaiwurden markearre en fermindere ta de fereaske struktueren. Mar hoe witsto - wat krekt feroaresûnder komplisearje de syngronisaasje koade, by útstek sûnder oanreitsje it op alle?
As allinich jo proses skriuwtagong hat op it momint fan syngronisaasje, dan kinne jo in trigger brûke dy't alle wizigingen foar ús sammelje:
-- целевые таблицы
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;
No kinne wy triggers tapasse foardat de syngronisaasje begjint (of se ynskeakelje fia 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();
En dan ekstrahearje wy rêstich alle wizigingen dy't wy nedich binne út 'e logtabellen en rinne se troch ekstra handlers.
3.3. Ymportearje keppele sets
Hjirboppe hawwe wy gefallen beskôge as de gegevensstruktueren fan 'e boarne en bestimming itselde binne. Mar wat as de upload fan in ekstern systeem in formaat hat oars as de opslachstruktuer yn ús databank?
Litte wy as foarbyld de opslach fan kliïnten en har akkounts nimme, de klassike opsje "in protte-op-ien":
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)
);Mar de download fan in eksterne boarne komt nei ús yn 'e foarm fan "alles yn ien":
CREATE TEMPORARY TABLE invoice_import(
client_inn
varchar
, client_name
varchar
, invoice_number
varchar
, invoice_dt
date
, invoice_sum
numeric(32,2)
);Fansels kinne klantgegevens yn dizze ferzje duplikearre wurde, en it haadrekord is "akkount":
0123456789;Вася;A-01;2020-03-16;1000.00
9876543210;Петя;A-02;2020-03-16;666.00
0123456789;Вася;B-03;2020-03-16;9999.00
Foar it model sille wy gewoan ús testgegevens ynfoegje, mar tink om - COPY effisjinter!
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);Litte wy earst dy "besunigingen" markearje wêrop ús "feiten" ferwize. Yn ús gefal ferwize faktueren nei klanten:
CREATE TEMPORARY TABLE client_import AS
SELECT DISTINCT ON(client_inn)
-- можно просто SELECT DISTINCT, если данные заведомо непротиворечивы
client_inn inn
, client_name "name"
FROM
invoice_import;Om akkounts korrekt te assosjearjen mei klant-ID's, moatte wy dizze identifiers earst útfine of generearje. Litte wy fjilden ûnder har tafoegje:
ALTER TABLE invoice_import ADD COLUMN client_id integer;
ALTER TABLE client_import ADD COLUMN client_id integer;Litte wy de hjirboppe beskreaune tabelsyngronisaasjemetoade brûke mei in lyts amendemint - wy sille neat yn 'e doeltabel bywurkje of wiskje, om't wy kliïnten "allinich taheakje" ymportearje:
-- проставляем в таблице импорта 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; -- прикладной ключ
Eins is alles yn invoice_import No hawwe wy it kontaktfjild ynfold client_id, wêrmei't wy de faktuer ynfoegje.
Boarne: www.habr.com