Installieren und Verwenden von TimescaleDB unter Ubuntu 18.04

Der Autor hat das Computer History Museum für eine Spende im Rahmen des Programms Write for DOnations ausgewählt.

Einführung

Viele Anwendungen, wie z. B. Überwachungs- und Datenerfassungssysteme, sammeln Daten zur weiteren Analyse. Mit diesen Analysen wird häufig untersucht, wie sich ein Datenelement oder ein System über die Zeit ändert. In diesen Instanzen werden die Daten als Zeitreihen dargestellt, wobei jeder Datenpunkt mit einem Zeitstempel versehen ist. Ein Beispiel würde wie folgt aussehen:

2019-11-01 09:00:00    server.cpu.1    0.9
2019-11-01 09:00:00    server.cpu.15   0.8
2019-11-01 09:01:00    server.cpu.1    0.9
2019-11-01 09:01:00    server.cpu.15   0.8
...

Die Verwaltung von Zeitreihendaten ist mit dem Aufstieg des Internets der Dinge (IoT) und des industriellen Internets der Dinge zu einer grundlegenden Fähigkeit geworden. Es gibt immer mehr Geräte, die verschiedene Zeitreihendaten sammeln: Fitnesstracker, Smartwatches, Heim-Wetterstationen und verschiedene Sensoren, um nur einige zu nennen. Diese Geräte sammeln eine Menge Daten, die alle irgendwo gespeichert werden müssen.

Zur Speicherung von Daten werden meist klassische relationale Datenbanken verwendet, die jedoch nicht immer perfekt geeignet sind, wenn es um die riesigen Datenvolumina von Zeitreihen geht. Wenn Sie eine große Menge von Zeitreihendaten verarbeiten müssen, können relationale Datenbanken zu langsam für diese Aufgabe sein. Aus diesem Grund wurden speziell optimierte Datenbanken, so genannte NoSQL-Datenbanken, entwickelt, um die Probleme mit relationalen Datenbanken zu vermeiden.

TimescaleDB ist eine Open-Source-Datenbank, die für die Speicherung von Zeitreihendaten optimiert ist. Sie ist als eine Erweiterung von PostgreSQL implementiert und kombiniert die Benutzerfreundlichkeit rationaler Datenbanken mit der Geschwindigkeit von NoSQL-Datenbanken. So können Sie mit PostgreSQL sowohl Geschäftsdaten als auch Zeitreihendaten an einem Ort speichern.

Durch Befolgung dieses Tutorials richten Sie TimescaleDB unter Ubuntu 18.04 ein und lernen, wie Sie damit arbeiten. Sie werden Zeitreihendatenbanken erstellen und einfache Abfragen vornehmen. Am Ende erfahren Sie, wie Sie unnötige Daten entfernen können.

Voraussetzungen

Um dieser Anleitung zu folgen, benötigen Sie:

• Einen Ubuntu 18.04-Server, der gemäß unserem Leitfaden zur Ersteinrichtung des Servers für Ubuntu 18.04 eingerichtet wurde, einschließlich eines non-root users, aber mit sudo-Berechtigungen, und einer Firewall.
• Auf Ihrem Server installiertes PostgreSQL. Folgen Sie Schritt 1 von Installieren und Verwenden von PostgreSQL unter Ubuntu 18.04, um die Datenbank zu installieren.

Schritt 1 — Installieren der TimescaleDB

TimescaleDB ist in den Repositorys des Standardpakets von Ubuntu nicht verfügbar. Darum installieren Sie in diesem Schritt die Datenbank aus dem TimescaleDB PPA (Privatpaket-Repository).

Fügen Sie zunächst das APT-Repository von Timescale hinzu:

sudo add-apt-repository ppa:timescale/timescaledb-ppa

Bestätigen Sie diese Aktion durch Betätigung der Eingabetaste.

Als Nächstes aktualisieren Sie Ihren APT-Cache, um Ihre Paketlisten zu aktualisieren:

sudo apt update

Sie können nun mit der Installation fortfahren. In diesem Tutorial kommt PostgreSQL Version 10 zum Einsatz. Falls Sie eine andere Version von PostgreSQL verwenden (z. B. 9.6 oder 11), ersetzen Sie den Wert im folgenden Befehl und führen Sie den Befehl aus:

