Access and Maintenance

How to Shrink the Home Assistant Database With the Recorder

Find the entities that fill your Home Assistant database, exclude the noisy ones, purge old history, and repack so the file shrinks. Back up first.

On this page
  1. Quick answer
  2. Check the database size and location
  3. Find the entities that write the most data
  4. Exclude noisy entities
  5. Purge old history
  6. Repack so the file shrinks
  7. Keep history for less time
  8. Reduce updates at the source
  9. Other database engines are not a shrink button
  10. What you keep and what you lose

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_days sets how many days of history to keep. The default is 10.
  • auto_purge runs the purge every night. By default this happens at about 4:12 AM local time.
  • auto_repack repacks 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.

The complete guide to access and maintenance

How to Access Home Assistant: URLs, Default Login, Tokens and SSO
Read the guide

Keep exploring

Guides from other topics

All topics →