Claude Opus 5 explains this procedure, but I’d like someone to verify it.
With HA stopped this is straightforward, but there are three traps: state_attributes rows are shared and orphan themselves silently, states.old_state_id self-references, and the statistics tables use a different metadata table than states. Here’s the full sequence.
0. Before you touch anything
ha core stop # or: docker stop homeassistant / systemctl stop ...
cp home-assistant_v2.db{,-wal,-shm} /backup/ # -wal/-shm may not exist; that's fine
sqlite3 home-assistant_v2.db
Make sure HA is genuinely stopped, not just unresponsive — a live writer plus a CLI session is how you get a corrupt file. Then confirm the schema matches what’s below, since HA does change it:
.schema states
.schema statistics_meta
1. Pick your targets
Build a scratch table so every later statement refers to the same set:
CREATE TEMP TABLE victims AS
SELECT metadata_id, entity_id FROM states_meta
WHERE entity_id IN ('sensor.foo', 'sensor.bar')
OR entity_id GLOB 'sensor.p1_*';
SELECT * FROM victims;
Read that output carefully — a sloppy GLOB is the main way this goes wrong. Then see what you’re about to remove:
SELECT v.entity_id, COUNT(*) AS rows
FROM states s JOIN victims v ON s.metadata_id = v.metadata_id
GROUP BY v.entity_id ORDER BY rows DESC;
2. Clear dangling self-references
PRAGMA foreign_keys = OFF;
UPDATE states SET old_state_id = NULL
WHERE old_state_id IN (
SELECT state_id FROM states WHERE metadata_id IN (SELECT metadata_id FROM victims)
);
If you’re deleting an entity’s entire history this is a no-op, because the chain only ever links rows of the same entity. Run it anyway — it’s cheap insurance, and mandatory if you ever add a last_updated_ts bound to the delete.
3. Delete the states, in batches
One DELETE of twenty million rows builds a rollback journal roughly the size of the data. Chunk it:
DELETE FROM states WHERE state_id IN (
SELECT state_id FROM states
WHERE metadata_id IN (SELECT metadata_id FROM victims)
LIMIT 200000
);
Re-run until it reports 0 changes. From the shell you can loop it:
while [ "$(sqlite3 home-assistant_v2.db "SELECT changes();" ...)" ]; do ...; done
but honestly just pressing up-arrow-enter a few dozen times in the CLI is fine.
4. Reap the orphaned attributes
This is where most of your 10 GB actually lives, and nothing deletes it automatically:
DELETE FROM state_attributes
WHERE NOT EXISTS (
SELECT 1 FROM states WHERE states.attributes_id = state_attributes.attributes_id
);
Without an index on states.attributes_id this degenerates into a full scan per row and will run for hours. Check and, if needed, add a throwaway index:
PRAGMA index_list('states');
CREATE INDEX IF NOT EXISTS tmp_ix_attr ON states(attributes_id);
-- run the DELETE above --
DROP INDEX tmp_ix_attr;
5. Long-term statistics
Separate tables, separate metadata table, and note that statistics.metadata_id points at statistics_meta.id — not at states_meta:
CREATE TEMP TABLE stat_victims AS
SELECT id FROM statistics_meta
WHERE statistic_id IN (SELECT entity_id FROM victims);
DELETE FROM statistics WHERE metadata_id IN (SELECT id FROM stat_victims);
DELETE FROM statistics_short_term WHERE metadata_id IN (SELECT id FROM stat_victims);
DELETE FROM statistics_meta WHERE id IN (SELECT id FROM stat_victims);
Only do this if you actually want the history gone. Statistics are compact (one row per hour and per 5 minutes) and they’re what feeds the Energy dashboard and long-range history graphs — for power and energy sensors you almost always want to keep them while dropping the raw states.
6. Metadata rows
DELETE FROM states_meta WHERE metadata_id IN (SELECT metadata_id FROM victims);
Safe: if the entity still exists in HA, recorder just allocates a fresh metadata_id on the next state change. Leave recorder_runs, schema_changes, migration_changes and statistics_runs alone.
7. Reclaim the space
PRAGMA integrity_check;
VACUUM;
VACUUM rewrites the whole file, so you need free disk space equal to the current size, and it will take a good while on 10 GB. Nothing shrinks before this step. Then restart HA and check the log for recorder errors on the first few state writes.
One thing worth weighing: recorder.purge_entities does steps 2–6 correctly while HA is running (keep_days: 0 on a list of entity_id or entity_globs), followed by recorder.purge with repack: true for the vacuum. It’s slower and it does not touch statistics, but it can’t leave you with an inconsistent database. The manual route is worth it mainly when you’re deleting most of a very large file and want it done in one pass — which, at 10 GB, you probably are.