sudo apt install timescaledb-postgresql-10

TimescaleDB ist nun installiert und bereit für den Einsatz. Als Nächstes aktivieren Sie die Datenbank und passen einige der damit verbundenen Einstellungen in der PostgreSQL-Konfigurationsdatei an, um die Datenbank zu optimieren.

Schritt 2 — Konfigurieren von TimescaleDB

Das TimescaleDB-Modul funktioniert gut mit den Standard-Konfigurationseinstellungen für PostgreSQL, jedoch schlagen die Entwickler von TimescaleDB eine Konfiguration bestimmter Parameter vor, um die Leistung zu erhöhen und die Ressourcen von Prozessor, Arbeitsspeicher und Festplatte besser zu nutzen. Dies kann automatisch mit dem Tool timescaledb-tune oder durch die manuelle Bearbeitung der Datei postgresql.conf Ihres Servers erfolgen.

In diesem Tutorial verwenden Sie das Tool timescaledb-tune, das die Datei postgresql.conf lesen und interaktiv Änderungen vorschlagen wird.

Führen Sie den folgenden Befehl aus, um den Konfigurationsassistenten zu starten:

sudo timescaledb-tune

Zuerst werden Sie aufgefordert, den Pfad zu der PostgreSQL-Konfigurationsdatei zu bestätigen:

Output
Using postgresql.conf at this path:
/etc/postgresql/10/main/postgresql.conf

Is this correct? [(y)es/(n)o]:

Das Dienstprogramm erkennt den Pfad zu der Konfigurationsdatei automatisch, bestätigen Sie also durch Eingabe von y:

Output
...
Is this correct? [(y)es/(n)o]: y
Writing backup to:
/tmp/timescaledb_tune.backup201911181111

Als Nächstes werden Sie dazu aufgefordert, die Variable shared_preload_libraries zu ändern, um beim Starten des PostgreSQL-Servers das TimescaleDB-Modul im Voraus zu laden:

Output
shared_preload_libraries needs to be updated
Current:
#shared_preload_libraries = ''
Recommended:
shared_preload_libraries = 'timescaledb'
Is this okay? [(y)es/(n)o]:

shared_preload_libraries akzeptiert eine durch Trennzeichen getrennte Liste der Module als Wert, die angibt, welche Module PostgreSQL vor dem Start des Datenbankservers laden soll. Durch Vornehmen dieser Änderung wird das Modul timescaledb der Liste hinzugefügt.

Aktivieren Sie das TimescaleDB-Modul, indem Sie in dieser Eingabeaufforderung y eingeben und die Eingabetaste drücken:

Output
...
Is this okay? [(y)es/(n)o]:  y
success: shared_preload_libraries will be updated

Basierend auf den Eigenschaften Ihres Servers und der PostgreSQL-Version wird Ihnen das Skript dann anbieten, Ihre Einstellungen zu optimieren. Drücken Sie y, um den Optimierungsvorgang zu starten:

Output
Tune memory/parallelism/WAL and other settings? [(y)es/(n)o]:  y
Recommendations based on 7.79 GB of available memory and 4 CPUs for PostgreSQL 10

Memory settings recommendations
Current:
shared_buffers = 128MB
#effective_cache_size = 4GB
#maintenance_work_mem = 64MB
#work_mem = 4MB
Recommended:
shared_buffers = 1994MB
effective_cache_size = 5982MB
maintenance_work_mem = 1021001kB
work_mem = 5105kB
Is this okay? [(y)es/(s)kip/(q)uit]:

timescaledb-tune erkennt automatisch den verfügbaren Arbeitsspeicher des Servers und berechnet empfohlene Werte für eine Reihe von Einstellungen. shared_buffers zum Beispiel ermittelt die für das Zwischenspeichern von Daten zugewiesene Menge an Arbeitsspeicher. Standardmäßig ist dieser Wert relativ niedrig, um einer größeren Palette von Plattformen gerecht zu werden; darum schlägt timescaledb-tune vor, diesen Wert von 128 MB auf 1994 MB zu erhöhen, sodass Sie Ressourcen besser nutzen können, indem mehr Platz zum Speichern zwischengespeicherter Informationen (z. B. wiederholte Abfragen) zur Verfügung steht. Die Variable work_mem wurde ebenfalls erhöht, um kompliziertere Sortiervorgänge zuzulassen.

