I would like further detail about this also. I suspect that SQLite wasn't choking and it was something else. I cannot imagine a single home being able to overwhelm SQLite [0]. Unless the OP has a couple thousand devices all looking to write to it at the same time and they cannot queue and take turns.
Came here to question this too. I have trouble with this claim because I do not see how a bunch of sensors could produce so much data as to overwhelm a Raspberry Pi...
Maybe the problem was having the database on a super-slow micro-SD card?
Yeah the microSD will die eventually even if it’s a high endurance one - but it’s fine for a small installation, I’ve been running a quality one until I really started building out the sensor network. With just switches it was fine. Now running on a mid tier usb3 ssd.
Low power sensors are only being polled every few minutes anyway. I have a dozen temp/humidity sensors and several dozen lights etc and my HA on a Pi 4 has no lag issues from it.
Author here. The problem with SQLite is when you start having a lot of sensors. That's when SQLite failed to keep up on a Pi. Mind you, this was back on Pi 3s. It might be better today, and I know there has been performance improvements to SQLite, but for me moving to Influx for time series and MySQL for the database solved things.
Also, even on the best SD cards, you will eventually break them if you write this much.
For this use-case, you can even likely implement own writer queue for every sensor type, and then batch insert them.