За сложену обраду великих скупова података (разл : увоз, конверзије и синхронизација са екстерним извором) често постоји потреба привремено „запамти“ и одмах брзо обрадити нешто обимно.
Типичан задатак ове врсте обично звучи отприлике овако: "Баш овде последње примљене уплате, потребно је да их брзо отпремите на веб локацију и повежете са својим налозима"
Али када обим овог „нечега“ почне да се мери стотинама мегабајта, а сервис мора да настави да ради са базом података 24к7, јављају се многи нежељени ефекти који ће вам уништити живот.
Да бисте се носили са њима у ПостгреСКЛ-у (и не само у њему), можете користити неке оптимизације које ће вам омогућити да све обрађујете брже и са мањом потрошњом ресурса.
1. Где послати?
Прво, одлучимо где можемо да отпремимо податке које желимо да „обрадимо“.
1.1. Привремене табеле (ТЕМПОРАРИ ТАБЛЕ)
У принципу, за ПостгреСКЛ привремене табеле су исте као и све друге. Стога, сујеверја као „Све тамо се чува само у меморији и може да се заврши“. Али постоји и неколико значајних разлика.
Ваш сопствени „именски простор“ за сваку везу са базом података
Ако две везе покушају да се повежу у исто време CREATE TABLE x, онда ће неко сигурно добити грешка нејединствености објекти базе података.
Али ако обоје покушају да изврше CREATE TEMPORARY TABLE x, онда ће и једни и други то радити нормално, и сви ће добити ваш примерак табеле. И између њих неће бити ништа заједничко.
"Самоуништење" приликом искључивања
Када се веза затвори, све привремене табеле се аутоматски бришу, дакле ручно DROP TABLE x нема сврхе осим...
Ако радите кроз пгбоунцер у режиму трансакције, онда база података наставља да верује да је ова веза и даље активна, ау њој ова привремена табела и даље постоји.
Стога, покушај да га поново креирате, са друге везе на пгбоунцер, резултираће грешком. Али ово се може заобићи употребом CREATE TEMPORARY TABLE IF NOT EXISTS x.
Истина, боље је то никако не радити, јер тада можете „одједном“ пронаћи податке преостале од „претходног власника“. Уместо тога, много је боље прочитати упутство и видети да је приликом креирања табеле могуће додати ON COMMIT DROP - то јест, када се трансакција заврши, табела ће бити аутоматски обрисана.
Нерепликација
Пошто припадају само одређеној вези, привремене табеле се не реплицирају. Али ово елиминише потребу за двоструким бележењем података у хеап + ВАЛ, па је ИНСЕРТ/УПДАТЕ/ДЕЛЕТЕ у њега много брже.
Али пошто је привремена табела и даље „скоро обична“ табела, не може се креирати ни на реплици. Бар за сада, иако одговарајућа закрпа већ дуго кружи.
1.2. УНЛОГГЕД ТАБЛЕ
Али шта би требало да урадите, на пример, ако имате неку врсту гломазног ЕТЛ процеса који се не може имплементирати у оквиру једне трансакције, али још увек имате пгбоунцер у режиму трансакције? ..
Или је проток података толико велики да Нема довољно пропусног опсега на једној вези из базе података (читај, један процес по ЦПУ-у)?..
Или су неке операције у току асинхроно у различитим везама?..
Овде постоји само једна опција - привремено креирајте непривремену табелу. Игра речи, да. То је:
- креирао „своје“ табеле са максимално насумичним именима како се не би укрштали ни са ким
- Екстракт: испунио их подацима из спољног извора
- Трансформисати: конвертован, попуњена кључна поља за повезивање
- Оптерећење: сипа готове податке у циљне табеле
- обрисали „моје“ табеле
А сада - мува у мелу. Заправо, сва уписивања у ПостгреСКЛ се дешавају два пута - , затим у тела табеле/индекса. Све ово је урађено да подржи АЦИД и исправну видљивост података између COMMIT'забринут и ROLLBACK'нулте трансакције.
Али ово нам не треба! Имамо цео процес Или је било потпуно успешно или није.. Није битно колико ће међутрансакција бити – нисмо заинтересовани да „настављамо процес из средине“, посебно када није јасно где је то било.
Да би то урадили, програмери ПостгреСКЛ-а, још у верзији 9.1, представили су нешто као :
Са овом индикацијом, табела се креира као нерегистрована. Подаци уписани у нерегистроване табеле не пролазе кроз дневник уписивања унапред (погледајте Поглавље 29), што доводи до тога да такве табеле раде много брже него обично. Међутим, они нису имуни на неуспех; у случају квара сервера или гашења у нужди, нерегистрована табела аутоматски скраћено. Додатно, садржај нерегистроване табеле није реплицирано на славе сервере. Сви индекси креирани на нерегистрованој табели аутоматски постају нерегистровани.
Укратко биће много брже, али ако сервер базе података „падне“, то ће бити непријатно. Али колико често се то дешава и да ли ваш ЕТЛ процес зна како да то исправно исправи „из средине“ након „ревитализације“ базе података?..
Ако није, а горњи случај је сличан вашем, користите UNLOGGEDали никад не омогућавајте овај атрибут на стварним табелама, подаци из којих су Вам драги.
1.3. ОН ЦОММИТ { ИЗБРИШИ РЕДОВЕ | КАП}
Ова конструкција вам омогућава да наведете аутоматско понашање када се трансакција заврши приликом креирања табеле.
О томе ON COMMIT DROP Већ сам написао горе, генерише DROP TABLE, Али са ON COMMIT DELETE ROWS ситуација је интересантнија – овде се генерише TRUNCATE TABLE.
Пошто је цела инфраструктура за складиштење мета-описа привремене табеле потпуно иста као и обичне табеле, онда Стално креирање и брисање привремених табела доводи до озбиљног „отицања“ системских табела пг_цласс, пг_аттрибуте, пг_аттрдеф, пг_депенд,…
Сада замислите да имате радника на директној вези са базом података, који сваке секунде отвара нову трансакцију, креира, попуњава, обрађује и брише привремену табелу... У системским табелама ће се накупити вишак смећа и ово ће изазвати додатне кочнице за сваку операцију.
Генерално, немојте то радити! У овом случају је много ефикасније CREATE TEMPORARY TABLE x ... ON COMMIT DELETE ROWS извадите га из циклуса трансакције - тада су до почетка сваке нове трансакције табеле већ постојаће (сачувајте позив CREATE), али биће празан, хвала TRUNCATE (такође смо сачували његов позив) приликом завршетка претходне трансакције.
1.4. КАО...УКЉУЧУЈУЋИ...
На почетку сам поменуо да је један од типичних случајева употребе привремених табела разне врсте увоза - а програмер уморно копира-пејста листу поља циљне табеле у декларацију свог привременог...
Али лењост је мотор напретка! Зато креирајте нову табелу „на основу узорка“ може бити много једноставније:
CREATE TEMPORARY TABLE import_table(
LIKE target_table
);Пошто тада можете да генеришете много података у овој табели, претраживање кроз њу никада неће бити брзо. Али постоји традиционално решење за ово - индекси! И да, привремена табела такође може имати индексе.
Пошто се, често, потребни индекси поклапају са индексима циљне табеле, можете једноставно писати LIKE target_table INCLUDING INDEXES.
Ако и вама треба DEFAULT-вредности (на пример, за попуњавање вредности примарног кључа), можете користити LIKE target_table INCLUDING DEFAULTS. Или једноставно - LIKE target_table INCLUDING ALL — копира подразумеване вредности, индексе, ограничења,...
Али овде морате да схватите да ако сте креирали одмах увезите табелу са индексима, тада ће подацима требати дуже да се учитајунего ако прво све попуниш, па тек онда умоташ индексе - погледај како то ради као пример .
У принципу, !
2. Како писати?
Само да кажем - искористите -проток уместо "паковања" INSERT, . Можете чак и директно из унапред генерисане датотеке.
3. Како обрадити?
Дакле, хајде да наш увод изгледа овако:
- имате табелу са подацима о клијентима ускладиштеним у вашој бази података 1М записа
- сваки дан вам клијент шаље нову пуна "слика"
- из искуства знате да с времена на време не мења се више од 10К записа
Класичан пример такве ситуације је — укупно има много адреса, али у сваком недељном уплоад-у има врло мало промена (преименовање насеља, комбиновање улица, изглед нових кућа) чак и на националном нивоу.
3.1. Потпуни алгоритам синхронизације
Ради једноставности, рецимо да не морате чак ни да реструктурирате податке - само доведите табелу у жељени облик, то јест:
- уклонити све што више не постоји
- надоградња све што је већ постојало и што треба да се ажурира
- убаци све што се још није догодило
Зашто би операције требало да се раде овим редоследом? Зато што ће на овај начин величина табеле минимално расти ().
ИЗБРИШИ ИЗ дст
Не, наравно можете проћи са само две операције:
- уклонити (
DELETE) све уопште - убаци све са нове слике
Али у исто време, захваљујући МВЦЦ, Величина табеле ће се повећати тачно два пута! Добијање +1М слика записа у табели због ажурирања од 10К је тако-тако вишак...
ТРУНЦАТЕ дст
Искуснији програмер зна да се цео таблет може очистити прилично јефтино:
- јасно (
TRUNCATE) цела табела - убаци све са нове слике
Метода је ефикасна, , али постоји проблем... Додаћемо 1М записа још дуго, тако да не можемо себи приуштити да оставимо табелу празну све ово време (као што ће се догодити без умотавања у једну трансакцију).
Што значи:
- почињемо дуготрајна трансакција
TRUNCATEнамеће АццессЕкцлусиве-блокирање- убацивање радимо дуго, а сви остали у овом тренутку не могу ни
SELECT
Нешто не иде добро...
ИЗМЕНИ ТАБЕЛУ… ПРЕИМЕНИ… / ИСПУСТИ ТАБЕЛУ…
Алтернатива је да се све попуни у посебну нову табелу, а затим је једноставно преименује уместо старе. Пар гадних ситница:
- још увек такође АццессЕкцлусиве, иако знатно мање времена
- сви планови/статистика упита за ову табелу су ресетовани,
- сви страни кључеви су покварени (ФК) за сто
Постојала је ВИП закрпа од Симон Риггса која је предложила израду ALTER-операција замене тела табеле на нивоу фајла, без додиривања статистике и ФК, али није прикупио кворум.
ИЗБРИШИ, АЖУРИРАЈ, УМЕТНИ
Дакле, одлучујемо се о неблокирајућој опцији од три операције. Скоро три... Како то најефикасније урадити?
-- все делаем в рамках транзакции, чтобы никто не видел "промежуточных" состояний
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. Увозна накнадна обрада
У истом КЛАДР-у сви измењени записи морају се додатно проћи кроз накнадну обраду – нормализовани, истакнуте кључне речи и сведене на потребне структуре. Али како знаш - шта се тачно променилобез компликовања кода за синхронизацију, идеално без додиривања уопште?
Ако само ваш процес има приступ за писање у време синхронизације, онда можете да користите окидач који ће прикупити све промене за нас:
-- целевые таблицы
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;
Сада можемо применити окидаче пре почетка синхронизације (или их омогућити преко 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();
А онда мирно извлачимо све промене које су нам потребне из табела дневника и покрећемо их кроз додатне руковаоце.
3.3. Увоз повезаних скупова
Горе смо размотрили случајеве када су структуре података извора и одредишта исте. Али шта ако отпремање са спољног система има формат који се разликује од структуре складиштења у нашој бази података?
Узмимо као пример складиштење клијената и њихових налога, класичну опцију „више на један“:
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)
);Али преузимање са спољног извора долази нам у облику „све у једном“:
CREATE TEMPORARY TABLE invoice_import(
client_inn
varchar
, client_name
varchar
, invoice_number
varchar
, invoice_dt
date
, invoice_sum
numeric(32,2)
);Очигледно, подаци о клијентима могу да се дуплирају у овој верзији, а главни запис је „налог“:
0123456789;Вася;A-01;2020-03-16;1000.00
9876543210;Петя;A-02;2020-03-16;666.00
0123456789;Вася;B-03;2020-03-16;9999.00
За модел, једноставно ћемо уметнути наше тестне податке, али запамтите - COPY ефикаснији!
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);Прво, хајде да истакнемо оне „резове“ на које се односе наше „чињенице“. У нашем случају, фактуре се односе на купце:
CREATE TEMPORARY TABLE client_import AS
SELECT DISTINCT ON(client_inn)
-- можно просто SELECT DISTINCT, если данные заведомо непротиворечивы
client_inn inn
, client_name "name"
FROM
invoice_import;Да бисмо исправно повезали налоге са ИД-овима клијената, прво морамо да сазнамо или генеришемо ове идентификаторе. Хајде да додамо поља испод њих:
ALTER TABLE invoice_import ADD COLUMN client_id integer;
ALTER TABLE client_import ADD COLUMN client_id integer;Хајде да користимо метод синхронизације табеле описан горе са малом изменом - нећемо ажурирати или избрисати ништа у циљној табели, јер увозимо клијенте „само за додавање“:
-- проставляем в таблице импорта 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; -- прикладной ключ
У ствари, све је унутра invoice_import Сада имамо попуњено поље за контакт client_id, уз које ћемо уметнути рачун.
Извор: ввв.хабр.цом