Wenn Sie mehr über die Feinabstimmung der Arbeitsspeichereinstellungen für PostgreSQL erfahren möchten, konsultieren Sie den Artikel Optimierung Ihres PostgreSQL-Servers in der PostgreSQL-Wiki.

Geben Sie y ein, um die Werte zu akzeptieren:

Output
...
Is this okay? [(y)es/(s)kip/(q)uit]:  y
success: memory settings will be updated

Wenn Ihr Server über mehrere CPUs verfügt, finden Sie an dieser Stelle Empfehlungen für die Parallelitätseinstellungen. Diese Einstellungen bestimmen darüber, wie mehrere CPUs simultane Abfragen parallel ausführen können, um Datenbanken zu durchsuchen und die angeforderten Daten schneller zurückzugeben.

Diejenigen mit mehreren CPUs werden auf Empfehlungen wie diese treffen:

Output
Parallelism settings recommendations
Current:
missing: timescaledb.max_background_workers
#max_worker_processes = 8
#max_parallel_workers_per_gather = 2
#max_parallel_workers = 8
Recommended:
timescaledb.max_background_workers = 8
max_worker_processes = 13
max_parallel_workers_per_gather = 1
max_parallel_workers = 2
Is this okay? [(y)es/(s)kip/(q)uit]:

Diese Einstellungen steuern die Anzahl der Worker, die Anfragen und Hintergrundaufgaben verarbeiten. Mehr über diese Einstellungen können Sie der TimescaleDB- und der PostgreSQL-Dokumentation entnehmen.

Geben Sie y ein und betätigen Sie dann die Eingabetaste, um diese Einstellungen zu akzeptieren:

Output
...
Is this okay? [(y)es/(s)kip/(q)uit]: y
success: parallelism settings will be updated

Als Nächstes finden Sie Empfehlungen für Write Ahead Log (WAL):

Output
WAL settings recommendations
Current:
#wal_buffers = -1
#min_wal_size = 80MB
#max_wal_size = 1GB
Recommended:
wal_buffers = 16MB
min_wal_size = 4GB
max_wal_size = 8GB
Is this okay? [(y)es/(s)kip/(q)uit]:

WAL ist eine Protokollierungsmethode, mit der PostgreSQL Änderungen an Datendateien protokolliert, bevor die Änderungen an der Datenbank vorgenommen werden. Durch Priorisierung eines aktuellen Datensatzes mit Datenänderungen sorgt WAL dafür, dass Sie Ihre Datenbank im Falle eines Absturzes rekonstruieren können. So wird die Datenintegrität gewahrt. Die Standardeinstellungen können jedoch ineffiziente Input/Output (I/O)-Operationen verursachen, was die Schreibperformance verringert. Um dieses Problem zu beheben, geben Sie y ein und bestätigen Sie mit der Eingabetaste:

Output
...
Is this okay? [(y)es/(s)kip/(q)uit]: y
success: WAL settings will be updated

Sie finden nun verschiedene Empfehlungen:

Output
Miscellaneous settings recommendations
Current:
#default_statistics_target = 100
#random_page_cost = 4.0
#checkpoint_completion_target = 0.5
#max_locks_per_transaction = 64
#autovacuum_max_workers = 3
#autovacuum_naptime = 1min
#effective_io_concurrency = 1
Recommended:
default_statistics_target = 500
random_page_cost = 1.1
checkpoint_completion_target = 0.9
max_locks_per_transaction = 64
autovacuum_max_workers = 10
autovacuum_naptime = 10
effective_io_concurrency = 200
Is this okay? [(y)es/(s)kip/(q)uit]:

Alle diese verschiedenen Parameter zielen auf Leistungssteigerung ab. Beispielsweise können SSDs viele gleichzeitige Anfragen verarbeiten, sodass der beste Wert für die effective_io_concurrency im Hunderterbereich liegen könnte. Weitere Informationen über diese Optionen finden Sie in der PostgreSQL-Dokumentation.

Drücken Sie y und dann die Eingabetaste, um fortzufahren.

