Every state change in Home Assistant ends up in a database. A light turning on, a temperature reading, a phone's battery dropping by one percent, an ESP board reporting its Wi-Fi signal every few seconds. The recorder integration writes all of it, and with the defaults that is fine for a small setup. Add a few energy meters, a dozen ESPHome devices and some chatty cloud integrations, though, and the file grows into gigabytes, backups get slow, and a Raspberry Pi with an SD card starts writing much more than it should.

The fix is rarely "switch to MariaDB". In most homes a handful of entities produce most of the rows. Find them, stop recording what nobody looks at, and leave the rest of the defaults alone. Here is how to do that without losing the history you care about.

What the recorder keeps and for how long

The recorder is on by default. History depends on it, so you do not add it yourself. Unless you change it, it writes to a SQLite file at /config/home-assistant_v2.db.

There are two kinds of data in that file, and they age differently:

  • States and events. Every state update gets its own row, and that includes updates where only an attribute changed, such as the song on a media player or the colour of a light. These rows are deleted after purge_keep_days, which defaults to 10.
  • Statistics. For sensors with a state class (measurement, total, total_increasing), Home Assistant compiles 5-minute short-term statistics and hourly long-term statistics. The short-term ones are purged together with the states. The hourly ones are never purged. That is what feeds the energy dashboard and the statistics graph card, and why you can still see last year's temperature curve after the 10 days of raw states are gone.

So if you want a year of temperature or energy history, you do not need purge_keep_days: 365. Hourly statistics already keep it, at 24 rows per sensor per day. Raising the purge window mostly keeps millions of raw state rows that you will never scroll through.

Housekeeping that already runs on its own

Two scheduled jobs come with the defaults:

  • auto_purge: true deletes states and events older than purge_keep_days every night at 04:12 local time.
  • auto_repack: true rewrites the database on the second Sunday of each month, after the purge.

The difference matters. A purge deletes rows, but the file does not shrink straight away. It just stops growing as fast. Only a repack gives the space back to the disk. If you deleted a lot of data and want the space now, see the manual actions further down.

Check how big the database is

Go to Settings > System > Repairs, open the three dots menu, choose System information and find Estimated Database Size (MiB) in the Recorder section. Write the number down so you can compare it later.

Then check the free disk space. The recorder docs are firm about this: always keep at least as much free space as the database takes. If the database is 1.5 GiB, you need another 1.5 GiB free, because rebuilds and repacks copy data, especially during Home Assistant upgrades that change the schema. If the SQLite file gets corrupted, Home Assistant moves it aside and starts a new one, and for that the docs call 2.5 times the database size free essential. A full disk during an upgrade is one of the ways people lose their history.

Find the entities that write the most

Guessing is a poor way to choose exclusions. Look at the data instead. The SQLite Web app from the Home Assistant Community Apps repository opens the default database in a browser. Install it under Settings > Apps, start it and open its web UI.

The states table does not store entity IDs directly. They live in states_meta, so you need a join. This query counts rows per entity:

SELECT states_meta.entity_id, COUNT(*) AS row_count
FROM states
JOIN states_meta ON states.metadata_id = states_meta.metadata_id
GROUP BY states_meta.entity_id
ORDER BY row_count DESC
LIMIT 25;

Treat the database as read-only. The Home Assistant data docs warn that writing to it while Home Assistant runs can corrupt your history. Run SELECT queries only.

Entities that often end up near the top:

  • power and current sensors from smart plugs or energy monitors that report every few seconds;
  • Wi-Fi signal, uptime and IP address sensors that ESPHome or other firmware add to every device;
  • phone sensors from the companion app (battery, steps, connection type);
  • weather and sun entities with many attributes that change often.

If a single entity has ten times more rows than the next one, start there.

Stop recording what you never look at

Exclusions go in configuration.yaml. There is no UI for them, so edit the file and restart Home Assistant afterwards. A starting point:

recorder:
  commit_interval: 30
  exclude:
    domains:
      - update
    entity_globs:
      - sensor.*_wifi_signal
      - sensor.*_uptime
    entities:
      - sensor.last_boot

