Die Microservices-Architektur hat, wie alles im Leben, ihre Vor- und Nachteile. Einige Prozesse werden einfacher, während andere komplexer werden. Um schneller auf Änderungen reagieren zu können und eine bessere Skalierbarkeit zu erreichen, müssen wir jedoch gewisse Opfer bringen. Eines davon ist die Komplexität der Analyse. Während in einer Monolith-Anwendung die gesamte operative Analyse auf SQL-Abfragen an einer analytischen Replikation beruhen kann, hat jeder Service in einer Microservices-Architektur seine eigene Datenbank. Es scheint, dass man mit nur einer Abfrage nicht auskommt (oder vielleicht doch?). Für alle, 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 – herzlich willkommen.

Mein Name ist Pavel Sivasch und ich arbeite bei DomClick im Team, das für die Betreuung des analytischen Data Warehouses zuständig ist. Unsere Arbeit lässt sich grob der Datenengineering zuordnen, jedoch ist das Spektrum der Aufgaben deutlich breiter. Es gibt die typischen ETL/ELT-Prozesse im Datenengineering, die Unterstützung und Anpassung von Analysetools sowie die Entwicklung eigener Tools. Insbesondere für die operative Berichterstattung haben wir beschlossen, so zu tun, als hätten wir ein monolithisches System, und den Analysten eine zentrale Datenbank bereitzustellen, die alle benötigten Informationen enthält.

Wir haben verschiedene Optionen in Betracht gezogen. Man könnte ein vollwertiges Speicher-System aufbauen – wir haben es sogar ausprobiert, aber ehrlich gesagt ist es uns nicht gelungen, häufige Änderungen in der Logik mit dem recht langsamen Prozess des Aufbaus des Speichers und der Integration von Änderungen zu vereinen (wenn jemand es geschafft hat, bitte teilen Sie Ihre Erfahrungen in den Kommentaren mit). Es hätte den Analysten gesagt werden können: "Leute, lernt Python und nutzt analytische Instanzen", aber das wäre eine zusätzliche Anforderung bei der Personalbeschaffung und wir wollten dem, wenn möglich, aus dem Weg gehen. Deshalb entschieden wir uns, die Technologie FDW (Foreign Data Wrapper) auszuprobieren: im Grunde ist das ein standardmäßiger dblink, der im SQL-Standard vorhanden ist, jedoch mit einer viel benutzerfreundlicheren Schnittstelle. Auf dieser Basis haben wir eine Lösung entwickelt, die sich schließlich bewährt hat und auf der wir stehen geblieben sind. Die Einzelheiten dazu sind das Thema eines eigenen Artikels, vielleicht sogar mehrerer, denn es gibt viel zu erzählen: von der Synchronisierung von Datenbankschemas bis hin zum Zugriffsmanagement und der Anonymisierung personenbezogener Daten. Es sollte auch erwähnt werden, dass diese Lösung keinen Ersatz für echte analytische Datenbanken und Speichersysteme darstellt, sondern lediglich ein spezifisches Problem löst.

Auf oberster Ebene sieht das so aus:

Es gibt eine PostgreSQL-Datenbank, in der die Benutzer ihre Arbeitsdaten speichern können. Das Wichtigste ist, dass über FDW analytische Replikate aller Dienste mit dieser Datenbank verbunden sind. Dies ermöglicht es, Abfragen an mehrere Datenbanken zu richten, unabhängig davon, ob es sich um PostgreSQL, MySQL, MongoDB oder etwas anderes handelt (eine Datei, eine API – wenn es keinen geeigneten Wrapper gibt, kann man auch seinen eigenen schreiben). Soweit alles klar? Super! Auf Wiedersehen?

Wenn alles so schnell und einfach enden würde, gäbe es wahrscheinlich keinen Artikel.

Es ist wichtig genau zu verstehen, wie PostgreSQL Anfragen an entfernte Server verarbeitet. Das scheint logisch, wird jedoch oft nicht beachtet: PostgreSQL zerlegt eine Anfrage in Teile, die unabhängig auf den entfernten Servern ausgeführt werden, sammelt diese Daten und führt die abschließenden Berechnungen selbst durch. Daher hängt die Geschwindigkeit der Anfrage stark davon ab, wie sie geschrieben ist. Es sollte auch erwähnt werden: Wenn Daten von einem entfernten Server ankommen, sind sie bereits ohne Indizes, ohne alles, was dem Planner helfen könnte. Das bedeutet, dass nur wir ihm helfen und Hinweise geben können. Genau darüber möchte ich genauer berichten.

