Datenbankinhalte effizient auslesen (Seite 3)

Wie oben beschrieben, besteht das Messnetz aus mehreren Messstationen. Daher bietet sich eine Zweiteilung an – eine Tabelle station für die Verwaltung der einzelnen Messstationen und eine Tabelle sensorData für die erfassten Messdaten.

Die Tabelle “Datenbanktabelle station” zeigt die Struktur der gleichnamigen Datenbanktabelle. Die Einteilung erlaubt eine flexible Handhabung des Messnetzes. Jede Station hat eine Kennung in Form einer eigenen ID, über die Sie Daten eindeutig referenzieren.

Das erlaubt es, die Informationen zu den Messstationen zentral zu ändern, falls neue hinzukommen, bestehende entfernt werden oder sich deren Ausstattung oder Position ändert. Jede Messstation liefert die folgenden Daten an das DBMS, die in der Tabelle namens sensorData erfasst werden.

Die in den beiden Tabellen gewählten Datentypen [29] sind auf ein Messnetz mit bis zu 32 767 Messstationen zugeschnitten. Sollte das nicht ausreichen, wählen Sie für ID als Datentyp Serial (4 Bytes Länge) oder Bigserial (8 Bytes Länge). Das ermöglicht dann ein Messnetz aus gut 2 Milliarden beziehungsweise über 9 Trillionen Stationen. Beachten Sie dazu die Empfehlungen zur Anordnung der Spalten in den Tabellen (siehe Kasten “Nacheinander”).

Setzen Sie die ID der Messstation in der Tabelle sensorData an den Anfang der Struktur, vergrößert sich jeder Datensatz aufgrund des Auffüllens der ersten Spalte um 42 Bytes auf insgesamt 148 Bytes. Bei wenigen Messstationen fällt das nicht ins Gewicht, bei 30 000 Stationen schon – diese benötigen dann als Verwaltungsinformationen 4,2 MByte statt 3 MByte, also mehr als ein Drittel zusätzlich.

Bei der gewählten Struktur führt eine Änderung der Reihenfolge der Spalten nicht zu einer Größenänderung des Datensatzes. PostgreSQL benutzt für jede Spalte 8 Bytes. Ergänzen Sie jedoch ein oder mehrere Textfelder, fällt das sofort wesentlich ins Gewicht, insbesondere wenn PostgreSQL Tausende oder gar Millionen Datensätze verwaltet.

Die Verbindung zwischen den beiden Tabellen station und sensorData realisieren Sie über einen Fremdschlüssel namens stationId. Daraus ergeben sich die SQL-Statements zum Erzeugen der beiden Tabellen aus Listing 2.

BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS 'station' (
  name VARCHAR (50),
  latitude REAL,
  longitude REAL,
  level REAL,
  id SMALLSERIAL NOT NULL,
  hasTemperatureSensor BOOLEAN NOT NULL DEFAULT false,
  hasHumiditySensor BOOLEAN NOT NULL DEFAULT false,
  hasParticleSensor BOOLEAN NOT NULL DEFAULT false
);
CREATE TABLE IF NOT EXISTS 'sensorData' (
  timeOfCapture TIMESTAMP NOT NULL,
  temperature REAL,
  humidity REAL,
  particles REAL,
  stationId SMALLSERIAL NOT NULL
);
COMMIT;

Alle SQL-Anweisungen erfolgen als eine abgeschlossene Transaktion. Aus diesem Grund sind sie im Beispiel durch die beiden Anweisungen BEGIN TRANSACTION und COMMIT eingefasst. Erstere leitet die Transaktion ein, Letztere schließt sie ab. Die beiden Create-Anweisungen sind zudem um die Angabe IF NOT EXISTS erweitert. Dadurch legt die Datenbank die Tabellen nur an, wenn sie noch nicht existieren.

Ebenso ist jetzt eine erste Größenabschätzung zum benötigten Speicherplatz möglich. Bei 100 Messstationen benötigen Sie 10 KByte Verwaltungsinformationen in der Datenbank. Liefern die Messstationen alle 15 Minuten Messwerte, fallen etwa 15 KByte Daten pro Stunde an oder etwa 375 KByte pro Tag. In der Kalkulation eines Jahres sind somit Speicher- und Datenübertragungskosten für 135 MByte zu berücksichtigen.

Interne Strukturen

Was ein DBMS außerhalb des profanen Speicherns und Wiederfindens von Daten leistet, hebt es letztendlich aus der Masse hervor. Es gilt als aktiv, sofern es selbst über Views, Trigger, Funktionen, Stored Procedures und Event Handler verfügt. Als passiv gilt es hingegen, wenn solche Funktionen ein zusätzliches Programm erfordern. Zu Ersteren gehört neben PostgreSQL auch Maria DB, zu Letzteren RSQL, das unsere Schwesterzeitschrift LinuxUser in Ausgabe 01/2018 vorgestellt hat [31].

Mit einem View stellen Sie eine spezifische Sicht auf die Daten bereit. Den View benennen Sie zunächst und hinterlegen danach dazu eine SQL-Anweisung, die ausgeführt wird, sobald Sie den View aufrufen. Ein View lässt sich mit einem Alias in der Shell vergleichen. Er akzeptiert keine zusätzlichen Parameter im Aufruf.