home assistant takes a shit ton of time to load and the obvious suspect was sqlite. the check confirmed it hard. the database sat on an old 2.5 inch spinning drive, had grown to 907 megabytes with 4.09 million rows, and a simple 24 hour range scan took between 4.7 and 22.5 seconds. the thing had been writing history for entities that churn constantly, ble beacons, radar sensors, carbon readings, and it was drowning in its own log.
the fix had three parts. first, a new recorder config moved the database onto the nvme drive with faster commits and excludes for all the churn entities. second, the history of those entities got purged properly, which dropped the database from 4.09 million rows to 384 thousand, and then a repack took the file from 907 megabytes to 164. the long term statistics, the real data like weight and sleep and steps, stayed intact at over half a million rows. that is why the file is bigger than the ideal estimate, it is mostly real history now instead of noise.
the log spam was a separate discovery. the fire tv playback template was generating warnings every few seconds because the backend returns playing false when the tv service is down. a couple of default guards fixed it and 232 thousand warnings just stopped. write rate to the database dropped from a few rows per second to a couple per minute.
the same 24 hour scan now answers in zero seconds and home assistant was down for about two minutes total during the migration. there is a frozen rollback copy left behind to delete once everything still feels good, and the host itself is still memory squeezed, which is the next bottleneck when things get slow again.