🥇DBA: organitza de manera competent la sincronització i les importacions

Per al processament complex de grans conjunts de dades (diferents Processos ETL: importacions, conversions i sincronització amb una font externa) sovint hi ha una necessitat "recorda" temporalment i immediatament processa ràpidament quelcom voluminós.

Una tasca típica d'aquest tipus sol semblar a això: "Aquí mateix departament de comptabilitat descarregat del banc client els darrers pagaments rebuts, els heu de pujar ràpidament al lloc web i enllaçar-los als vostres comptes"

Però quan el volum d'aquest "alguna cosa" comença a mesurar-se en centenars de megabytes i el servei ha de continuar funcionant amb la base de dades les 24 hores del dia, els 7 dies de la setmana, sorgeixen molts efectes secundaris que us arruïnaran la vida.

Per tractar-los a PostgreSQL (i no només en ell), podeu utilitzar algunes optimitzacions que us permetran processar-ho tot més ràpidament i amb menys consum de recursos.

1. On enviar?

Primer, decidim on podem penjar les dades que volem "processar".

1.1. Taules temporals (TAULA TEMPORAL)

En principi, per a PostgreSQL les taules temporals són les mateixes que qualsevol altra. Per tant, supersticions com "Tot el que hi ha només s'emmagatzema a la memòria i pot acabar". Però també hi ha diverses diferències significatives.

El vostre propi "espai de noms" per a cada connexió a la base de dades

Si dues connexions intenteu connectar-vos al mateix temps CREATE TABLE x, llavors algú ho aconseguirà definitivament error de no unicitat objectes de base de dades.

Però si tots dos intenten executar CREATE TEMPORARY TABLE x, llavors tots dos ho faran amb normalitat, i tothom ho aconseguirà la teva còpia taules. I no hi haurà res en comú entre ells.

"Autodestrucció" en desconnectar

Quan es tanca la connexió, totes les taules temporals s'eliminen automàticament, per tant manualment DROP TABLE x no té sentit excepte...

Si esteu treballant pgbouncer en mode de transacció, aleshores la base de dades continua creient que aquesta connexió encara està activa, i en ella encara existeix aquesta taula temporal.

Per tant, si intenteu crear-lo de nou, des d'una connexió diferent a pgbouncer, es produirà un error. Però això es pot evitar mitjançant l'ús CREATE TEMPORARY TABLE IF NOT EXISTS x.

És cert que és millor no fer-ho de totes maneres, perquè aleshores podreu trobar "de sobte" les dades que queden del "propietari anterior". En canvi, és molt millor llegir el manual i veure que en crear una taula és possible afegir-hi ON COMMIT DROP - és a dir, quan finalitzi la transacció, la taula s'eliminarà automàticament.

No replicació

Com que només pertanyen a una connexió específica, les taules temporals no es repliquen. Però això elimina la necessitat d'enregistrament doble de dades a l'munt + WAL, de manera que INSERT/UPDATE/DELETE és significativament més ràpid.

Però com que una taula temporal encara és una taula "gairebé normal", tampoc no es pot crear en una rèplica. Almenys de moment, tot i que fa temps que circula el pegat corresponent.

1.2. TAULA NO REGISTRAT

Però què hauríeu de fer, per exemple, si teniu algun tipus de procés ETL complicat que no es pot implementar en una transacció, però encara teniu pgbouncer en mode de transacció? ..

O el flux de dades és tan gran que No hi ha prou amplada de banda en una connexió d'una base de dades (llegir, un procés per CPU)?...

O s'estan fent algunes operacions de manera asíncrona en diferents connexions?...

Aquí només hi ha una opció: crear temporalment una taula no temporal. Joc de paraules, sí. Això és:

vaig crear taules "les meves" amb noms màxims aleatoris per no creuar-se amb ningú
Extreure: els va omplir amb dades d'una font externa
Transformar: convertit, emplenat amb camps d'enllaç clau
Càrrega: va abocar dades preparades a les taules de destinació
S'han suprimit les "meves" taules

I ara - una mosca a la pomada. De fet, totes les escriptures a PostgreSQL es produeixen dues vegades - primer a WAL, després als cossos de la taula/índex. Tot això es fa per donar suport a ACID i corregir la visibilitat de les dades entre COMMIT'nou i ROLLBACK'transaccions nul·les.

Però això no ho necessitem! Tenim tot el procés O va tenir un èxit total o no ho va ser.. No importa quantes transaccions intermèdies hi haurà: no ens interessa "continuar el procés des del mig", sobretot quan no està clar on era.

Per fer-ho, els desenvolupadors de PostgreSQL, a la versió 9.1, van introduir una cosa com Taules NO REGISTRADES:

