Quando si tratta di elaborare grandi set di dati complessi (diversi : importazioni, conversioni e sincronizzazioni con una fonte esterna) sorge spesso la necessità di 'memorizzare' temporaneamente e trattare rapidamente qualcosa di voluminoso.
Un compito tipico di questo tipo è di solito formulato più o meno così: «Ecco qui gli ultimi pagamenti ricevuti, bisogna caricarli rapidamente sul sito e associarli ai conti»
Ma quando il volume di questo 'qualcosa' inizia a misurarsi in centinaia di megabyte, e il servizio deve continuare a lavorare con il database in modalità 24×7, emergono molti effetti collaterali che possono complicarvi la vita.
Per affrontarli in PostgreSQL (e non solo in esso), è possibile utilizzare alcune funzionalità di ottimizzazione che consentono di elaborare tutto più rapidamente e con un minore consumo di risorse.
1. Dove caricare?
Prima di tutto, definiamo dove possiamo caricare i dati che vogliamo 'processare'.
1.1. Tabelle temporanee (TEMPORARY TABLE)
In linea di principio, per PostgreSQL le tabelle temporanee sono tabelle proprio come tutte le altre. Quindi le superstizioni come «lì tutto è memorizzato solo in memoria, e questa potrebbe finire»non sono corrette. Ma ci sono anche diverse differenze sostanziali.
Uno 'spazio dei nomi' per ogni connessione al DB
Se due connessioni tentano di eseguire contemporaneamente CREATE TABLE x, allora qualcuna riceverà sicuramente un errore di non unicità degli oggetti DB.
Ma se entrambi tentano di eseguire CREATE TEMPORANEO TABELLA x, allora entrambi lo faranno correttamente e ciascuno riceverà la propria istanza della tabella. E non ci sarà nulla in comune tra loro.
«Autoannullamento» alla disconnessione
Quando la connessione si chiude, tutte le tabelle temporanee vengono automaticamente rimosse, quindi eseguire manualmente DROP TABLE x non ha alcun senso, tranne...
Se si lavora tramite pgbouncer in modalità transazione, il database continuerà a ritenere che questa connessione sia ancora attiva, e in essa questa tabella temporanea esisterà ancora.
Quindi, il tentativo di crearla di nuovo, già da un'altra connessione a pgbouncer, porterà a un errore. Ma questo si può aggirare, utilizzando CREARE UNA TAVOLO TEMPORANEA SE NON ESISTE x.
È vero, sarebbe meglio non farlo, perché poi si potrebbe «scoprire all'improvviso» lì dentro i dati rimasti dal «proprietario precedente». È molto meglio leggere il manuale e vedere che nella creazione della tabella c'è la possibilità di aggiungere ALLA COMMIT DROP — cioè al completamento della transazione, la tabella verrà eliminata automaticamente.
Non replicazione
A causa della sua appartenenza solo a una certa connessione, le tabelle temporanee non vengono replicate. Tuttavia, questo elimina la necessità di una doppia registrazione dei dati in heap + WAL, quindi INSERT/UPDATE/DELETE in essa è notevolmente più veloce.
Ma poiché una tabella temporanea è comunque una tabella «quasi normale», non è possibile crearla nemmeno sulla replica. Almeno, per ora, sebbene la patch corrispondente circoli da molto tempo.
1.2. Tabelle non registrate (UNLOGGED TABLE)
Ma cosa fare, ad esempio, se hai un qualche pesante processo ETL che non riesci a realizzare all'interno di una singola transazione, e hai comunque pgbouncer in modalità transazione?..
Oppure il flusso di dati è così grande che non c'è sufficiente larghezza di banda di una connessione con il DB (leggi, un processo per CPU)?..
Oppure parte delle operazioni avviene in modo asincrono in diverse connessioni?..
L'unica opzione qui è — creare temporaneamente una tabella non temporanea. Gioco di parole, eh. Cioè:
- ho creato «le mie» tabelle con nomi massimamente casuali, per non sovrappormi a nessuno
- Estrai: ho caricato i dati da una fonte esterna
- Trasforma: ho trasformato, riempito i campi chiave di collegamento
- Carico: ho trasferito i dati pronti nelle tabelle di destinazione
- ho eliminato «le mie» tabelle
E ora — un cucchiaio di catrame. Fondamentalmente, tutta la registrazione in PostgreSQL avviene due volte — , poi nei corpi delle tabelle/indici. Tutto ciò è stato fatto per supportare ACID e una corretta visibilità dei dati tra COMMIT‘nutati e ROLLBACK‘nutati transazioni.
Ma a noi non serve! Tutto il nostro processo è andato a buon fine, o no. Non importa quante transazioni intermedie ci siano — non ci interessa «continuare il processo da metà», soprattutto quando non è chiaro dove fosse.
Per questo gli sviluppatori di PostgreSQL già nella versione 9.1 hanno introdotto una funzione come :
Con questa indicazione, la tabella viene creata come non registrata. I dati scritti nelle tabelle non registrate non passano attraverso il log delle scritture (vedi Capitolo 29), risultando in tabelle di questo tipo lavorano molto più velocemente delle normali. Tuttavia, non sono protette da guasti; in caso di guasto o spegnimento anomalo del server, la tabella non registrata viene troncata automaticamente. Inoltre, il contenuto della tabella non registrata non viene replicato ai server secondari. Qualsiasi indice creato per la tabella non registrata diventa automaticamente non registrato.
In breve, sarà notevolmente più veloce, ma se il server del DB va in crash — sarà sgradevole. Ma succede spesso? E il vostro processo ETL è in grado di riprendersi correttamente "a metà" dopo il "riavvio" del DB?...
Se non è così, e il caso sopra è simile al vostro — usate UNLOGGED, ma non attivate mai questo attributo su tabelle reali , i cui dati sono preziosi per voi.1.3. ON COMMIT { DELETE ROWS | DROP }
Questa struttura consente di impostare un comportamento automatico al termine della transazione durante la creazione della tabella.
Ho già scritto sopra, genera
Su ALLA COMMIT DROP DROP TABLE , ma la situazione conè più interessante — qui viene generato ALLA COMMIT ELIMINA RIGHE TRUNCATE TABLE Poiché l'intera infrastruttura di archiviazione della descrizione meta per la tabella temporanea è esattamente la stessa di quella della tabella normale,.
la creazione e cancellazione frequente di tabelle temporanee porta a un forte "aumento" delle tabelle di sistema pg_class, pg_attribute, pg_attrdef, pg_depend,… Ora immaginate di avere un worker connesso direttamente al DB, che ogni secondo apre una nuova transazione, crea, riempie, elabora e cancella una tabella temporanea… Si accumulerà spazzatura nelle tabelle di sistema, causando rallentamenti in ogni operazione.
In generale, non si dovrebbe fare così! In questo caso è molto più efficiente
CREATE TEMPORARY TABLE x ... ON COMMIT DELETE ROWS realizzare al di fuori del ciclo delle transazioni — così all'inizio di ogni nuova transazione la tabella esisterà già (risparmiando una chiamata ), ma CREATEsarà vuota , grazie aTRUNCATE (abbiamo risparmiato anche quella chiamata) al termine della transazione precedente. 1.4. LIKE… INCLUDING …
Ho menzionato all'inizio che uno dei casi d'uso tipici per le tabelle temporanee è rappresentato da vari tipi di importazioni — e lo sviluppatore copia e incolla stancamente l'elenco dei campi della tabella di destinazione nella dichiarazione della propria temporanea…
Ma la pigrizia è il motore del progresso! Quindi
creare una nuova tabella "per esempio" si può fare molto più facilmente: CREATE TEMPORARY TABLE import_table( LIKE target_table );
CREA UNA TABELLA TEMPORANEA import_table(
SIMILE a target_table
);Poiché è possibile generare un numero considerevole di dati in questa tabella, le ricerche in essa non saranno affatto rapide. Ma c'è una soluzione tradizionale a questo problema: gli indici! E sì, anche la tabella temporanea può avere indici.
Poiché spesso gli indici necessari coincidono con quelli della tabella di destinazione, è possibile semplicemente scrivere LIKE target_table INCLUSI GLI INDICI.
Se hai bisogno anche di DEFAULT-valori (ad esempio, per popolare i valori della chiave primaria), puoi avvalerti di LIKE target_table INCLUDENDO I VALORI PREDEFINITI. O semplicemente — LIKE target_table INCLUSO TUTTO — copierà i valori predefiniti, gli indici, i vincoli,…
Ma qui è importante capire che se hai creato una tabella di importazione direttamente con indici, il caricamento dei dati richiederà più tempo, rispetto a prima caricare tutti i dati e poi applicare gli indici — guarda come lo fa .
In generale, !
2. Come scrivere?
Dico semplicemente: utilizza -flusso invece di "batch" INSERISCI, . Puoi anche farlo direttamente da un file precedentemente formato.
3. Come elaborare?
Dunque, supponiamo che il nostro input sia più o meno così:
- hai nel database una tabella con dati dei clienti di 1M record
- ogni giorno il cliente ti invia un nuovo full "snapshot"
- sulla base della tua esperienza sai che di volta in volta cambiano non più di 10K record
Un classico esempio di una situazione simile è — ci sono molti indirizzi in totale, ma in ciascun'esportazione settimanale di cambiamenti (rinominazioni di località, fusioni di strade, comparsa di nuovi edifici) ci sono davvero pochi cambiamenti anche in scala nazionale.
3.1. Algoritmo di sincronizzazione completa
Per semplicità presupponiamo che non sia necessario neppure ristrutturare i dati — basta portare la tabella nella forma desiderata, cioè:
- a eliminare tutto ciò che non esiste più
- aggiornare tutto ciò che esisteva già e deve essere aggiornato
- inserire tutto ciò che non esisteva ancora
Perché è importante eseguire le operazioni in questo ordine? Perché in questo modo la dimensione della tabella crescerà in modo minimo ().
DELETE FROM dst
No, certo è possibile ridursi a sole due operazioni:
- a eliminare (
DELETE) tutto ciò che - inserire tutto dal nuovo snapshot
Ma a questo punto, grazie al MVCC, la dimensione della tabella aumenterà esattamente del doppio! Ottenere +1M di snapshot di record nella tabella a causa dell'aggiornamento di 10K è un'eccessività…
TRUNCATE dst
Un sviluppatore più esperto sa che l'intera tabella può essere pulita a un costo abbastanza basso:
- pulire (
(abbiamo risparmiato anche quella chiamata) al termine della transazione precedente.) l'intera tabella - inserire tutto dal nuovo snapshot
Un metodo efficace, , ma c'è un problema... Iniettare 1M di record ci vorrà molto tempo, quindi non possiamo permetterci di tenere la tabella vuota per tutto questo tempo (come accadrebbe senza racchiudere in un'unica transazione).
E quindi:
- iniziamo una lunga transazione
(abbiamo risparmiato anche quella chiamata) al termine della transazione precedente.applica AccessExclusive-bloccando- facciamo inserimenti a lungo, mentre tutti gli altri in quel momento non possono neanche
SELECT
C'è qualcosa che non va...
ALTER TABLE… RENAME… / DROP TABLE …
Come opzione, caricare tutto in una nuova tabella separata, e poi semplicemente rinominarla al posto della vecchia. Un paio di cose antipatiche:
- anche questo AccessExclusive, anche se molto meno nel tempo
- tutti i piani delle query/statistiche di questa tabella vengono azzerati,
- tutte le chiavi esterne (FK) sulla tabella
C'era una patch WIP da Simon Riggs, che suggeriva di fare ALTER-operazione per sostituire il corpo della tabella a livello di file, senza toccare le statistiche e le FK, ma non ha raggiunto il quorum.
DELETE, UPDATE, INSERT
Quindi, ci fermiamo sull'opzione non bloccante delle tre operazioni. Quasi tre... Come possiamo farlo nel modo più efficace?
-- facciamo tutto nell'ambito di una transazione, affinché nessuno veda stati "intermedi"
BEGIN;
-- creiamo una tabella temporanea con i dati importati
CREATE TEMPORARY TABLE tmp(
LIKE dst INCLUDING INDEXES -- a somiglianza, insieme agli indici
) ON COMMIT DROP; -- al di fuori della transazione non ci serve
-- rapidamente iniettiamo la nuova immagine tramite COPY
COPY tmp FROM STDIN;
-- ...
-- .
-- eliminiamo i mancanti
DELETE FROM
dst D
USING
dst X
LEFT JOIN
tmp Y
USING(pk1, pk2) -- campi della chiave primaria
WHERE
(D.pk1, D.pk2) = (X.pk1, X.pk2) AND
Y IS NOT DISTINCT FROM NULL; -- "anti-join"
-- aggiorniamo i rimanenti
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); -- non c'è bisogno di aggiornare i corrispondenti
-- inseriamo i mancanti
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. Post-elaborazione dell'importazione
Nello stesso KLDAR, tutte le registrazioni modificate devono essere ulteriormente elaborate — normalizzate, estratte le parole chiave, portate nelle strutture desiderate. Ma come sapere — cosa è stato effettivamente modificato, senza complicare il codice di sincronizzazione, idealmente, senza neanche toccarlo?
Se l'accesso in scrittura al momento della sincronizzazione è disponibile solo per il tuo processo, puoi utilizzare un trigger che raccoglie tutte le modifiche per noi:
-- tabelle di destinazione
CREATE TABLE kladr(...);
CREATE TABLE kladr_house(...);
-- tabelle con la cronologia delle modifiche
CREATE TABLE kladr$log(
ro kladr, -- qui si trovano le copie complete delle vecchie/nome nuove registrazioni
rn kladr
);
CREATE TABLE kladr_house$log(
ro kladr_house,
rn kladr_house
);
-- funzione generale per la registrazione delle modifiche
CREATE OR REPLACE FUNCTION diff$log() RETURNS trigger AS $$
DECLARE
dst varchar = TG_TABLE_NAME || '$log';
stmt text = '';
BEGIN
-- controlliamo la necessità di registrazione durante l'aggiornamento della registrazione
IF TG_OP = 'UPDATE' THEN
IF NEW IS NOT DISTINCT FROM OLD THEN
RETURN NEW;
END IF;
END IF;
-- creiamo la registrazione del log
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 (o attivare 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();
E poi estraiamo tranquillamente tutte le modifiche necessarie dalle tabelle di log e le inoltriamo ai gestori aggiuntivi.
3.3. Importazione di set correlati
In precedenza abbiamo esaminato i casi in cui le strutture dei dati della sorgente e del ricevente coincidono. Ma cosa fare se l'esportazione da un sistema esterno ha un formato diverso dalla struttura di archiviazione nel nostro database?
Consideriamo come esempio la memorizzazione dei clienti e delle fatture per essi, un caso classico di "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)
);Ecco che l'esportazione da una fonte esterna ci arriva in 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)
);Evidentemente, i dati sui clienti possono essere duplicati in questa forma, e la registrazione principale è "fattura":
0123456789;Vasja;A-01;2020-03-16;1000.00
9876543210;Petja;A-02;2020-03-16;666.00
0123456789;Vasja;B-03;2020-03-16;9999.00
Per il modello inseriremo semplicemente i nostri dati di prova, ma ricordiamo — COPY più efficace!
INSERT INTO invoice_import
VALUES
('0123456789', 'Vasja', 'A-01', '2020-03-16', 1000.00)
, ('9876543210', 'Petja', 'A-02', '2020-03-16', 666.00)
, ('0123456789', 'Vasja', 'B-03', '2020-03-16', 9999.00);Iniziamo identificando i "tagli" a cui i nostri "fatti" si riferiscono. Nel nostro caso, le fatture si riferiscono ai clienti:
CREA UNA TABELLA TEMPORANEA client_import COME
SELEZIONA DISTINTI SU(client_inn)
-- puoi semplicemente usare SELECT DISTINCT, se i dati sono già coerenti
client_inn inn
, client_name "name"
DA
invoice_import;Per collegare correttamente le fatture agli ID dei clienti, dobbiamo prima conoscere o generare questi identificatori. Aggiungiamo i campi per essi:
ALTER TABLE invoice_import ADD COLUMN client_id integer;
ALTER TABLE client_import ADD COLUMN client_id integer;Utilizzeremo il metodo descritto sopra per sincronizzare le tabelle con una piccola modifica: non aggiorneremo né elimineremo nulla nella tabella di destinazione, poiché l'importazione dei clienti è "append-only":
-- aggiorniamo la tabella di importazione con gli ID delle registrazioni già esistenti
UPDATE
client_import T
SET
client_id = D.client_id
FROM
client D
WHERE
T.inn = D.inn; -- chiave unica
-- inseriamo le registrazioni mancanti e impostiamo i loro ID
WITH ins AS (
INSERT INTO client(
inn
, name
)
SELEZIONA
inn
, name
DA
client_import
DOVE
client_id IS NULL -- se l'ID non è stato impostato
RETURNING *
)
UPDATE
client_import T
SET
client_id = D.client_id
FROM
ins D
WHERE
T.inn = D.inn; -- chiave unica
-- impostiamo gli ID dei clienti nei record delle fatture
UPDATE
invoice_import T
SET
client_id = D.client_id
FROM
client_import D
WHERE
T.client_inn = D.inn; -- chiave applicativa
In effetti, è tutto — in invoice_import ora abbiamo il campo di collegamento compilato client_id, con il quale inseriremo la fattura.
Fonte: habr.com
