Von materialisierten Ansichten zu kontinuierlichen Aggregaten: Verbesserung von PostgreSQL mit Echtzeitanalysen

Benutzern den Zugriff auf Datenanalysen in Echtzeit zu ermöglichen, ist eine Schlüsselfunktion vieler moderner Anwendungen. Stellen Sie sich vor, Sie nutzen Ihre bevorzugte SaaS- Plattform – wahrscheinlich gibt es ein intuitives Dashboard, das Echtzeitdaten und historische Erkenntnisse präsentiert. Sie können wahrscheinlich mit der Plattform interagieren, benutzerdefinierte Berichte erstellen, detaillierte Kennzahlen untersuchen und Trends über Wochen oder Monate hinweg visualisieren.

Als Benutzer möchten Sie sicherlich nicht, dass diese Plattform langsam ist. Das bedeutet, dass die Datenbank, auf der diese Produkte basieren, Abfragen über große Datenmengen, einschließlich komplexer, analytischer Abfragen, schnell ausführen muss.

Während PostgreSQL ist heute die beliebteste Datenbank unter Entwicklern Es ist nicht dafür bekannt, schnell große Datenmengen abzufragen. Aber keine Sorge: Postgres hat immer ein Tool in seiner Toolbox. Eine der besten sind materialisierte Ansichten.

Was sind materialisierte PostgreSQL-Ansichten?

Basierend auf der Materialisierungstechnik berechnen materialisierte PostgreSQL-Ansichten häufig ausgeführte Abfragen vorab und speichern die Ergebnisse als Tabelle. Im Gegensatz zu Standard-PostgreSQL-Ansichten, die die zugrunde liegende Abfrage jedes Mal ausführen, wenn auf die Ansicht verwiesen wird, behalten materialisierte Ansichten das Ergebnis der Quellabfrage in der Datenbank bei. Das Tolle daran ist, dass Ihre Datenbank die Abfrage nicht jedes Mal ausführen muss, wenn Sie sie ausführen: Die Ergebnisse sind bereits auf der Festplatte verfügbar – Sie erhalten die Antwort auf Ihre Abfrage viel schneller.

Dies ist eine großartige Möglichkeit, Abfrageantworten für Abfragen zu optimieren, deren Berechnung ressourcenintensiv ist. Zum Beispiel Abfragen, die die Verarbeitung großer Datenmengen, Aggregationen oder mehrere Verknüpfungen umfassen können.

Das Arbeiten mit materialisierten Ansichten ist super einfach. Um eine Ansicht zu erstellen, verwenden Sie die Anweisung CREATE MATERIALIZED VIEW und die Abfrage Ihrer Wahl.

Sobald Ihre materialisierte Ansicht erstellt wurde, können Sie sie als reguläre PostgreSQL- Tabelle abfragen:

 CREATE MATERIALIZED VIEW customer_orders AS SELECT customer_id, COUNT(*) as total_orders FROM orders GROUP BY customer_id;

 -- Query the materialized view SELECT * FROM customer_orders;

Diese materialisierte Ansicht wird schnell veraltet sein, bis Sie sie aktualisieren: Selbst wenn Sie neue Daten zur Basistabelle hinzufügen (oder Daten aktualisieren oder löschen), enthält die materialisierte Ansicht diese Änderungen nicht automatisch; Es handelt sich um eine Momentaufnahme zum Zeitpunkt der Erstellung. Um die materialisierte Ansicht zu aktualisieren, müssen Sie REFRESH MATERIALIZED VIEW ausführen.

 REFRESH MATERIALIZED VIEW customer_orders;

Dieser letzte Punkt (wie Aktualisierungen gehandhabt werden) ist die Achillesferse materialisierter Ansichten, wie wir im nächsten Abschnitt besprechen werden.

Was ist mit Echtzeitanalysen? Einschränkungen materialisierter PostgreSQL-Ansichten

Wie bereits erwähnt, sind materialisierte PostgreSQL-Ansichten ein leistungsstarkes Tool zur Beschleunigung häufig ausgeführter Abfragen, insbesondere wenn diese Abfragen große Datenmengen umfassen. Aber materialisierte Ansichten bringen einen nicht idealen Aspekt mit sich: Um Ihre materialisierten Ansichten auf dem neuesten Stand zu halten, müssen sie aktualisiert werden.