Amb aquesta indicació, la taula es crea com a no registrada. Les dades escrites a taules no registrades no passen pel registre d'escriptura anticipada (vegeu el capítol 29), cosa que fa que aquestes taules treballar molt més ràpid del que és habitual. Tanmateix, no són immunes al fracàs; en cas de fallada del servidor o tancament d'emergència, una taula no registrada truncat automàticament. A més, el contingut de la taula no registrada no replicat als servidors esclaus. Qualsevol índex creat en una taula no registrada es desregistra automàticament.

En definitiva, serà molt més ràpid, però si el servidor de bases de dades "cau", serà desagradable. Però amb quina freqüència passa això i el vostre procés ETL sap com corregir-ho correctament "des del mig" després de "revitalitzar" la base de dades?...

Si no, i el cas anterior és similar al vostre, feu servir UNLOGGEDperò mai no habiliteu aquest atribut a les taules reals, les dades de les quals us són estimades.

1.3. ON COMMIT { ELIMINAR FILES | TIRAR}

Aquesta construcció us permet especificar un comportament automàtic quan es completa una transacció en crear una taula.

Про ON COMMIT DROP Ja he escrit més amunt, genera DROP TABLE, però amb ON COMMIT DELETE ROWS la situació és més interessant: aquí es genera TRUNCATE TABLE.

Com que tota la infraestructura per emmagatzemar la metadescripció d'una taula temporal és exactament la mateixa que la d'una taula normal, aleshores La creació i supressió constants de taules temporals condueixen a una "inflació" severa de les taules del sistema pg_class, pg_attribute, pg_attrdef, pg_depend,...

Ara imagineu que teniu un treballador en connexió directa a la base de dades, que obre una nova transacció cada segon, crea, omple, processa i elimina una taula temporal... Hi haurà un excés d'escombraries acumulades a les taules del sistema, i això provocarà frens addicionals per a cada operació.

En general, no feu això! En aquest cas és molt més efectiu CREATE TEMPORARY TABLE x ... ON COMMIT DELETE ROWS traieu-lo del cicle de transaccions; llavors, al començament de cada nova transacció, les taules ja estan existirà (guardeu una trucada CREATE), però estarà buit, gràcies a TRUNCATE (també hem desat la seva trucada) en completar la transacció anterior.

1.4. COM... INCLÒS...

Vaig esmentar al principi que un dels casos d'ús típics de les taules temporals són diversos tipus d'importació, i el desenvolupador enganxa amb cansament la llista de camps de la taula de destinació a la declaració del seu temporal...

Però la mandra és el motor del progrés! Aixo es perqué crear una taula nova "basada en la mostra" pot ser molt més senzill:

CREATE TEMPORARY TABLE import_table(
  LIKE target_table
);

Com que aleshores podeu generar moltes dades en aquesta taula, la cerca mai no serà ràpida. Però hi ha una solució tradicional a això: els índexs! I, sí, una taula temporal també pot tenir índexs.

Com que, sovint, els índexs necessaris coincideixen amb els índexs de la taula de destinació, podeu escriure simplement LIKE target_table INCLUDING INDEXES.

Si també cal DEFAULT-valors (per exemple, per omplir els valors de la clau primària), podeu utilitzar LIKE target_table INCLUDING DEFAULTS. O simplement - LIKE target_table INCLUDING ALL — copia els valors per defecte, els índexs, les restriccions,...

Però aquí ho heu d'entendre si heu creat importa la taula immediatament amb índexs, aleshores les dades trigaran més a carregar-seque si primer ho ompliu tot i només després enrotlleu els índexs; mireu com ho fa com a exemple pg_dump.

En general, els RTFM!

2. Com escriure?

Deixa'm dir-ho: fes-ho servir COPY-flow en lloc de "pack" INSERT, acceleració de vegades. Fins i tot podeu directament des d'un fitxer pregenerat.

3. Com processar?

Per tant, deixem que la nostra introducció sembli una cosa així:

teniu una taula amb les dades del client emmagatzemades a la vostra base de dades 1M registres
cada dia un client t'envia un de nou "imatge" completa
per experiència ho saps de tant en tant no es canvien més de 10 registres

Un exemple clàssic d'aquesta situació és Base KLADR — hi ha moltes adreces en total, però en cada pujada setmanal hi ha molt pocs canvis (denominació dels assentaments, combinació de carrers, aparició de noves cases) fins i tot a escala nacional.

3.1. Algorisme de sincronització completa

Per senzillesa, diguem que ni tan sols cal reestructurar les dades; només cal portar la taula a la forma desitjada, és a dir:

удалить tot allò que ja no existeix
per actualitzar tot el que ja existia i cal actualitzar
inserir tot el que encara no ha passat

Per què s'han de fer les operacions en aquest ordre? Perquè així és com la mida de la taula creixerà mínimament (recordeu MVCC!).

ELIMINAR DE DST

No, per descomptat, només pots fer-te amb dues operacions:

