That would not be good, I agree 
So purging is not based on time stored in each table, but only the time stored in events table? Because states table also has time information, if I remember correctly.
Correct
It does have it, and the time values are the same for last_updated and time_fired for data that was recorded in the last few months (some users will still have ones that are not in sync on older data). They didn’t use to be the same but that was done to prepare to be able to optimize it in the future, we just aren’t ready to do that yet.
Interesting, thank you very much.
Self-sufficient tables were of course then a great improvement.
When was this introduced?
And it would “only” affect data that was stored ages ago as new data purges could be based on the times per table?
So no infinite growth. Only data before this change would be permanently stored.
Might it be an option to perform an automatic transfer of the time information from events to state for those old setups?
Since the links are available, an automatic update of the states table can be performed and afterwards the link can be broken. Then even old data would be complete and each table would be self-sufficient.
From memory it would be a join/merge of the two tables in a new one, then delete and rename (less direct approach to prevent data loss).
At this point we are no longer discussing the original topic and have wandered into feature request territory.
I suggest waiting for future optimizations to come down the pipe as what you are discussing here will likely mean adding more features that we would have to account for which would delay work on future optimizations.
1 Like
Okay, looking forward to future improvements then 
Fingers crossed you will find an easy solution. Should significantly reduce write cycles, judging by my db
I’m looking forward to all the changes! It’s great to see the Recorder database getting some developer love!
Speaking of FRs, there have been a number of them requesting variations on the theme of making it possible to retain some events and state changes longer than others, rather than have just one retention period for all (or exclude the entity altogether.) Here’s the one I submitted, but it’s a subject which comes up a lot, with a number of (good) ideas on how to implement it.
Is something along these lines in consideration?
Thank you!!
Don’t take it personally mate, most of the mods here seem to say that any time you suggest any feedback at all no matter how much you kiss up to them. I just praised the 2022.5 changes on the discord and mentioned one thing I considered unnecessary then got called “massively negative”.
I think you’ve got some very good points personally and I have noticed the sqlite database starting to grow out of control lately. @bdraco thanks for working on it! Much appreciated.
3 Likes
Wow, I had to look this up on discord because I was curious who you talked to and what you said. Do you think this is the best way to start a convo as your first post on discord?
TBH, it seems like you just said that to pick a fight. What do you expect?
1 Like
We can talk about this in PM if you like, don’t want to derail the thread.
There’s nothing to talk about, I’m just baffled that you think this is positive feedback. Nothing you said in discord was positive, you just started with a sarcastic assumption. Making your previous post a lie just to make the mods look bad. I just don’t get it. Why lie?
Why not share the whole conversation rather than one message taken out of context, and intentionally leaving out the bit where I said I liked everything else in 2022.5?
Anyway, I shall endeavour to not feed the troll, despite the fact it’s a mod. Won’t say anything else about it.
Sure, i’ll glady highlight everything too. Lets see:
You start with a scarastic assumption.
Call beginners plebs…
Have 1 small compliment.
Continue to bash the work…
Yes, The mods here were totally uncalled for asking you to be nicer.
1 Like
Hi, I don’t want to take sides, but the last 6 messages (7, including this one) are completely off-topic for this thread (which is already getting quite long). For the sake of keeping things on-topic, maintaining a good sinal-to-noise ratio, and making it easier to read for people in the future, I suggest moving these 7 messages (this one included) to another topic somewhere else. Thanks!
9 Likes
Wow! It’s been a long time since I looked forward to a version update. You’re restoring my faith!
1 Like
I’ve been using the following to keep my recorder information in memory to avoid excess writing to my SD card:
recorder:
purge_keep_days: 1
db_url: 'sqlite:///:memory:'
I upgraded to 2022.5.0 today and it looks like the in-memory option is not longer supported. Is there a alternate method to keep the recorder information in memory?
Unfortunately the in-memory sqlite does not have proper locking between threads which leads to random failures so its not longer available.
Although it is not technically supported, you can probably work around it by creating a ramdisk instead.
While I appreciate that the Home Assistant team is (finally) giving some attention to the excessive writing it used to do, I do feel like disabling in-memory sqlite is kind of abrupt.
Maybe it isn’t the best solution, but we are not given an alternative other than letting HA take control over the lifespan of my SSD (or SD card) again. (I’ve sacrificed 2 SD cards and 1 SSD in the past and I didn’t even want all that data)
2 Likes
Hi All, I also ran into this with the recent 2022.5.0 update and after playing around a bit trying to mount a ramdisk, I ended up just storing the database in /dev/shm which seems to be working.
Not sure how it’ll work over time but if you were using 'sqlite:///:memory' the following recorder config seems to get it back into memory on the 2022.5.0 release:
recorder:
db_url: 'sqlite:////dev/shm/ha-recorder-v2.db'
[ other options ]
I’m new to Home Assistant (fantastic software) so not sure how this will work over time. My setup (PI4/4GB/SSD dedicated to HA) has the data going to the InfluxDB add-on for longer term storage.
Hope that helps a little!
5 Likes