u/das_Keks

I'm running HomeAssistant via docker compose on quite old hardware (AMD Athlon II X2 240e dual core CPU, 20GB DDR3 RAM, rougly 16 years old), which is generally working pretty well.

I'm also creating nightly homeassistant db backups via SQLite VACUUM INTO, which creates a compacted backup of the db.

To not stress the server SSD too much with constant writes of all the sensor data, my HomeAssistant db is located on a HDD. I noticed that over time, creating the compacted copy of my around 4GB large HomeAssistant database got slower and slower and lately already took around one hour.
This makes sense because the db file is living on a HDD, is spread across many disk blocks and over time gets fragmented a lot because new sensor data is appended all the time, while old sensor data is pruged or stored in the statistics table in a lower resolution.

So I wanted to also compact the live db, which in the end was a huge win!
For this is stopped HomeAssistant, compacted the db with VACUUM INTO and replaced the original db with the compacted version.
After that the nightly backup (which is done without stopping HA) went down from one hour to just one minute. Also, looking up history data in HomeAssistant became a lot faster since now the data is defragmented and the HDD head doesn't have to jump around to read consecutive sensor values.

Since I didn't want to shut down HomeAssistant for too long for the live db compaction I first copied the db file to my servers /tmp dir, which lives in-memory. This made the random access during VACUUM significantly faster.

One important takeaway that might also be relevant for others who run a large HA instance on slow hardware / HDD: The first compaction attempt corrupted my db because the default docker stop grace period is only 10 seconds, which wasn't enough to gracefully shut down HA and write all changes from the write-ahead-log (wal file) to the SQLite DB. So I added stop_grace_period: 1m to my docker-compose file, which gives HA enough time to gracefully stop.

Another note on the compaction:
Generally the HomeAssistant recorder integration has auto_repack which is enabled by default and repacks the db every second Sunday. However, it seems like there is no way to configure it to run more often or trigger it manually. Also, looking on my db backup logs, there wasn't a significant speedup after every 2nd Sunday.

Here's my compact_db.sh if anyone is interested in doing something similar.

#!/bin/sh

set -e

cd "$(dirname "$0")"

echo "Stopping HomeAssistant"
docker compose -f /opt/homeassistant/docker-compose.yml stop

echo "Copying home-assistant_v2.db to /tmp (in-memory) to speed up random reads during VACUUM ..."
cp home-assistant_v2.db /tmp/home-assistant_v2.db

echo "Compacting DB ..."
sqlite3 /tmp/home-assistant_v2.db "VACUUM main INTO './ha_tmp.db'"

echo "Removing /tmp/home-assistant_v2.db ..."
rm -v /tmp/home-assistant_v2.db*

echo "Moving original db ..."
mv home-assistant_v2.db home-assistant_v2_compact_backup.db

echo "Placing compacted db ..."
mv ha_tmp.db home-assistant_v2.db

echo "Starting HomeAssistant"
docker compose -f /opt/homeassistant/docker-compose.yml start

echo "Done!"