Die Microservice-Architektur hat wie alles auf dieser Welt ihre Vor- und Nachteile. Einige Prozesse damit werden einfacher, andere komplizierter. Und im Interesse der Änderungsgeschwindigkeit und der besseren Skalierbarkeit müssen Opfer gebracht werden. Eine davon ist die Komplikation der Analytik. Wenn in einem Monolithen alle betrieblichen Analysen für ein analytisches Replikat auf SQL-Abfragen reduziert werden können, hat in einer Multiservice-Architektur jeder Dienst seine eigene Basis, und es scheint, dass auf eine Abfrage nicht verzichtet werden kann (oder kann auf sie verzichtet werden?). Für diejenigen, die daran interessiert sind, wie wir das Problem der operativen Analyse in unserem Unternehmen gelöst haben und wie wir gelernt haben, mit dieser Lösung zu leben - willkommen.

Mein Name ist Pavel Sivash. In DomKlik arbeite ich in einem Team, das für die Wartung des analytischen Data Warehouse verantwortlich ist. Herkömmlicherweise können unsere Aktivitäten auf das Datum des Engineering zurückgeführt werden, aber tatsächlich ist das Aufgabenspektrum viel breiter. Es gibt ETL / ELT-Standards für das Date Engineering, die Unterstützung und Anpassung von Tools für die Datenanalyse und die Entwicklung eigener Tools. Insbesondere für die operative Berichterstattung haben wir beschlossen, so zu tun, als hätten wir einen Monolithen, und den Analysten eine Basis zu geben, auf der sie alle benötigten Daten haben.

Im Allgemeinen haben wir verschiedene Optionen in Betracht gezogen. Es war möglich, ein vollwertiges Repository zu erstellen - wir haben es sogar versucht, aber um ehrlich zu sein, haben wir es nicht geschafft, Freunde zu finden, die häufig genug Änderungen an der Logik vorgenommen haben, und zwar mit einem ziemlich langsamen Prozess, ein Repository zu erstellen und Änderungen daran vorzunehmen (wenn jemand erfolgreich war, schreiben Sie in die Kommentare, wie). Man könnte den Analysten sagen: „Leute, lernt Python und geht zu analytischen Hinweisen“, aber dies ist eine zusätzliche Voraussetzung für die Einstellung von Mitarbeitern, und es schien, dass dies nach Möglichkeit vermieden werden sollte. Wir haben uns entschlossen, die FDW-Technologie (Foreign Data Wrapper) zu verwenden: Tatsächlich ist dies der Standard-Dblink, der im SQL-Standard enthalten ist, aber über eine wesentlich bequemere Benutzeroberfläche verfügt. Auf dieser Grundlage haben wir eine Entscheidung getroffen, die sich schließlich beruhigt hat. Wir haben damit aufgehört. Seine Details sind Gegenstand eines separaten Artikels oder vielleicht auch nicht eines.weil ich viel darüber reden möchte: von der Synchronisation von Datenbankschemata über die Zugriffskontrolle bis hin zur Anonymisierung personenbezogener Daten. Sie müssen auch reservieren, dass diese Lösung keinen Ersatz für echte analytische Datenbanken und Repositorys darstellt, sondern nur ein bestimmtes Problem löst.

Auf höchster Ebene sieht es so aus:

Es gibt eine PostgreSQL-Datenbank, in der Benutzer ihre Arbeitsdaten speichern können, und vor allem sind analytische Replikate aller Dienste über FDW mit dieser Datenbank verbunden. Auf diese Weise können Sie eine Abfrage in mehrere Datenbanken schreiben, unabhängig davon, um was es sich handelt: PostgreSQL, MySQL, MongoDB oder etwas anderes (Datei, API, wenn plötzlich kein geeigneter Wrapper vorhanden ist, können Sie Ihren eigenen schreiben). Nun, alles scheint super zu sein! Sind wir nicht einverstanden?

Wenn alles so schnell und einfach geendet hätte, hätte es wahrscheinlich keinen Artikel gegeben.

Es ist wichtig zu verstehen, wie Postgres Anforderungen an Remoteserver verarbeitet. Dies scheint logisch, wird jedoch häufig nicht beachtet: postgres unterteilt die Anforderung in Teile, die unabhängig voneinander auf Remote-Servern ausgeführt werden, sammelt diese Daten und die endgültigen Berechnungen werden von selbst ausgeführt, sodass die Geschwindigkeit der Anforderung stark davon abhängt, wie sie geschrieben wird. Es sollte auch beachtet werden: Wenn Daten von einem Remote-Server stammen, haben sie keine Indizes mehr, es gibt nichts, was dem Scheduler helfen kann, daher können nur wir selbst helfen und sie vorschlagen. Und ich möchte Ihnen mehr darüber erzählen.

Einfache Anfrage und planen Sie damit

Um zu zeigen, wie postgres eine Abfrage in einer 6-Millionen-Zeilentabelle auf einem Remote-Server ausführt, sehen wir uns einen einfachen Plan an.

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

Mit der Anweisung VERBOSE können Sie die Anforderung anzeigen, die an den Remote-Server gesendet wird und deren Ergebnisse wir zur weiteren Verarbeitung erhalten (RemoteSQL-Zeile).

Lassen Sie uns etwas weiter gehen und unserer Abfrage mehrere Filter hinzufügen: einen nach dem Booleschen Feld, einen nach dem Zeitstempel im Intervall und einen nach 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

Hier liegt der Moment, auf den Sie beim Schreiben von Abfragen achten müssen. Filter wurden nicht auf den Remote-Server übertragen, was bedeutet, dass der Postgres zur Ausführung alle 6 Millionen Zeilen erweitert, nur um dann lokal zu filtern (Filterzeile) und eine Aggregation durchzuführen. Der Schlüssel zum Erfolg besteht darin, eine Anforderung zu schreiben, damit die Filter auf den Remotecomputer übertragen werden und nur die erforderlichen Zeilen empfangen und aggregiert werden.

