I wanted to ask if anyone knows a way to use Timescale DB as an alternative to Influx DB in Homeassistant for Grafana?
The reason why I would like to use Timescale DB is that Influx DB does not allow to rename tags afterwards (or at least this is only possible in a very cumbersome way). https://github.com/influxdata/influxdb/issues/4157
Since there have been many changes to the components in Homeassistant recently, the names of the entities have also been changed in some cases. Thus I have incomplete data in Influx DB and Grafana, which I can only “fill” again with several queries for the different names of the entities.
Since Grafana now supports native Timescale DB, I wanted to try it as an alternative
I new component would need to be written, much like the InfluxDB one. I was also considering looking at Timescale to replace my Influx configuration, just out of curiosity, using it together with Hasura. Gaven’t had the time to experiment with it yet though.
I’m actually shocked this hasn’t come up before and that a component for TimescaleDB wasn’t made before InfluxDB, considering the Home Assistant recorder component uses SQLAlchemy, which is obviously compatible with Postgres. Does anyone know if someone has plans to make the component?
TimescaleDB didn’t even exist when the InfluxDB component was created, so it’s not surprising at all.
I’m sure there are a lot more users of InfluxDB at this point, especially in the HA space, since it’s a more mature product.
Even though TimescaleDB speaks SQL, I would guess it’s not a direct drop-in replacement (hypertables and all that jazz) to use with SQLAlchemy, but I don’t really know much.
I got here from a Google search because I was thinking it might be interesting to do something similar.
Have you tried just connecting to a database with the recorder. It will treat it as a Postgresql server and should be able to read/write to it just fine.
You’d then need to enable the timescaledb extension, and create a hypertable from one of the existing tables that HA creates via recorder, presuming those tables have a field that could be used to partition on.
Today I took some time to install Timescale DB via Docker. After that I made a backup of my old Postgres database via “pg_dump --schema-only -f old_db.bak old_db”. Then I created the corresponding database in Timescale and restored the Postgres backup.
After that I wanted to create a hypertable using “SELECT create_hypertable('events', 'time_fired');”, which unfortunately didn’t work BTW, according to the Timescale data migration instructions, the table must be empty before the hypertables are created.
Replying from mobile here so I will keep it short. First great work.
You could add a unique constraint to the events table for event_id then you’ll be able to recreate the foreign key.
I unfortunately haven’t had time to even update my home assistant setup lately let alone play with Timescale so thanks for doing the experiment and sharing it with the community.
Thank you very much @freol!
I tried your custom component today and so far everything seems to work.
Maybe one more clue. For the initial setup of the postgis extension the postgres superuser is apparently needed. Otherwise you get the following error message:
Protokolldetails ( ERROR )
Mon Dec 23 2019 08:44:26 GMT+0100 (Mitteleuropäische Normalzeit)
Error during connection setup: (psycopg2.errors.InsufficientPrivilege) permission denied to create extension "postgis"
HINT: Must be superuser to create this extension.
[SQL: CREATE EXTENSION IF NOT EXISTS postgis CASCADE]
(Background on this error at: http://sqlalche.me/e/f405) (retrying in 3 seconds)
After that you can switch back to the actual homeassistant user for postgres
because I use Home Assistant Core as a docker container, I can’t test your addon. I already use Timescaledb as a standalone Docker Container.
In any case, thanks for your efforts! I think it can be very helpful for others.
Hey there,
I am intrigued by the proposed performance increase when compared to InfluxDB, but has anyone of you guys actually noticed a difference? I have used InfluxDB for over 4 years now and only when querying a lot of things at once over a multiple year time span is when I can actually notice the time it takes to load (in Grafana). My services run on a full blown PC though, not a tiny RPI, so that may help InfluxDB to still perform.
Did one of you migrate their existing InfluxDB data to TimescaleDB?
Did you notice performance improvements? CPU, Memory and Latency?
The query performance in Grafana is felt to be slightly worse compared with Influx.
Unfortunately I can’t say anything about the other things, because I neither migrated my data nor do I have a special look on the system performance, because I also have a real computer with enough resources.
I use the TimescaleDB mainly because of the DB functions it offers me.
Thank you.
I already read this comparison, but its written by the creators of TimescaleDB, which is why I do not trust their findings that much. While it may be true that TimescaleDB is more suited for extremely big pools of data, I am not sure InfluxDB is the worse choice for a relatively small dataset produced by something like Home Assistant. They also ramble quite a lot about the fact that TimescaleDB is way more stable because it relies on the PostgresQL foundation while Influx had to rewrite everything from scratch, and while that is certainly not irrelevant, I have never ever experienced anything even remotely bad while using InfluxDB. It just worked flawlessly, all of the time, and I don’t even run it on linux but FreeBSD.
So, all in all, I am not sure if the simple fact that TimescaleDB is a SQL based DB is enough for me to justify a DB switch. If someone comes across an in-depth and more importantly independent performance comparison of both, please share it. I have searched quite a bit but have not been able to find something of relevance.
Why don’t you just do a comparison test yourself and share your findings with us?
If you have a computer with enough system resources, it should be no problem for you to run both databases in parallel.
If you do not benefit from the full SQL compatibility of Timescale and have been fully satisfied with Influx so far, there is probably no reason for you to change.
BTW, according to the 