What each part does:

  • domains skips a whole domain. Here update entities, whose history you rarely need.
  • entity_globs matches patterns, where * stands for any text and ? for a single character. With consistent names across ESPHome devices, one line covers all of them.
  • entities lists specific entity IDs. sensor.last_boot is the example the recorder docs themselves give of an often-logged entity.
  • event_types, not used here, skips events by type, for example a custom event that an automation fires all the time. It does not affect state history.

Adjust the names to your own install: the patterns above only work if your entities really are called that.

There is also include. Be careful with it. Once you set any include filter, everything not listed stops being recorded. That suits a deliberately minimal setup, but it is easy to forget a new device and wonder later why it has no history. For most homes, exclude alone is the safer choice.

Two other things to know:

  • Hiding an entity does not stop it from being recorded. Hidden entities still end up in the database.
  • The Activity integration's own filter is not the same thing. It only hides entries from the activity panel. Recorder exclusions keep the data out of the database altogether.

Don't exclude what the energy dashboard needs

Long-term statistics are compiled from recorded states. If you exclude a power or energy sensor, it gets no new statistics, and the energy dashboard or a statistics graph will stop showing it. So for a noisy energy sensor, it is usually better to make it report less often than to exclude it.

With ESPHome you can do that on the device:

sensor:
  - platform: hlw8012
    # pins and other options for your device
    power:
      name: "Power"
      filters:
        - throttle_average: 60s

throttle_average: 60s sends one averaged value per minute instead of every reading, as long as at least one reading arrived in that minute. You keep a meaningful curve and send far fewer rows to the database.

Also look at force_update on ESPHome sensors. When it is true, Home Assistant creates a state change on every update, even if the value stayed the same. The ESPHome docs warn this can significantly increase database size. Leave it false unless a tool such as Grafana really needs it.

Slow down writes on an SD card

commit_interval sets how often the recorder writes batched changes to disk. The default is 5 seconds. A higher value means fewer writes, which the recorder docs specifically recommend on a Raspberry Pi running from an SD card, giving 30 seconds as an example. The database lags a little behind, but History and Activity still show current data. Set it to 0 only if you need every change committed as soon as possible.

If you have a long-running Pi with an SD card, this is the one setting worth changing even when the database size looks fine.

Clean up what is already there

Exclusions only apply from the restart onward. Rows already in the database stay until they age out. To remove them sooner, use the recorder actions in Developer tools > Actions.

To delete data for specific noisy entities:

action: recorder.purge_entities
data:
  keep_days: 0
  entity_globs:
    - sensor.*_wifi_signal
    - sensor.*_uptime

keep_days defaults to 0, which removes all matching data. Set a higher number to keep recent history. Write entity_globs as a YAML list. There is a known issue where several globs entered in the visual editor end up as one space-separated string, the action reports no error and nothing gets purged.

To apply your new exclude filters to old data and shrink the file in one go:

action: recorder.purge
data:
  apply_filter: true
  repack: true

repack rewrites the whole database on SQLite and PostgreSQL. It is a heavy operation: expect slowdowns and extra disk use while it runs. Run it when the disk has free space at least equal to the database size, and not just before you need Home Assistant to respond quickly.

If a single entity is a constant problem, you can schedule recorder.purge_entities in an automation, for example every night with keep_days: 5. The Home Assistant docs use exactly that pattern for a noisy power sensor.

Do you need MariaDB or PostgreSQL?

Usually not. The recorder docs say SQLite is the default, recommended and most tested option. MariaDB (10.3 or later), MySQL (8.0 or later) and PostgreSQL (12 or later) are supported, but changing the database can lose history and migration between engines is not supported. For MariaDB, the docs also note a performance regression in versions older than 10.5.17, 10.6.9, 10.7.5 and 10.8.4.

A separate database server makes sense when you already run one and know how to back it up, or when you want to query the data from other tools. It does not fix a database that is large because a few sensors write too much. Exclusions and filters fix that.

Common mistakes

  • Raising purge_keep_days to keep a year of graphs. Long-term statistics already do that, much more cheaply.
  • Excluding an energy sensor and losing it from the energy dashboard. Throttle it on the device instead.
  • Adding one include entry and losing history for everything else.
  • Expecting a smaller file right after a purge. Only a repack gives the space back.
  • Running out of disk during an upgrade. Keep free space at least equal to the database size.
  • Editing the database directly while Home Assistant runs. Read only, or use the recorder actions.
  • Assuming hiding an entity stops recording. It does not. Use exclude.