Das ist ein Blödsinn

Mit booleschen Feldern ist alles einfach. In der ursprünglichen Anfrage war das Problem auf die is- Anweisung zurückzuführen . Wenn wir es durch = ersetzen , erhalten wir das folgende Ergebnis:

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

Wie Sie sehen können, flog der Filter zu einem Remote-Server und die Laufzeit wurde von 27 auf 19 Sekunden reduziert.

Es ist anzumerken, dass sich der Operator is vom Operator = dadurch unterscheidet, dass er mit dem Nullwert arbeiten kann. Dies bedeutet, dass nicht wahr im Filter False und Null hinterlässt, während ! = True nur False hinterlässt. Wenn Sie den Operator is not ersetzen , sollten daher zwei Bedingungen mit dem Operator OR an den Filter übergeben werden, z. B. WHERE (col! = True) OR (col ist null) .

Fahren Sie mit sortiertem Booleschen Wert fort. Setzen Sie in der Zwischenzeit den Filter nach Booleschem Wert auf seine ursprüngliche Form zurück, um die Auswirkungen anderer Änderungen unabhängig zu berücksichtigen.

timestamptz? hz

Im Allgemeinen muss man oft experimentieren, wie eine Abfrage geschrieben wird, an der Remoteserver beteiligt sind, und erst dann nach einer Erklärung suchen, warum dies geschieht. Sehr wenig Informationen dazu finden Sie im Internet. In Experimenten haben wir festgestellt, dass der Filter nach einem festen Datum mit einem Knall zum Remote-Server fliegt. Wenn wir das Datum jedoch dynamisch einstellen möchten, z. B. jetzt () oder CURRENT_DATE, geschieht dies nicht. In unserem Beispiel haben wir einen Filter hinzugefügt, sodass die Spalte "created_at" Daten für genau 1 Monat in der Vergangenheit enthält (ZWISCHEN CURRENT_DATE - INTERVALL '7 Monate' UND CURRENT_DATE - INTERVALL '6 Monate'). Was haben wir in diesem Fall getan?

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

Wir haben den Scheduler aufgefordert, das Datum in der Unterabfrage im Voraus zu berechnen und die vorgefertigte Variable an den Filter zu übergeben. Und dieser Hinweis gab uns ein hervorragendes Ergebnis, die Abfrage wurde fast sechsmal schneller!

Auch hier ist es wichtig, vorsichtig zu sein: Der Datentyp in der Unterabfrage muss mit dem Feld übereinstimmen, nach dem wir filtern. Andernfalls entscheidet der Scheduler, dass die Daten unterschiedlich sind, und Sie müssen zuerst alle Daten abrufen und lokal filtern.

Setzen Sie den Filter nach Datum auf seinen ursprünglichen Wert zurück.

Freddy vs. Jsonb

Im Allgemeinen haben boolesche Felder und Daten unsere Abfrage bereits beschleunigt, es gab jedoch noch einen weiteren Datentyp. Der Kampf mit der Filterung ist offen gesagt immer noch nicht vorbei, obwohl es Erfolge gibt. So haben wir es geschafft, den Filter nach jsonb- Feld an den Remote-Server zu übergeben.

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

Anstatt Operatoren zu filtern, müssen Sie den Operator verwenden, ein jsonb in einem anderen zu haben. 7 Sekunden statt der ersten 29. Bisher ist dies die einzige erfolgreiche Option zum Übertragen von Filtern über jsonb auf einen Remote-Server. Es ist jedoch wichtig, eine Einschränkung zu berücksichtigen: Wir verwenden die Datenbankversion 9.6, planen jedoch, die neuesten Tests abzuschließen und bis Ende April auf Version 12 umzusteigen . Während wir aktualisieren, werden wir schreiben, wie sich dies auswirkt, da es viele Änderungen gibt, für die es viele Hoffnungen gibt: json_path, neues CTE-Verhalten, Push-Down (vorhanden ab Version 10). Ich würde es gerne bald versuchen.

Mach ihn fertig

Wir haben einzeln geprüft, wie sich jede Änderung auf die Geschwindigkeit der Anfrage auswirkt. Nun wollen wir sehen, was passiert, wenn alle drei Filter richtig geschrieben sind.

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

Ja, die Anfrage sieht komplizierter aus, es ist ein erzwungenes Board, aber die Ausführungsgeschwindigkeit beträgt 2 Sekunden, was mehr als zehnmal schneller ist! Und wir sprechen von einer einfachen Abfrage für einen relativ kleinen Datensatz. Auf reale Anfragen haben wir bis zu mehreren hundert Mal Wachstum erhalten.

Zusammenfassend: Wenn Sie PostgreSQL mit FDW verwenden, überprüfen Sie immer, ob alle Filter an den Remote-Server gesendet wurden, und Sie werden zufrieden sein ... Zumindest bis Sie zu den Verknüpfungen zwischen Tabellen von verschiedenen Servern gelangen. Aber das ist die Geschichte für einen anderen Artikel.

Vielen Dank für Ihre Aufmerksamkeit! Ich würde mich freuen, Fragen, Kommentare sowie Geschichten über Ihre Erfahrungen in den Kommentaren zu hören.

Online-Analyse in der Microservice-Architektur: Hilfe und Vorschlag Postgres FDW и и Post Post ̶п̶р̶о̶с̶т̶т иь̶

Einfache Anfrage und planen Sie damit

Das ist ein Blödsinn

timestamptz? hz

Freddy vs. Jsonb

Mach ihn fertig

More articles: