Switch to a real DB

[5HT2]

I'm having way too many issues with redis persistence, and honestly, a beats key in a redisjson database with 265020 values (it is literally a json array with 265,020 values in the root key) is not the best way to go about this. This is exactly the sort of thing SQL is for and I wish I knew that when I decided to write this project 2 years ago, so it's time to fix it (after Christmas, ideally).

[lmaotrigine]

on a slightly related note, is there anything blocking automated clean-up of old beats values? as far as i can see, it is only used to display counts on the website. would keeping counts alone suffice? do you foresee any other data analysis use cases that would require having timestamps of every single ping?

this is going to limit growth on any realistic infrastructure, regardless of storage backend.

[5HT2]

Uh, I do need to keep every single ping for both historical data and to correct bugs / issues caused by Docker cold boots causing data loss and so on.

I also currently use this to keep track of sleep patterns and have fairly accurate hours/minutes for when I am asleep.

As it stands, retaining this data is quite important to me and I have about 5 years of sleep/awake cycles tracked using it.

---

Do you feel like keeping this data in a SQL-like database and keeping the counters in something lighter, or maybe in-memory with a write cache + save to DB on exit (&on cache flush) would be a better approach?

[lmaotrigine]

okay, those are both fair use cases. it's not the I/O that's the problem, so either one works. writing directly to the database is reasonably fast, and it wouldn't be a bottleneck with ~1 insert per second.

the issue is space. i feel like there should be a way to denote "this device has been active for 6 hours" that doesn't require 21,600 rows, while still providing the same insights.