Dieses einzelne Problem führt zu drei wichtigen Einschränkungen:

Aktualisierungen sind ineffizient und rechenintensiv

Beim Aktualisieren einer materialisierten Ansicht wird die Abfrage für den gesamten Datensatz neu berechnet. Wenn Sie eine Aktualisierung durchführen, werden im Grunde die alten materialisierten Daten gelöscht und dann durch neue, rematerialisierte Daten ersetzt. Umsetzung inkrementelle Aktualisierungen (wobei nur die geänderten Daten aktualisiert werden) würde den Aggregationsprozess wesentlich effizienter machen, es ist jedoch schwierig, es in einer konsistenten relationalen Datenbank korrekt zu implementieren. Mit zusätzlichen Skripten sind manchmal Problemumgehungen möglich, aber sie sind alles andere als einfach, insbesondere bei komplexen Abfragen oder wenn verspätete Daten eintreffen.

Aktualisierungen werden nicht automatisch ausgeführt

Wie bereits erwähnt, integrieren materialisierte Ansichten nicht automatisch die neuesten Daten. Sie müssen durch Ausführen von REFRESH MATERIALIZED VIEW aktualisiert werden. Das Ausführen manueller Aktualisierungen ist in einer Produktionsumgebung nicht möglich. Eine viel realistischere Einrichtung wäre die Automatisierung der Aktualisierung.

Leider verfügen materialisierte Ansichten nicht über eine integrierte automatische Aktualisierungsfunktion. Daher ist zum Erstellen eines Zeitplans für die automatische Aktualisierung materialisierter Ansichten in PostgreSQL eine Art Planer erforderlich. Dies kann datenbankintern mit einer Erweiterung oder außerhalb der Datenbank mit einem Scheduler wie cron erfolgen. Dies gelingt jedoch, da Aktualisierungen teuer sind und viel Zeit in Anspruch nehmen. Es kann sehr leicht passieren, dass Sie die Ansicht nicht schnell genug aktualisieren können.

Materialisierte Ansichten zeigen keine aktuellen Ergebnisse an

Die statische Natur materialisierter Ansichten hat zur Folge, dass ihnen bei der Abfrage die seit der letzten Aktualisierung hinzugefügten oder geänderten Daten fehlen (selbst wenn diese Aktualisierung nach einem Zeitplan erfolgt). Wenn Ihr Planungsfenster auf eine Stunde eingestellt ist, ist Ihr Gesamtwert bis zu einer Stunde plus der tatsächlichen Zeit für die Durchführung der Aktualisierung veraltet. Heutzutage erfordern viele Anwendungen jedoch die kontinuierliche Erfassung von Daten, und häufig müssen diese Anwendungen ihren Benutzern aktuelle Ergebnisse bieten, um sicherzustellen, dass sie bei der Abfrage der Ansicht korrekte Informationen abrufen.

Es ist bedauerlich, dass materialisierte Ansichten durch diese Einschränkungen eingeschränkt werden. Sollten materialisierte Ansichten vollständig verworfen werden, wenn Sie eine SaaS-Plattform aus einem Live-Datensatz erstellen und häufig neue Daten eingehen?

Die Antwort ist nein. Mit Timescale haben wir eine Lösung entwickelt, die materialisierte Ansichten effektiv verbessert, um sie für moderne Anwendungen besser geeignet zu machen: kontinuierliche Aggregate.

Lernen Sie kontinuierliche Aggregate kennen: Materialisierte Ansichten mit automatischen Aktualisierungen für Echtzeitanalysen

Stellen Sie sich eine Welt vor, in der materialisierte Ansichten nicht nur statische Schnappschüsse sind, sondern dynamisch und effizient aktualisiert werden. Sie können auf die gewünschte Verbesserung der Abfrageleistung zugreifen, ohne sich um irgendetwas anderes kümmern zu müssen. Nun, es scheint, als hätten wir die kontinuierlichen Aggregate von Timescale beschrieben.

