since i haven’t found anything in the home assistant community about this / hardly found anything anywhere else and the help in this forum mainly consists of guesses anyway, here are the settings for a proper influxdb database setup to bring up the quality and lower database size.
All credits go to Janina Trischler from
she found the proper settings for influxdb to create a database, create a retention policy (so your raw data wont be stored forever and gets compressed properly at the right time) and, the most tricky for me, the right CONTINUOUS QUERY to not create one for every measurement (C°, %, V, …). I wrote it down for a new influxdb setup. She also has a part for already existing databases (to not loose existing data when messing with the auto-generated retention policy for home_assistant) in her post.
Just my settings, definitely not a guideline and should be changed for own needs:
Raw Data from home assistant stored 6 months(4380h0m0s). After that it gets compressed / shrunk / downsized to 5 minutes, stored for 2 years (17520h0m0s). After that, 15 minutes for 5 years (43800h0m0s).
CREATE DATABASE "home_assistant" WITH DURATION 4380h0m0s REPLICATION 1 SHARD DURATION 168h NAME "6M_raw"
Create Retention Policy:
CREATE RETENTION POLICY "2y_5m" ON "home_assistant" DURATION 17520h0m0s REPLICATION 1 CREATE RETENTION POLICY "5y_15m" ON "home_assistant" DURATION 43800h0m0s REPLICATION 1
SHOW RETENTION POLICIES
Create Continuous Querys for the last two retention policys:
CREATE CONTINUOUS QUERY "6M_raw-2y_5m" ON "home_assistant" BEGIN SELECT mean(value) AS value INTO "home_assistant"."2y_5m".:MEASUREMENT FROM "home_assistant"."6m_raw"./.*/ WHERE time < now() -4380h0m0s GROUP BY time(5m),* END CREATE CONTINUOUS QUERY "2y_5m-5y_15m" ON "home_assistant" BEGIN SELECT mean(value) AS value INTO "home_assistant"."5y_15m".:MEASUREMENT FROM "home_assistant"."2y_5m"./.*/ WHERE time < now() -17520h0m0s GROUP BY time(15m),* END
SHOW CONTINUOUS QUERIES
for completeness, my influxdb entry in the configuration.yaml
influxdb: tags_attributes: friendly_name exclude: domains: - group - history_graph - media_player - scan_store - zone - zwave - automation - camera - frl_store - light_store - script - timer - updater entities: - sensor.date - sensor.time