Expaso
commented
3 years ago Hello MACscr!
Thank you for your suggestion!
Basic Setup
The TimescaleDB addon is basically a PostgreSql Database server with TimescaleDb extentions on top of it.
Depending on what you would like to achieve, you will indeed need extra components. It's a bit like mix and match clothes. Depending on how you want to look, you pick your shirt and shoes. This makes it a little difficult to write a tutorial about how you should dress yourself. Because it's for a good part personal taste.
What I can do is describe my own setup.
First, you need this addon. It will install a timescale-db enabled sql-database for you. Seconds, you need to feed this thing with data. Third, you need ways to retrieve that data and do cool stuff with it.
I imagine you already did step 1. And so, now you need some data flowing in..
What I did was: 1- Instruct the recorder to send all it's data to Timescale, so we gain performance in parts of home-assistant like the history browser, and other chars. For this, you simple need to point your recorder-connection-string to timescale-db. This is done by simply using this connection-string for the recorder: postgresql://homeassistant:yourpasswordhere@77b2833f-timescaledb/homeassistant
This will store all data of home-assistant into your timescaledb instance.
Homeassistant will use thse tables, but you can also query that data yourself.
2- Use LTSS for long-term storage of statistics data. For this, please see the documentation of LTSS. You can use the exact same connectionstring as in the previous step. This will feed data about sensor-changes into the LTSS table:
Now you have data, you can use it to create graphics. For example about the charging state of my car. I use Grafana for this.
My dashboard looks like this:
The query behind the the top-left chars for example:
Please use the documentation of Grafana about how to create charts, and you need some knowledge of SQL of course. Th extra column 'state_numeric' was a computed column created by me, to aid charts that work on numeric states. You can create this computed column using this script:
CREATE OR REPLACE FUNCTION public.isnumeric(
text)
RETURNS boolean
LANGUAGE 'plpgsql'
COST 100
IMMUTABLE STRICT PARALLEL UNSAFE
AS $BODY$
DECLARE x NUMERIC;
BEGIN
x = $1::NUMERIC;
RETURN TRUE;
EXCEPTION WHEN others THEN
RETURN FALSE;
END;
$BODY$;
ALTER FUNCTION public.isnumeric(text)
OWNER TO postgres;and then
ALTER TABLE public.ltss
ADD COLUMN state_numeric double precision GENERATED ALWAYS AS (
CASE
WHEN isnumeric((state)::text) THEN (state)::double precision
ELSE NULL::double precision
END) STORED;
I coupled Grafana to Timescale with the following config:
Please note that I have created a special grafana user here, for security reasons. You have to create users yourself using the PostgreSQL documentation.
This is only one of many usescases for timescaledb, but it's a real-world example. It should give you a starting point, from which you can dig deeper into your data and create nice dashoards or alerting.
Does this at least partially answer your question?
Reducing the size of the database
To save storage space, you basicially have 3 options:
1) Compress the data. You have just proven that it even can beat Influx.
Pros:
Very easy Instant space savings. Cons:
Compressed data cannot be updated. Normally not a problem (because Influx can not update at all!), but should you have the need, you must first decompress the chuck to update, and after update re-compress again. Little bit of overhead (depending on the type of query, some are faster!) when querying compressed data. 2) Remove data you don’t need (like wiping the attributes column to NULL)
Pros:
Space saving Easy to do Cons:
Should you need that data in the future, bad luck. 3) Downsample your data, using Continuous Aggregates 6
Pros:
Saves a LOT of space. Will speed op queries over large timespans tremendously. Cons:
No more access to all measurements, but only the chosen aggregates values, like min/max/avg/mean within the chosen timeframe (like 5 minute/hour/day/month/etc). You have to overthink your data-needs upfront, because you basically are replacing a large set of old data with 1 record containing aggregated values. There is no way you can get your original data back if you deleted it to save space. Now, you could even combine all 3 methods in a mix and match style if you like, depending on your hunger for data. Maybe start with compression first, and create some continues aggregates to speed up queries spanning thousands of record. That way, you have a nice balance between performance, and not losing any data.
With that in place, you could start with dropping old chunks of data to save space if needed. Clearing the attributes column is more difficult with compression in place, but also
The good news: lots of options, freedom everywhere :slight_smile:
MACscr
yoheines
bingo6
jli113
Davincible
edouardkleinhans
wuast94
luckylinux
42Network
Would really appreciate some additional documentation on actually integrating it with home assistant. At its default installation state, it simply installs the server. Would love additional information on getting it fully working with Home Assistant (I guess LTSS is required too?) and replacing recorder if possible (does it currently go to both)? Some examples of actually using that data within HA would be great too.
Please dontt take these as complaints as I do definitely appreciate what was done here. Just need a bit more information on how use this special integration after being sold on how good it was. =)