Kontinuierliche Aggregate (verfügbar für alle PostgreSQL-Datenbanken über die TimescaleDB-Erweiterung und in AWS über die Timescale-Plattform) sind materialisierte Ansichten, die durch effiziente, automatisierte Aktualisierungsfunktionen und ein Echtzeitelement erweitert werden. Sie sehen fast genau wie materialisierte Ansichten aus und fühlen sich auch so an, ermöglichen aber Folgendes:

• Automatische Aktualisierungen über eine Aktualisierungsrichtlinie
• Ein effizienterer Aktualisierungsprozess: Wenn eine Aktualisierung ausgeführt wird, werden nur die Daten berührt, die sich seit der letzten Aktualisierung geändert haben
• Aktuelle Ergebnisse, die die Anwendungsfälle erweitern, in denen materialisierte Ansichten genutzt werden können (z. B. Echtzeitanalysen, Live-Dashboards, Berichte und andere)

Aktualisierungen automatisch und ressourceneffizient durchführen

Das Erstellen eines kontinuierlichen Aggregats ähnelt stark dem Erstellen einer materialisierten Ansicht (und kann auch als reguläre PostgreSQL-Tabelle abgefragt werden):

 CREATE MATERIALIZED VIEW hourly_sales WITH (timescaledb.continuous) AS SELECT time_bucket(INTERVAL '1 hour', sale_time) as hour, product_id, SUM(units_sold) as total_units_sold FROM sales_data GROUP BY hour, product_id;

Aber anders als bei materialisierten Ansichten ist das Erstellen einer Aktualisierungsrichtlinie unkompliziert. Sie können das Aktualisierungsintervall innerhalb der Datenbank einfach definieren und so sicherstellen, dass Ihr kontinuierliches Aggregat automatisch und regelmäßig aktualisiert wird.

Im folgenden Beispiel wird eine Aktualisierungsrichtlinie eingerichtet, um das kontinuierliche Aggregat alle 30 Minuten zu aktualisieren. Der Parameter end_offset definiert den Zeitbereich der zu aktualisierenden Daten und „ schedule_interval legt fest, wie oft das kontinuierliche Aggregat aktualisiert wird:

 -- Setting up a refresh policy SELECT add_continuous_aggregate_policy('hourly_sales', end_offset => INTERVAL '1 minute', schedule_interval => INTERVAL '30 minutes');

Wenn diese Aktualisierungsrichtlinie in Kraft tritt, wird der Prozess viel effizienter sein, als wenn wir eine einfache materialisierte Ansicht verwenden würden. Im Gegensatz zur Ausführung von REFRESH MATERIALIZED VIEW löscht Timescale beim Aktualisieren eines kontinuierlichen Aggregats nicht alle alten Daten und berechnet das Aggregat anhand dieser neu: Die Engine führt die Abfrage lediglich anhand des letzten Aktualisierungszeitraums (z. B. 30 Minuten) aus und fügt sie hinzu zur Materialisierung.

In ähnlicher Weise werden UPDATE und DELETE -Vorgänge, die während dieser letzten Periode ausgeführt wurden, identifiziert und der Block (Partition), an dem sie beteiligt sind, neu berechnet. (Kontinuierliche Aggregate, die auf Zeitskalen basieren Hypertabellen , bei denen es sich um automatisch partitionierte PostgreSQL-Tabellen handelt. Dies ist ein enormer Vorteil, da die Engine nur bestimmte Partitionen und nicht die gesamte Tabelle neu berechnen kann, wenn sich die Daten geändert haben.)

Anzeige aktueller Ergebnisse für Echtzeitanalysen

Aber wie lösen kontinuierliche Aggregate das Problem der Anzeige aktueller Ergebnisse? Was passiert, wenn nach der letzten Aktualisierung neue Daten hinzugefügt wurden und ich das kontinuierliche Aggregat abfrage?

Um diese Funktionalität zu ermöglichen, haben wir hinzugefügt Echtzeit-Aggregationsfunktionalität zu kontinuierlichen Aggregaten. Wenn die Echtzeitaggregation aktiviert ist und Sie Ihr kontinuierliches Aggregat abfragen, wird das angezeigte Ergebnis zwei Teile kombinieren:

