Quick answer
Find the entities that write the most rows, exclude the ones you do not need history for, purge old history, then repack the database so the file shrinks. Back up first. Stop Home Assistant before you touch the database file directly.
Check the database size and location
Home Assistant stores its history in a SQLite file called home-assistant_v2.db, in your configuration folder. Check its size with your file manager or the SSH app. Also check free disk space, because a repack needs room to work.
Find the entities that write the most data
Most of the growth usually comes from a handful of entities. Common culprits include:
- Power and energy sensors that update every few seconds
- Signal strength and uptime sensors
- Media players
- Weather entities
To see the top entities, open the database with the SQLite Web app or another SQLite browser, and run a query like this one. It counts rows per entity, with the busiest first. Check the column names against your version if the query fails.
SELECT sm.entity_id, COUNT(*) AS row_count
FROM states s
JOIN states_meta sm ON s.metadata_id = sm.metadata_id
GROUP BY sm.entity_id
ORDER BY row_count DESC
LIMIT 20;
The entities at the top of the list are your candidates for exclusion.
Exclude noisy entities
Exclude entities you do not need history for. Add a recorder: block with an exclude: section to configuration.yaml. You can exclude by domain, by exact entity, by name pattern (entity_globs), or by event type:
recorder:
exclude:
domains:
- sun
entity_globs:
- sensor.*_linkquality
entities:
- sensor.hacs
event_types:
- call_service
Recorder changes in YAML need a full restart. Excluded entities keep working in automations. They only lose their history and their Logbook entries.
Purge old history
A purge deletes rows older than the number of days you choose. Run it from Developer tools > Actions in YAML mode, or from an automation:
action: recorder.purge
data:
keep_days: 10
repack: true
apply_filter: true
apply_filter: true also removes stored rows for entities you have newly excluded. repack: true rebuilds the file, which is covered in the next section.
To purge only certain entities, use recorder.purge_entities. It accepts entity_id, domains or entity_globs, along with keep_days.
Repack so the file shrinks
A purge removes rows, but the database file does not get smaller until it is repacked. A repack rebuilds the file (this is the SQLite VACUUM operation). It needs free disk space while it runs, and that space can be close to the size of the database, sometimes more. Check your free space before you start.
If you ever edit or copy home-assistant_v2.db by hand, stop Home Assistant first.
Keep history for less time
purge_keep_dayssets how many days of history to keep. The default is 10.auto_purgeruns the purge every night. By default this happens at about 4:12 AM local time.auto_repackrepacks the file after the automatic purge. By default this runs monthly.
recorder:
purge_keep_days: 10
auto_purge: true
auto_repack: true
A shorter history window does not break the Energy dashboard. The dashboard uses long-term statistics, which are kept separately from the history that the purge removes.
Reduce updates at the source
Some devices and bridges publish many attributes, and each change gets written to the database. The Zigbee2MQTT app, for example, has a setting that stops it publishing legacy entity attributes. Check the documentation for the integration you use, because the option name and availability depend on its version.
If an entity changes often and you rarely look at its history, excluding it is usually a better choice than keeping it.
Other database engines are not a shrink button
Home Assistant can use MariaDB, through an app, or PostgreSQL, through a db_url setting. Switching engines does not remove old rows or stop a database from growing. Purging and excluding still do the work.
What you keep and what you lose
- Excluded entities lose their history and Logbook entries, but keep working in automations.
- Purged rows are gone. Long-term statistics, which feed the Energy dashboard and long-range graphs, are kept separately.
- Deleting the database file wipes all history and statistics. Treat it as a last resort, and only after a backup.
Before any purge, make a backup. See how to back up and restore Home Assistant. After changing the Recorder settings, restart Home Assistant as described in how to update and reboot Home Assistant safely.