Lately I come across an middle ground solution between embedded SQL database and influxdb which is called QuestDB. I wanted to check if there is any interest in making it available as openHAB 3.x persistence service.
QuestDB brings some semantics known from influx - namely time series approach, while still being well performing embedded java solution. There are several native parts which are portable across major operating systems. Some optimizations in questdb make it very efficient to store and retrieve sensor data.
In this regard especially interesting feature is âdesignated timestampâ which turns tables into âappend onlyâ mode. This trick alone allows to use memory mapped files which are flushed to disk when buffer is full.
Another one is handling of âsymbolâ columns which are repetitive strings (think of ON/OFF or multi state items) which are translated into numeric values making disk utilization much smaller than storing all values as text.
I began my own experimentation on my development box to see is it possible to get quest running together with OH. So far I managed to get it up and store some basic data. Hopefully I will also be able to retrieve it.
Since the future of InfluxDB is somewhat unclear (v3 seems to be cloud-only, v2 is dead and v1 unmaintained), I have started to consider QuestDB. Looks promisingâŠ
I am using the InfluxDB-persistence addon from openHAB to feed the data into a QuestDB (running in docker). QuestDB is able to digest the Influx Line Protocol, so it works as a âdrop-inâ-replacement for an InfluxDB instance.
Initial tests have worked, so this may be a path to reach a stable path to persistenceâŠ
My setup of the whole system is mainly according to the documentations of the various components. It runs on a Raspberry Pi4 and uses docker with the official images for OpenAB, QuestDB and Grafana.
OpenHAB in a docker container, with
Influx Persistence addon, configured to use port 9000 for connection to the DB and using the IP-Address of the host system (!) (for consistence, see belowâŠ).
QuestDB in another docker container, with the standard port (9000) for Web-(Admin)-access and port 8812 exposed (this is the standard port for the PostgreSQL wire protocol).
Grafana also in another container with port 3000 exposed (for Web-access) and
QuestDB datasource addon configured to use port 8812 (PG wire prot.) for connection to QuestDB, and the IP address of the host system for the server address. Do not use âlocalhostâ or â127.0.0.1â, because this will not work between the docker instances!
Of course you have to select all the data which should be used for graphic charts in OpenHAB, best define and use a group for the respected items⊠But this should be done in any persistence addons. And define a usefull strategy, âeveryChangeâ may be just too often. For most items I use a strategy of âonce per minuteâ or less often.
So this seems to be a good path to get rid of the InfluxDB with minimal effortsâŠ
Hope this helps⊠and no, we do not need a special âQuestDBâ persistence addon