• Die materialisierten Daten in der zugrunde liegenden materialisierten Ansicht, die bei der letzten Aktualisierung aktualisiert wurden.
• Die neuesten, noch nicht materialisierten Rohdaten, die sich immer noch ausschließlich in Ihrer Basistabelle (oder genauer gesagt Hypertabelle) befinden.

Diese Funktionalität wandelt materialisierte Ansichten von statischen Schnappschüssen in dynamische Einheiten um und stellt so sicher, dass die gespeicherten Daten nicht nur eine historische Widerspiegelung, sondern eine aktuelle Darstellung der zugrunde liegenden Datensätze sind.

Verwendung kontinuierlicher Aggregate: Beispiel

Auch wenn sich das alles gut anhört, wird es (hoffentlich) mit einem Beispiel viel besser zusammenpassen.

Stellen Sie sich eine Plattform vor, die von Transportunternehmen und Mitfahrunternehmen genutzt wird. Diese Plattform enthält ein Dashboard, in dem Unternehmen einen Überblick über den Status ihrer Flotte sehen können, einschließlich einer Tabelle mit dem aktuellen Status wichtiger Kennzahlen und zwei Visualisierungen, die zeigen, wie sich die Kennzahlen an diesem bestimmten Tag und im Kontext der Woche entwickeln.

Um diese Anwendung zu betreiben, hätten wir zunächst eine Hypertabelle, in die ständig die Daten zu den Fahrten eingefügt werden. Die Hypertabelle könnte etwa so aussehen:

 CREATE TABLE rides ( ride_id SERIAL PRIMARY KEY, vehicle_id INT, start_time TIMESTAMPTZ NOT NULL, end_time TIMESTAMPTZ NOT NULL, distance FLOAT NOT NULL, price_paid FLOAT NOT NULL ); SELECT create_hypertable('rides', 'start_time');

Hypertabellen sind sehr schnell und sehr skalierbar – diese Tabelle bleibt auch dann leistungsfähig, wenn sie Milliarden von Zeilen enthält.

Um die Tabelle mit einer Live-Übersicht zu versorgen, würden wir eine kontinuierliche Aggregation verwenden, um die Daten in 30-Minuten-Takten einzuteilen. Dadurch würde der Prozess schnell und reaktionsschnell bleiben:

 -- Create continuous aggregate for live overview CREATE MATERIALIZED VIEW live_dashboard WITH (timescaledb.continuous, timescaledb.materialized_only=false)) AS SELECT vehicle_id, time_bucket(INTERVAL '30 minute', start_time) as minute, COUNT(ride_id) as number_of_rides, AVG(price_paid) as average_price FROM rides GROUP BY vehicle_id, minute;

 -- Set up a refresh policy SELECT add_continuous_aggregate_policy('live_dashboard', end_offset => INTERVAL '10 minutes', schedule_interval => INTERVAL '15 minute');

Im vorherigen Code stellt der end_offset Parameter sicher, dass das Aggregat nicht sofort versucht, die allerneuesten Daten zu aktualisieren, wodurch etwas Pufferzeit zur Verfügung steht, um eventuelle Verzögerungen bei der Datenankunft auszugleichen. Wenn Sie end_offset auf 10 minutes festlegen, aktualisiert das Aggregat Daten, die mindestens 10 Minuten alt sind, und stellt so sicher, dass keine Aktualisierungen aufgrund geringfügiger Verzögerungen beim Datenzufluss verpasst werden. In einem realen Anwendungsfall würden Sie diesen Wert basierend auf der durchschnittlichen Verzögerung anpassen, die Sie in Ihrer Datenpipeline beobachten.

