DBA: компетентно организирајте синхронизации и увоз

За сложена обработка на големи збирки податоци (различни ETL процеси: увоз, конверзии и синхронизација со надворешен извор) често има потреба привремено „запомнете“ и веднаш брзо обработете нешто обемно.

Типична задача од овој вид обично звучи вака: "Точно тука сметководство истоварено од клиентската банка последните примени плаќања, треба брзо да ги прикачите на веб-локацијата и да ги поврзете со вашите сметки“.

Но, кога обемот на ова „нешто“ почнува да се мери во стотици мегабајти, а услугата мора да продолжи да работи со базата на податоци 24x7, се појавуваат многу несакани ефекти кои ќе ви го уништат животот.

За да се справите со нив во PostgreSQL (и не само во него), можете да користите некои оптимизации кои ќе ви овозможат да обработите сè побрзо и со помала потрошувачка на ресурси.

1. Каде да се испорача?

Прво, да одлучиме каде можеме да ги поставиме податоците што сакаме да ги „обработиме“.

1.1. Привремени табели (ПРИВРЕМЕНА ТАБЕЛА)

Во принцип, за PostgreSQL привремените табели се исти како и сите други. Затоа, суеверија како „Таму сè е зачувано само во меморијата и може да заврши“. Но, постојат и неколку значајни разлики.

Ваш сопствен „именски простор“ за секоја врска со базата на податоци

Ако две конекции се обидат да се поврзат истовремено CREATE TABLE x, тогаш некој дефинитивно ќе добие грешка во неуникатноста објекти на базата на податоци.

Но, ако и двајцата се обидат да извршат CREATE TEMPORARY TABLE x, тогаш и двајцата ќе го направат тоа нормално, и сите ќе добијат вашата копија табели. И нема да има ништо заедничко меѓу нив.

„Самоуништување“ при исклучување

Кога врската е затворена, сите привремени табели автоматски се бришат, па рачно DROP TABLE x нема поента освен...

Ако работите преку pgbouncer во режим на трансакција, тогаш базата на податоци продолжува да верува дека оваа врска е сè уште активна, и во неа оваа привремена табела сè уште постои.

Затоа, обидот повторно да го креирате, од различна врска со pgbouncer, ќе резултира со грешка. Но, ова може да се заобиколи со користење CREATE TEMPORARY TABLE IF NOT EXISTS x.

Точно, подобро е да не го правите тоа во секој случај, затоа што тогаш можете „одеднаш“ да ги најдете таму податоците што остануваат од „претходниот сопственик“. Наместо тоа, многу е подобро да го прочитате упатството и да видите дека при креирањето табела е можно да се додаде ON COMMIT DROP - односно, кога трансакцијата ќе заврши, табелата автоматски ќе се избрише.

Не-репликација

Бидејќи тие припаѓаат само на одредена врска, привремените табели не се реплицираат. Но ова ја елиминира потребата за двојно снимање на податоците во куп + WAL, па Вметнете/Ажурирање/БРИШИ во него е многу побрзо.

Но, бидејќи привремената табела е сè уште „речиси обична“ табела, таа не може да се креира ниту на реплика. Барем засега, иако соодветниот фластер веќе подолго време циркулира.

1.2. ОТПИШАНА ТАБЕЛА

Но, што треба да направите, на пример, ако имате некој вид незгоден процес на ETL што не може да се имплементира во една трансакција, но сепак имате pgbouncer во режим на трансакција? ..

Или протокот на податоци е толку голем што Нема доволно пропусен опсег на една врска од база на податоци (читај, еден процес по процесор)?..

Или се водат некои операции асинхроно во различни врски?..

Овде има само една опција - привремено креирајте непривремена табела. Карама, да. Тоа е:

• создадов „мои сопствени“ табели со максимално случајни имиња за да не се вкрстуваат со никого
• Екстракт: ги пополнил со податоци од надворешен извор
• Трансформира: конвертирано, пополнето во полињата за поврзување на клучеви
• Товар: истури готови податоци во целните табели
• избришани „мои“ табели

И сега - мува во маст. Всушност, сите пишувања во PostgreSQL се случуваат двапати - прво во ВАЛ, потоа во табелата/телата на индексот. Сето ова е направено за поддршка на ACID и правилна видливост на податоците помеѓу COMMIT„Нути и ROLLBACK'нулти трансакции.

Но, ова не ни треба! Го имаме целиот процес Или беше целосно успешен или не беше.. Не е важно колку посредни трансакции ќе има - не сме заинтересирани „да го продолжиме процесот од средината“, особено кога не е јасно каде беше.

За да го направите ова, развивачите на PostgreSQL, уште во верзијата 9.1, воведоа такво нешто како ОТПИШАНИ табели:

Со оваа индикација, табелата се креира како нелогирана. Податоците напишани на ненајавени табели не поминуваат низ дневникот за однапред запишување (види Поглавје 29), што предизвикува таквите табели да работи многу побрзо од вообичаеното. Сепак, тие не се имуни на неуспех; во случај на дефект на серверот или итно исклучување, ненајавена табела автоматски скратена. Дополнително, содржината на ненајавената табела не се реплицира на роб сервери. Сите индекси создадени на нелогирана табела автоматски се отпишуваат.

Накусо ќе биде многу побрзо, но ако серверот на базата на податоци „падне“, тоа ќе биде непријатно. Но, колку често се случува ова и дали вашиот процес на ETL знае како правилно да го поправи тоа „од средината“ откако ќе ја „ревитализира“ базата на податоци?..