Output
...
Is this okay? [(y)es/(s)kip/(q)uit]: y
success: miscellaneous settings will be updated
Saving changes to: /etc/postgresql/10/main/postgresql.conf

Als Ergebnis erhalten Sie eine gebrauchsfertige Konfigurationsdatei unter /etc/postgresql/10/main/postgresql.conf.

sudo timescaledb-tune --quiet --yes

Damit die Konfigurationsänderungen wirksam werden, müssen Sie den Dienst PostgreSQL neu starten:

sudo systemctl restart postgresql.service

Nun wird die Datenbank mit optimalen Parametern ausgeführt und ist bereit dazu, mit den Zeitreihendaten zu arbeiten. In den nächsten Schritten werden Sie das Arbeiten mit diesen Daten testen; Sie erstellen neue Datenbanken und Hypertabellen und führen Operationen aus.

Schritt 3 — Erstellen einer neuen Datenbank und Hypertabelle

Mit Ihrer optimierten TimescaleDB-Einrichtung sind Sie bereit, mit Zeitreihendaten zu arbeiten. TimescaleDB ist als eine Erweiterung von PostgreSQL implementiert, sodass sich Operationen mit Zeitreihendaten nicht wesentlich von relationalen Datenoperationen unterscheiden. Gleichzeitig erlaubt die Datenbank, Daten aus Zeitreihen und relationalen Tabellen in Zukunft frei zu kombinieren.

Zum Demonstrieren dieser Möglichkeit verwenden Sie PostgreSQL-Befehle, um eine Datenbank zu erstellen und dann die TimescaleDB-Erweiterung zu aktivieren. Dadurch erstellen Sie eine Hypertabelle, die eine Abstraktion vieler individueller Tabellen auf höherer Ebene ist. Hypertabellen sind die Hauptstrukturen, mit denen Sie in TimescaleDB arbeiten.

Melden Sie sich bei Ihrer PostgreSQL-Datenbank an:

sudo -u postgres psql

Erstellen Sie nun eine neue Datenbank und stellen Sie eine Verbindung zu ihr her. In diesem Tutorial wird die Datenbank timeseries heißen:

CREATE DATABASE timeseries;
\c timeseries

Weitere Informationen über die Arbeit mit der PostgreSQL-Datenbank finden Sie in unserem Tutorial Erstellen, Entfernen und Verwalten von Tabellen in PostgreSQL auf einem Cloud-Server.

Aktivieren Sie zum Schluss die TimescaleDB-Erweiterung:

CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;

Sie sehen den folgenden Output:

Output
WARNING:
WELCOME TO
 _____ _                               _     ____________