Ein einfacher Abfrageplan und der dazugehörige

Um zu zeigen, wie PostgreSQL eine Abfrage an eine Tabelle mit 6 Millionen Zeilen auf einem entfernten Server, werfen wir einen Blick auf den einfachen Plan.

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

Die Verwendung der VERBOSE-Anweisung ermöglicht es, die Abfrage zu sehen, die an den entfernten Server gesendet wird, und die Ergebnisse, die wir für die weitere Verarbeitung erhalten (Zeile RemoteSQL).

Lassen Sie uns einen Schritt weiter gehen und einige Filter zu unserer Abfrage hinzufügen: einen nach boolean Feld, einen nach Vorkommen timestamp in einem 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 months' 
AND CURRENT_DATE - INTERVAL '6 months'
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 months'::interval)) AND ("table".created_dt <= ((('now'::cstring)::date)::timestamp with time zone - '6 months'::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 genau der Punkt, auf den man bei der Erstellung von Abfragen achten sollte. Die Filter wurden nicht an den entfernten Server übermittelt, was bedeutet, dass PostgreSQL alle 6 Millionen Zeilen abruft, um sie dann lokal zu filtern (Zeile Filter) und die Aggregation durchzuführen. Der Schlüssel zum Erfolg liegt darin, die Abfrage so zu gestalten, dass die Filter an die entfernte Maschine übermittelt werden und wir nur die benötigten Zeilen erhalten und aggregieren.

Das ist wirklich ein Bullshit.

Bei Booleans ist alles ganz einfach. Im ursprünglichen Abfrage gab es ein Problem mit dem Operator. ist. Wenn wir es durch =ersetzen, erhalten wir folgendes 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)
  Ausgabe: 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)
        Ausgabe: "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)))
        Entfernte Zeilen durch Filter: 3567989
        Remote SQL: SELECT created_dt, meta FROM fdw_schema.table WHERE (is_active)
Planungszeit: 0.834 ms
Ausführungszeit: 19064.534 ms

Wie Sie sehen können, wurde der Filter an den Remote-Server gesendet, und die Ausführungszeit wurde von 27 auf 19 Sekunden reduziert.

Es ist zu beachten, dass der Operator ist sich von dem Operator = darin unterscheidet, dass er mit dem Wert Null umgehen kann. Das bedeutet, dass is not True im Filter die Werte False und Null belässt, während != True nur die Werte False belässt. Daher sollten bei der Ersetzung des Operators is not zwei Bedingungen mit dem Operator OR im Filter übergeben werden, zum Beispiel: WHERE (col != True) OR (col is null).

Nachdem wir uns mit Boolean auseinandergesetzt haben, machen wir weiter. Lassen Sie uns den Filter für den booleschen Wert zunächst auf die ursprüngliche Form zurücksetzen, um die Auswirkungen anderer Änderungen unabhängig zu betrachten.

timestamptz? hz

In der Regel muss man oft experimentieren, um herauszufinden, wie man eine Anfrage korrekt formuliert, die entfernte Server einbezieht, bevor man nach Erklärungen sucht, warum es genau so funktioniert. Im Internet ist nur sehr wenig Informationen zu finden. In unseren Experimenten haben wir festgestellt, dass der Filter nach einem festen Datum problemlos auf den entfernten Server übertragen wird, aber wenn wir versuchen, ein Datum dynamisch festzulegen, wie zum Beispiel now() oder CURRENT_DATE, funktioniert das nicht. In unserem Beispiel haben wir einen Filter hinzugefügt, damit die Spalte created_at Daten enthält, die genau 1 Monat in der Vergangenheit liegen (BETWEEN CURRENT_DATE - INTERVAL '7 month' AND CURRENT_DATE - INTERVAL '6 month'). Welche Maßnahmen haben wir in diesem Fall ergriffen?