Um die Visualisierung mit der Tagesansicht zu unterstützen, würden wir ein zweites kontinuierliches Aggregat erstellen. In diesem Diagramm werden die Daten stundenweise angezeigt, sodass wir nicht wie im vorherigen eine minutengenaue Granularität benötigen:

 -- Create continuous aggregate for daily overview CREATE MATERIALIZED VIEW hourly_metrics WITH (timescaledb.continuous, timescaledb.materialized_only=false) AS SELECT vehicle_id, time_bucket(INTERVAL '1 hour', start_time) as hour, COUNT(ride_id) as number_of_rides, SUM(price_paid) as total_revenue FROM rides WHERE start_time > NOW() - INTERVAL '1 day' GROUP BY vehicle_id, hour;

 -- Define refresh policy SELECT add_continuous_aggregate_policy('hourly_metrics', end_offset => INTERVAL '10 minutes', schedule_interval => INTERVAL `1 hour`);

Um schließlich das Diagramm mit der Wochenansicht zu unterstützen, würden wir ein weiteres kontinuierliches Aggregat erstellen und dieses Mal die Daten nach Tag aggregieren:

 -- Create continuous aggregate to power chart with weekly overview CREATE MATERIALIZED VIEW daily_metrics WITH (timescaledb.continuous, timescaledb.materialized_only=false) AS SELECT vehicle_id, time_bucket(INTERVAL '1 day', start_time) as day, COUNT(ride_id) as number_of_rides, SUM(price_paid) as total_revenue FROM rides WHERE start_time > NOW() - INTERVAL '1 week' GROUP BY vehicle_id, day;

 -- Define refresh policy SELECT add_continuous_aggregate_policy('daily_metrics', end_offset => INTERVAL '10 minutes', schedule_interval => INTERVAL '1 day);

PS Um die Definition kontinuierlicher Aggregate noch effizienter zu gestalten, Timescale führte hierarchische kontinuierliche Aggregate ein in TimescaleDB 2.9. Sobald Sie sich mit kontinuierlichen Aggregaten vertraut gemacht haben, können Sie damit beginnen, sie zusätzlich zu anderen kontinuierlichen Aggregaten zu erstellen. Im vorherigen Beispiel könnten Sie beispielsweise auch die stündlichen Aggregate zusätzlich zum minutengenauen Aggregat definieren.

Abschluss

Auch wenn PostgreSQL ursprünglich nicht für Anwendungen entwickelt wurde, die große Live-Datenmengen verarbeiten müssen, wissen Sie was – diese Art von Arbeitslasten gibt es mittlerweile überall. PostgreSQL verfügt jedoch über Funktionen, die bei dieser Aufgabe helfen. Materialisierte Ansichten gehören zu den leistungsstärksten Ansichten, da sie die Vorabberechnung von Abfrageergebnissen und deren Speicherung auf der Festplatte für einen schnellen Abruf ermöglichen.

Materialisierte Ansichten weisen jedoch drei wichtige Einschränkungen auf. Erstens ist das Auslösen von Aktualisierungen sehr rechenineffizient. Zweitens ist selbst die Einrichtung dieser automatischen Aktualisierungen kein reibungsloser Prozess. Drittens zeigen materialisierte Ansichten keine aktuellen Ergebnisse an, da sie die Daten ausschließen, die seit der letzten Aktualisierung hinzugefügt oder geändert wurden.

Diese Einschränkungen machen materialisierte Ansichten für viele moderne Anwendungen zu einer unpraktischen Lösung. Um dieses Problem zu lösen, haben wir kontinuierliche Aggregate gebaut. Dabei handelt es sich um materialisierte PostgreSQL-Ansichten, in denen Sie ganz einfach eine Aktualisierungsrichtlinie definieren können, sodass die Aktualisierungen automatisch erfolgen. Diese Aktualisierungen erfolgen ebenfalls inkrementell und sind daher wesentlich effizienter. Schließlich ermöglichen Ihnen kontinuierliche Aggregate, die materialisierten Daten mit den seit der letzten Aktualisierung hinzugefügten und geänderten Rohdaten zu kombinieren, um sicherzustellen, dass Sie nur aktuelle Ergebnisse erhalten.

Wenn Sie PostgreSQL auf Ihrer Hardware ausführen, können Sie auf kontinuierliche Aggregate zugreifen Installieren der TimescaleDB-Erweiterung . Wenn Sie AWS verwenden, schauen Sie sich die Timescale-Plattform an . Die ersten 30 Tage sind kostenlos.

Geschrieben von Carlota Soto und Mat Arye.