Ако не, а случајот погоре е сличен на вашиот, користете UNLOGGEDно никогаш не го овозможувајте овој атрибут на вистински табели, податоците од кои ви се драги.

1.3. ON COMMIT { ИЗБРИШЕ РЕДИТЕ | КАПКА}

Оваа конструкција ви овозможува да наведете автоматско однесување кога трансакцијата е завршена при креирање табела.

на ON COMMIT DROP Веќе напишав погоре, генерира DROP TABLE, но со ON COMMIT DELETE ROWS ситуацијата е поинтересна - овде се генерира TRUNCATE TABLE.

Бидејќи целата инфраструктура за складирање на мета-опис на привремена табела е потполно иста како онаа на обична табела, тогаш Постојаното креирање и бришење на привремени табели доведува до сериозно „отекување“ на системските табели pg_class, pg_attribute, pg_attrdef, pg_depend,…

Сега замислете дека имате работник на директно поврзување со базата на податоци, кој секоја секунда отвора нова трансакција, креира, пополнува, обработува и брише привремена табела... Ќе има вишок ѓубре насобрано во системските табели, и ова ќе предизвика дополнителни сопирачки за секоја операција.

Во принцип, не правете го ова! Во овој случај, тоа е многу поефикасно 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 — ги копира стандардните, индекси, ограничувања,...

Но, тука треба да разберете дека ако сте создале увезете табела веднаш со индекси, тогаш податоците ќе треба подолго да се вчитаатотколку ако прво пополните сè, па дури потоа завиткајте ги индексите - погледнете како го прави ова како пример pg_dump.

Во принцип, RTFM!

2. Како се пишува?

Само да кажам - искористете го COPY-проток наместо „пакет“ INSERT, забрзување на моменти. Можете дури и директно од претходно генерирана датотека.

3. Како да се обработи?

Значи, да дозволиме нашиот вовед да изгледа вака:

• имате табела со податоци за клиентот зачувана во вашата база на податоци Записи од 1 милион
• секој ден клиентот ви испраќа нов целосна „слика“
• од искуство знаеш дека од време на време не се менуваат повеќе од 10K записи

Класичен пример за таква ситуација е KLADR база — вкупно има многу адреси, но во секое неделно прикачување има многу малку промени (преименување на населени места, комбинирање улици, изглед на нови куќи) дури и на национално ниво.

3.1. Алгоритам за целосна синхронизација

За едноставност, да речеме дека дури и не треба да ги реструктуирате податоците - само доведете ја табелата во посакуваната форма, односно:

• отстрани се што повеќе не постои
• надградба се што веќе постоело и треба да се ажурира
• да вметнете се што се уште не се случило

Зошто операциите треба да се прават по овој редослед? Затоа што вака големината на масата ќе порасне минимално (запомнете го MVCC!).

БРИШИ ОД dst

Не, се разбира дека можете да поминете со само две операции:

• отстрани (DELETE) сè воопшто
• да вметнете се од новата слика

Но, во исто време, благодарение на MVCC, Големината на табелата ќе се зголеми точно двапати! Добивањето +1M слики од записи во табелата поради ажурирање од 10K е толку вишок...

СУРНИ дст

Поискусен развивач знае дека целиот таблет може да се исчисти прилично евтино:

• јасно (TRUNCATE) целата табела
• да вметнете се од новата слика

Методот е ефикасен, понекогаш сосема применливо, но има проблем... Ќе додаваме записи од 1M долго време, така што не можеме да си дозволиме да ја оставиме табелата празна за сето ова време (како што ќе се случи без да ја завиткаме во една трансакција).

Што значи:

• почнуваме долготрајна трансакција
• TRUNCATE наметнува Пристап Ексклузив- блокирање
• ние го правиме вметнувањето долго време, а сите други во ова време дури и не може SELECT

Нешто не оди добро...

ПРОМЕНИ ТАБЕЛА… ПРЕИМЕНИ… / ОСТАВИ ТАБЕЛА…

Алтернатива е да се пополни сè во посебна нова табела, а потоа едноставно да се преименува на местото на старата. Неколку непријатни ситници:

• сепак исто така Пристап Ексклузив, иако значително помалку време
• сите планови/статистички податоци за пребарување за оваа табела се ресетирани, треба да се изврши ANALYZE
• сите странски клучеви се скршени (ФК) до масата

Имаше WIP лепенка од Сајмон Ригс што предложи правење ALTER-операција за замена на телото на табелата на ниво на датотека, без допирање на статистиката и FK, но не собра кворум.

ИЗБРИШИ, АЖУРИРАЈ, ВНЕСИ

Значи, се задоволуваме со опцијата за неблокирање од три операции. Речиси три... Како да го направите ова најефективно?

-- все делаем в рамках транзакции, чтобы никто не видел "промежуточных" состояний
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. Увоз пост-обработка

Во истиот KLADR, сите променети записи мора дополнително да се извршат преку пост-обработка - нормализирани, означени клучни зборови и сведени на потребните структури. Но, како знаеш - што точно се сменибез комплицирање на кодот за синхронизација, идеално без воопшто да го допирате?

Ако само вашиот процес има пристап за запишување во моментот на синхронизација, тогаш можете да користите активирач што ќе ги собере сите промени за нас:

-- целевые таблицы
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, со кој ќе ја вметнеме фактурата.

Извор: www.habr.com