DBA: organizza con competenza sincronizzazioni e importazioni

Per l'elaborazione complessa di set di dati di grandi dimensioni (diversi Processi ETL: importazioni, conversioni e sincronizzazione con una fonte esterna) spesso ce n'è bisogno “ricordare” temporaneamente ed elaborare immediatamente rapidamente qualcosa di voluminoso.

Un compito tipico di questo tipo di solito suona più o meno così: "Giusto qui reparto contabilità scaricato dalla banca cliente gli ultimi pagamenti ricevuti, devi caricarli velocemente sul sito e collegarli ai tuoi conti"

Ma quando il volume di questo “qualcosa” inizia a misurare centinaia di megabyte e il servizio deve continuare a funzionare con il database 24 ore su 7, XNUMX giorni su XNUMX, sorgono molti effetti collaterali che ti rovineranno la vita.

Per gestirli in PostgreSQL (e non solo in esso), puoi utilizzare alcune ottimizzazioni che ti permetteranno di elaborare tutto più velocemente e con un minor consumo di risorse.

1. Dove spedire?

Per prima cosa decidiamo dove possiamo caricare i dati che vogliamo “elaborare”.

1.1. Tabelle temporanee (TABELLA TEMPORANEA)

In linea di principio, per PostgreSQL le tabelle temporanee sono uguali a tutte le altre. Pertanto, le superstizioni piacciono “Tutto lì è conservato solo nella memoria e può finire”. Ma ci sono anche molte differenze significative.

Il tuo "spazio dei nomi" per ogni connessione al database

Se due connessioni tentano di connettersi contemporaneamente CREATE TABLE x, allora qualcuno lo capirà sicuramente errore di non unicità oggetti del database.

Ma se entrambi tentano di eseguire CREATE TEMPORARY TABLE x, quindi entrambi lo faranno normalmente e tutti lo otterranno la tua copia tavoli. E non ci sarà nulla in comune tra loro.

"Autodistruzione" durante la disconnessione

Quando la connessione viene chiusa, tutte le tabelle temporanee vengono cancellate automaticamente, quindi manualmente DROP TABLE x non ha senso se non...

Se stai lavorando pgbouncer in modalità transazione, il database continua a credere che questa connessione sia ancora attiva e in essa questa tabella temporanea esiste ancora.

Pertanto, provare a crearlo nuovamente, da una connessione diversa a pgbouncer, risulterà in un errore. Ma questo può essere aggirato utilizzando CREATE TEMPORARY TABLE IF NOT EXISTS x.

È vero, è comunque meglio non farlo, perché altrimenti potresti "improvvisamente" trovare lì i dati rimanenti del "proprietario precedente". Invece è molto meglio leggere il manuale e vedere che quando si crea una tabella è possibile aggiungere ON COMMIT DROP - ovvero, al termine della transazione, la tabella verrà automaticamente eliminata.

Non replica

Poiché appartengono solo a una connessione specifica, le tabelle temporanee non vengono replicate. Ma ciò elimina la necessità di una doppia registrazione dei dati in heap + WAL, quindi INSERT/UPDATE/DELETE è molto più veloce.

Ma poiché una tabella temporanea è pur sempre una tabella “quasi ordinaria”, non può essere creata nemmeno su una replica. Almeno per ora, anche se la patch corrispondente circola già da tempo.

1.2. TABELLA NON REGISTRATA

Ma cosa dovresti fare, ad esempio, se hai una sorta di processo ETL ingombrante che non può essere implementato all'interno di una transazione, ma hai ancora pgbouncer in modalità transazione? ..

Oppure il flusso di dati è così grande La larghezza di banda non è sufficiente su una connessione da un database (leggi, un processo per CPU)?..

Oppure sono in corso alcune operazioni in modo asincrono in connessioni diverse?..

C'è solo un'opzione qui: creare temporaneamente una tabella non temporanea. Gioco di parole, sì. Questo è:

• ho creato tabelle "mie" con nomi il più possibile casuali in modo da non intersecarsi con nessuno
• Estratto: li ha riempiti con dati provenienti da una fonte esterna
• Trasformare: convertito, compilato i campi di collegamento chiave
• Caricare: ha inserito i dati pronti nelle tabelle di destinazione
• cancellato le "mie" tabelle

E ora - un neo. Infatti, tutte le scritture in PostgreSQL avvengono due volte - prima in WAL, quindi nei corpi della tabella/indice. Tutto ciò viene fatto per supportare ACID e correggere la visibilità dei dati tra COMMIT'nocciola e ROLLBACK'transazioni nulle.

Ma non ne abbiamo bisogno! Abbiamo l'intero processo O ha avuto pieno successo oppure no.. Non importa quante transazioni intermedie ci saranno: non siamo interessati a "continuare il processo dal centro", soprattutto quando non è chiaro dove si trovasse.