erklären analysieren ausführlich
SELECT count(1)
FROM fdw_schema.tabelle 
WHERE is_active is True
AND created_dt >= (SELECT CURRENT_DATE::timestamptz - INTERVAL '7 Monate') 
AND created_dt >'source' = 'test';

Aggregat  (Kosten=306875.17..306875.18 Zeilen=1 Breite=8) (tatsächliche Zeit=4789.114..4789.115 Zeilen=1 Schleifen=1)
  Ausgabe: count(1)
  InitPlan 1 (gibt $0 zurück)
    ->  Ergebnis  (Kosten=0.00..0.02 Zeilen=1 Breite=8) (tatsächliche Zeit=0.007..0.008 Zeilen=1 Schleifen=1)
          Ausgabe: ((('jetzt'::cstring)::datum)::timestamp mit Zeitzone - '7 Monate'::intervall)
  InitPlan 2 (gibt $1 zurück)
    ->  Ergebnis  (Kosten=0.00..0.02 Zeilen=1 Breite=8) (tatsächliche Zeit=0.002..0.002 Zeilen=1 Schleifen=1)
          Ausgabe: ((('jetzt'::cstring)::datum)::timestamp mit Zeitzone - '6 Monate'::intervall)
  ->  Foreign Scan auf fdw_schema."tabelle"  (Kosten=100.02..306874.86 Zeilen=105 Breite=0) (tatsächliche Zeit=23.475..4681.419 Zeilen=1360025 Schleifen=1)
        Ausgabe: "tabelle".id, "tabelle".is_active, "tabelle".meta, "tabelle".created_dt
        Filter: (("tabelle".is_active IS TRUE) AND (("tabelle".meta ->> 'source'::text) = 'test'::text))
        Zeilen entfernt durch Filter: 76934
        Remoter SQL: SELECT is_active, meta FROM fdw_schema.tabelle WHERE ((created_dt >= $1::timestamp mit Zeitzone)) AND ((created_dt < $2::timestamp mit Zeitzone))
Planungszeit: 0.703 ms
Ausführungszeit: 4789.379 ms

Wir haben dem Planer geraten, das Datum im Unterabfrage vorab zu berechnen und die vorbereitete Variable bereits im Filter zu verwenden. Und dieser Hinweis hat großartige Ergebnisse geliefert, die Abfrage ist fast sechsmal schneller geworden!

Es ist wichtig, darauf zu achten: Der Datentyp in der Unterabfrage muss mit dem Feldtyp übereinstimmen, nach dem wir filtern. Andernfalls wird der Planer entscheiden, dass die Typen unterschiedlich sind und zunächst alle Daten abrufen, um sie dann lokal zu filtern.

Setzen wir den Datumsfilter auf den ursprünglichen Wert zurück.

Freddy vs. Jsonb

Die booleschen Felder und Datumsangaben haben unseren Abfrageprozess bereits erheblich beschleunigt, jedoch blieb ein weiterer Datentyp zu optimieren. Die Herausforderung bei der Filterung bleibt, ehrlich gesagt, bestehen, obwohl wir auch hier Fortschritte gemacht haben. So haben wir den Filter übermittelt für jsonb das Feld auf einen Remote-Server.

erklären analysieren ausführlich
SELECT count(1)
FROM fdw_schema.table 
WHERE is_active is True
AND created_dt BETWEEN CURRENT_DATE - INTERVAL '7 Monate' 
AND CURRENT_DATE - INTERVAL '6 Monate'
AND meta @> '{"source":"test"}'::jsonb;

Aggregat  (Kosten=245463.60..245463.61 Zeilen=1 Breite=8) (tatsächliche Zeit=6727.589..6727.590 Zeilen=1 Schleifen=1)
  Ausgabe: count(1)
  ->  Foreign Scan auf fdw_schema."table"  (Kosten=1100.00..245459.90 Zeilen=1478 Breite=0) (tatsächliche Zeit=16.213..6634.794 Zeilen=1360025 Schleifen=1)
        Ausgabe: "table".id, "table".is_active, "table".meta, "table".created_dt
        Filter: (("table".is_active IS TRUE) AND ("table".created_dt >= (('jetzt'::cstring)::date - '7 Monate'::interval)) AND ("table".created_dt <= ((('jetzt'::cstring)::date)::timestamp mit Zeitzone - '6 Monate'::interval)))
        Durch Filter entfernte Zeilen: 619961
        Entfernte SQL: SELECT created_dt, is_active FROM fdw_schema.table WHERE ((meta @> '{"source": "test"}'::jsonb))