|_   _(_)                             | |    |  _  \ ___ \
  | |  _ _ __ ___   ___  ___  ___ __ _| | ___| | | | |_/ /
  | | | |  _ ` _ \ / _ \/ __|/ __/ _` | |/ _ \ | | | ___ \
  | | | | | | | | |  __/\__ \ (_| (_| | |  __/ |/ /| |_/ /
  |_| |_|_| |_| |_|\___||___/\___\__,_|_|\___|___/ \____/
               Running version 1.5.1
For more information on TimescaleDB, please visit the following links:

 1. Getting started: https://docs.timescale.com/getting-started
 2. API reference documentation: https://docs.timescale.com/api
 3. How TimescaleDB is designed: https://docs.timescale.com/introduction/architecture

Note: TimescaleDB collects anonymous reports to better understand and assist our users.
For more information and how to disable, please see our docs https://docs.timescaledb.com/using-timescaledb/telemetry.

CREATE EXTENSION

Wie bereits erwähnt, sind die primären Interaktionspunkte mit Ihren Zeitreihendaten Hypertabellen, die aus vielen einzelnen Tabellen namens Chunks bestehen.

Um eine Hypertabelle zu erstellen, beginnen Sie mit einer regulären SQL-Tabelle und konvertieren diese dann mit der Funktion create_hypertable in eine Hypertabelle.

Erstellen Sie eine Tabelle, die Daten zur Verfolgung von Temperatur und Feuchtigkeit für verschiedene Geräte im Zeitablauf speichert:

CREATE TABLE conditions (
  time        TIMESTAMP WITH TIME ZONE NOT NULL,
  device_id   TEXT,
  temperature  NUMERIC,
  humidity     NUMERIC
);

Dieser Befehl erstellt eine Tabelle namens conditions mit vier Spalten. Die erste Spalte speichert den Zeitstempel, der die Zeitzone enthält und nicht leer sein darf. Als Nächstes werden Sie die Zeitspalte verwenden, um Ihre Tabelle in eine nach Zeit partitionierte Hypertabelle umzuwandeln:

SELECT create_hypertable('conditions', 'time');

Dieser Befehl ruft die Funktion create_hypertable() auf, die eine TimescaleDB-Hypertabelle aus einer PostgreSQL-Tabelle erstellt und diese ersetzt.

Sie erhalten die folgende Ausgabe:

Output
    create_hypertable
-------------------------
 (1,public,conditions,t)
(1 row)

In diesem Schritt haben Sie eine neue Hypertabelle zur Speicherung von Zeitreihendaten angelegt. Nun können Sie sie mit Daten füllen, indem Sie in die Hypertabelle schreiben und dann den Löschvorgang ausführen.

Schritt 4 — Schreiben und Löschen von Daten

In diesem Schritt fügen Sie Daten unter Verwendung von SQL-Standardbefehlen ein und importieren große Datensätze aus externen Quellen. Dies veranschaulicht Ihnen die relationalen Datenbankaspekte von TimescaleDB.

Probieren Sie zunächst die grundlegenden Befehle aus. Sie können Daten in die Hypertabelle einfügen, indem Sie den SQL-Standardbefehl INSERT verwenden. Fügen Sie mit dem folgenden Befehl einige Beispieldaten für Temperatur und Feuchtigkeit für das theoretische Gerät weather-pro-000000 ein:

INSERT INTO conditions(time, device_id, temperature, humidity)
  VALUES (NOW(), 'weather-pro-000000', 84.1, 84.1);

Sie sehen die folgende Ausgabe:

Output
INSERT 0 1

Außerdem können Sie mehrere Datenzeilen gleichzeitig einfügen. Versuchen Sie Folgendes:

INSERT INTO conditions
  VALUES
    (NOW(), 'weather-pro-000002', 71.0, 51.0),
    (NOW(), 'weather-pro-000003', 70.5, 50.5),
    (NOW(), 'weather-pro-000004', 70.0, 50.2);

Sie erhalten Folgendes:

Output
INSERT 0 3

Sie können auch festlegen, dass der Befehl INSERT einige oder alle eingefügten Daten mit der Anweisung RETURNING zurückgibt:

INSERT INTO conditions
  VALUES (NOW(), 'weather-pro-000002', 70.1, 50.1) RETURNING *;

Sie sehen den folgenden Output:

Output
             time              |     device_id      | temperature | humidity
-------------------------------+--------------------+-------------+----------
 2019-09-15 14:14:01.576651+00 | weather-pro-000002 |        70.1 |     50.1
(1 row)

Wenn Sie Daten aus der Hypertabelle löschen möchten, verwenden Sie den SQL-Standardbefehl DELETE. Führen Sie Folgendes aus, um alle Daten zu löschen, die eine temperature (Temperatur) von mehr als 80 oder eine humidity (Luftfeuchtigkeit) von mehr als 50 haben:

DELETE FROM conditions WHERE temperature > 80;
DELETE FROM conditions WHERE humidity > 50;

Verwenden Sie nach dem Löschvorgang den Befehl VACUUM, um Platz freizugeben, der noch von gelöschten Daten verwendet wird.

VACUUM conditions;

Weitere Informationen über den Befehl VACUUM finden Sie in der PostgreSQL-Dokumentation.

Diese Befehle eignen sich gut für die Dateneingabe in kleinem Maßstab. Da Zeitreihendaten aber oft riesige Datensätze von mehreren Geräten gleichzeitig generieren, ist es wichtig zu wissen, wie sich Hunderte oder Tausende von Zeilen auf einmal einfügen lassen. Wenn Sie Daten aus externen Quellen in strukturierter Form, z. B. im Format csv, erstellt haben, kann diese Aufgabe schnell erledigt werden.

Um dies auszuprobieren, verwenden Sie einen Beispieldatensatz, der Temperatur- und Luftfeuchtigkeitsdaten von verschiedenen Orten darstellt. Dies ist ein offizieller TimescaleDB-Datensatz, der zum Testen der Datenbank dient. Weitere Informationen über Beispieldatensätze finden Sie in der TimescaleDB-Dokumentation.

Sehen wir uns an, wie Sie Daten aus dem Beispieldatensatz weather_small importieren können. Beenden Sie zunächst PostgreSQL:

\q

Laden Sie dann den Datensatz herunter und extrahieren Sie ihn:

wget https://timescaledata.blob.core.windows.net/datasets/weather_small.tar.gz
tar -xvzf weather_small.tar.gz

Als Nächstes importieren Sie die Temperatur- und Luftfeuchtigkeitsdaten in Ihre Datenbank:

sudo -u postgres psql -d timeseries -c "\COPY conditions FROM weather_small_conditions.csv CSV"

Dadurch wird eine Verbindung mit der Datenbank timeseries hergestellt und der Befehl \COPY ausgeführt, der die Daten aus der ausgewählten Datei in die Hypertabelle conditions kopiert. Der Vorgang dauert einige Sekunden.

Nachdem die Daten in Ihre Tabelle eingespeist worden sind, erhalten Sie die folgende Ausgabe:

Output
COPY 1000000

In diesem Schritt haben Sie Daten manuell und in Stapeln in die Hypertabelle eingefügt. Als Nächstes fahren Sie mit der Durchführung von Abfragen fort.

Schritt 5 — Abfragen von Daten

Nachdem Ihre Tabelle nun Daten enthält, können Sie verschiedene Abfragen durchführen, um sie zu analysieren.

Melden Sie sich zunächst bei der Datenbank an:

sudo -u postgres psql -d timeseries

Wie bereits erwähnt, können Sie für die Arbeit mit Hypertabellen SQL-Standardbefehle verwenden. Um beispielsweise die letzten 10 Einträge aus der Hypertabelle conditions anzuzeigen, führen Sie den folgenden Befehl aus:

SELECT * FROM conditions LIMIT 10;

Sie sehen den folgenden Output:

Output
          time          |     device_id      |    temperature     | humidity
------------------------+--------------------+--------------------+----------
 2016-11-15 12:00:00+00 | weather-pro-000000 |               39.9 |     49.9
 2016-11-15 12:00:00+00 | weather-pro-000001 |               32.4 |     49.8
 2016-11-15 12:00:00+00 | weather-pro-000002 | 39.800000000000004 |     50.2
 2016-11-15 12:00:00+00 | weather-pro-000003 | 36.800000000000004 |     49.8
 2016-11-15 12:00:00+00 | weather-pro-000004 |               71.8 |     50.1
 2016-11-15 12:00:00+00 | weather-pro-000005 |               71.8 |     49.9
 2016-11-15 12:00:00+00 | weather-pro-000006 |                 37 |     49.8
 2016-11-15 12:00:00+00 | weather-pro-000007 |                 72 |       50
 2016-11-15 12:00:00+00 | weather-pro-000008 |               31.3 |       50
 2016-11-15 12:00:00+00 | weather-pro-000009 |               84.4 |     87.8
(10 rows)

Mit diesem Befehl können Sie sehen, welche Daten sich in der Datenbank befinden. Da die Datenbank eine Million Einträge enthält, haben Sie mit LIMIT 10 die Ausgabe auf 10 Einträge beschränkt.

Um die letzten Einträge zu sehen, sortieren Sie das Datenarray in absteigender Reihenfolge nach der Zeit:

SELECT * FROM conditions ORDER BY time DESC LIMIT 20;

Dadurch werden die 20 letzten Einträge ausgegeben.

Sie können auch einen Filter hinzufügen. Um beispielsweise Einträge von dem Gerät weather-pro-000000 anzuzeigen, führen Sie Folgendes aus:

SELECT * FROM conditions WHERE device_id = 'weather-pro-000000' ORDER BY time DESC LIMIT 10;

In diesem Fall sehen Sie die 10 letzten Temperatur- und Feuchtigkeitsdatenpunkte, die vom Gerät weather-pro-000000 aufgezeichnet wurden.

Zusätzlich zu den SQL-Standardbefehlen bietet TimescaleDB auch eine Reihe spezieller Funktionen, die für die Analyse von Zeitreihendaten nützlich sind. Um beispielsweise den Mittelwert der Temperaturwerte zu ermitteln, können Sie die folgende Abfrage mit der Funktion percentile_cont verwenden:

SELECT percentile_cont(0.5)
  WITHIN GROUP (ORDER BY temperature)
  FROM conditions
  WHERE device_id = 'weather-pro-000000';

Sie sehen den folgenden Output:

Output
 percentile_cont
-----------------
            40.5
(1 row)

Auf diese Weise sehen Sie den Mittelwert der Temperatur für den gesamten Beobachtungszeitraum des Ortes, an dem sich der Sensor weather-pro-000000 befindet.

Um die letzten Werte der einzelnen Sensoren anzuzeigen, können Sie die Funktion last verwenden:

select device_id, last(temperature, time)
  FROM conditions
  GROUP BY device_id;

In der Ausgabe sehen Sie eine Liste aller Sensoren und der jeweiligen letzten Werte.

Um die ersten Werte zu erhalten, verwenden Sie die Funktion first.

Das folgende Beispiel ist komplexer. Es zeigt die stündlichen Durchschnitts-, Minimal- und Maximaltemperaturen für den ausgewählten Sensor in den letzten 24 Stunden an:

SELECT time_bucket('1 hour', time) "hour",
trunc(avg(temperature), 2) avg_temp,
trunc(min(temperature), 2) min_temp,
trunc(max(temperature), 2) max_temp
FROM conditions
WHERE device_id = 'weather-pro-000000'
GROUP BY "hour" ORDER BY "hour" DESC LIMIT 24;

Hier haben Sie die Funktion time_bucket verwendet, die eine leistungsfähigere Version der PostgreSQL-Funktion date_trunc darstellt. Als Ergebnis sehen Sie, in welchen Tagesabschnitten die Temperatur steigt oder fällt:

Output
          hour          | avg_temp | min_temp | max_temp
------------------------+----------+----------+----------
 2016-11-16 21:00:00+00 |    42.00 |    42.00 |    42.00
 2016-11-16 20:00:00+00 |    41.92 |    41.69 |    42.00
 2016-11-16 19:00:00+00 |    41.07 |    40.59 |    41.59
 2016-11-16 18:00:00+00 |    40.11 |    39.79 |    40.59
 2016-11-16 17:00:00+00 |    39.46 |    38.99 |    39.79
 2016-11-16 16:00:00+00 |    38.54 |    38.19 |    38.99
 2016-11-16 15:00:00+00 |    37.56 |    37.09 |    38.09
 2016-11-16 14:00:00+00 |    36.62 |    36.39 |    37.09
 2016-11-16 13:00:00+00 |    35.59 |    34.79 |    36.29
 2016-11-16 12:00:00+00 |    34.59 |    34.19 |    34.79
 2016-11-16 11:00:00+00 |    33.94 |    33.49 |    34.19
 2016-11-16 10:00:00+00 |    33.27 |    32.79 |    33.39
 2016-11-16 09:00:00+00 |    33.37 |    32.69 |    34.09
 2016-11-16 08:00:00+00 |    34.94 |    34.19 |    35.49
 2016-11-16 07:00:00+00 |    36.12 |    35.49 |    36.69
 2016-11-16 06:00:00+00 |    37.02 |    36.69 |    37.49
 2016-11-16 05:00:00+00 |    38.05 |    37.49 |    38.39
 2016-11-16 04:00:00+00 |    38.71 |    38.39 |    39.19
 2016-11-16 03:00:00+00 |    39.72 |    39.19 |    40.19
 2016-11-16 02:00:00+00 |    40.67 |    40.29 |    40.99
 2016-11-16 01:00:00+00 |    41.63 |    40.99 |    42.00
 2016-11-16 00:00:00+00 |    42.00 |    42.00 |    42.00
 2016-11-15 23:00:00+00 |    42.00 |    42.00 |    42.00
 2016-11-15 22:00:00+00 |    42.00 |    42.00 |    42.00
(24 rows)

Weitere nützliche Funktionen finden Sie in der TimescaleDB-Dokumentation.

Nun wissen Sie, wie Sie mit Ihren Daten umgehen können. Als Nächstes werden Sie unnötige Daten löschen und Daten komprimieren.

Schritt 6 — Konfigurieren der Datenkomprimierung und -löschung

Wenn sich Daten ansammeln, nehmen sie immer mehr Platz auf Ihrer Festplatte ein. Um Platz zu sparen, bietet die neueste Version von TimescaleDB eine Komprimierungsfunktion für Daten. Diese Funktion erfordert keine Anpassung der Dateisystemeinstellungen und ermöglicht eine rasche Optimierung Ihrer Datenbank. Weitere Informationen über die Funktionsweise dieser Komprimierung finden Sie in diesem Komprimierungsartikel von TimescaleDB.

Aktivieren Sie zunächst die Komprimierung Ihrer Hypertabelle:

ALTER TABLE conditions SET (
  timescaledb.compress,
  timescaledb.compress_segmentby = 'device_id'
);

Sie erhalten die folgenden Ausgabedaten:

Output
NOTICE:  adding index _compressed_hypertable_2_device_id__ts_meta_sequence_num_idx ON _timescaledb_internal._compressed_hypertable_2 USING BTREE(device_id, _ts_meta_sequence_num)
ALTER TABLE

SELECT add_compress_chunks_policy('conditions', INTERVAL '7 days');

Sie können die Statistiken der komprimierten Daten mit folgendem Befehl anzeigen:

SELECT *
FROM timescaledb_information.compressed_chunk_stats;

Sie sehen dann eine Liste von Chunks mit ihrem Status: Komprimierungsstatus und wie viel Speicherplatz unkomprimierte und komprimierte Daten in Bytes belegen.

Wenn es nicht notwendig ist, Daten über einen längeren Zeitraum zu speichern, können Sie veraltete Daten löschen, um noch mehr Speicherplatz freizugeben. Dafür gibt es eine spezielle Funktion namens drop_chunks. Sie ermöglicht das Löschen von Chunks, die Daten enthalten, die älter als die angegebene Zeit sind:

SELECT drop_chunks(interval '24 hours', 'conditions');

Diese Abfrage löscht alle Chunks aus der Hypertabelle conditions, die nur Daten enthalten, die älter als ein Tag sind.

Sie erhalten die folgende Ausgabe:

Output
              drop_chunks
----------------------------------------
 _timescaledb_internal._hyper_1_2_chunk
(1 row)

Um alte Daten automatisch zu löschen, können Sie eine Aufgabe cron konfigurieren. In unserem Tutorial erfahren Sie mehr über die Verwendung von cron zur Automatisierung verschiedener Systemaufgaben.

Verlassen Sie die Datenbank:

\q

Bearbeiten Sie als Nächstes crontab mit dem folgenden Befehl, der von der Shell aus ausgeführt werden sollte:

crontab -e

Fügen Sie nun am Ende der Datei die folgende Zeile hinzu:

...

0 1 * * * /usr/bin/psql -h localhost -p 5432 -U postgres -d postgres -c "SELECT drop_chunks(interval '24 hours', 'conditions');" >/dev/null 2>&1

Dieser Job löscht jeden Tag um 1:00 Uhr obsolete Daten, die älter als ein Tag sind.

Zusammenfassung

Sie haben TimescaleDB auf Ihrem Ubuntu 18.04-Server eingerichtet. Sie haben auch das Erstellen von Hypertabellen, das Einfügen von Daten in diese Tabellen, das Abfragen der Daten, das Komprimieren und das Löschen unnötiger Datensätze getestet. Mit diesen Beispielen können Sie bei der Speicherung von Zeitreihendaten die wichtigsten Vorteile von TimescaleDB gegenüber traditionellen relationalen Datenbankverwaltungssystemen nutzen, einschließlich:

• Höhere Datenerfassungsraten
• Schnellere Abfrageleistung
• Zeitorientierte Funktionen

Nachdem Sie wissen, wie sich Zeitreihendaten speichern lassen, können Sie die Daten nun zum Erstellen von Diagrammen verwenden. TimescaleDB ist mit Visualisierungstools kompatibel, die mit PostgreSQL funktionieren (wie Grafana). Sie können unser Tutorial Installieren und Schützen von Grafana unter Ubuntu 18.04 konsultieren, um mehr über dieses beliebte Visualisierungstool zu erfahren.