Per fare ciò, gli sviluppatori di PostgreSQL, nella versione 9.1, hanno introdotto qualcosa come Tabelle NON REGISTRATE:

Con questa indicazione la tabella viene creata come non registrata. I dati scritti nelle tabelle non registrate non passano attraverso il log write-ahead (vedere il Capitolo 29), causando la lavorare molto più velocemente del solito. Tuttavia, non sono immuni dal fallimento; in caso di guasto del server o arresto di emergenza, una tabella non registrata troncato automaticamente. Inoltre, il contenuto della tabella non registrata non replicato ai server schiavi. Qualsiasi indice creato su una tabella non registrata diventa automaticamente non registrato.

In breve, sarà molto più veloce, ma se il server del database “cade”, sarà spiacevole. Ma quanto spesso ciò accade e il tuo processo ETL sa come correggerlo correttamente “dal centro” dopo aver “rivitalizzato” il database?

In caso contrario, e il caso precedente è simile al tuo, utilizza UNLOGGEDma mai non abilitare questo attributo sulle tabelle reali, i dati da cui ti sono cari.

1.3. SU COMMIT { CANCELLA RIGHE | GOCCIOLARE}

Questo costrutto consente di specificare il comportamento automatico al completamento di una transazione durante la creazione di una tabella.

Про ON COMMIT DROP Ho già scritto sopra, genera DROP TABLE, ma con ON COMMIT DELETE ROWS la situazione è più interessante: viene generata qui TRUNCATE TABLE.

Poiché l'intera infrastruttura per l'archiviazione della meta-descrizione di una tabella temporanea è esattamente la stessa di una tabella normale La creazione e l'eliminazione costante di tabelle temporanee porta a un grave "gonfiore" delle tabelle di sistema pg_class, pg_attribute, pg_attrdef, pg_depend,…

Ora immagina di avere un lavoratore in connessione diretta al database, che apre una nuova transazione ogni secondo, crea, riempie, elabora ed elimina una tabella temporanea... Ci sarà un eccesso di spazzatura accumulata nelle tabelle di sistema e ciò causerà freni aggiuntivi per ogni operazione.

In generale, non farlo! In questo caso è molto più efficace CREATE TEMPORARY TABLE x ... ON COMMIT DELETE ROWS toglilo dal ciclo della transazione, quindi all'inizio di ogni nuova transazione le tabelle sono già presenti esisterà (salva una chiamata CREATE), ma sarà vuoto, grazie a TRUNCATE (abbiamo anche salvato la sua chiamata) durante il completamento della transazione precedente.

1.4. COME...INCLUSO...

Ho menzionato all'inizio che uno dei casi d'uso tipici per le tabelle temporanee sono vari tipi di importazioni - e lo sviluppatore copia e incolla stancamente l'elenco dei campi della tabella di destinazione nella dichiarazione del suo temporaneo...

Ma la pigrizia è il motore del progresso! Ecco perché creare una nuova tabella “basata sul campione” può essere molto più semplice:

CREATE TEMPORARY TABLE import_table(
  LIKE target_table
);

Dato che puoi generare molti dati in questa tabella, la ricerca al suo interno non sarà mai veloce. Ma esiste una soluzione tradizionale a questo: gli indici! E sì, una tabella temporanea può anche avere indici.

Poiché, spesso, gli indici richiesti coincidono con gli indici della tabella di destinazione, puoi semplicemente scrivere LIKE target_table INCLUDING INDEXES.

Se anche tu hai bisogno DEFAULT-values ​​(ad esempio, per inserire i valori della chiave primaria), è possibile utilizzare LIKE target_table INCLUDING DEFAULTS. O semplicemente - LIKE target_table INCLUDING ALL — copia valori predefiniti, indici, vincoli,...

Ma qui devi capirlo se hai creato importare immediatamente la tabella con gli indici, il caricamento dei dati richiederà più tempoche se prima riempissi tutto e solo dopo arrotolassi gli indici: guarda come funziona come esempio pg_dump.

In generale, RTFM!

2. Come scrivere?

Lasciatemi solo dire: usatelo COPY-flow invece di “pack” INSERT, accelerazione a volte. Puoi anche direttamente da un file pregenerato.

3. Come elaborare?

Quindi, lasciamo che la nostra introduzione assomigli a questa:

• hai una tabella con i dati del cliente memorizzati nel tuo database 1 milione di record
• ogni giorno un cliente te ne invia uno nuovo "immagine" completa
• per esperienza lo sai di tanto in tanto non vengono modificati più di 10 record

Un classico esempio di tale situazione è Base KLADR — ci sono molti indirizzi in totale, ma in ogni caricamento settimanale ci sono pochissimi cambiamenti (rinomina degli insediamenti, combinazione di strade, comparsa di nuove case) anche su scala nazionale.

3.1. Algoritmo di sincronizzazione completa

Per semplicità, diciamo che non è nemmeno necessario ristrutturare i dati, basta portare la tabella nella forma desiderata, ovvero:

• rimuovere tutto ciò che non esiste più
• aggiornare tutto quello che già esisteva e necessita di essere aggiornato
• inserire tutto quello che non è ancora successo

Perché le operazioni dovrebbero essere eseguite in questo ordine? Perché è così che le dimensioni della tabella aumenteranno minimamente (ricorda MVCC!).

ELIMINA DA dst

No, certo che puoi cavartela con sole due operazioni:

• rimuovere (DELETE) tutto in generale
• inserire tutto dalla nuova immagine

Ma allo stesso tempo, grazie a MVCC, La dimensione della tabella aumenterà esattamente due volte! Ottenere +1 milione di immagini di record nella tabella a causa di un aggiornamento di 10K è così così ridondante...

TRONCARE dst

Uno sviluppatore più esperto sa che l'intero tablet può essere pulito in modo abbastanza economico:

• pulito (TRUNCATE) l'intera tabella
• inserire tutto dalla nuova immagine

Il metodo è efficace a volte abbastanza applicabile, ma c'è un problema... Aggiungeremo 1 milione di record per molto tempo, quindi non possiamo permetterci di lasciare la tabella vuota per tutto questo tempo (come accadrà senza racchiuderla in un'unica transazione).

Ciò significa:

• stiamo iniziando transazione di lunga durata
• TRUNCATE impone Accesso esclusivo-blocco
• facciamo l'inserimento per molto tempo, e tutti gli altri in questo momento non posso nemmeno SELECT

Qualcosa non sta andando bene...

ALTERA TABELLA... RINOMINA... / RIMUOVERE TABELLA...

Un'alternativa è riempire tutto in una nuova tabella separata e quindi semplicemente rinominarla al posto di quella vecchia. Un paio di piccole cose brutte:

• ancora troppo Accesso esclusivo, anche se in tempi nettamente inferiori
• tutti i piani/statistiche di query per questa tabella vengono reimpostati, è necessario eseguire ANALISI
• tutte le chiavi esterne sono rotte (FK) al tavolo

C'era una patch WIP di Simon Riggs che suggeriva di realizzarla ALTER-un'operazione per sostituire il corpo della tabella a livello di file, senza toccare statistiche e FK, ma senza raccogliere il quorum.

ELIMINA, AGGIORNA, INSERISCI

Quindi, optiamo per l'opzione non bloccante di tre operazioni. Quasi tre... Come farlo nel modo più efficace?

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

Nello stesso KLADR, tutti i record modificati devono essere ulteriormente sottoposti a post-elaborazione: normalizzati, evidenziate le parole chiave e ridotti alle strutture richieste. Ma come fai a sapere... cosa è cambiato esattamentesenza complicare il codice di sincronizzazione, idealmente senza toccarlo affatto?

Se solo il tuo processo ha accesso in scrittura al momento della sincronizzazione, puoi utilizzare un trigger che raccoglierà tutte le modifiche per noi:

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

Ora possiamo applicare i trigger prima di avviare la sincronizzazione (o abilitarli tramite 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();

Quindi estraiamo con calma tutte le modifiche di cui abbiamo bisogno dalle tabelle di registro e le eseguiamo tramite gestori aggiuntivi.

3.3. Importazione di set collegati

Sopra abbiamo considerato i casi in cui le strutture dati dell'origine e della destinazione sono le stesse. Ma cosa succede se l'upload da un sistema esterno ha un formato diverso dalla struttura di archiviazione presente nel nostro database?

Prendiamo ad esempio l’archiviazione dei clienti e dei loro conti, la classica opzione “molti-a-uno”:

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

Ma il download da una fonte esterna ci arriva sotto forma di “tutto in uno”:

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

Ovviamente i dati del cliente possono essere duplicati in questa versione, e il record principale è “account”:

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 il modello, inseriremo semplicemente i nostri dati di test, ma ricorda: COPY più efficiente!

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

Innanzitutto evidenziamo quei “tagli” a cui si riferiscono i nostri “fatti”. Nel nostro caso le fatture si riferiscono ai clienti:

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

Per associare correttamente gli account agli ID cliente, dobbiamo prima scoprire o generare questi identificatori. Aggiungiamo i campi sotto di essi:

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

Usiamo il metodo di sincronizzazione delle tabelle descritto sopra con una piccola modifica: non aggiorneremo o elimineremo nulla nella tabella di destinazione, perché importiamo i client "append-only":

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

In realtà c'è tutto invoice_import Ora abbiamo compilato il campo del contatto client_id, con il quale inseriremo la fattura.

Fonte: habr.com