Planungszeit: 0.747 ms
Ausführungszeit: 6727.815 ms

Anstelle von Filteroperatoren sollte der Existenzoperator eines jsonb anderen verwendet werden. 7 Sekunden statt ursprünglicher 29. Bisher ist dies die einzige erfolgreiche Methode zur Übertragung der Filter. jsonb zu einem entfernten Server, aber hier gibt es eine wichtige Einschränkung: Wir verwenden die Version 9.6 der Datenbank, planen jedoch, bis Ende April die letzten Tests abzuschließen und auf Version 12 umzusteigen. Sobald wir aktualisieren, werden wir berichten, wie sich das ausgewirkt hat, denn es gibt viele Änderungen, auf die wir hoffen: json_path, neues Verhalten von CTE, push down (bereits seit Version 10 vorhanden). Wir sind sehr gespannt darauf, das so schnell wie möglich auszuprobieren.

Finish him

Wir haben überprüft, wie sich jede Änderung einzeln auf die Abfragegeschwindigkeit auswirkt. Lassen Sie uns nun sehen, was passiert, wenn alle drei Filter korrekt geschrieben sind.

erklären analysieren ausführlich
SELECT count(1)
FROM fdw_schema.tabelle 
WHERE is_active = True
AND created_dt >= (SELECT CURRENT_DATE::timestamptz - INTERVAL '7 Monate') 
AND created_dt  '{"source":"test"}'::jsonb;

Aggregat (Kosten=322041.51..322041.52 Zeilen=1 Breite=8) (tatsächliche Zeit=2278.867..2278.867 Zeilen=1 Schleifen=1)
  Ausgabe: count(1)
  InitPlan 1 (gibt $0 zurück)
    -> Ergebnis (Kosten=0.00..0.02 Zeilen=1 Breite=8) (tatsächliche Zeit=0.010..0.010 Zeilen=1 Schleifen=1)
          Ausgabe: ((('jetzt'::cstring)::date)::timestamp mit Zeitzone - '7 Monate'::intervall)
  InitPlan 2 (gibt $1 zurück)
    -> Ergebnis (Kosten=0.00..0.02 Zeilen=1 Breite=8) (tatsächliche Zeit=0.003..0.003 Zeilen=1 Schleifen=1)
          Ausgabe: ((('jetzt'::cstring)::date)::timestamp mit Zeitzone - '6 Monate'::intervall)
  -> Foreign Scan auf fdw_schema."tabelle" (Kosten=100.02..322041.41 Zeilen=25 Breite=0) (tatsächliche Zeit=8.597..2153.809 Zeilen=1360025 Schleifen=1)
        Ausgabe: "tabelle".id, "tabelle".is_active, "tabelle".meta, "tabelle".created_dt
        Remote SQL: SELECT NULL FROM fdw_schema.tabelle WHERE (is_active) AND ((created_dt >= $1::timestamp mit Zeitzone)) AND ((created_dt  '{"source": "test"}'::jsonb))
Planungszeit: 0.820 ms
Ausführungszeit: 2279.087 ms

Ja, die Abfrage sieht komplizierter aus, das ist eine notwendige Belastung, aber die Ausführungszeit beträgt 2 Sekunden, was mehr als 10-mal schneller ist! Und dabei sprechen wir von einer einfachen Abfrage an einem relativ kleinen Datensatz. Bei realen Abfragen haben wir Zuwächse von mehreren hunderttausend Mal erhalten.

Zusammenfassend: Wenn Sie PostgreSQL mit FDW verwenden, überprüfen Sie immer, ob alle Filter an den entfernten Server gesendet werden, und Sie werden glücklich sein... zumindest bis Sie zu Joins zwischen Tabellen aus verschiedenen erreichen. Server. Aber das ist schon eine Geschichte für einen anderen Artikel.

Danke für Ihre Aufmerksamkeit! Ich freue mich über Fragen, Kommentare und Geschichten über Ihre Erfahrungen in den Kommentaren.

Quelle: habr.com