Pour le traitement complexe de grands ensembles de données (différents : importations, conversions et synchronisation avec une source externe) il y a souvent besoin « se souvenir » temporairement et traiter immédiatement et rapidement quelque chose de volumineux.
Une tâche typique de ce type ressemble généralement à ceci : "Ici les derniers paiements reçus, vous devez les télécharger rapidement sur le site et les lier à vos comptes"
Mais lorsque le volume de ce « quelque chose » commence à se mesurer en centaines de mégaoctets et que le service doit continuer à fonctionner avec la base de données 24h/7 et XNUMXj/XNUMX, de nombreux effets secondaires surviennent qui ruineront votre vie.
Pour les gérer dans PostgreSQL (et pas seulement), vous pouvez utiliser certaines optimisations qui vous permettront de tout traiter plus rapidement et avec moins de consommation de ressources.
1. Où expédier ?
Tout d’abord, décidons où nous pouvons télécharger les données que nous souhaitons « traiter ».
1.1. Tables temporaires (TABLE TEMPORAIRE)
En principe, pour PostgreSQL, les tables temporaires sont les mêmes que les autres. C’est pourquoi les superstitions comme "Tout y est stocké uniquement en mémoire, et cela peut finir". Mais il existe également plusieurs différences significatives.
Votre propre « espace de noms » pour chaque connexion à la base de données
Si deux connexions tentent de se connecter en même temps CREATE TABLE x, alors quelqu'un aura certainement erreur de non-unicité objets de base de données.
Mais si les deux essayent d'exécuter CREATE TEMPORARY TABLE x, alors les deux le feront normalement, et tout le monde obtiendra votre copie les tables. Et il n'y aura rien de commun entre eux.
"Autodestruction" lors de la déconnexion
Lorsque la connexion est fermée, toutes les tables temporaires sont automatiquement supprimées, donc manuellement DROP TABLE x ça ne sert à rien sauf...
Si vous travaillez pgbouncer en mode transaction, alors la base de données continue de croire que cette connexion est toujours active et que cette table temporaire existe toujours.
Par conséquent, essayer de le créer à nouveau, à partir d’une connexion différente à pgbouncer, entraînera une erreur. Mais cela peut être contourné en utilisant CREATE TEMPORARY TABLE IF NOT EXISTS x.
Certes, il vaut mieux ne pas le faire de toute façon, car vous pourrez alors y retrouver « soudainement » les données restantes de « l'ancien propriétaire ». Au lieu de cela, il est préférable de lire le manuel et de voir que lors de la création d'un tableau, il est possible d'ajouter ON COMMIT DROP - c'est-à-dire qu'une fois la transaction terminée, la table sera automatiquement supprimée.
Non-réplication
Parce qu'elles appartiennent uniquement à une connexion spécifique, les tables temporaires ne sont pas répliquées. Mais cela élimine le besoin d'un double enregistrement des données dans le tas + WAL, donc INSERT/UPDATE/DELETE y est beaucoup plus rapide.
Mais comme une table temporaire reste une table « presque ordinaire », elle ne peut pas non plus être créée sur une réplique. Du moins pour le moment, même si le patch correspondant circule depuis longtemps.
1.2. TABLE NON ENREGISTRÉE
Mais que devez-vous faire, par exemple, si vous disposez d'un processus ETL fastidieux qui ne peut pas être implémenté en une seule transaction, mais que vous avez toujours pgbouncer en mode transaction? ..
Ou le flux de données est si important que Il n'y a pas assez de bande passante sur une connexion à partir d'une base de données (lecture, un processus par CPU) ?...
Ou des opérations sont en cours de manière asynchrone dans des connexions différentes ?..
Il n'y a qu'une seule option ici - créer temporairement une table non temporaire. Jeu de mots, ouais. C'est-à-dire:
- créé "mes propres" tables avec des noms au maximum aléatoires afin de ne croiser personne
- Extraction: les a remplis avec des données provenant d'une source externe
- Transformer: converti, rempli des champs de liaison clés
- Charge : versé les données prêtes dans les tables cibles
- supprimé "mes" tables
Et maintenant – une mouche dans la pommade. En fait, toutes les écritures dans PostgreSQL se produisent deux fois - , puis dans les corps de table/index. Tout cela est fait pour prendre en charge ACID et corriger la visibilité des données entre COMMIT'noisette et ROLLBACK'transactions nulles.
Mais nous n’en avons pas besoin ! Nous avons tout le processus Soit c’était complètement réussi, soit ce ne fut pas le cas.. Peu importe le nombre de transactions intermédiaires - nous ne sommes pas intéressés à « poursuivre le processus à partir du milieu », surtout lorsqu'il n'est pas clair où il se trouve.
Pour ce faire, les développeurs de PostgreSQL, dès la version 9.1, ont introduit une solution telle que :
Avec cette indication, la table est créée comme non journalisée. Les données écrites dans des tables non journalisées ne transitent pas par le journal d'écriture anticipée (voir Chapitre 29), ce qui entraîne la suppression de ces tables. travailler beaucoup plus vite que d'habitude. Cependant, ils ne sont pas à l’abri de l’échec ; en cas de panne du serveur ou d'arrêt d'urgence, une table non journalisée automatiquement tronqué. De plus, le contenu de la table non journalisée non répliqué aux serveurs esclaves. Tous les index créés sur une table non journalisée le deviennent automatiquement.
En bref, ce sera beaucoup plus rapide, mais si le serveur de base de données « tombe », ce sera désagréable. Mais à quelle fréquence cela se produit-il, et votre processus ETL sait-il comment corriger cela correctement « par le milieu » après avoir « revitalisé » la base de données ?
Si ce n'est pas le cas et que le cas ci-dessus est similaire au vôtre, utilisez UNLOGGEDmais jamais n'activez pas cet attribut sur les tables réelles, dont les données vous sont chères.
1.3. ON COMMIT { SUPPRIMER LES LIGNES | BAISSE}
Cette construction vous permet de spécifier un comportement automatique lorsqu'une transaction est terminée lors de la création d'une table.
Sur ON COMMIT DROP J'ai déjà écrit plus haut, ça génère DROP TABLE, mais avec ON COMMIT DELETE ROWS la situation est plus intéressante - elle est générée ici TRUNCATE TABLE.
Puisque toute l'infrastructure de stockage de la méta-description d'une table temporaire est exactement la même que celle d'une table normale, alors La création et la suppression constantes de tables temporaires entraînent un grave « gonflement » des tables système pg_class, pg_attribute, pg_attrdef, pg_depend,…
Imaginez maintenant que vous ayez un travailleur connecté directement à la base de données, qui ouvre une nouvelle transaction chaque seconde, crée, remplit, traite et supprime une table temporaire... Il y aura un excès de déchets accumulés dans les tables système, et cela entraînera des freins supplémentaires pour chaque opération.
En général, ne faites pas ça ! Dans ce cas, c'est beaucoup plus efficace CREATE TEMPORARY TABLE x ... ON COMMIT DELETE ROWS retirez-le du cycle de transaction - puis au début de chaque nouvelle transaction, les tables sont déjà existera (enregistrer un appel CREATE), mais sera vide, grâce à TRUNCATE (nous avons également enregistré son appel) lors de la réalisation de la transaction précédente.
1.4. COMME... Y COMPRIS...
J'ai mentionné au début que l'un des cas d'utilisation typiques des tables temporaires concerne divers types d'importations - et le développeur copie-colle avec lassitude la liste des champs de la table cible dans la déclaration de son temporaire...
Mais la paresse est le moteur du progrès ! C'est pourquoi créer un nouveau tableau "basé sur un échantillon" ça peut être beaucoup plus simple :
CREATE TEMPORARY TABLE import_table(
LIKE target_table
);Puisque vous pouvez alors générer beaucoup de données dans ce tableau, la recherche ne sera jamais rapide. Mais il existe une solution traditionnelle à ce problème : les index ! Et oui, une table temporaire peut aussi avoir des index.
Puisque, souvent, les index requis coïncident avec les index de la table cible, vous pouvez simplement écrire LIKE target_table INCLUDING INDEXES.
Si vous avez également besoin DEFAULT-valeurs (par exemple, pour remplir les valeurs de la clé primaire), vous pouvez utiliser LIKE target_table INCLUDING DEFAULTS. Ou simplement - LIKE target_table INCLUDING ALL — copie les valeurs par défaut, les index, les contraintes,...
Mais ici, vous devez comprendre que si vous avez créé importez la table immédiatement avec les index, le chargement des données prendra alors plus de tempsque si vous remplissiez d'abord tout, puis regroupiez ensuite les index - regardez comment il procède à titre d'exemple .
En général, !
2. Comment écrire ?
Laissez-moi juste vous dire : utilisez-le -flow au lieu de "pack" INSERT, . Vous pouvez même directement à partir d'un fichier pré-généré.
3. Comment traiter ?
Alors, laissons notre introduction ressembler à ceci :
- vous avez une table avec les données client stockées dans votre base de données 1 million d'enregistrements
- chaque jour un client vous en envoie un nouveau "image" complète
- par expérience tu sais que de temps en temps pas plus de 10 XNUMX enregistrements sont modifiés
Un exemple classique d'une telle situation est — il y a beaucoup d'adresses au total, mais dans chaque téléchargement hebdomadaire il y a très peu de changements (renommage des colonies, combinaison de rues, apparition de nouvelles maisons), même à l'échelle nationale.
3.1. Algorithme de synchronisation complète
Pour plus de simplicité, disons que vous n'avez même pas besoin de restructurer les données - il suffit de mettre le tableau sous la forme souhaitée, c'est-à-dire :
- supprimer tout ce qui n'existe plus
- rafraîchir tout ce qui existait déjà et doit être mis à jour
- insérer tout ce qui n'est pas encore arrivé
Pourquoi faut-il faire les opérations dans cet ordre ? Parce que c'est ainsi que la taille de la table augmentera de manière minimale ().
SUPPRIMER DE l'heure d'été
Non, bien sûr, vous pouvez vous en sortir avec seulement deux opérations :
- supprimer (
DELETE) tout en général - insérer tout à partir de la nouvelle image
Mais en même temps, grâce à MVCC, La taille du tableau augmentera exactement deux fois! Obtenir +1 million d'images d'enregistrements dans la table en raison d'une mise à jour de 10 XNUMX est une redondance moyenne...
TRONQUER l'heure d'été
Un développeur plus expérimenté sait que la tablette entière peut être nettoyée à moindre coût :
- clair (
TRUNCATE) le tableau entier - insérer tout à partir de la nouvelle image
La méthode est efficace, , mais il y a un problème... Nous allons ajouter 1 million d'enregistrements pendant longtemps, nous ne pouvons donc pas nous permettre de laisser la table vide pendant tout ce temps (comme cela se produira sans l'envelopper dans une seule transaction).
Ce qui signifie:
- nous commençons transaction de longue durée
TRUNCATEimpose Accès exclusif-blocage- nous faisons l'insertion depuis longtemps, et tout le monde en ce moment je ne peux même pas
SELECT
Quelque chose ne va pas...
ALTER TABLE… RENOMMER… / SUPPRIMER LA TABLE…
Une alternative consiste à tout remplir dans un nouveau tableau séparé, puis à le renommer simplement à la place de l'ancien. Quelques petites choses désagréables :
- encore aussi Accès exclusif, bien que beaucoup moins de temps
- tous les plans/statistiques de requête pour cette table sont réinitialisés,
- toutes les clés étrangères sont cassées (FK) à table
Il y avait un patch WIP de Simon Riggs qui suggérait de créer ALTER-une opération de remplacement du corps de la table au niveau du fichier, sans toucher aux statistiques et au FK, mais n'ayant pas collecté le quorum.
SUPPRIMER, METTRE À JOUR, INSÉRER
Nous optons donc pour l'option non bloquante de trois opérations. Presque trois... Comment y parvenir le plus efficacement possible ?
-- все делаем в рамках транзакции, чтобы никто не видел "промежуточных" состояний
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. Post-traitement d'importation
Dans le même KLADR, tous les enregistrements modifiés doivent en outre être traités via un post-traitement - normalisés, les mots-clés mis en évidence et réduits aux structures requises. Mais comment sais-tu - qu'est-ce qui a changé exactementsans compliquer le code de synchronisation, idéalement sans y toucher du tout ?
Si seul votre processus dispose d'un accès en écriture au moment de la synchronisation, vous pouvez alors utiliser un déclencheur qui collectera toutes les modifications pour nous :
-- целевые таблицы
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;
Nous pouvons désormais appliquer des déclencheurs avant de démarrer la synchronisation (ou les activer via 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();
Et puis nous extrayons calmement toutes les modifications dont nous avons besoin des tables de journaux et les exécutons via des gestionnaires supplémentaires.
3.3. Importation d'ensembles liés
Ci-dessus, nous avons considéré des cas où les structures de données de la source et de la destination sont les mêmes. Mais que se passe-t-il si le téléchargement depuis un système externe a un format différent de la structure de stockage dans notre base de données ?
Prenons comme exemple le stockage des clients et de leurs comptes, l'option classique « plusieurs-à-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)
);Mais le téléchargement depuis une source externe nous parvient sous la forme du « tout en un » :
CREATE TEMPORARY TABLE invoice_import(
client_inn
varchar
, client_name
varchar
, invoice_number
varchar
, invoice_dt
date
, invoice_sum
numeric(32,2)
);Évidemment, les données clients peuvent être dupliquées dans cette version, et l'enregistrement principal est « 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
Pour le modèle, nous insérerons simplement nos données de test, mais rappelez-vous : COPY plus efficace!
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);Tout d'abord, soulignons les « réductions » auxquelles font référence nos « faits ». Dans notre cas, les factures font référence aux clients :
CREATE TEMPORARY TABLE client_import AS
SELECT DISTINCT ON(client_inn)
-- можно просто SELECT DISTINCT, если данные заведомо непротиворечивы
client_inn inn
, client_name "name"
FROM
invoice_import;Afin d'associer correctement les comptes aux identifiants clients, nous devons d'abord connaître ou générer ces identifiants. Ajoutons des champs en dessous :
ALTER TABLE invoice_import ADD COLUMN client_id integer;
ALTER TABLE client_import ADD COLUMN client_id integer;Utilisons la méthode de synchronisation de table décrite ci-dessus avec une petite modification - nous ne mettrons ni ne supprimerons quoi que ce soit dans la table cible, car nous importons les clients « en ajout uniquement » :
-- проставляем в таблице импорта 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; -- прикладной ключ
En fait, tout est dans invoice_import Nous avons maintenant le champ de contact rempli client_id, avec lequel nous insérerons la facture.
Source: habr.com