При сложна обработка на големи набори данни (различни : импортиране, конвертиране и синхронизиране с външен източник) често възниква необходимост временно да се „запомни“ и бързо да се обработи нещо обемно.
Типичната задача от този род обикновено звучи така: „Тук последните постъпили плащания, трябва да ги качим бързо на сайта и да ги свържем с акаунтите“
Но когато обемът на това „нещо“ започне да се измерва в стотици мегабайти и услугата трябва да продължи да работи с базата в режим 24×7, възникват множество странични ефекти, които ще влошат ситуацията.
За да се справите с тях в PostgreSQL (и не само там), можете да използвате някои възможности за оптимизация, които ще позволят да се обработи всичко по-бързо и с по-малко ресурсна консумация.
1. Къде да качим?
Първо, нека да определим къде можем да заредим данните, които искаме да „обработим“.
1.1. Временни таблици (TEMPORARY TABLE)
В принцип, за PostgreSQL временните таблици са същите като всички останали таблици. Затова суеверията като „там всичко се съхранява само в паметта, а тя може да свърши“не са верни. Но има и няколко съществени различия.
Свой „неймспейс“ за всяко свързване към базата данни
Ако две свързвания опитат едновременно да изпълнят CREATE TABLE x, то някой определено ще получи грешка за уникалност на обектите в базата данни.
Ако обаче и двете опитат да изпълнят CREATE ВРЕМЕНЕН ТАБЛИЦА x, то и двете ще го направят нормално и всяко ще получи свой екземпляр на таблицата. И няма да има нищо общо между тях.
„Автоматично изтриване“ при разпадане на връзката
При затваряне на връзката всички временни таблици автоматично се изтриват, затова „ръчно“ да изпълнявате DROP TABLE x няма никакъв смисъл, освен...
Ако работите през pgbouncer в режим на транзакции, то базата все пак счита, че това свързване е все още активно и в него тази временна таблица все още съществува.
Затова опитът за повторно създаване на таблицата от друго свързване към pgbouncer ще доведе до грешка. Но това може да се заобиколи, като се използва СЪЗДАЙТЕ ВРЕМЕННА ТАБЛИЦА АКО НЯМА x.
Наистина, по-добре е да не го правите, защото по-късно може да „откриете“ там данни, останали от „предишния собственик“. Вместо това е много по-добре да прочетете ръководството и да видите, че при създаване на таблица имате възможност да добавите Когато се ангажира Изтриване — т.е. при приключване на транзакцията таблицата ще бъде автоматично изтрита.
Не-репликация
Поради принадлежността си само на определено свързване, временните таблици не се репликират. Въпреки това това освобождава от необходимостта от двойно записване на данни в heap + WAL, затова INSERT/UPDATE/DELETE в нея е значително по-бързо.
Но тъй като временната таблица е все пак "почти обикновена" таблица, тя не може да бъде създадена и на репликата. Поне в момента, въпреки че съответният патч отдавна е наличен.
1.2. Нежурналируеми таблици (UNLOGGED TABLE)
Но какво да правите, например, ако имате някакъв обемен ETL процес, който не може да бъде осъществен в рамките на една транзакция, а вие имате pgbouncer в режим на транзакции?..
Или потокът от данни е толкова голям, че пропускната способност на едно свързване с БД (чети, един процес на CPU)?..
Или част от операциите вървят асинхронно в различни свързвания?..
Тук вариантът е само един — временно да създадете не-временна таблица. Каламбур, а? Тоест:
- създадох "своите" таблици с максимално случайни имена, за да не се пресекат с никого
- Extract: заредих в тях данни от външен източник
- Transform: преобразувах и попълних ключовите свързващи полета
- Натоварване: преливам готовите данни в целевите таблици
- изтрих "своите" таблици
А сега — лъжица катран. Всъщност, всички записи в PostgreSQL се извършват два пъти — , след това вече в телата на таблиците/индексите. Всичко това е направено за поддръжка на ACID и коректна видимост на данните между COMMITизпълненията и ROLLBACKизпълнените транзакции.
Но на нас не ни трябва това! Целият процес или мина успешно, или не. Няма значение колко междинни транзакции ще има — не ни интересува "да продължим процеса от средата", особено когато не е ясно къде е била.
За това разработчиците на PostgreSQL още в версия 9.1 внедриха нещо като :
С това указание таблицата се създава като нежурналируема. Данните, записвани в нежурналируеми таблици, не минават през журнала за предзапис (вижте Глава 29), в резултат на което такива таблици работят много по-бързо от обикновените. Въпреки това, те не са защитени от срив; при срив или аварийно изключване на сървъра нежурналируемата таблица автоматично се отрязва. Освен това, съдържанието на нежурналируемата таблица не се реплицира. на управлявани сървъри. Всички индекси, създадени за незаписваща се таблица, автоматично стават незаписващи се.
Накратко, ще бъде значително по-бързо, но ако базата данни „падне“ — ще бъде неприятно. Но колко често се случва това и може ли вашият ETL процес да го коригира правилно „от средата“ след „възраждането“ на БД?..
Ако все пак не, и случаят по-горе е подобен на вашия — използвайте UNLOGGED, но никога не включвайте този атрибут в реални таблици, чиито данни са важни за вас.
1.3. ON COMMIT { DELETE ROWS | DROP }
Тази конструкция позволява при създаването на таблица да се зададе автоматично поведение при завършване на транзакцията.
Относно Когато се ангажира Изтриване вече написах по-горе, той генерира DROP TABLE, а ето с Когато се ангажира ИЗТРИЙ РЕДОВЕ ситуацията е по-интересна — тук се генерира TRUNCATE TABLE.
Тъй като цялата инфраструктура за съхранение на метаописания за временното таблица е същата като при обикновените, то постоянното създаване и изтриване на временни таблици води до значително „набъбване“ на системните таблици pg_class, pg_attribute, pg_attrdef, pg_depend,…
Сега си представете, че имате работник на директно свързване с БД, който всяка секунда отваря нова транзакция, създава, запълва, обработва и изтрива временна таблица… В системните таблици ще се натрупа много боклук, а това означава излишно забавяне при всяка операция.
В обобщение, не правете така! В този случай е много по-ефективно CREATE TEMPORARY TABLE x ... ON COMMIT DELETE ROWS да изнесете извън цикъла на транзакции — тогава в началото на всяка нова транзакция таблиците вече ще съществуват (спестяваме извикването CREATE), но ще бъде празна, благодарение на TRUNCATE (и неговото извикване също спестихме) при завършване на предишната транзакция.
1.4. LIKE… INCLUDING …
Споменах в началото, че един от типичните случаи на използване за временни таблици — това са различни видове импорти — и разработчикът лениво копира списъка с полета на целевата таблица в декларацията на своята временна…
Но мързелът е двигателят на прогреса! Затова създаването на нова таблица „по образец“ е много по-лесно:
CREATE TEMPORARY TABLE import_table(
LIKE target_table
);Тъй като можете да генерирате наистина много данни в тази таблица, търсенето в нея няма да е бързо. Но против това има традиционно решение — индексите! И, да, временната таблица също може да има индекси.
Тъй като често нужните индекси съвпадат с индексите на целевата таблица, можете просто да напишете КАТО target_table ВКЛЮЧИТЕЛНО ИНДЕКСИТЕ.
Ако имате нужда и от DEFAULT-значения (например, за попълване на стойности на първичния ключ), можете да използвате КАТО target_table ВКЛЮЧВАЩИ ДЕФАУЛТНИ ЗНАЧЕНИЯ. Или просто — КАТО target_table ВКЛЮЧВАШЕ ВСИЧКО — ще копира по подразбиране, индекси, ограничения,…
Но тук вече трябва да разберете, че ако сте създали импортна таблица веднага с индекси, то данните ще се внасят по-дълго, отколкото ако първо заредите всичко и след това добавите индексите — вижте за пример, как го прави .
В общи линии, !
2. Как да пишем?
Ще кажа просто — използвайте -поток вместо „пакет“ INSERT, . Можете дори директно от предварително форматиран файл.
3. Как да обработваме?
И така, нека нашето начало изглежда приблизително така:
- в базата ви има таблица с клиентски данни на 1M записи
- всеки ден клиент изпраща нов пълен „образ“
- по опит знаете, че от път до път появяват се не повече от 10K записи
Класическият пример за подобна ситуация е — общо адресите са много, но в всяка седмична експортиране на промените (преименувания на населени места, обединения на улици, появяване на нови сгради) има съвсем малко дори в мащабите на цялата страна.
3.1. Алгоритъм на пълната синхронизация
За простота да предположим, че дори не ви е нужно да реструктурирате данните — просто да приведете таблицата в нужния вид, т.е.:
- да изтриете всичко, което вече не съществува
- да актуализират всичко, което вече е било и трябва да обновявате
- да добавите всичко, което още не е било
Защо точно в такъв ред е нужно да се извършват операциите? Защото точно така размерът на таблицата ще нарасне минимално ().
DELETE FROM dst
Не, разбира се можете да се справите само с две операции:
- да изтриете (
DELETE) всъщност всичко - да добавите всички от новия образ
Но в същото време, благодарение на MVCC, размерът на таблицата ще се увеличи точно два пъти! Получаване на +1M образи на записи в таблицата заради обновление на 10K — да, излишно…
TRUNCATE dst
По-опитният разработчик знае, че цялата таблица може да бъде достатъчно евтино очистена:
- да очистите (
TRUNCATE) таблицата цяла - да добавите всички от новия образ
Методът е ефективен, , но има един проблем… Вливането на 1M записи ще отнеме д-о-олго, така че да оставим таблицата празна за всичкото това време (както ще се случи без обграждане в една транзакция) не можем да си позволим.
А значи:
- при нас започва дълга транзакция
TRUNCATEналага AccessExclusive-блокировка- дълго правим вмъкването, а всичките останали по това време не могат дори
SELECT
Нещата не изглеждат добре...
Промяна на таблица… Преименуване… / Изтриване на таблица …
Вариант — да се излее всичко в отделна нова таблица и след това просто да се променят на мястото на старата. Няколко досадни детайла:
- така също AccessExclusive, макар и значително по-малко по време
- всички планове за заявки/статистика на тази таблица се сбрасырат,
- разрушават се всички външни ключове (FK) на таблицата
Имаше WIP пач от Simon Riggs, който предложи да се направи ALTER-операция за подмяна на тялото на таблицата на файлово ниво, без да се пипа статистиката и FK, но не събра кворум.
DELETE, UPDATE, INSERT
И така, спираме на неблокиращия вариант от три операции. Почти три… Как да го направим най-ефективно?
-- правим всичко в рамките на транзакцията, за да не вижда никой "промеждутъчен" статус
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);Първо ще отделим тези „разрези“, на които нашите „факти“ се отнасят. В нашия случай сметките се отнасят към клиентите:
СЪЗДАЙТЕ ВРЕМЕННА ТАБЛИЦА client_import КАТО
ИЗБЕРЕТЕ ДИСТИНКТНО ПО(client_inn)
-- може просто да се използва SELECT DISTINCT, ако данните са предварително нес противоречиви
client_inn inn
, client_name "name"
ОТ
invoice_import;За да могат фактурите да бъдат коректно свързани с ID на клиентите, първо трябва да разберем или да генерираме тези идентификатори. Ще добавим полета за тях:
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; -- уникален ключ
-- вмъкваме липсващите записи и им присвояваме ID
С С ins КАТО (
ВМЪКНЕТЕ В client(
inn
, name
)
ИЗБЕРЕТЕ
inn
, name
ОТ
client_import
WHERE
client_id IS NULL -- ако ID не е присвоен
ВРЪЩАЙКИ *
)
UPDATE
client_import T
SET
client_id = D.client_id
FROM
ins D
WHERE
T.inn = D.inn; -- уникален ключ
-- присвояваме 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, с което ще добавим фактурата.
Източник: habr.com
