Get best of the week

Analyses en ligne dans l'architecture de microservices: aidez et suggérez Postgres FDW Post и Post Post ̶п̶р̶о̶с̶т̶и̶т иь̶

L'architecture de microservices, comme tout dans ce monde, a ses avantages et ses inconvénients. Certains processus deviennent plus faciles, d'autres plus compliqués. Et pour des raisons de vitesse de changement et de meilleure évolutivité, des sacrifices doivent être consentis. L'un d'eux est la complication de l'analyse. Si dans un monolithe toutes les analyses opérationnelles peuvent être réduites à des requêtes SQL pour une réplique analytique, alors dans une architecture multiservice, chaque service a sa propre base et il semble qu'une requête ne peut pas être supprimée (ou peut-elle être supprimée?). Pour ceux qui souhaitent savoir comment nous avons résolu le problème de l'analyse opérationnelle dans notre entreprise et comment nous avons appris à vivre avec cette solution - bienvenue.


Mon nom est Pavel Sivash, à DomKlik je travaille dans une équipe qui est responsable de la maintenance de l'entrepôt de données analytiques. Classiquement, nos activités peuvent être attribuées à la date de l'ingénierie, mais, en fait, l'éventail des tâches est beaucoup plus large. Il existe une norme ETL / ELT pour l'ingénierie des dates, le support et l'adaptation des outils pour l'analyse des données et le développement de leurs propres outils. En particulier, pour le reporting opérationnel, nous avons décidé de «faire semblant» d'avoir un monolithe et de donner aux analystes une base dans laquelle ils disposeront de toutes les données dont ils ont besoin.

