Fhem in Verbindung mit Postgres / Timescaledb

Begonnen von Kulli, 01 März 2024, 16:22:17

Vorheriges Thema - Nächstes Thema

Kulli

Hallo

Ich habe eine spezielle Frage hier an die DB Experten:
fhem lässt sich ja seit geraumer Zeit auch mit Postgres betreiben.
Ich habe jetzt die Timescaledb plugins installiert und beabsichtige intern auf hypertabes umzusteigen, oder besser gesagt zu erweitern.
Zur Zeit nutze ich noch MariaDB, aber die DB kommt langsam an ihre Grenzen mit >20GB DB. Die Abfragen werden extrem langsam und das "Value" halt ein String ist, macht Berechnungen innerhalb des Schemas nicht einfacher oder schneller.

Daher habe ich mir folgende Lösung ausgedacht die ich hier von Euch kritisch hinterfragt haben möchte:
In Postgres werden Current und history 1:1 weiter von fhem verwendet um Messwerte abzulegen.
Ich nutze aber einen Trigger um neue Einträge in hypertables zu kopieren
Schema:
 Header: mit den Kopfdaten der Messreihen, also Device, Reading, Type, etc
 hpt_floats: FK auf header, timestamp, floatwert (Alles was Zahl ist wird hier abgelegt)
 hpt_string: FK auf header, timestamp, string (Alles was nicht in hpt_floats passt)

die hpt sind hypertables die auf timestamp und FK partitioniert werden.
Die hypertables ermöglichen mir den Einsatz von Materialized views, als berechnete Werte in 15 Min, Stunde und Tag.

Ein Bereinigungsjob löscht alte Einträge (zB älter 3 Tage) in history alle 24 Stunden, damit die Tabellen klein bleiben aber die Möglichkeit bieten per DB_Rep Tagesberechungen durchzuführen oder in notifies auf die Historie zuzugreifen.

Da ich Grafana in erster Linie für die Auswertungen nutze, sollten Abfragen auf die Hypertables oder sogar die Views viel schneller sein als es heute der Fall ist.

Zur Zeit habe ich rund 130 Millionen Datensätze in Mariadb.
(Und ja, ich räume fast alles immer auf :-) Ich habe eine etwas größere Installation inkl. PV, Solar, SPSen usw...)

Hat jemand das schon einmal aufgesetzt und damit Erfahrungen gesammelt?
Seht ihr größere Probleme mit einem solchen Lösungsansatz?

LG
Kulli

rudolfkoenig

Ich habe etwas Erfahrung mit PostgreSQL+TimescaleDB+Grafana, allerdings nicht in Zusammenhang mit FHEM.

Timescaledb ermoeglicht Partitionierung, ist also interessant, wenn haeufig benutzte Abfragen mehrere Hypertables betreffen, oder wenn man komplette Zeitintervalle in grossen Tabellen entfernen will (drop ist schneller/sauberer als delete).

Es gibt auch Nachteile mit TimescaleDB: manche (aufwendige) Queries werden nicht "intuitiv" optimiert, manche Backup Verfahren funktionieren nicht, die Konfiguration muss auf die Parallelitaet angepasst werden (Stichwort Anzahl der Locks), und upgrade kann kompliziert werden. Die Hardware sollte mit der parallelen Abfragen klarkommen.

Ich wuerde bei der Migration von MySQL zu Postgres auf fdw setzen, und Postgres ohne Timescaledb verwenden :)