Hello, I currently use PostgreSQL running on a separate server as my database. I had a nasty power outage and that server is DOA.
I set this up with a remote database years ago, but I think it would be better to move my database and MQTT server all onto the i3 NUC I’m running HAOS on. The NUC processor runs at less than 20%.
I just need about 60 days of history. I use history stats to record, daily, weekly, monthly and yearly stats for a dashboard. Beyond that I do the occasional history graph for individual entities.
What would be the best database option that I could implement on my HAOS device? I have a pretty big system so my gut feeling is SQLite is too small. I have excluded a lot of entities from the recorder using yaml.
Imho go back to native and extend the days to keep data so your live goes as long as you want then let lt stats take over unless you have a specific reason not to.
Your gut is not data and analysis.
Note recent issues with 2024.7-9 have primarily been with folks running external databases and keeping abnormal amounts of data as compared to typical. Yet I have also.not seen a single report of a person with a large builtin sqllite DB blowing up.
If we were really talking about how to do it right we’d be splitting the data into irs own time series db and reserving sqllite for config and other tasks but that’s not how ha is currently built and with the recent boosts to perform on sqllite hosting the DB I simply don’t see a reason to do otherwise unless again you have a very specific reason.
You do you if course but even at twice the typical live data storage (60 instead of 30) I’m using builtin and just making sure my disk can handle it. Your machine is barely idling.
Well… if you mean recently, then ok… But overall, if no-one else i’ve had my share of built-in sqlite crashes. And last problems were not so long ago (last crash was last year, i believe). These crashes appeared randomly, when no power outage was present, no other errors… just crash and new db created. And of course all history gone.
Now i rather stick with mariadb. I’ve had it on my synology before, now i have HA addon to keep in on one machine, and i also send my data to influxdb on my synology as a backup. I tried and went back to sqlite a few times, each time hoping that problems were resolved only to learn that i lost all my data again…
Yes, i did have problems recently, when all with external db had problems - my first problem with mariadb. But at least i didn’t loose my history (like i did when i’ve had sqlite). Mariadb is way more forgiving than sqlite.