En général, nous avons considéré différentes options. Il était possible de créer un référentiel à part entière - nous avons même essayé, mais honnêtement, nous n'avons pas réussi à nous faire des amis assez souvent des changements dans la logique avec un processus assez lent de construction d'un référentiel et y apporter des modifications (si quelqu'un a réussi, écrivez dans les commentaires comment). On pourrait dire aux analystes: "Les gars, apprenez le python et allez aux indices analytiques", mais c'est une exigence supplémentaire pour le recrutement du personnel, et il semblait que cela devrait être évité si possible. Nous avons décidé d'essayer d'utiliser la technologie FDW (Foreign Data Wrapper): en fait, c'est le dblink standard, qui est dans le standard SQL, mais avec son interface beaucoup plus pratique. Sur la base de cela, nous avons pris une décision, qui s'est finalement stabilisée, nous nous y sommes arrêtés. Ses détails font l'objet d'un article séparé, ou peut-être pas un,parce que je veux parler de beaucoup: de la synchronisation des schémas de bases de données au contrôle d'accès et à l'anonymisation des données personnelles. Vous devez également faire une réservation que cette solution ne remplace pas de véritables bases de données et référentiels analytiques, elle ne résout qu'un problème spécifique.

Au niveau supérieur, cela ressemble à ceci:


Il existe une base de données PostgreSQL, où les utilisateurs peuvent stocker leurs données de travail, et surtout, des répliques analytiques de tous les services sont connectées à cette base de données via FDW. Cela permet d'écrire une requête dans plusieurs bases de données, quelle qu'elle soit: PostgreSQL, MySQL, MongoDB ou autre (fichier, API, si soudainement il n'y a pas de wrapper approprié, vous pouvez écrire le vôtre). Eh bien, tout semble super! Sommes-nous en désaccord?

Si tout s'était terminé aussi rapidement et simplement, alors, probablement, il n'y aurait pas eu d'article.

Il est important de comprendre clairement comment postgres gère les requêtes vers des serveurs distants. Cela semble logique, mais souvent ils n'y prêtent pas attention: postgres divise la demande en parties exécutées indépendamment sur des serveurs distants, recueille ces données et les calculs finaux sont effectués par lui-même, la vitesse de la demande dépendra donc grandement de la façon dont elle est écrite. Il convient également de noter: lorsque les données proviennent d'un serveur distant, elles n'ont plus d'index, rien ne peut aider le planificateur, donc, nous seuls pouvons l'aider et le suggérer. Et je voudrais vous en dire plus à ce sujet.

Demande simple et planifier avec elle


Pour montrer comment postgres exécute une requête sur une table de 6 millions de lignes sur un serveur distant, regardons un plan simple.

explain analyze verbose  
SELECT count(1)
FROM fdw_schema.table;

Aggregate  (cost=418383.23..418383.24 rows=1 width=8) (actual time=3857.198..3857.198 rows=1 loops=1)
  Output: count(1)
  ->  Foreign Scan on fdw_schema."table"  (cost=100.00..402376.14 rows=6402838 width=0) (actual time=4.874..3256.511 rows=6406868 loops=1)
        Output: "table".id, "table".is_active, "table".meta, "table".created_dt
        Remote SQL: SELECT NULL FROM fdw_schema.table
Planning time: 0.986 ms
Execution time: 3857.436 ms

L'utilisation de l'instruction VERBOSE vous permet de voir la requête qui sera envoyée au serveur distant et les résultats que nous recevrons pour un traitement ultérieur (ligne RemoteSQL).

Allons un peu plus loin et ajoutons quelques filtres à notre requête: un par le champ booléen , un par l' horodatage de l'intervalle et un par jsonb .

explain analyze verbose
SELECT count(1)
FROM fdw_schema.table 
WHERE is_active is True
AND created_dt BETWEEN CURRENT_DATE - INTERVAL '7 month' 
AND CURRENT_DATE - INTERVAL '6 month'
AND meta->>'source' = 'test';

Aggregate  (cost=577487.69..577487.70 rows=1 width=8) (actual time=27473.818..25473.819 rows=1 loops=1)
  Output: count(1)
  ->  Foreign Scan on fdw_schema."table"  (cost=100.00..577469.21 rows=7390 width=0) (actual time=31.369..25372.466 rows=1360025 loops=1)
        Output: "table".id, "table".is_active, "table".meta, "table".created_dt
        Filter: (("table".is_active IS TRUE) AND (("table".meta ->> 'source'::text) = 'test'::text) AND ("table".created_dt >= (('now'::cstring)::date - '7 mons'::interval)) AND ("table".created_dt <= ((('now'::cstring)::date)::timestamp with time zone - '6 mons'::interval)))
        Rows Removed by Filter: 5046843
        Remote SQL: SELECT created_dt, is_active, meta FROM fdw_schema.table
Planning time: 0.665 ms
Execution time: 27474.118 ms

C'est là que réside le moment auquel vous devez faire attention lors de l'écriture de requêtes. Les filtres n'ont pas été transférés sur le serveur distant, ce qui signifie que pour l'exécuter, le postgres étend les 6 millions de lignes, puis seulement pour filtrer localement (ligne de filtre) et effectuer une agrégation. La clé du succès est d'écrire une demande afin que les filtres soient transférés vers la machine distante, et nous ne recevions et agrégions que les lignes nécessaires.

C'est de la merde


Avec les champs booléens, tout est simple. Dans la demande d'origine, le problème était dû à la déclaration is . Si nous le remplaçons par = , nous obtenons le résultat suivant:

explain analyze verbose
SELECT count(1)
FROM fdw_schema.table
WHERE is_active = True
AND created_dt BETWEEN CURRENT_DATE - INTERVAL '7 month' 
AND CURRENT_DATE - INTERVAL '6 month'
AND meta->>'source' = 'test';

Aggregate  (cost=508010.14..508010.15 rows=1 width=8) (actual time=19064.314..19064.314 rows=1 loops=1)
  Output: count(1)
  ->  Foreign Scan on fdw_schema."table"  (cost=100.00..507988.44 rows=8679 width=0) (actual time=33.035..18951.278 rows=1360025 loops=1)
        Output: "table".id, "table".is_active, "table".meta, "table".created_dt
        Filter: ((("table".meta ->> 'source'::text) = 'test'::text) AND ("table".created_dt >= (('now'::cstring)::date - '7 mons'::interval)) AND ("table".created_dt <= ((('now'::cstring)::date)::timestamp with time zone - '6 mons'::interval)))
        Rows Removed by Filter: 3567989
        Remote SQL: SELECT created_dt, meta FROM fdw_schema.table WHERE (is_active)
Planning time: 0.834 ms
Execution time: 19064.534 ms

Comme vous pouvez le voir, le filtre a volé vers un serveur distant et le temps d'exécution a été réduit de 27 à 19 secondes.

Il convient de noter que l'opérateur is diffère de l'opérateur = en ce qu'il peut fonctionner avec la valeur Null. Cela signifie que ce n'est pas vrai dans le filtre laissera False et Null, tandis que ! = True ne laissera que False. Par conséquent, lorsque vous remplacez l' opérateur is not , deux conditions par l'opérateur OR doivent être transmises au filtre, par exemple, WHERE (col! = True) OR (col est null) .

Avec booléen trié, passez à autre chose. En attendant, remettez le filtre par valeur booléenne dans sa forme d'origine, pour tenir compte indépendamment de l'effet des autres modifications.

timestamptz? hz


En général, il faut souvent expérimenter comment écrire une requête impliquant des serveurs distants, et ensuite seulement chercher une explication de la raison pour laquelle cela se produit. Très peu d'informations à ce sujet peuvent être trouvées sur Internet. Ainsi, dans des expériences, nous avons constaté que le filtre par une date fixe vole vers le serveur distant avec un bang, mais lorsque nous voulons définir la date dynamiquement, par exemple, now () ou CURRENT_DATE, cela ne se produit pas. Dans notre exemple, nous avons ajouté un filtre afin que la colonne created_at contienne exactement les données du mois précédent (BETWEEN CURRENT_DATE - INTERVAL '7 month' AND CURRENT_DATE - INTERVAL '6 month'). Qu'avons-nous fait dans ce cas?

explain analyze verbose
SELECT count(1)
FROM fdw_schema.table 
WHERE is_active is True
AND created_dt >= (SELECT CURRENT_DATE::timestamptz - INTERVAL '7 month') 
AND created_dt <(SELECT CURRENT_DATE::timestamptz - INTERVAL '6 month')
AND meta->>'source' = 'test';

Aggregate  (cost=306875.17..306875.18 rows=1 width=8) (actual time=4789.114..4789.115 rows=1 loops=1)
  Output: count(1)
  InitPlan 1 (returns $0)
    ->  Result  (cost=0.00..0.02 rows=1 width=8) (actual time=0.007..0.008 rows=1 loops=1)
          Output: ((('now'::cstring)::date)::timestamp with time zone - '7 mons'::interval)
  InitPlan 2 (returns $1)
    ->  Result  (cost=0.00..0.02 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=1)
          Output: ((('now'::cstring)::date)::timestamp with time zone - '6 mons'::interval)
  ->  Foreign Scan on fdw_schema."table"  (cost=100.02..306874.86 rows=105 width=0) (actual time=23.475..4681.419 rows=1360025 loops=1)
        Output: "table".id, "table".is_active, "table".meta, "table".created_dt
        Filter: (("table".is_active IS TRUE) AND (("table".meta ->> 'source'::text) = 'test'::text))
        Rows Removed by Filter: 76934
        Remote SQL: SELECT is_active, meta FROM fdw_schema.table WHERE ((created_dt >= $1::timestamp with time zone)) AND ((created_dt < $2::timestamp with time zone))
Planning time: 0.703 ms
Execution time: 4789.379 ms

Nous avons invité le planificateur à calculer à l'avance la date dans la sous-requête et à transmettre la variable prête à l'emploi au filtre. Et cet indice nous a donné un excellent résultat, la requête est devenue presque 6 fois plus rapide!

Encore une fois, il est important d'être prudent ici: le type de données dans la sous-requête doit être le même que le champ pour lequel nous filtrons, sinon le planificateur décidera que puisque les types sont différents et vous devez d'abord obtenir toutes les données et les filtrer localement.

Remettez le filtre par date à sa valeur d'origine.

Freddy vs. Jsonb


En général, les champs booléens et les dates ont déjà accéléré notre requête, mais il y avait un autre type de données. Franchement, la bataille avec le filtrage n'est pas terminée, bien qu'il y ait des succès. Voici donc comment nous avons réussi à passer le filtre par champ jsonb au serveur distant.

explain analyze verbose
SELECT count(1)
FROM fdw_schema.table 
WHERE is_active is True
AND created_dt BETWEEN CURRENT_DATE - INTERVAL '7 month' 
AND CURRENT_DATE - INTERVAL '6 month'
AND meta @> '{"source":"test"}'::jsonb;

Aggregate  (cost=245463.60..245463.61 rows=1 width=8) (actual time=6727.589..6727.590 rows=1 loops=1)
  Output: count(1)
  ->  Foreign Scan on fdw_schema."table"  (cost=1100.00..245459.90 rows=1478 width=0) (actual time=16.213..6634.794 rows=1360025 loops=1)
        Output: "table".id, "table".is_active, "table".meta, "table".created_dt
        Filter: (("table".is_active IS TRUE) AND ("table".created_dt >= (('now'::cstring)::date - '7 mons'::interval)) AND ("table".created_dt <= ((('now'::cstring)::date)::timestamp with time zone - '6 mons'::interval)))
        Rows Removed by Filter: 619961
        Remote SQL: SELECT created_dt, is_active FROM fdw_schema.table WHERE ((meta @> '{"source": "test"}'::jsonb))
Planning time: 0.747 ms
Execution time: 6727.815 ms

Au lieu de filtrer les opérateurs, vous devez utiliser l'opérateur d'avoir un jsonb dans un autre. 7 secondes au lieu des 29. Au départ, il s'agit de la seule option réussie de transfert de filtres via jsonb vers un serveur distant, mais il est important de tenir compte d'une limitation: nous utilisons la base de données version 9.6, mais nous prévoyons de terminer les derniers tests et de passer à la version 12 d'ici la fin avril. Au fur et à mesure de la mise à jour, nous écrirons comment cela a affecté, car il y a beaucoup de changements pour lesquels il y a beaucoup d'espoirs: json_path, nouveau comportement CTE, push down (existant depuis la version 10). J'aimerais l'essayer bientôt.

Finis-le


Nous avons vérifié comment chaque modification affecte la vitesse de la demande individuellement. Voyons maintenant ce qui se passe lorsque les trois filtres sont écrits correctement.

explain analyze verbose
SELECT count(1)
FROM fdw_schema.table 
WHERE is_active = True
AND created_dt >= (SELECT CURRENT_DATE::timestamptz - INTERVAL '7 month') 
AND created_dt <(SELECT CURRENT_DATE::timestamptz - INTERVAL '6 month')
AND meta @> '{"source":"test"}'::jsonb;

Aggregate  (cost=322041.51..322041.52 rows=1 width=8) (actual time=2278.867..2278.867 rows=1 loops=1)
  Output: count(1)
  InitPlan 1 (returns $0)
    ->  Result  (cost=0.00..0.02 rows=1 width=8) (actual time=0.010..0.010 rows=1 loops=1)
          Output: ((('now'::cstring)::date)::timestamp with time zone - '7 mons'::interval)
  InitPlan 2 (returns $1)
    ->  Result  (cost=0.00..0.02 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=1)
          Output: ((('now'::cstring)::date)::timestamp with time zone - '6 mons'::interval)
  ->  Foreign Scan on fdw_schema."table"  (cost=100.02..322041.41 rows=25 width=0) (actual time=8.597..2153.809 rows=1360025 loops=1)
        Output: "table".id, "table".is_active, "table".meta, "table".created_dt
        Remote SQL: SELECT NULL FROM fdw_schema.table WHERE (is_active) AND ((created_dt >= $1::timestamp with time zone)) AND ((created_dt < $2::timestamp with time zone)) AND ((meta @> '{"source": "test"}'::jsonb))
Planning time: 0.820 ms
Execution time: 2279.087 ms

Oui, la requête semble plus compliquée, c'est une carte forcée, mais la vitesse d'exécution est de 2 secondes, ce qui est plus de 10 fois plus rapide! Et nous parlons d'une simple requête sur un ensemble de données relativement petit. Sur de vraies demandes, nous avons enregistré une croissance jusqu'à plusieurs centaines de fois.

Pour résumer: si vous utilisez PostgreSQL avec FDW, vérifiez toujours si tous les filtres sont envoyés au serveur distant, et vous serez satisfait ... Au moins jusqu'à ce que vous arriviez aux jointures entre les tables de différents serveurs. Mais c'est l'histoire d'un autre article.

Merci pour l'attention! Je serai heureux d'entendre des questions, des commentaires, ainsi que des histoires sur votre expérience dans les commentaires.

More articles:


All Articles