удалить (DELETE) tot en general
inserir tot a partir de la nova imatge

Però al mateix temps, gràcies a MVCC, La mida de la taula augmentarà exactament el doble! Obtenir +1 M d'imatges de registres a la taula a causa d'una actualització de 10 XNUMX és molt redundància...

TRUNCAT dst

Un desenvolupador més experimentat sap que la tauleta sencera es pot netejar molt barat:

Escriviu-lo (TRUNCATE) tota la taula
inserir tot a partir de la nova imatge

El mètode és efectiu, de vegades bastant aplicable, però hi ha un problema... Anem afegint 1M registres durant molt de temps, així que no ens podem permetre el luxe de deixar la taula buida durant tot aquest temps (com passarà sense embolicar-la en una sola transacció).

Que significa:

estem començant transacció de llarga durada
TRUNCATE imposa Accés exclusiu-bloqueig
fem la inserció durant molt de temps, i tots els altres en aquest moment ni tan sols pot SELECT

Alguna cosa no va bé...

ALTERA LA TAULA... CANVIA EL NOM... / DEIXA LA TAULA...

Una alternativa és omplir-ho tot en una taula nova a part i, a continuació, canviar-la simplement el nom en lloc de l'antiga. Un parell de petites coses desagradables:

encara també Accés exclusiu, encara que molt menys temps
es restableixen tots els plans/estadístiques de consulta d'aquesta taula, cal executar ANALYZE
totes les claus externes estan trencades (FK) a la taula

Hi havia un pegat WIP de Simon Riggs que suggeria fer-ho ALTER-una operació per substituir el cos de la taula a nivell de fitxer, sense tocar les estadístiques i FK, però no va recollir quòrum.

ELIMINAR, ACTUALITZAR, INSERIR

Així doncs, ens conformem amb l'opció de no bloqueig de tres operacions. Gairebé tres... Com fer-ho de la manera més eficaç?

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

En el mateix KLADR, tots els registres modificats s'han d'executar addicionalment mitjançant un postprocessament: normalitzats, ressaltats i reduïts a les estructures requerides. Però com ho saps... què ha canviat exactamentsense complicar el codi de sincronització, idealment sense tocar-lo en absolut?

Si només el vostre procés té accés d'escriptura en el moment de la sincronització, podeu utilitzar un activador que reculli tots els canvis per nosaltres:

-- целевые таблицы
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;

Ara podem aplicar activadors abans d'iniciar la sincronització (o habilitar-los mitjançant 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();

A continuació, extreurem amb calma tots els canvis que necessitem de les taules de registre i els executem mitjançant controladors addicionals.

3.3. Importació de conjunts enllaçats

Més amunt hem considerat casos en què les estructures de dades de l'origen i de la destinació són les mateixes. Però, què passa si la càrrega des d'un sistema extern té un format diferent de l'estructura d'emmagatzematge de la nostra base de dades?

Prenguem com a exemple l'emmagatzematge dels clients i els seus comptes, la clàssica opció "molts a un":

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)
);

Però la descàrrega d'una font externa ens arriba en forma de "tot en un":

CREATE TEMPORARY TABLE invoice_import(
  client_inn
    varchar
, client_name
    varchar
, invoice_number
    varchar
, invoice_dt
    date
, invoice_sum
    numeric(32,2)
);

Òbviament, les dades del client es poden duplicar en aquesta versió, i el registre principal és "compte":

0123456789;Вася;A-01;2020-03-16;1000.00
9876543210;Петя;A-02;2020-03-16;666.00
0123456789;Вася;B-03;2020-03-16;9999.00

Per al model, simplement inserirem les nostres dades de prova, però recordeu: COPY més eficient!

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);

En primer lloc, destaquem aquells "talls" als quals fan referència els nostres "fets". En el nostre cas, les factures es refereixen a clients:

CREATE TEMPORARY TABLE client_import AS
SELECT DISTINCT ON(client_inn)
-- можно просто SELECT DISTINCT, если данные заведомо непротиворечивы
  client_inn inn
, client_name "name"
FROM
  invoice_import;

Per associar correctament els comptes amb els identificadors de client, primer hem d'esbrinar o generar aquests identificadors. Afegim camps sota ells:

ALTER TABLE invoice_import ADD COLUMN client_id integer;
ALTER TABLE client_import ADD COLUMN client_id integer;

Utilitzem el mètode de sincronització de taules descrit anteriorment amb una petita modificació: no actualitzarem ni suprimirem res de la taula de destinació, perquè importem clients "només per afegir":

-- проставляем в таблице импорта 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; -- прикладной ключ

De fet, tot està dins invoice_import Ara tenim el camp de contacte omplert client_id, amb la qual inserirem la factura.

Font: www.habr.com

DBA: organitza de manera competent sincronitzacions i importacions